September 23, 2013 at 12:15 pm
I have a report that calls procedure.
Procedure is simple select from VIEW:
...
FROM
vwNzEmpNonPrjTime
WHERE
co_code = @L_CO_CODE
AND ('**all**' IN (@L_ORG) OR org_code IN (SELECT * FROM getValuesAsTable(@L_ORG, ',')))
AND ('**all**' IN (@L_EMPLOYEE) OR EmployeeNumber IN (SELECT * FROM getValuesAsTable(@L_EMPLOYEE, ',')))
@L_ORG and @L_EMPLOYEE are input parameters for "Multi Value" SSRS parameters.
Inside stored porcedure I cannot use IN @var method.
I have to convert comma delimited strings into temp table.
but this seems to create a problem with execution code.
If I change procedure code and have:
WHERE
org_code = @L_ORG
AND EmployeeNumber = @L_EMPLOYEE
code executes in 1 sec, no clustered index scan.
As sooon as I change it back to the code at the top
I get clustered index scan and it executes more than 10 sec.
By the way, SELECT * FROM getValuesAsTable(@L_EMPLOYEE, ','))
shouldn't be a problem. It runs in 200 miliseconds even with a lot of values.
September 23, 2013 at 12:36 pm
I could be wrong on this, but from what I can tell, the WHERE clause will induce a table scan; this part in particular:
OR org_code IN (SELECT * FROM getValuesAsTable(@L_ORG, ',')))
There's a SELECT * there with no WHERE clause, which automatically forces a table/index scan. Combined with the IN clause you're using against it, there could be quite a performance problem there, even if the SELECT alone is operating fine. Also, it seems that getValuesAsTable is a table-valued function; is this correct? If it is, that may be damaging performance as well (though someone else will undoubtedly know more on the matter; I'm still green on the issue).
If possible, please provide some table definitions and clarify what getValuseAsTable is; doing so will make it a lot easier to help here. My observations are mostly general guesses, but specific advice will help to get your problem resolved much more neatly.
- 😀
September 23, 2013 at 1:14 pm
getValuesAsTable is table-valued function
It works like this:
SELECT * FROM getValuesAsTable ('1,2,3',',')
---------------------------------------------
1
2
3
September 23, 2013 at 1:18 pm
After some tests I think the problem is with this code:
('**all**' IN (@L_ORG) ....
As soon as I remove this part procedure runs in 1 sec.
September 23, 2013 at 1:32 pm
See if this makes things any better.
FROM
vwNzEmpNonPrjTime e
outer apply getValuesAsTable(@L_ORG, ',') o
outer apply getValuesAsTable(@L_EMPLOYEE, ',') et
WHERE
co_code = @L_CO_CODE
AND (CHARINDEX('**all**', @L_ORG) > 0 OR o.org_code = o.[SomeColumn])
AND (CHARINDEX('**all**', @L_EMPLOYEE) > 0 OR EmployeeNumber = et.[SomeColumn])
I don't know what your splitter code looks like but if it is using xml, a cursor or a loop I would highly recommend you look at the link in my signature about splitting strings and use the splitter found there instead.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 2:23 pm
Dear Sean Lange.
You really are SSChampion !!!!!
It runs in 1 sec now !!!
Wow.
I just need to understand what is the trick
and what was wrong with my old code
because I use similar approach in a whole bunch of stored procedures....
Thanks a Lot !!!
September 23, 2013 at 2:47 pm
RVO (9/23/2013)
Dear Sean Lange.You really are SSChampion !!!!!
It runs in 1 sec now !!!
Wow.
I just need to understand what is the trick
and what was wrong with my old code
because I use similar approach in a whole bunch of stored procedures....
Thanks a Lot !!!
You are welcome. Probably the biggest performance boost here is using CHARINDEX instead of IN. The APPLY is just easier to read in my opinion than those subselects, doubt there is much (if any) performance gain here.
You didn't mention how your splitter works but your code will run even faster if it is using a tally splitter like the one I suggested.
Glad that my post helped and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply