Odd performance problem - 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

SQL Server Development (2000)

Odd performance problem

Author

Topic

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-18 : 14:19:18

I have a query that is performing much slower when it is within a stored proc than when i run the qury as a script. The execution plans are vastly different and I am not sure why.

The proc is as follows(the first query is the one i am having problems with) [url]http://rafb.net/paste/results/q66xHc92.html[/url]
and is called for testing -> up_SearchProspectByName '','','r'

the script is as follows [url]http://rafb.net/paste/results/cCEIWX57.html[/url]

Can anyone see why these 2 identical queries would produce such different execution plans and times?

Thanks,
Christian

Krankensteins
Starting Member

24 Posts

Posted - 2006-04-18 : 14:30:16

I get similar problem on large table with functions. Because i havnt time i yust place ther HINT on index and they begins work.

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-18 : 14:31:33

quote:
Originally posted by Krankensteins

I get similar problem on large table with functions. Because i havnt time i yust place ther HINT on index and they begins work.

pardon me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:37:58

Have you tried recompiling the stored procedure to see if you'll get a different execution plan?

Tara Kizer
aka tduggan

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-18 : 14:39:33

quote:
Originally posted by tkizer

Have you tried recompiling the stored procedure to see if you'll get a different execution plan?

Tara Kizer
aka tduggan

Yes I tried adding With Recompile to the proc.

The difference between the 2 is 3-4 seconds for the script and 14-35 for the proc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:42:20

Could you post the execution plans for both? If you modified the stored procedure to only have one select statement (the one you are interested in), does it produce the same execution plan as the query when run by itself?

Tara Kizer
aka tduggan

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-18 : 14:45:26

quote:
Originally posted by tkizer

Could you post the execution plans for both? If you modified the stored procedure to only have one select statement (the one you are interested in), does it produce the same execution plan as the query when run by itself?

Tara Kizer
aka tduggan

How do I generate a text execution plan? The graphical one is huge.

edit: I just removed the second query and the if statement and no change. Still a completely different execution plan.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:50:03

SET SHOWPLAN_TEXT ON

I'd prefer the graphical one though, but others here like the textual one.

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-18 : 14:51:03

quote:

edit: I just removed the second query and the if statement and no change. Still a completely different execution plan.

Did you recompile after this change? Try a DROP/CREATE on it after the change to be sure.

You might also try running DBCC FREEPROCCACHE to free the procedure cache. I also run DBCC DROPCLEANBUFFERS when I'm comparing two different queries. So I run both of these to level out the playing field in between each run. It means you'll have a clean baseline and your comparison is accurate.

Tara Kizer
aka tduggan

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-18 : 14:55:04

quote:
Originally posted by tkizer

quote:

edit: I just removed the second query and the if statement and no change. Still a completely different execution plan.

Did you recompile after this change? Try a DROP/CREATE on it after the change to be sure.

You might also try running DBCC FREEPROCCACHE to free the procedure cache. I also run DBCC DROPCLEANBUFFERS when I'm comparing two different queries. So I run both of these to level out the playing field in between each run. It means you'll have a clean baseline and your comparison is accurate.

Tara Kizer
aka tduggan

i did that. I have been doing that after each run.

I have no way to get the graphical online.

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-18 : 15:00:09

Here is the proc execution plan [url]http://rafb.net/paste/results/xFfxx487.html[/url]

Here is the scripts: [url]http://rafb.net/paste/results/46hVVt50.html[/url]

Cyclonik
Posting Yak Master

114 Posts

Posted - 2006-04-19 : 09:59:07

quick bump for the morning :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources