Another Dynamix SQL question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Another Dynamix SQL question

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-07-28 : 09:03:38

Sometimes I have the feeling I haven't learned a lot these years.

Can someone explain why a parameterized query is just as fast as a badly constructed dynamic SQL?

(Besides the injection thingy, but that's not the point).

I thought parameterized dynamic SQL was faster than just dynamic SQL

Both examples shown here on a 7.5 million record table giving the same results (7 sec's to execute).



declare @sql varchar(1000)

set @sql =  'select * from tblAppObjectData where tekst1 =''i@7vH8BLiYCmBKr''' 

exec (@sql)
 
-------

declare @sql nvarchar(255)
set @sql = 'select * from tblAppObjectData where tekst1 = @str'
exec sp_executesql @sql, 
  N'@str varchar(25)', 
  @str = 'i@7vH8BLiYCmBKr'

Henri
~~~~
The envious praises me unknowingly

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 09:25:11

by executing this one statement the speed is the same.
the difference come when you change your tekst1 search values.
make 100 loops and change the search term 100 times. i bet there'll be a difference then.

that's because parametrized gets cachedm while non parametrized gets recompiled each time.

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-07-28 : 09:39:12

I made a loop, but I don't see any difference at all.
Do I construct it wrong? Can it be that it will make a difference if the server has a lot more RAM?

thx!

Henri
~~~~
The envious praises me unknowingly

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 09:42:39

did you make ''i@7vH8BLiYCmBKr''' different in each loop?

i guess it would... if it didn't then my understanding of dynamic sql has been wrong all this time...

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-07-28 : 09:52:48

Yes, I made it different, but also when I keep some of the loop the same, the total execution time was the same than making the same loop the EXEC (@sql) way.

I made a statement to a friend and he asked "Have you tested it?". Now I have and I'm confused.

Of course: making a good index speeds both ways of executing sql up by a factor 100 or so, more then the difference between whatever two ways of executing dynamic sql.

But I try to optimize my code and hoped changing the way I execute SQl was giving me better performance (besides better security, I know

).

Thx, anyway. If someone knows more about the subject.... I'm willing to read!

Henri
~~~~
The envious praises me unknowingly

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-07-28 : 10:02:13

try this also... i'm intrigued:

declare @sql nvarchar(255)
set @str = 'i@7vH8BLiYCmBKr'
select * from tblAppObjectData where tekst1 = @str

what's exec time of this compared to others?

Go with the flow & have fun! Else fight the flow

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-07-28 : 10:29:44

None, whatsoever. However somehow the sql is executed a little faster if I change NVARCHAR to VARCHAR.

Henri
~~~~
The envious praises me unknowingly

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-07-28 : 10:40:23

'select * from tblAppObjectData where tekst1 =''i@7vH8BLiYCmBKr''' is very chip to compile...
maybe even after 100 loops you cannot see the difference...

Kristen
Test

22859 Posts

Posted - 2005-07-28 : 10:57:23

BoL: If object names in the statement string are not fully qualified, the execution plan is not reused

I reckon you need some "dbo." and stuff like that in your query

You might also want to chuck in a

DBCC DROPCLEANBUFFERS

between each test to flush the cache (not a good idea for a production server though!)

Kristen

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-07-29 : 03:04:30

The DBCC DROPCLEANBUFFERS was useful, thx.

This testing is interesting. I have a test server at home (windows 2003, one Xeon processor, but only 512 RAM).

If I stop and start the SQL server service I can do a lot of things and if I run these things a second time they are lightning fast!

However, soon SQL is using all the space it can get and if I run a query for two times, the second time is fast. If I switch to three different query's (simple ones, but evaluating millions of records) then they all get sluggish and If I repeat the last one, this one is fast.

though the use of DBO. is recommended I didn't see a difference in the testing results and all the plans where reused wether I used the EXEC (@SQL) of the parameterized query (the way it's supposed to be done).

This is what it looks like: It doesn't matter how the SQL is constructed (string based, paramterized of just plain old T-SQL in the query analyzer), if the 'question' to the SQL server is good SQL, SQL reuses the execution plan or at least uses the cache to dig up the information.

Maybe I confuse cache with reuse of execution plan... Does anyone knows a good read?

Henri
~~~~
The envious praises me unknowingly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources