|
|
I have migrated my Access03 to ADP on SQLExpress2005. All is well when I run
my query "View_Salesman_ProductSalesCount" with just a few products selected
but when I select more than 30 products (319 of them) the message timeout
will be displayed. Is there a way to increase this timeout. My SQL statement
is mainly generated by the SSMSE, I am still very far away from creating SQL
of my own.
SQL in:View_Salesman_ProductSalesCount
SELECT [Sman Code], COUNT([1]) AS Countof1, COUNT([2]) AS Countof2,
COUNT([3]) AS Countof3, COUNT([4]) AS Countof4
FROM dbo.qxtbCountUsers
GROUP BY [Sman Code]
SQL in:dbo.qxtbCountUsers
SELECT [Sman Code], custcode, SUM(CASE dbo.qryBuyPre.fyr WHEN '1' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [1],
SUM(CASE dbo.qryBuyPre.fyr WHEN '2' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [2],
SUM(CASE dbo.qryBuyPre.fyr WHEN '3' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [3],
SUM(CASE dbo.qryBuyPre.fyr WHEN '4' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [4]
FROM dbo.qryBuyPre
GROUP BY [Sman Code], custcode
SQL in:dbo.qryBuyPre
SELECT custcode, [Sman Code], Buy, fyr
FROM dbo.qryTblOrders3
WHERE (Buy = 1)
SQL in:dbo.qryTblOrders3
SELECT RTRIM(dbo.Tbl_orders3.Quantity) + ':' +
RTRIM(dbo.Tbl_orders3.Bns) AS [Q + B],
(SELECT sumofsel
FROM dbo.qryFullYr) AS sumofsel,
dbo.Tbl_orders3.[Item No], dbo.Tbl_CustomerProfile.POSTCODE,
dbo.Tbl_CustomerProfile.TypeCde,
dbo.Tbl_CustomerProfile.CustName,
dbo.Tbl_orders3.Fyr1, dbo.Tbl_orders3.fyr, dbo.Tbl_orders3.Mth,
dbo.Tbl_CustomerProfile.custcode,
dbo.Tbl_orders3.[Sman Code], dbo.Tbl_orders3.Quantity,
dbo.Tbl_orders3.Bns, dbo.Tbl_orders3.Sales, dbo.tbl_Choice.ClsC,
dbo.tbl_Choice.ClsA,
dbo.tbl_Choice.Brd, dbo.tbl_Choice.Choice, CASE WHEN
[sales] > 0 THEN 1 WHEN [bns] > 0 THEN 1 ELSE 0 END AS Buy,
(SELECT COUNT(ItmSel) AS CountOfItmSel
FROM dbo.qryTLprd) AS itemcde, CASE WHEN
(SELECT COUNT(ItmSel) AS CountOfItmSel
FROM dbo.qryTLprd) > 1 THEN ' SKUs' ELSE
(SELECT qryTLprd.ItemDesc AS FirstOfItemDesc
FROM qryTLprd) END AS itemdes
FROM dbo.Tbl_orders3 INNER JOIN
dbo.Tbl_CustomerProfile ON dbo.Tbl_orders3.CustAutoNo
= dbo.Tbl_CustomerProfile.CustAutoNo CROSS JOIN
dbo.tbl_Choice
In the last query, ItmSel is actually a checkbox in Tbl_Product for
selecting the product to be analysed.
Any help would be appreciated
Thanks
|
|
well I'd probbaly try to rewrite this as some sort of PIVOT clause.
It should run about 10 times faster for you.
SQL in:dbo.qxtbCountUsers
SELECT [Sman Code], custcode, SUM(CASE dbo.qryBuyPre.fyr WHEN '1'
THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [1],
SUM(CASE dbo.qryBuyPre.fyr WHEN '2' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [2],
SUM(CASE dbo.qryBuyPre.fyr WHEN '3' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [3],
SUM(CASE dbo.qryBuyPre.fyr WHEN '4' THEN
dbo.qryBuyPre.buy ELSE NULL END) AS [4]
FROM dbo.qryBuyPre
GROUP BY [Sman Code], custcode
On Dec 15, 8:53 pm, ChoonBoy <Choon...[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] > I have migrated my Access03 to ADP on SQLExpress2005. All is well when I run
> my query "View_Salesman_ProductSalesCount" with just a few products selected
> but when I select more than 30 products (319 of them) the message timeout
> will be displayed. Is there a way to increase this timeout. My SQL statement
> is mainly generated by the SSMSE, I am still very far away from creating SQL
> of my own.
>
> SQL in:View_Salesman_ProductSalesCount
> SELECT [Sman Code], COUNT([1]) AS Countof1, COUNT([2]) AS Countof2,
> COUNT([3]) AS Countof3, COUNT([4]) AS Countof4
> FROM dbo.qxtbCountUsers
> GROUP BY [Sman Code]
>
> SQL in:dbo.qxtbCountUsers
> SELECT [Sman Code], custcode, SUM(CASE dbo.qryBuyPre.fyr WHEN '1' THEN
> dbo.qryBuyPre.buy ELSE NULL END) AS [1],
> SUM(CASE dbo.qryBuyPre.fyr WHEN '2' THEN
> dbo.qryBuyPre.buy ELSE NULL END) AS [2],
> SUM(CASE dbo.qryBuyPre.fyr WHEN '3' THEN
> dbo.qryBuyPre.buy ELSE NULL END) AS [3],
> SUM(CASE dbo.qryBuyPre.fyr WHEN '4' THEN
> dbo.qryBuyPre.buy ELSE NULL END) AS [4]
> FROM dbo.qryBuyPre
> GROUP BY [Sman Code], custcode
>
> SQL in:dbo.qryBuyPre
> SELECT custcode, [Sman Code], Buy, fyr
> FROM dbo.qryTblOrders3
> WHERE (Buy = 1)
>
> SQL in:dbo.qryTblOrders3
> SELECT RTRIM(dbo.Tbl_orders3.Quantity) + ':' +
> RTRIM(dbo.Tbl_orders3.Bns) AS [Q + B],
> (SELECT sumofsel
> FROM dbo.qryFullYr) AS sumofsel,
> dbo.Tbl_orders3.[Item No], dbo.Tbl_CustomerProfile.POSTCODE,
> dbo.Tbl_CustomerProfile.TypeCde,
> dbo.Tbl_CustomerProfile.CustName,
> dbo.Tbl_orders3.Fyr1, dbo.Tbl_orders3.fyr, dbo.Tbl_orders3.Mth,
> dbo.Tbl_CustomerProfile.custcode,
> dbo.Tbl_orders3.[Sman Code], dbo.Tbl_orders3.Quantity,
> dbo.Tbl_orders3.Bns, dbo.Tbl_orders3.Sales, dbo.tbl_Choice.ClsC,
> dbo.tbl_Choice.ClsA,
> dbo.tbl_Choice.Brd, dbo.tbl_Choice.Choice, CASE WHEN
> [sales] > 0 THEN 1 WHEN [bns] > 0 THEN 1 ELSE 0 END AS Buy,
> (SELECT COUNT(ItmSel) AS CountOfItmSel
> FROM dbo.qryTLprd) AS itemcde, CASE WHEN
> (SELECT COUNT(ItmSel) AS CountOfItmSel
> FROM dbo.qryTLprd) > 1 THEN ' SKUs' ELSE
> (SELECT qryTLprd.ItemDesc AS FirstOfItemDesc
> FROM qryTLprd) END AS itemdes
> FROM dbo.Tbl_orders3 INNER JOIN
> dbo.Tbl_CustomerProfile ON dbo.Tbl_orders3.CustAutoNo
> = dbo.Tbl_CustomerProfile.CustAutoNo CROSS JOIN
> dbo.tbl_Choice
>
> In the last query, ItmSel is actually a checkbox in Tbl_Product for
> selecting the product to be analysed.
>
> Any help would be appreciated
>
> Thanks
|
|
|