Speeding up the initial query

Question

Speeding up the initial query

phoenixhawk

SSC Rookie

Points: 26
More actions
December 18, 2007 at 3:09 am

#201863

Hi!
I have a MS SQL 2005 Express Edition installed on a laptop (Windows XP) for demo purposes.
I would like to find out how to make the initial query faster. The first query to the database will always cause the server to "hang" while it increases the RAM usage from less than 100mb to around 700mb and then return the results of the query. This "effect" only happens for the first query, subsequently, the performance is fine.
Is there anyway to "prime" the db server so that when the laptop is started, the server is ready to go (minus the "lag"), so that users will notice this?
All suggestions and feedback is welcome 🙂 Thanks!
pH

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

I don't have a handy copy of express to play with, but you should be able to configure the server's min and max memory settings from management studio express.

In the object exsplorer, right click the servername, select properties and go to the memory tab. There you can se the server's memory allocation

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

...and there's always the possibility of rewriting the query. Can you post it so we can take a look to see if there's something to be done there?

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

SwePeso SSC-Dedicated Points: 39758 More actions · Answer 3

Also the SQL Server needs to create a query plan if it does not already exists in the query cache.

The second time you run the query, both plan and possible data are in the cache and then the query result do take much lesser time to fetch.

N 56°04'39.16"
E 12°55'05.25"

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

You could also just look at firing the query once "just because" as SQL Server starts up....Especially if you increase the minimum RAM as Gila described earlier. wrap it in a SP and use the link below.

Set automatic execution of stored procedure on startup

You might also care to set the initial size of your tempDB to something acceptable - it growing in 1MB increments through auto-grow usually puts a serious damper on performance upfront. I pegged mine (dev laptop) @3GB+2GB for logs, and it increased initial performance substantially.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Steve Jones - SSC Editor SSC Guru Points: 738876 More actions · Answer 5

I'd go with Matt's solution. Even if you change memory, you have to read in the initial data into the data cache, which is slow.

We've done this, created a startup proc that executes any and all procs that users need to "prime" the data cache.

phoenixhawk SSC Rookie Points: 26 More actions · Answer 6

Thanks for the suggestions! :w00t:

Just to recap and elaborate... I have a visualization tied to the database. The query depends on what the user does on the UI, and the related query gets called in this order:

Java Swing UI -> Apache Cayenne (via jTDS) -> MSSQL 2005.

Normally, there is a initial "lag" time of 10 to 15 seconds when the db server "starts up", ie. growing the RAM to around 700mb or so. My queries are 100% read-only queries so its not that demanding... but...

My issue isn't concerned with query optimization, but "database startup optimization". The db must be prepared to service the request and not take its own sweet time. Most of the time you already have a running db server so this slight "start-up" delay is not there, but for demos running on laptops, the delay is there everytime you bootup and customers aren't too impressed with this delay, they think that your app has bad performance. And its no use trying to explain, you know the "don't give me excuses, just get it running FAST/FASTER!"... ah well.. 😛 So after optimizing the java code, all that's left is the db startup delay 🙂

In summary, I think I will the up minimum RAM, configure a bigger temp storage, and run a SP when the OS starts up. I have never dabbled in SPs before, guess there's a first time every time... 😀

Thanks for all the useful advice, really appreciate the help! 😎

(hope this topic helps other people too)

pH