query SQL data 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

[robjens]

Try using a DataReader it's fast forward only and is only practicle where don't need to write data (as the name would suggest). Fastest thing in the West. But if your queries are still slow, I'd start to check out possible memory leaks, hardware problems, network lags or database structure and integrity. Flushing it sometime might work. I haven't worked with MS SQL anytime I'm afraid so I don't know the ins and outs of it, but it's safe to presume that eventhough you have TOP 5 set, it would still need to read through all rows for any Where or Order clauses to be effective. How many rows does the table have anyway? Did you cross check it with the web to see what the acceptable standards are on database/table size, structure etc?

If the database is very bloated and you don't wish to tweek it's structure, you could always resort to threading for those processes that take very long.

But anyway I see you log IPs and DateStamps so i assume it's prolly a visitors log table and these tend to get very large if you don't put some restriction on it. I'd suggest implementing some cleaning methods or archive policy to keep the table with most recent entries compact and archive to a different table those rows that are outdated. But i'm flying on assumptions there :)