Performance sp_ExecuteSQL vs. same query in Query Analyzer

Question

Post reply

Performance sp_ExecuteSQL vs. same query in Query Analyzer

Whigger

Mr or Mrs. 500

Points: 566
More actions
November 8, 2004 at 6:43 am

#108950

Hi folks,
We use a lot of sql executed via the sp_executesql. We now have an insert-query taking about 8 minutes for 2 million records. Excuting the same query in QA takes about 1 minute...
Who has good advise taking into account that we cannot skip the sp_executesql?
TNX,
Whigger

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

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

womalley Ten Centuries Points: 1329 More actions · Answer 1

my first question would be why do you need sp_executesql?

my guess is that vales are being passed in to your procedure

and you need to build the insert dynamically..??..

If this is the case it may suit you better to use XML and OPENXML

have you tried using XML ?

Will

Whigger Mr or Mrs. 500 Points: 566 More actions · Answer 2

The sp_ExecuteSQL is core of our 'load-engine' which is perfectly generic this manner for our batch-processing(and won't be changed in the near future, because of impact)!

The insert statements are generated on-the-fly for several tables over the syscolumns so the sp_ExecuteSQL suites perfectly! Performance was no issue untill we found out this difference of 8 minutes vs 1 minute, today!

TNX in advance,

Whigger

Jags2001 SSCommitted Points: 1529 More actions · Answer 3

I think the main reason is that the optimizer does not cache the execution plans for SQL in sp_execute, where as SQL executed directly are cached.

Adam Machanic SSCoach Points: 15370 More actions · Answer 4

Actually, both are cached. Look in syscacheobjects and you'll see both executable and compiled plans after either sp_executesql or ad hoc SQL.

Whigger, can you post some code showing us what's being done in the insert? It doesn't make a lot of sense that there would be such a big discrepency between the two.

--
Adam Machanic
whoisactive

Whigger Mr or Mrs. 500 Points: 566 More actions · Answer 5

Thanks for the help, so far!

declare @SQLString nvarchar(4000)

set @SQLSTring = 'insert into aggr_Sales

(Price, NetPrice, SalesID, DealerID, DealerName, Month, ProductGroup, NetProfit, GrossProfit, Quantity, Quarter, Year)

select

Price, NetPrice, SalesID, DealerID, DealerName, Month, ProductGroup, NetProfit, GrossProfit, Quantity, Quarter, Year

from tr_aggr_Sales'

exec sp_ExecuteSQL @Sqlstring

The "tr_aggr_Sales" is a view over the source tables giving an aggregation (SUM's & COUNT's)

TNX,

Whigger

Andy Robertson SSC Eights! Points: 935 More actions · Answer 6

Could it be to do with the execution plan being re-used (so when you ran it in QA it was much quicker)?

Whigger Mr or Mrs. 500 Points: 566 More actions · Answer 7

Nope...!

We first ran the sp_ExecuteSQL: approx. 8 minutes

Then the QA: approx. 1 minute

Then the sp_ExecuteSQL again: approx. 8 minutes..........

Adam Machanic SSCoach Points: 15370 More actions · Answer 8

Whigger,

Does this include sp_executesql from QA?

This really makes no sense...

Are the execution plans the same?

--
Adam Machanic
whoisactive

Russell Shilling SSCarpal Tunnel Points: 4299 More actions · Answer 9

My guess would be that the sp_executesql wraps the code in a transaction, while the SQL run in QA is not.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Adam Machanic SSCoach Points: 15370 More actions · Answer 10

Russell,

sp_executesql does not wrap an additional transaction, AFAIK:

exec sp_executesql N'select @@trancount'

-- returns: 0

--
Adam Machanic
whoisactive

Adam Machanic SSCoach Points: 15370 More actions · Answer 11

Whigger,

I have an idea, this may be similar to an OSQL problem I've seen before; can you see if output from DBCC USEROPTIONS is the same in QA as it is when you execute it from sp_executesql? I'm thinking that QUOTED_IDENTIFIER may be getting turned off, which could change index usage.

--
Adam Machanic
whoisactive

Whigger Mr or Mrs. 500 Points: 566 More actions · Answer 12

Both give the same result...! And QUOTED_IDENTIFIER is in both cases SET.

Could it be the case that SQL Server uses an index that is set on an other (similar) view in case of QA and not with sp_ExecuteSQL?

TNX,

Whigger

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

You should be able to see each step SQL Server is making by examining the execution plans of both alternatives.

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