slow query

[hawkes1967]

I have a query which responds immediately when run however if I add an order by clause it takes 40 seconds. Below is the query with the order by clause
SELECT distinct Licenseplate, platetypecode.platetypecode, platetypecode.platetypecodeid
FROM Ticket INNER JOIN PlateTypeCode
ON PlateTypeCode.PlateTypeCodeID = Ticket.PlateTypeCodeID
ORDER BY licenseplate

The Ticket table contains approx. 11,000 records. I have created a nonclustered index for the licenseplate field, a 7 char varchar field.
Any suggestions for speeding up the query?

[rmiao]

Have index on PlateTypeCodeID? Enable show query plan to see how the query works.

[hawkes1967]

yes, I have an index on the platetypecodeid field. I enabled the show query execution plan and it has sort cost 39%, hash match root cost 23% and clustered index scan 28%. If I remove the order by clause the records are returned immediately, once I put the order by clause back it takes 40 seconds.

[rmiao]

By default, sql server doesn't take nonclustered index into account when sorting by a column with a nonclustered index. Try create clustered index on licenseplate if possible.

[skhanal]

You can create a clustered index on licenseplate column and see if that improves.

If you can't change the clustered index, compare the plans you get with and without order by. If it is using a different set of index, you can use hints to force the plan.

[bheiser]

Hi,

You might try sorting the records from a derived table. This may force a different, and possibly better execution plan. Let me know if it helps! (I guess you can let me know if it doesn't too)

select * from
(SELECT distinct Licenseplate, platetypecode.platetypecode, platetypecode.platetypecodeid
FROM Ticket INNER JOIN PlateTypeCode
ON PlateTypeCode.PlateTypeCodeID = Ticket.PlateTypeCodeID) d
ORDER BY d.licenseplate

[hawkes1967]

Thanks for all the replies. I used the suggestion to make the licenseplate field a clustered index and droping the primary key field as a clustered index. Using a derived table didn't change the speed unfortunately. Thanks again.