April 16, 2009 at 10:05 am
insert SIRSdata
select R.*
from Budget..Hrbg as R , Revenue..BudHrbg as BD
where R.BudID= BD.BudID and R.servcdate between BD.startDate and BD.endDate
this query is taking too long to run, is there any alternate query i can use to perform this.
April 16, 2009 at 10:07 am
how do i do this insert in batches just in case to improve performance.
April 16, 2009 at 11:17 am
Maybe you can use SELECT INTO. SELECT INTO is nonlogged and run faster then INSERT SELECT.
April 16, 2009 at 11:25 am
If you know the values of StartDt & EndDt are likely to be, break them down into smaller chunks so that you're only moving certain date ranges.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2009 at 11:25 am
is this somethin like...
select R.* into
from Budget..Hrbg as R , Revenue..BudHrbg as BD
where R.BudID= BD.BudID and R.servcdate between BD.startDate and BD.endDate
April 16, 2009 at 11:33 am
Tara (4/16/2009)
insert SIRSdata
select R.*
from Budget..Hrbg as R , Revenue..BudHrbg as BD
where R.BudID= BD.BudID and R.servcdate between BD.startDate and BD.endDate
this query is taking too long to run, is there any alternate query i can use to perform this.
Inserts are simply going to take however long they take to move X amount of data. However, you can possibly tune the SELECT query, but the syntax you have there is very simple and direct. The only opportunity lies in using indexing. Have you looked at the actual execution plan? If not, post here (a video that shows how) and we'll see if something can be done.
If you wanted to break it down by date, you'd just add more filters:
-- your query plus...
AND r.ServcDate BETWEEN '1/1/2009' and '2/1/2009'
That would move only one month's worth. You can then do the same thing to '3/1/2009', etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 16, 2009 at 12:56 pm
Correct syntax is:
select R.* into SIRSdata
from Budget..Hrbg as R , Revenue..BudHrbg as BD
where R.BudID= BD.BudID and R.servcdate between BD.startDate and BD.endDate
but this query create a table SIRSdata and insert rows from Budget..Hrbg as R , Revenue..BudHrbg. Your orginal query only insert rows. If you have any data in SIRSdata you can't use this solution.
I had a similar problem in my ETL application. This trick help me, my query run even 100 x faster.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply