Hi,

We have stored proc which select the data from 5 tables by
joining and then insert into one table by insert into.

When i checked the job i noticed that its taking long time
more than 5 hrs. and cmd value in sysprocesses is showing
$ins.

I checked with apps team and they confirmed that they are
dropping the indexes before inserting the data and creating
it after inserting the data. the job is takin more that 4
hrs at cmd $ins.

Pleaes let me know what other things I have to check.

Do I have to separate the code like select the data first in
temptable then insert into user tab. what is the difference
in this as join is creating the worktable in tempdb.

job is populating arround 4 millions records to that
table...

Also sp_sysmon showing above 90% cache hits...
Please suggest to improve this insert

The first thing to do is to check the query plan of the
select statement. The most likely problem is that the select
is using a sub-optimal query plan and that this is really
what is taking the most time.

You can check this by running only the select part of your
SQL request with SET SHOWPLAN and SET STATISTICS IO turned
on.

Post the results here if you want help in analyzing the
query plan (along with the source table structure and index
definitions)

Michael

The first thing to do is to check the query plan of the
select statement. The most likely problem is that the select
is using a sub-optimal query plan and that this is really
what is taking the most time.

You can check this by running only the select part of your
SQL request with SET SHOWPLAN and SET STATISTICS IO turned
on.

Post the results here if you want help in analyzing the
query plan (along with the source table structure and index
definitions)

Michael

Thanks ...

So is that $ins value in sysprocesses.cmd column is because
of select from 5 tables which trying to populate data into
work table?

Please confirm mean while I will concentrate on the showplan
and will update u soon.

What you think about partitioning the table so that insert
will have multiple insertion point?

Thaks for the help

Thanks ...

So is that $ins value in sysprocesses.cmd column is because
of select from 5 tables which trying to populate data into
work table?

Please confirm mean while I will concentrate on the showplan
and will update u soon.

What you think about partitioning the table so that insert
will have multiple insertion point?

Thaks for the help