Using parameter makes query to run 10X slower

Question

Using parameter makes query to run 10X slower

Jouni Karppinen

SSC-Addicted

Points: 470
More actions
October 8, 2006 at 5:28 pm

#172930

Hi
Could someone explain what could be causing my SP execution time problems.
SP:
DECLARE @parentItem int
SET @parentItem = 6886
SELECT [fieldlist here]
WHERE [some where clauses]
AND tItem.itemID IN (SELECT ItemID FROM dbo.GetChilds(@parentItem))
Now to performance....
If I run Query like that, it shows in Client Statistics that Execution time was around 1000ms
If I replace @parentItem with real value execution time drops to 170ms???
So when SP content is:
DECLARE @parentItem int
SET @parentItem = 6886
SELECT [fieldlist here]
WHERE [some where clauses]
AND tItem.itemID IN (SELECT ItemID FROM dbo.GetChilds(6886))
It runs almost 10X times faster than when I pass parameter to GetChilds function.
How this can be fixed?
Parameter in dbo.GetChilds function is declared as int
- Jouni -

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Vivi SSC-Addicted Points: 457 More actions · Answer 1

My guess: For all rows, the database will have to run the "SELECT ItemID FROM dbo.GetChilds(@parentItem)", and spend extra time to get the value of @parentItem and pass it to GetChilds().

If I am right, the more rows in the table, the more performance drop you will see.

My question is: Does anybody know how to prevent this performance drop when the value of the parameter can not be pre-determined? Is there a better way to write the query?

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 2

You could try rebuilding the indexes on the affected tables - your stats could be wrong and misleading the query optimizer. You could also try re-writing the query to use a join instead of a subquery, if thats possible.

Graham-214561 SSC Enthusiast Points: 195 More actions · Answer 3

Try re-writing your function (getChild) which I am assuming is using the parameter in a where clause

Create a local parameter of type int and set that parameter to the value you are sending in and use the local copy in the restriction within the function

I have found in the past that this can sometimes have a marked impact on performance

Holger Steidel SSChasing Mays Points: 602 More actions · Answer 4

Always use the same datatype for the parameter as the column or the function in the query. Otherwise SQL Server needs to do a conversion or chose the wrong execution plan.

For example if your parameter is text and you use it to give over an int value to the query SQL needs to convert it and slow down.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

You may want to search this site for "parameter sniffing". It should provide some alternative and very important insights.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Paul Rubel Ten Centuries Points: 1047 More actions · Answer 6

The additional time would come from one of two likely sources. The first is a change in the navigation within the GetChilds stored procedure. Perform Display Estimated Execution Plan and make sure they both use an index to seek. The other possability, and this is more likely it, SP is being recompiled when you run it with the variable. The profiler or other tools will identify if a recompile is taking place.

Jouni Karppinen SSC-Addicted Points: 470 More actions · Answer 7

Thank you all for your ideas. I think that Paul Rubel is on right track when succesting that SP is being recompiled.

dbo.GetChilds function is actually a bit hazardous and it's working faster than I expected.

What I'm trying to achieve is that I have UI with 2 panels. On left hand panel I have a Tree-view and on right hand panel I would like to show all recursive childs of selected leaf on treeview.

So basically something like this

ROOT                       | LISTVIEW with ITEM2 selected:
  |                        | - ITEM2
  |- ITEM 1                | - CHILD1
  |- ITEM 2                | - CHILD2
       |- CHILD 1          | - SUBCHILD 1
       |    |- SUBCHILD 1  | - SUBCHILD 2
       |- CHILD 2          | - SUBCHILD 3
            |- SUBCHILD 2  |
            |- SUBCHILD 3  |

I'm using syntax

WITH [ RECURSIVE ] query_alias_name [ ( column_list ) ]
AS ( select_query )
query_using_query_alias_name

as described in this article to get all childs recursively to listview.

As I'm not a SQL specialist, I would love to hear alternative way to function to implement recursive query to limit result