Query to SQL server slow

[qfchen]

I had tried to use SQL statement to retrieve data record from SQL server, but when the database grow, the query speed getting slower. I just want to get most recent 5 records, any way to make it fast? Below is the SQL

SELECT Top (5) EventNumber, IP, DateTime, DateTimeStamp, EsdMaxLog, EsdMaxLin, EsdMaxAbs, EsdCntAll, EsdCntLast, SvMax, SvCurrent, EsdLimLog, EsdLimLin,
EsdLimAbs, Distance, SvRange, CdmFilter
FROM EM_AWARE
WHERE (DateTimeStamp > @Param1) AND (DateTimeStamp < @Param2)
ORDER BY DateTimeStamp DESC

[code green]

I think it is the ORDER BY DateTimeStamp DESC which is the killer.
Remember every single record that fits the WHERE condition is being ordered then only the TOP 5 returned.

First thing I would ensure DateTimeStamp has an INDEX.
if you can add another WHERE filter to reduce the records futher it will help.

As you only want the latest five records could you employ an ID field (auto-increment), then query the five highest ID numbers?

[debasisdas]

Try using BETWEEN clause along with the previous suggestion.

[ck9663]

Just be careful with BETWEEN, it's INclusive.

Happy Coding!!!

~~ CK