Software

Ask something
Discussion Locked

Slow Queries

0 helpful
7 replies

by twelter

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a stored procedure that is taking forever to run during our month end processing. It takes about 18 hours to run. I basically creates a cursor for approximately 1.6 million records. Then it loops through these records and checks if the 'id' exists in another table, which contains about 4.6 million records. If the 'id' doesn't exist and some other criteria exists, a record gets created in the 2nd table. I've already added keys to the appropriate tables as they previously didn't have any. That seemed to only speed it up by about 6 hours. Does anyone have any other ideas or things I should look for to make this more efficient? Thanks!

7 replies

by visintin

have you already tried to 'LEFT OUTER JOIN' instead of using cursor?
If you do:
SELECT *
FROM tbl16m AS T1
LEFT JOIN tbl46m AS T2 ON T1.ID=T2.ID
WHERE T2.otherFiled IS NULL
you'll get from T1 only those records that do NOT exists on T2, and then you can chek and insert...
Good luck
Luigi

by lexacorp

Rather than stepping through the complete 1.6million records, try

select * from table1
left join table2 on table1.id = table2.id
where table2.id = Null

Then do your updates as required on that recordset.

by setanta

Create a view using either the outer join or a (select * from t1 where id not in (select id from t2).
By creating the view the access path to your information will be held permanently in cache.
You can use the view to perform a set update or insert (cursors are row by row and much slower than set operations)
Hope this helps

by web head

Use LEFT OUTER JOIN (as described in the other posts) instead of NOT IN to find rows in tbl16M that don't have corresponding id in tbl4M.

by just

Is this SQL Server? SET NOCOUNT ON at the very beginning of the proc will prevent SQL Server from sending you a count of rows inserted in your 4.6M row table after every insert.

Are you fully qualifying object names? database.owner.table_name will avoid lookups to see where the object lives.

Get rid of the cursor. If you can't, specify a FORWARD_ONLY, READ_ONLY cursor. INSENSITIVE creates a temporary copy of all the data in tempdb and works on it, which would be a real drag. The LEFTOUTER JOIN is much better.

Finally, can you break it up into separate procs, perhaps by date or customer? If there are 4 weeks in the month, and the data won't overlap, add a date range to the stored proc and pass it June 1-7, then June 8-14, and so on. You then have four processes working on your data at once. Only do this if it won't cause one proc to block the other. Good Luck.

by |_|di

try something like

insert into 2ndTable
from
( select a,b,c from 1stTable inner join
anotherTable on 1stTable.id = anotherTable.id
where exists criteria
)

cursors are probably the worst performing solution you can come up with ( although they are very understandable to programmers ). Inner joins only return entries that exist on the join of both tables - in this case matching id's.

by jslezak

What are the 'other criteria'? How many inserts do you expect per run.

Make sure your indexes are constructed well enough to avoid excessive index range scans.

Consider the use of the old-fashioned match/merge sequenctial logic. An old rule of thumb is that if you are going to access more than 10% of a table anyway, full table scans may be faster than indexes.

If your database supports a MINUS variation of UNION, then your primary cursor could be the input table keys minus the targettable keys, leaving you a smaller group of records to run through.

This discussion is closed to new replies.