Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

stored procedure takes too long...any ideas?? 1

Status
Not open for further replies.

kingz2000

Programmer
May 28, 2002
304
DE

Hi,

I've got a procedure which basically calls a group by SQL:

[
select Claim_Ref,max(s1seq) as Last from V_Stam_clm group by claim_ref order by claim_ref asc]

and then with the results I go through all datasets and delete certain datasets depending on their value:

[delete from stam_clm where claim_ref = @Claim_Ref and s1seq <> @Last;]

This takes for ever, well 4.5 hours, due to the number of datasets..I was wondering if there was a better solution in general.

Thanks in advance
 
Try putting an non unique index on (claim_ref, s1seq). Also, your delete could just be where claim_ref = @Claim_Ref and s1seq < @Last; Last is the max value, and you want to delete the ones less than max, correct?

 
yes thats true

..is it better to say 'less than the highest' instead of ''not the highest'??if so, thank you

2nd point ...how do I set a non unique index???
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top