Dynamic Sql and Performance

Question

Dynamic Sql and Performance

karthik M

One Orange Chip

Points: 29139
More actions
September 4, 2007 at 2:05 am

#120373

Hi Experts,

Will Dynamic sql affect performance or not ?
------------------------------------------------------
select name into #t1
from sysobjects
where type ='U'
and name not in ('ProductCommitmentWorkflow',
                 'Tranche',
                 'CompanyTrans',
                 'ProductFeatureSelection',
                 'ProductFeature',
                 'ProductSubType',
                 'ProductType'
                 )

create unique index id1 on #t1(name)

declare p1 cursor
for select name from #t1
for read only

open p1

declare @name varchar(40)

fetch p1 into @name
while @@fetch_status = 0
begin
declare @sql varchar(255)

select @sql = 'Truncate table '+ @name
exec (@sql)

fetch p1 into @name
end
close p1
deallocate p1
--------------------------------------------------------------------
Regards
Karthik

karthik

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Karthik,

It depends on how log it takes to execute the above query. If it takes more than a few seconds, then the answer is likely that it does not matter that you are using dynamic SQL.

SQL Server uses caching for query plans. This cache is based on the text of the query, so in case of dynamic SQL the plan will not be reused if the generated string is different. It costs time to generate the query plan. So if your query is running fast (less than a few seconds) and is executed frequently, I would explore a solution that is not using dynamic SQL.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

Karthik,

also, instead of using cursors to iterate through the tables you may want to consider sp_msforeachtable . (this is not in books online, but searching these forums or google helps )

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

David McFarland SSChampion Points: 11815 More actions · Answer 3

To be fair, sp_msforeachtable uses a cursor as well, but at least it's already written for you.

noeld SSC Guru Points: 96590 More actions · Answer 4

to perform DDL operations like the ones you posted (truncate tables) dynamic sql does *not* matter in terms of performance because it won't be dominated by the query but by the I/O instead.

* Noel

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

Karthik,

You didn't believe me?

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=395872#bm396072

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Jeff,

I am believing you !

Are you Happy now ?

I thought this is different question, then only i posted this question.

Regards

Karthik

karthik