Performance of a query

Hi All,

I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.

All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.


query looks some thing like this.

insert into table d (col1,col2,col3,........... )
values (a.col1,b.col2,c.col3 ................)
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @.minid and @.currid

The @.minid starts from 1 and @.currid starts from 50000 ,I am running this in a loop, in next iteration @.minid will become 50001 and currid 100,000 and so on.

Any idea why it is taking that much time?.

Thanks in advance,
bsr.where...
...and a.id in between @.minid and @.currid"a.id in between"? Try your copy/paste again...

Any idea why it is taking that much time?

Yes:...I am running this in a loop...

Use set-based operations instead of loops if you want efficiency.|||and a.id between @.minid and @.currid...

I posted sample query, actually it is nothing do with the syntax,some how query is running very slow..any ideas?

Thanks in advance
bsr|||It is because you are using loops rather than set-based processing. How many iterations are executed?|||How heavily indexed is the table receiving the inserts? Is it on a RAID-5 disk array? Are you carring blobs to the new table? How many other processes are running? Is blocking occuring on any of the three tables used to generate the source for the data?

maybe try putting the db into simple recovery mode, do the inserts, then put into full-mode and take a full backup. maybe drop all indexes on the receiving table, do the inserts, and add back the indexes. Maybe migrate to RAID 1+0. Can you use NOLOCK hints to extract data?

Without more information, or the results of a profile trace, you get general suggestions.|||Thanks a lot tomh53,

How heavily indexed is the table receiving the inserts?
A: Receiving table doesn't have any indexes,I am building a index after inserting the data.

Are you carring blobs to the new table?
A: No,it doesn't have any blobs (text or images columns).it is a small table with 20 columns,some of the columns are float and some are less than char(50).

How many other processes are running?
A: No other process was running at that time except this insert statement.A java program runs against this database but no process was running at that time since that was the first time I was setting up this new database.

Is blocking occuring on any of the three tables used to generate the source for the data?

A: No, since my insert statment was the only process running on those three tables there was no blocking or lock contention.

As I wrote earlier I ran show plan for this insert query it is using indexes properly, not doing any table scan.

Please advise.

Thanks in advance
bsr|||Hows your CPU/IO statistics during the 5 hours. I bet if you run "select * into d from a", it will use the same order of magnitude of time to complete.|||1. All id columns in your three tables must be added index
2. Do not need any loop.
3. check the excuting time for the query
Select a.col1,b.col2,c.col3 ................
from a,b,c
where a.id = b.id
and a.id = c.id
and a.id in between @.minid and @.currid

If excuting time is ok, change your script as the following

INSERT INTO(col1, col2, col3....)
Select a.col1,b.col2,c.col3 ................
from a,b,c
where a.id = b.id
and a.id = c.id
4. if still has the problem, try DTS data dump