sp_executesql

[andyrobins]

We are currently having an issue with using sp_executesql. When you run a basic query such as
select top 1000 * from <table> where surname like 'Smith%' it returns in 14ms as you would expect. Now, as was advised on many sites, if this query is going to be reused on a regular basis but with just the search string changing then wrapping it in a sp_executesql is advised to gain a performance benefit. However.....
When you run exec sp_executesql N'select top 1000 * from <table> where surname like @surname', N'@surname varchar(8)', @surname = 'Smith%' the statement takes 40-60 seconds! Not quite the performance benefit I was expecting...
Is this supposed to be like this, or does anyone have any idea why this is so slow?

Thanks in advance

Andy

 

[SQLSister]

I can't imagine where you got the idea that sp_executeSQL would improve performance. It does not. It should be avoided if possible.

If you want to improve performance you can put your SQL into a stored procedure and run it rather than running an on the fly SQL statement. This way an execution plan can be predetermined saving a small amount of time. But this is not always true and you will need to test to determine whether this will infact improve your performance.

Another thing to do to save time is to return only the fields you actually need. Select * should be avoided especially if you use a join or a union statement.

Also if you can avoid using wildcards, I would do so. If you know the last name you want, why do you need a wildcard after it? Where surname = 'Smith' is more efficient than where surname like 'Smith%'

Questions about posting. See faq183-874

 

[sunila7]

As SQLSister said converting the existing SQL to use sp_executeSQL is not a good Idea. You should avoid dynamic SQL's if possible.

Only in cases where you cannot avoid dynamic SQL use sp_executeSQL instead of EXECUTE statement, because the SQL Server query optimizer will reuse the execution plan it generates when you use sp_executeSQL.

Sunil

Sunil

 

[nigelrivett]

Suspect it's because with the first query the optimiser looks at 'Smith%' an thinks - I can use this index because it's ordered and I only have to look at entries that start with Smith.

With the parameterised the query plan is generated for
like ???????. The optimiser doesn't know that the parameter isn't going to start with a % so generates a complete scan.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[andyrobins]

My thanks to you all for a quick response. The actual sp_executesql was used by a outsourced developer creating a web application and we were astonished by the speed at which it executed a simple select statement and were just seeking confirmation that it should behave like this.

I thank you all for your quick and helpful responses.

Andy