Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Timeout Expired Issue 1

Status
Not open for further replies.
Cpreston

Cpreston

MIS
Mar 4, 2015
972
GB
Hi

I have 2 views, one works well and the other I keep getting an Error Message Execution timeout expired. The timeout period expired prior to completion of the operation or the server is not responding message

The only difference between the 2 views is the Having clause.

The one that works is
HAVING pa.BranchID IN (1, 9) AND pg.Level1ID = 893 AND pr.ProductCode LIKE 'am%' AND (Pso.stocklevelmin > 0) AND (PSO.StockLevelMax > 0)

The one that timesout is
HAVING pa.BranchID IN (1, 9) AND pg.Level1ID <> 893 AND pr.ProductCode NOT LIKE 'am%' AND (Pso.stocklevelmin > 0) AND (PSO.StockLevelMax > 0)

If I run the one that does not work in a query window it runs but takes some time to do so, just over 3 minutes. As soon as I oput the code into a view I get the timeout problem. I cant find where in the view options to change the timeout, it is set to 0 in the Tools|Options|Query execution.

Any ideas how I can get sort this please.
 
Sort by date Sort by votes
Would you care to share the whole statement that times out?


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Here is the code, thanks

SQL: 
SELECT        pr.ProductCode, pr.Description, pso.StockLevelMin, pso.StockLevelMax, st.stockavailable, SUM(PA.QuantityUsed) AS QTYUsed, pr.udfTopTier AS TOPTIER, ws.ScheduleNumber AS SchedNum, 
                         m3wo.m3 AS M3_WO, pa.ProductID,
                             (SELECT        DATEDIFF(dd, bd.start_previous_month, lid.InvoiceDate) + 1 - DATEDIFF(wk, bd.start_previous_month, lid.InvoiceDate) * 2 - CASE WHEN DATENAME(dw, bd.start_previous_month) 
                                                         = 'Sunday' THEN 1 ELSE 0 END - CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END -
                                                             (SELECT        COUNT(*) AS Expr1
                                                               FROM            dbo.[148-Holiday] AS hol
                                                               WHERE        hol.HolidayDate >= bd.start_previous_month AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1) AS WorkingDaysL1, pa.BranchID
FROM            dbo.ProductAnalysis AS pa INNER JOIN
                         dbo. Product AS pr ON pa.ProductID = pr.ProductID INNER JOIN
                         dbo.ProductGroup AS pg ON pr.ProductGroupID = pg.ProductGroupID INNER JOIN
                         dbo.[148-vwQuantityUsedCheck_Usage_MDF3] AS qucu ON pa.ProductID = qucu.ProductID INNER JOIN
                         dbo.productStockOption AS pso ON pso.productid = pr.productid INNER JOIN
                         dbo.Stock AS st ON st.ProductID = PSO.ProductID AND st.BranchID = pa.BranchID LEFT OUTER JOIN
                         [148-vwOnWOSchedule] AS ws ON ws.ProductID = pa.ProductID LEFT OUTER JOIN
                         [148-vwOnWorksOrder] AS m3wo ON m3wo.ProductID = pa.ProductID CROSS JOIN
                         dbo.LastInvoiceDate AS lid OUTER apply
                             (SELECT        DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS start_previous_month) AS bd
WHERE        pa.CalendarYear * 10000 + pa.CalendarMonth * 100 BETWEEN datepart(year, DATEADD(month, - 2, GETDATE())) * 10000 + datepart(MONTH, DATEADD(month, - 2, GETDATE())) 
                         * 100 /* get previous month - result could be previous year*/ AND datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100
GROUP BY pa.BranchID, pa.ProductID, bd.start_previous_month, pg.Level1ID, lid.InvoiceDate, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END, pr.ProductCode, pr.Description, pso.StockLevelMin, 
                         pso.StockLevelMax, st.stockavailable, pr.udfTopTier, m3wo.m3, ws.ScheduleNumber
HAVING        pa.BranchID IN (1, 9) AND pg.Level1ID <> 893 AND pr.ProductCode NOT LIKE 'am%' AND (Pso.stocklevelmin > 0) AND (PSO.StockLevelMax > 0)
 
Upvote 0 Downvote
I may be wrong and somebody can (should) correct me, but I think HAVING part of your SELECT should refer to any aggregated field (SUM, MAX, MIN, AVG, etc.) Since fields in your HAVING part (pa.BranchID, pg.Level1ID, pr.ProductCode, PSO.StockLevelMax) are not part of any aggregated function, they should be part of the WHERE portion of your SELECT:

[pre]
WHERE ...
[blue]AND pa.BranchID IN (1, 9)
AND pg.Level1ID <> 893
AND pr.ProductCode NOT LIKE 'am%'
AND (Pso.stocklevelmin > 0)
AND (PSO.StockLevelMax > 0)[/blue]
GROUP BY ...[/pre]


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Generally speaking.... I only using a having clause if I want to filter on an aggregate. For example:

Having Sum(ColumnName) > 1000


If you want to filter data, you should put the conditions in the where clause. In your case, you are not filtering on any aggregates, so I suggest you move all of the conditions from your having clause to the where clause. This may improve performance, and is easy to do, so I recommend you try it.

If this doesn't work, there are other things you could do...

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Uh.... yeah... what Andy said. :)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks for the replys, I have my code as below now, runs fine in query, but run it was view and still get timeout problem.

SQL: 
SELECT        pr.ProductCode, pr.Description, pso.StockLevelMin, pso.StockLevelMax, st.stockavailable, SUM(PA.QuantityUsed) AS QTYUsed, pr.udfTopTier AS TOPTIER, ws.ScheduleNumber AS SchedNum, 
                         m3wo.m3 AS M3_WO, pa.ProductID,
                             (SELECT        DATEDIFF(dd, bd.start_previous_month, lid.InvoiceDate) + 1 - DATEDIFF(wk, bd.start_previous_month, lid.InvoiceDate) * 2 - CASE WHEN DATENAME(dw, bd.start_previous_month) 
                                                         = 'Sunday' THEN 1 ELSE 0 END - CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' THEN 1 ELSE 0 END -
                                                             (SELECT        COUNT(*) AS Expr1
                                                               FROM            dbo.[148-Holiday] AS hol
                                                               WHERE        hol.HolidayDate >= bd.start_previous_month AND hol.HolidayDate < lid.InvoiceDate) AS WorkingDaysL1) AS WorkingDaysL1, pa.BranchID
FROM            dbo.ProductAnalysis AS pa INNER JOIN
                         dbo. Product AS pr ON pa.ProductID = pr.ProductID INNER JOIN
                         dbo.ProductGroup AS pg ON pr.ProductGroupID = pg.ProductGroupID INNER JOIN
                         dbo.[148-vwQuantityUsedCheck_Usage_MDF3] AS qucu ON pa.ProductID = qucu.ProductID INNER JOIN
                         dbo.productStockOption AS pso ON pso.productid = pr.productid INNER JOIN
                         dbo.Stock AS st ON st.ProductID = PSO.ProductID AND st.BranchID = pa.BranchID LEFT OUTER JOIN
                         [148-vwOnWOSchedule] AS ws ON ws.ProductID = pa.ProductID LEFT OUTER JOIN
                         [148-vwOnWorksOrder] AS m3wo ON m3wo.ProductID = pa.ProductID CROSS JOIN
                         dbo.LastInvoiceDate AS lid OUTER apply
                             (SELECT        DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS start_previous_month) AS bd
WHERE        pa.CalendarYear * 10000 + pa.CalendarMonth * 100 BETWEEN datepart(year, DATEADD(month, - 2, GETDATE())) * 10000 + datepart(MONTH, DATEADD(month, - 2, GETDATE())) 
                         * 100 /* get previous month - result could be previous year*/ 
						 AND datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100
						 AND  pa.BranchID IN (1, 9) 
AND pg.Level1ID <> 893 
AND pr.ProductCode NOT LIKE 'am%' 
AND (Pso.stocklevelmin > 0) 
AND (PSO.StockLevelMax > 0) 
GROUP BY pa.BranchID, pa.ProductID, bd.start_previous_month, pg.Level1ID, lid.InvoiceDate, CASE WHEN qucu.[m3 Used] = 0 THEN 0.0001 ELSE qucu.[m3 Used] END, pr.ProductCode, pr.Description, pso.StockLevelMin, 
                         pso.StockLevelMax, st.stockavailable, pr.udfTopTier, m3wo.m3, ws.ScheduleNumber
 
Upvote 0 Downvote
Just out of curiosity, can you try running this?

Code: 
SELECT	pr.ProductCode, 
        pr.Description, 
	pso.StockLevelMin, 
	pso.StockLevelMax, 
	st.stockavailable, 
	PA.QTYUsed, 
	pr.udfTopTier AS TOPTIER, 
	ws.ScheduleNumber AS SchedNum, 
        m3wo.m3 AS M3_WO, 
	pa.ProductID,
        (
	  SELECT DATEDIFF(dd, bd.start_previous_month, lid.InvoiceDate) 
		 + 1 
		 - DATEDIFF(wk, bd.start_previous_month, lid.InvoiceDate) * 2 
		 - CASE WHEN DATENAME(dw, bd.start_previous_month) = 'Sunday' 
		        THEN 1 
			ELSE 0 
			END 
		 - CASE WHEN DATENAME(dw, lid.InvoiceDate) = 'Saturday' 
		        THEN 1 
			ELSE 0 
			END 
	         - (
			SELECT	COUNT(*) AS Expr1
                        FROM    dbo.[148-Holiday] AS hol
                        WHERE   hol.HolidayDate >= bd.start_previous_month 
				AND hol.HolidayDate < lid.InvoiceDate
		    ) AS WorkingDaysL1
		) AS WorkingDaysL1, 
       pa.BranchID
FROM   (
	 Select ProductID,
		BranchId,
		SUM(QuantityUsed) As QTYUsed
	 From	dbo.ProductAnalysis
	 Where	BranchID In (1,9)
	 	And pa.CalendarYear * 10000 + pa.CalendarMonth * 100 
		  BETWEEN datepart(year, DATEADD(month, - 2, GETDATE())) * 10000 + datepart(MONTH, DATEADD(month, - 2, GETDATE())) 
					* 100 /* get previous month - result could be previous year*/ 
	          AND datepart(year, getdate()) * 10000 + datepart(month, getdate()) * 100
	 Group By BranchId,
		  ProductId
	) AS pa INNER JOIN
        dbo. Product AS pr ON pa.ProductID = pr.ProductID INNER JOIN
        dbo.ProductGroup AS pg ON pr.ProductGroupID = pg.ProductGroupID INNER JOIN
        dbo.[148-vwQuantityUsedCheck_Usage_MDF3] AS qucu ON pa.ProductID = qucu.ProductID INNER JOIN
        dbo.productStockOption AS pso ON pso.productid = pr.productid INNER JOIN
        dbo.Stock AS st ON st.ProductID = PSO.ProductID AND st.BranchID = pa.BranchID LEFT OUTER JOIN
        [148-vwOnWOSchedule] AS ws ON ws.ProductID = pa.ProductID LEFT OUTER JOIN
        [148-vwOnWorksOrder] AS m3wo ON m3wo.ProductID = pa.ProductID CROSS JOIN
        dbo.LastInvoiceDate AS lid OUTER apply
            (SELECT        DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 2, 0) AS start_previous_month) AS bd
WHERE   AND pg.Level1ID <> 893 
		AND pr.ProductCode NOT LIKE 'am%' 
		AND (Pso.stocklevelmin > 0) 
		AND (PSO.StockLevelMax > 0)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
[PRE]...
WHERE AND pg.Level1ID <> 893
...[/PRE]


---- Andy

There is a great need for a sarcasm font.
 
Upvote 0 Downvote
Hi

Tried running your suggestion gmmastros and I took the AND out, I am getting the below error's

Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "pa.CalendarYear" could not be bound.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "pa.CalendarMonth" could not be bound.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "pa.CalendarYear" could not be bound.
Msg 4104, Level 16, State 1, Line 37
The multi-part identifier "pa.CalendarMonth" could not be bound.
 
Upvote 0 Downvote
HI

Got it work added the AS pa. Works also in View mode so should be good from here, thanks for all the reply's , always appreciated.

From dbo.ProductAnalysis as pa


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Lidias13
  • Locked
  • Question
Issue trying to find &quot;closest&quot; dated record to a rather complex query (not working)
Replies
1
Views
127
ggriffit
ggriffit
Ikatiemarie
  • Locked
  • Question
Sp_send_dbmail permissions issue
Replies
0
Views
140
Ikatiemarie
Ikatiemarie
Cpreston
  • Locked
  • Question
Join Issue 1
2
Replies
29
Views
393
Andrzejek
Andrzejek
ahmedsa2018
  • Locked
  • Question
Are this query below have problem on performance issue ?
Replies
1
Views
95
ahmedsa2018
ahmedsa2018
Cpreston
  • Locked
  • Question
Conversion issue in 2016 1
Replies
9
Views
153
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top