January 8, 2009 at 9:03 am
We are running some tests against a SQL 2005 Test server and We have noticed this anomaly consistently for searches and reporting.
The very first search always take longer irrespective of how small the resultset and then the subsequent searches are a lot faster even if the resultset is huge.
For eg, the resultset is very small and returns on 4 rows but still takes almost 10 seconds. resultset is huge that returns ~7000 rows but only takes 2 seconds.
The application layer uses weblogic.
Is this to do with caching? Is there a workaround for this?
Thanks
January 8, 2009 at 11:37 am
aruram (1/8/2009)
The very first search always take longer irrespective of how small the resultset and then the subsequent searches are a lot faster even if the resultset is huge....
Is this to do with caching?
Probably. This is certainly the footprint of caching.
As to whether there is any way to mediate it, that depends on your tables, indexes, data and the query.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 8, 2009 at 11:43 am
First in regards to what? First of the day - after restarting SQL Server? What?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 8, 2009 at 11:45 am
For testing you generally follow one of the following approaches in order to normalize this otherwise inconsistent behavior:
1) empty the caches before each individual test, or
2) do several iterations of each test and throw out the first one or two results of each set, or
3) do a fixed number of iterations of each test (usually 4 to 10) and average all of the iterations together for each test.
The problem is, that none of these approaches can tell you what your actual cache hit% in production will be and so you cannot really tell whether you will usually get the slow result or the fast result. You usually assume that you will get good cache hit% on those things that are used frequently, but you cannot be sure unless you test a full production load.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply