September 12, 2002 at 7:24 am
Hi,
I wonder if anyone could offer any ideas. I need to set up a job which will run a query to insert ercords from on db to another (this is for a test). The source table has 3 1/2 million records.
The query is going to insert based on
SELECT * FROM dba.tablea where crdate between
@lastdateused and getdate
this is supposed to run every 5 minutes to get all records added (in the real world) but my test data alread exists, so it is taking forever.
I have a clustered index on the date.
Any suggestions?
September 12, 2002 at 8:04 am
Oops
That should read 32 1/2 million rows
September 12, 2002 at 10:20 am
quote:
OopsThat should read 32 1/2 million rows
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 12, 2002 at 10:25 am
Ooops. Clicked "Post New Reply" before I typed anything. Here is what I was thinking.
1) Have you looked at the execution plan to determine where you are getting the performance hit (what part of the query).
2) How may records are going to be inserted based on your query? Hopefully only a small amount
3) I'm not really up on performance differences between one format of a query, but have you tried this instead of between:
SELECT * FROM dba.tablea where crdate > @lastdateused and crdate < getdate
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 13, 2002 at 5:08 am
crdate >= @lastdateused and crdate <= getdate
is the same as BETWEEN, however BETWEEN generally runs fastest.
If you cannot see from the execution plan the problem then try
SET SHOWPLAN_TEXT ON
GO
YOURSELECTQUERY
GO
and post the results here for us to take a look.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply