November 21, 2003 at 4:25 pm
I have a database with 3 transactional type tables. One with about 5 million records another with 300,000.
There was some erroneous data put into the big table. That table has a company id is one of 4 of the following: “D”, “C”, “U” OR “W”. If I run an update (using a stored procedure) against the records on the smallest company it runs successfully but if I do it for one of the other companies it times out. The database is set to run without time outs (timeout = 0).
If I run the same query in Microsoft access with an odbc connection against the table it does not time out. Only takes approximately about 20 minutes or so.
The stored procecure looks like this:
CREATE PROCEDURE upDates AS
UPDATE mtForecasts
SET mtForecasts.fdtForecastAsOfDate= DATEADD(day, - 1, fdtForecastAsOfDate)
FROM mtForecasts
WHERE (ftxCompanyID = 'D') AND (YEAR(fdtForecastAsOfDate) = 2003) AND (MONTH(fdtForecastAsOfDate) = 11) AND (DAY(fdtForecastAsOfDate) = 18)
;
return
GO
What are the issues that cause an update query (stored proc) to time out. In an application I wrote that runs off of a daily dts that populates this data base I have to loop around small subsets of data to update records otherwise it times out.
It seems very cumbersome. Again it seems to run way more efficiently running these updates from access or vb via an odbc connection.
Help
November 24, 2003 at 7:42 am
Why are you breaking the date up that way? breaking up the date in the where clause that way will force a table scan.
You could probably increase the performance dramatically by putting an index on the date and using a consolidated date.
Joe Johnson
NETDIO,LLC.
Joe Johnson
NETDIO,LLC.
November 24, 2003 at 2:46 pm
What is a consolidated date? I tried using a specific date/time in the were clause. Example where fdtForecastAsOfDate = '4/10/2002 9:00:40 PM' but this did not seem to work.
I will try and add an index.
Thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply