sp_executesql Question

Question

sp_executesql Question

pnewhart

SSC Eights!

Points: 983
More actions
February 24, 2004 at 1:00 pm

#161509

I have a developer using a third-part reporting software. The software uses the sp_executesql stored procedure to obtain results rather than a direct query. I've only seen this used by MS Access when it does queries; I really never paid much attention to it. With some of the limited research I've done, it appears as though using this may be an efficient way to run queries since it is able to re-use execution plans. However, in this particular case, using this option versus direct sql, there is a dramatic decrease in performance.

The example is running a query selecting on an clustered index and returning 3 columns. The table has 62 million rows. When I run it in query analyzer, it returns the results in less than a second. Using the sp_executesql (even in query analyzer), it takes up to 15 seconds. When I show the execution plan, it appears that it is trying to query using the primary key column, which in this case is different than the clustered index I mentioned.

Any ideas?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Without knowing your query, table structure and indexes it will be difficult to tell what is going on. As always with dynamic sql I'd like to refer you to http://www.sommarskog.se/dynamic_sql.html.

Did you run both alternatives under same conditions?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

The basis of sp_executesql is that it is calling a Stored Procedure in your database, somewhere. Stored Procedures are "precompiled". That is, the execution plan for the Stored Procedure was created when the Stored Procedure itself was created.

What you're calling "direct SQL" sounds like what I call "embedded SQL". That is, the SQL is in your program as a "string" and you tell the program's connection to the database to execute the "string" of SQL. And it does, except that the "string" version is NOT precompiled. So, the SQL Server has to do extra work and create statistics, etc.

As a result, executing "embedded SQL" will almost always take longer than using sp_executesql to "call" a precompiled Stored Procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Croll SSC-Addicted Points: 488 More actions · Answer 3

Is your Primary key not the same as the clustered index that the ADHOC query is selecting? If not, I wonder if you should take a look at why query optimizer is opting for a different index. Try running the sp_executesql with an index hint to force it to use the clustered index. My guess is you will see the performance returned. Perhaps you should consider manually updating your statistics as well.

pnewhart SSC Eights! Points: 983 More actions · Answer 4

Yes, Jeff, I agree that's the way it's supposed to work. But, I think what's happening in this particular case, since the table is so large, SQL Server in it's infinite wisdom is assuming the best way to scan the table is to use a primary key. In my case the column I am using in the where clause is not the primary key; although it is a clustered index. To test my theory, I used the same queries, one using query analyzer (embedded) and, also using query analyzer, the sp_Executesql statement, on the same table in a different database having only a few thousand entries. In that case, the performace was the same and the execution plan showed use of the clustered index rather than the primary key.

pnewhart SSC Eights! Points: 983 More actions · Answer 5

pnewhart

SSC Eights!

Points: 983

February 25, 2004 at 9:54 am

#495616

Thanks, Steve, I'll try both.

pnewhart SSC Eights! Points: 983 More actions · Answer 6

Problem appears solved. The developer is using JBDC and another person in our department researched that angle and came up with this solution link:

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&oe=UTF-8&safe=off&threadm=060201c2cee4%24246fd790%248ef82ecf%40TK2MSFTNGXA04

This appears to cause the problem in query analyzer as well. Removing the conversion to nvarchar allows the sp_execute to operate correctly.

I apologize for not including the syntax originally. It's listed below for reference.

So,

exec sp_executesql N'SELECT A0.DCN,A0.Batch_Name_IA,A0.CreateDate,A0.Box_No,A0.ID FROM Claim_Export_Summary A0 WHERE A0.dcn = @P1 ', N'@P1 nvarchar(40) ', N'04014100150271'

runs slow, whereas

exec sp_executesql N'SELECT A0.DCN,A0.Batch_Name_IA,A0.CreateDate,A0.Box_No,A0.ID FROM Claim_Export_Summary A0 WHERE A0.dcn = @P1 ', N'@P1 varchar(40) ', N'04014100150271'

is FAST !!!!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Maybe it's just me and I really am going blind... would someone tell me what the difference is between the two lines that pnewhart put in his last post?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Well, the first one is in the upper line, while the second one is in the lower line

Looks like a copy'n'paste issue

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ButchH Ten Centuries Points: 1073 More actions · Answer 9

The REAL difference is the: nvarchar(40) in the first statement while the second is: varchar(40)

Butch

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Very good catch

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

pnewhart SSC Eights! Points: 983 More actions · Answer 11

That was the issue. The n' prefixes on the second line were also not necessary, I realized this after I posted.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

First, thanks for pointing out the difference there, Butch. Guess I better get some glasses!

And, thank you pnewhart, for pointing out just exactly how slow UniCode (the 'N' stuff) can be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)