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

[kingz2000]

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

 

[mrmitchmitchell]

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?

 

[kingz2000]

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???

 

[kingz2000]

THANX A BUNCH!! INDEX WORKED AMAZINGLY!!!

 

[mrmitchmitchell]

Great!
It's always best to avoid the use of <> for performance reasons.