I have an insert query written in a Stored procedure, that has a select statement for getting the records from 4 different tables and inserting.
The select query has almost 15 columns in the groupby part, b'coz of which the insert takes much longer time. Is there any way to increase the performance of this insert?. Like, can we use a cursor for the select and then loop thr the cursor to insert the records. pls give me suggestions abt how we change this insert statement.
Note : the select statement contains tables that has high volume of data.
It is impossible to tell what the problem might be with the information you give.
The slow part is most likely the SELECT query. You need to check the query plan used by this query to see if you are maybe missing an index, or if there are any other issues.
You should start by running the SELECT query with SET SHOWPLAN ON and SET STATISTICS IO ON to see what it does. This may immediately show some obvious issues - and if not some further digging may be necessary.
Thanx for ur reply maan. But wat I smell is the problem would be b'coz of the group by part in the select query. Can we put the select in a cursor and then loop it and insert into the table. Will this way increases the performance atleast a bit. ???