Timeout issue

  • Thread starter Thread starter ChoonBoy
  • Start date Start date
C

ChoonBoy

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


I have migrated my Access03 to ADP on SQLExpress2005. All is well  whenI 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
Click to expand...
 
Back
Top