November 9, 2005 at 12:50 pm
I am using MS SQL 2000 and MS Access 2000 (ADP not MDB) and all of a sudden after weeks of using the application I am starting to get Timeout Errors in stored procedures that previously worked just fine. (I am not using ADO code to run them, rather I am using CurrentConenction.execute() to run them.)
When I go run them in Query Analizer it also gives me a timeout error, unless I let it run for 7.20 minutes.
I went back to an "old" copy of the data and sp and ran one of them and it ran in less then a second, when I run the same sp on the current system I stopped it after 5 minutes. The data all looks good and about the same number of records. I did look at the execution plan for both and they are vastly different. Is there a way to tell SQL Server to "redo the plan?"
How can a sp work one day and the next not work? The one I am working with was used every singe day, then it stopped working.
Another one stoped working then the next day it worked!
November 9, 2005 at 1:17 pm
First thing I look for when a query is taking too long is a live lock condition: is your SPID blocked by another?
Then I deal with performance issues. Yes you can do an sp_recompile, but I'd take another look at your syntax.
November 9, 2005 at 1:35 pm
When was the last time that optimization was run?
SELECT t.name as TableName
, i.name as IndexName
, STATS_DATE(i.id, i.indid) as StatisticsDate
FROM sysobjects T
join sysindexes i
on t.id = i.id
where t.type = 'U'
SQL = Scarcely Qualifies as a Language
November 9, 2005 at 5:42 pm
Since I don't know what that means, or how to run it I will have to say: I don't know.
Also, what does the SQL code do?
November 9, 2005 at 5:46 pm
I did find out that if I changed one line it ran in under a second:
HAVING (dbo.vShipping_WOIDItem_Balance_KIT.BalLng <= 0)
If I took the less then away it ran in under a second???
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply