Trigger and Bulk Update
-
Hello,
Please can someone help me with a Trigger question...
I have an Orders Table in a SQL 2000 database. This Table currently contains 1.5 million rows. One of the columns within this Order Table is 'Shipped Date'.
This Ordr Table is the backbone of an extract for a Data Cube using a third party application.
I have been given a requirement to filter the Orders being extracted by only selecting those that have a 'Shipped Date' that falls between a given date range. This Min and Max date range is held in a seperate Table.
In order to select the data for the data cube I have built a View using around 15 Tables.
I have tried to add the date range Table into the View but it slows down performance dramatically. Without it it takes 1 minute, with it it takes several minutes.
As a solution I now have a Bit column in the Order table that is set to true if the Shipped Date is between two dates. I have found that using this method in the WHERE statement of the View is much faster.
My problem comes with maintaining this flag setting. If someone changes the date range either using my application or directly onto the table I want to refresh the flags for all of the Orders. To to this I have chosen to use a trigger on the update of these columns that Updates all the rows in the Order table by checking their Shipped Date against the new range.
However, when this trigger is executed as a result of my VB application doing an update I am getting a timeout. I have tried the app.timeout by setting this to 9999999. I guess doing an update of 1.5 million rows in a trigger is very bad practice.
So, with this in mind does anyone have an alternative solution that will be triggered by an update but not timeout. Would the trigger calling a Stored Procedure work?
Any suggestions welcome.
Thankyou in advance.
-
Having the trigger call an SP won't help, because the SP would still have to execute synchronously.
I would suggest having the trigger set a value, or create a row, in a 'WorkToDo' table, and have a SQL Server Job that runs periodically (like every minute) that looks in the WorkToDo table and does the required work if there is any. Of course this will mean there will be times when the flag column on the Order table is 'stale', but real world systems usually require us to make compromises.
ps Shipped Date is indexed, right?
-
How often is the third party Data Cube getting refreshed?
Corie Curcillo
MCT, MCDBA, MCSD -
My Gosh, TUNE THE SQL!!!! Dont write updates that add to your problems. It may be a simple as having the proper index that includes the shipping date.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply