Timeout issue

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
 
A

a a r o n _ k e m p f

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax when setting ado rs or conn timeout (not adp) 2
not receiving records in ADP 2
nesting subqueries 2
Excel Providing user message when RED X clicked & properly closing application. 2
Field list is blank 7
Convert Count(Case()) SQL Server Query to Access 3
Excel Target.column for Data Validation would like to target some cells within column 1
Conversion from Number to Word!! Aki 1

Top