Home » SQL ServerRSS

exec sql and transact

Hello guys.

i've a problem, in a stored procedure i have a sql (SELECT * FROM TABLA -is only an example, actually, my sql statment is more complex) if i execute the sql statment the time for delay results is 6 seconds. but if i execute the stored procecure (EXEC spexample that has the same statmen) the results is in 2 minutes. is very slowly exec te store procedure, but the statmen is fast.

excuse me for my english!!!

 

3 Answers Found

 

Answer 1

It's hard to give an answer based on this very little information you provided, but one of the common causes for such problems may be parameters sniffing problem.

Take a look at this blog post

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

and I believe Adam Haines has also blogs on this very problem.

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-parameter-sniffing/


Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog
 

Answer 2

- Are you using parameters in your stored procedure?
- How are you simulating this parameters when you execute the statements, using variables or using constants?

SQL Server uses different approach to estimate cardinality when you use parameters, than when you use variables.

Here is a good article to understand this behaviour.

Statistics Used by the Query Optimizer in Microsoft sql  Server 2005

 


AMB

Some guidelines for posting questions...

 

Answer 3

thank you very much Naom, your help was very usefull!!
 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter