Fixing Query to Resolve Timout??

[sconti11]

I have the following sql query:

Declare @Month as varchar
Declare @Turf as varchar

Set @Month = '1'

SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM WRK_ORDER
WHERE (@Month <> NULL) AND (YEAR(dateCreated) = '2010') AND (franchiseID = 'C368610D4B6D0D78862574580049390F')
AND (@Turf IS NULL) OR
(YEAR(dateCreated) = '2010') AND (franchiseID = 'C368610D4B6D0D78862574580049390F')
AND (MONTH(dateCreated) = (SELECT MAX(MONTH(dateCreated)) AS mMonth
FROM WRK_ORDER AS WRK_ORDER_1
WHERE (franchiseID = 'C368610D4B6D0D78862574580049390F')
AND (YEAR(dateCreated) = '2011'))) AND (@Turf IS NULL) OR
(@Month <> NULL) AND (YEAR(dateCreated) = '2010') AND (franchiseID = 'C368610D4B6D0D78862574580049390F') AND (turfID = 'Phoenix')
GROUP BY YEAR(dateCreated), franchiseID

I am trying to account for a few different scenarios within the WHERE clause. But when I run this as is, it times out.

When I remove the last OR scenario, it works fine. Not sure why this is happening??

 

[sconti11]

So...as clarification of what is happening in the sample query...I have the parameter (@Month) that I am testing to see if it is either NULL or has a value. If it is NULL then I need to run the WHERE clause that includes the subquery, if there is a value in the @Month parameter, then I want it to filter the data on the value of that parameter.

I believe I have it written wrong, but I am not sure why???

Hopefully this helps you to help me

 

[gmmastros]

I think you broke one of [!]MY[/!] cardinal rules.

When you mix AND with OR in a where clause, you must use a lot of parenthesis.[/!]

Clear you are using a lot of parenthesis in the where clause, but I think you got them wrong.

Let's look at an example:

[tt]
Where ShoeSize = 9
And EyeColor = 'Blue'
Or HairColor = 'Brown'
[/tt]

What results would you expect from that query? It's actually hard to tell because there are no parenthesis. Your WHERE clause has extra parenthesis that are not needed, like (@Turf Is NULL).

Your where clause has 2 OR's in it. As a first step, I would suggest adding another set of parenthesis, something like this:

[tt]
Where [!]([/!] conditions [!])[/!]
OR [!]([/!] conditions [!])[/!]
OR [!]([/!] conditions [!])[/!]
[/tt]




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

Thanks for the reminder on the paranthases placement!

The problem seems to be that I am writing this sql within a SSRS dataset query, and it is automatically removing the paranthases from around the OR conditions??

Have you experienced that?

I went back and looked at other datasets that have similar conditions with OR and AND ....no issues, and there is no extra paranthases on them??

So i adjusted the sql below and added () around the OR conidtions...this query continues to execute until it times out???

Declare @Month as varchar
Declare @Turf as varchar

Set @Month = '1'

SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM WRK_ORDER
WHERE ((@Month <> '') and (YEAR(dateCreated) = '2010') AND (franchiseID = 'C368610D4B6D0D78862574580049390F')
and Month(datecreated) = @Month) or ((YEAR(dateCreated) = '2010') AND (franchiseID = 'C368610D4B6D0D78862574580049390F')
AND (MONTH(dateCreated) = (SELECT MAX(MONTH(dateCreated)) AS mMonth
FROM WRK_ORDER AS WRK_ORDER_1
WHERE (franchiseID = 'C368610D4B6D0D78862574580049390F')
AND (YEAR(dateCreated) = '2011'))) AND (@Turf IS NULL))
GROUP BY YEAR(dateCreated), franchiseID

I know that I am a newbie at some of this SQL, but I thought that if you use AND all conditions must be true in order for that WHERE clause to be used, otherwise if I use an OR statement with the AND then the next WHERE, as long as all conditions are true, would execute???

The above mentioned query, where I set the @Month parameter to '1', the where clause should then skip the statement WHERE @Month <> ''.....is this a correct assumption?

 

[gmmastros]

Well.... one of your problems is this:

[tt]WHERE (@Month <> NULL)[/blue]

You cannot use <> with null because it will always evaluate to false. Instead, you should use:

[tt]WHERE (@Month Is NOT NULL)[/tt]

Also... You should make sure that there is an index on this table with the first column of the index as franchiseID. This will speed up the query. Can you tell me how many rows are in the WRK_ORDER table, and a best guess on the number of unique franchiseID rows?

If you are not sure about the index, then run this (in a query window)

[tt]sp_helpindex 'WRK_ORDER'[/tt]

I don't use SSRS, so I cannot directly comment on that. Sorry.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

There are approximately 205K in the WRK_ORDER table with 67 distinct franchiseID's

I do not currently have an index on this table, so i agree that this will assist.

But, I believe the issue is deeper than using an index, since as you can see, this is not that big of a table.

 

[gmmastros]

You may want to consider using dynamic SQL if your where clause conditions are changing a lot.

Take a look at this:

http://blogs.lessthandot.com/index....ming/do-you-use-column-param-or-param-is-null

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

So now you have me seriously thinking about revamping all of my code on this project!

The two issues I have is that I have never used dynamic sql before, and not sure how it will work for me?

You see I have several queries (datasets in SSRS) that require the AND/OR conditions, due to the fact that I have multiple parameters on the report, and most are not required. This means when the user leaves the parameter NULL, I need to account for this when calling the data from the database. So as you can imagine this would call for multiple AND/OR conditions.

Below is a small snippet of what I have:

SELECT a.Year, b.franchiseID, b.RAAG_Year, b.SRR_Total, b.WO_Total, b.Estimated_Total, b.Estimated_Dol, b.Booked_Total, b.Booked_Dol, b.Completed_Total,
b.Completed_Dol, b.Sales_Dol, b.CO_DOL_ADJ
FROM (SELECT DISTINCT Year
FROM DateDim) AS a LEFT OUTER JOIN
(SELECT COALESCE (b_1.SRR_Total, 0) AS SRR_Total, COALESCE (a_1.WO_Total, 0) AS WO_Total, COALESCE (c.Estimated_Total, 0) AS Estimated_Total,
COALESCE (c.Estimated_Dol, 0) AS Estimated_Dol, COALESCE (d.Booked_Total, 0) AS Booked_Total, COALESCE (d.Booked_Dol, 0) AS Booked_Dol,
COALESCE (e.Completed_Total, 0) AS Completed_Total, COALESCE (e.Completed_Dol, 0) AS Completed_Dol, COALESCE (e.Sales_Dol, 0) AS Sales_Dol,
COALESCE (b_1.franchiseID, a_1.franchiseID, c.franchiseID, d.franchiseID) AS franchiseID, COALESCE (b_1.RAAG_Year, a_1.RAAG_Year, c.RAAG_Year,
d.RAAG_Year) AS RAAG_Year, COALESCE (f.CO_DOL_ADJ, 0) AS CO_DOL_ADJ
FROM (SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM WRK_ORDER
WHERE (YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (turfID = @Turf) AND (MONTH(dateCreated) = @Month) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (turfID = @Turf) AND (@Month IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (MONTH(dateCreated) = @Month) AND (@Turf IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (@Month IS NULL) AND (@Turf IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (turfID = @Turf) AND (MONTH(dateCreated) = @Month) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (turfID = @Turf) AND (@Month IS NULL) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (MONTH(dateCreated) = @Month) AND (@Turf IS NULL) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (@Month IS NULL) AND (@Turf IS NULL) AND (@franchise IS NULL)
GROUP BY YEAR(dateCreated), franchiseID) AS a_1 FULL OUTER JOIN
(SELECT COUNT(srvcReqRefID) AS SRR_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM SVC_REQUEST
WHERE (YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (turfID = @Turf) AND (MONTH(dateCreated) = @Month) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (turfID = @Turf) AND (@Month IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (MONTH(dateCreated) = @Month) AND (@Turf IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise) AND (@Month IS NULL) AND (@Turf IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (turfID = @Turf) AND (MONTH(dateCreated) = @Month) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (turfID = @Turf) AND (@Month IS NULL) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (MONTH(dateCreated) = @Month) AND (@Turf IS NULL) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (@Month IS NULL) AND (@Turf IS NULL) AND (@franchise IS NULL)
GROUP BY YEAR(dateCreated), franchiseID) AS b_1 ON a_1.franchiseID = b_1.franchiseID AND a_1.RAAG_Year = b_1.RAAG_Year

I know there is always a better way to handle a problem, so I am open to suggestions.

But, again if I can call specific Select statements based on the value of a parameter selection, then I believe that I could reduce alot of the overhead code...is this right?

 

[gmmastros]

It looks like you have 3 optional parameters, @franchise, @Turf, and @Month. This leads to 8 different conditions, which you are trying to accommodate by writing 8 variations of the where clause (in 2 different places). This approach is problematic and difficult to maintain. Instead, you could try something like this...

WHERE   YEAR(dateCreated) = @RAAG_Year - 1
        AND (@franchise IS NULL OR franchiseID = @franchise) 
        AND (@Turf IS NULL OR turfID = @Turf) 
        AND (@MONTH IS NULL OR MONTH(dateCreated) = @Month)

Think about it this way...

[tt]AND (@Turf IS NULL OR turfID = @Turf) [/tt]

if you want to ignore the turfID filter criteria, you pass NULL in for @Turf, right. If @Turf is NULL, the first part of the OR condition evaluates to TRUE. In this case, it doesn't matter what the 2nd part of the condition evaluates to because TRUE or'd with any value is always true.

When you pass a value in for @Turf, the first part will evaluate to FALSE so now each value for trufID (in the table) will be compared to the @Turf variable and will only return if it matches.

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sconti11]

Yes...it makes perfect sense....it's just that for some reason SSRS (which is where I am writing this sql) re-aranges the paranthases for the OR condition.

So it changes the following:

SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM WRK_ORDER
WHERE (YEAR(dateCreated) = @RAAG_Year - 1) AND (@franchise IS NULL OR franchiseID = @franchise)
GROUP BY franchiseID, YEAR(dateCreated)

To:

SELECT COUNT(workorderID) AS WO_Total, YEAR(dateCreated) AS RAAG_Year, franchiseID
FROM WRK_ORDER
WHERE (YEAR(dateCreated) = @RAAG_Year - 1) AND (@franchise IS NULL) OR
(YEAR(dateCreated) = @RAAG_Year - 1) AND (franchiseID = @franchise)
GROUP BY franchiseID, YEAR(dateCreated)

I have no reason why???

But maybe if I was to writ the sql into a stored procedure like the way you suggested, and then call that procedure from the report??

I have not done this before, so it will be learning process if it is even the correct way to go.

 

[gmmastros]

The "rearrange" process is breaking the code by changing the logic. Try writing a stored procedure. I can help with the stored procedure part, but not so much with "calling it from the report". I'm sure it's possible, I just don't know how.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Sure, use SP for your report. If you want to re-write your SP in efficient way, I suggest to read very long and comprehensive article by Erland Sommarskog:

Dynamic Search Conditions in T-SQL Version for SQL 2008 (SP1 CU5 and later)


PluralSight Learning Library