August 30, 2004 at 12:45 pm
Hello all,
I was hoping someone had some ideas about a performance issue I am running into on a production sql server (clustered SQL 2000 on Windows 2003).
I have a query against a view that historically took 30 seconds to run. There are two elements in the where clause that use variables - one is a char(4) and one is a datetime datatype. The where clause looks something like:
select *
from myView
where sales_rep = @myChar
and date = @myDatetime
Now the query that historically took 30 seconds is taking 8 minutes (ARGH!). No code has changed, no sql server settings, or hardware settings have changed. The interesting thing is that if I hard code a value into the query, it runs in the normal 30 seconds. However, when using variables in the where clause, it takes 8 minutes. I suspect there is something going on with an implicit conversion that is invalidating one of my indexes...or something like that. Has anyone run into any similar issues? Suggestions?
Thanks
ajroney
August 30, 2004 at 12:57 pm
Ajroney,
I need some more info:
Is this the first time you have used the query with the variables?
Has the size of any of the view tables changed resently?
August 30, 2004 at 1:02 pm
Hi EdMann,
To answer your question the query with variables has been in production for at least 1 year. Also, there is datagrowth in the tables, but it has been about the same growth as I have seen historically.
Another note...I just took the query with the variables (against the view) and wrapped a stored procedure around it. Performance went back to normal (which is good :0)). I am wondering if I hit some threshold in the optimizer where it no longer used the correct indexes, and now that the query plan is cached using the stored procedure life is good.
August 30, 2004 at 1:56 pm
It's possible, but it is more likely that the server was restarted recently and the cached plan was not the most optimum. Whenever an object is executed for the first time the execution plan is created using the values passed to it. These variables could cause a less than efficient execution plan thus causing almost all subsequence executions to take longer than the usual.
My suggestion would be to return to your prior query, but replace your view with a function. The advantage of a UDF is removal of the implicit query that occurs within views.
Query A:Select * from vRegion where state = @State
Query B: Select * from dbo.fnRegion(@State)
The difference between query A and B is A actually perform 2 queries. The first returns the entire region and the second returns the subset of state. Query B inserts the state into the region query before it is executed.
Hope this helps,
Ed
August 31, 2004 at 2:20 am
1.Run this
UPDATE STATISTICS TableName(Indexname)
WITH FULLSCAN, NORECOMPUTE
2.Alter the view by giving the order by clause. Something like this.
Create view myView as
Select top 100 percent * from Tablename
where where sales_rep = @myChar
and date = @myDatetime
order by (primary index column)
This will improve the performance.
Thanks
Ganesh
August 31, 2004 at 7:11 am
Thank you all for your responses. Statistics were recompiled. Did not think of the order by clause in the view. I will give that a shot as well.
As far as the execution plan being cached, in our testing we ran the procedure/view a number of times without gaining any performance.
Thanks again!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply