Slow fully optimized SQL statement

[StewartUK]

I have the following SQL statement:

SELECT ContactPic ;
  FROM Donation ;
  WHERE ContactPic = Contactik AND DonDate = Dated ;
  AND WhyFk = whycode AND AlloFk = Allocode ;
INTO CURSOR CheckForDuplicateDon

There are just under 550000 records in the table. Showplan states that the query is partially optimized (it was fully optimized until I removed the index on DELETED() - there are no deleted records). However the query takes 3.8 seconds to run.

I thought the query would be almost instant (it is the 2nd time I run the same query). Can anyone advise me if they would have expected a faster execution and perhaps a way to make it faster, or if I am being overly optimistic?

Thanks,

Stewart

 

[JimWWinter]

One thing that might speed it up is changing the where clause to a single comparison by concatenating all the fields and creating an index that also concatenates all the fields.

Regards,
Jim

 

[StewartUK]

Jim, Mike - thanks for your replies.

I'm certainly reluctant to add another index to this table - there are 10 already.

The SQL is only returning 1 or 2 results.

Interestingly, setting DELETED OFF made the query run a little faster. Unfortuntely I can't guarantee that there will be no deleted records.

Not using it in a grid, it's for ensuring that a user hasn't created a duplicate record.

I'll live with what I've got.

Stewart