DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Slow first execution (then fast)

  1. #1
    Join Date
    Feb 2005
    Location
    Barcelona, Spain
    Posts
    3

    Slow first execution (then fast)

    I have a stored procedure in SQL Server which is a simple SELECT, with a simple WHERE. The table on which I want to do the SELECT is pretty big, 1.300.000 registers.
    The SELECT is simple...

    SELECT Table.*
    FROM Table
    WHERE Table.Field = @strField

    where @strField is a varChar(12) passed as an argument to the stored procedure. I also have an index created on the table for that field (the field is not the primary key).
    Well... my doubt is, why does the first execution last for 1 minute and 30 seconds (more or less), and all the subsequent executions last 2 seconds? If is a problem with how SQL Server manages the creation of an execution plan or how it uses it... is there a way to prepare this execution plan in advance, so the first time SQL Server executes the SP it doesnt have to do this long work?

    Thank you very much in advance... any tip or complete solution will be greatly appreciated

  2. #2
    Join Date
    Jun 2004
    Location
    Pakistan
    Posts
    292
    the data is stored in server's memory which is why for the second round (if the data in table is not different) it is grabbed from server's memory instead of reopening the connection, execute, and then display.
    new to programming but getting ther

  3. #3
    Join Date
    Feb 2005
    Location
    Barcelona, Spain
    Posts
    3
    But which data? The result? The entire table?
    If it was the result that was stored in memory, the same stored procedure with different parameters would, again, take longer (and it doesn't). And taking the whole table to memory would be inefficient... it is a very big table with more than a million registers.
    Am I right?

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    If you haven't already, you might try running the stored procedure under SQL Query Analyzer and viewing the execution plan to see what's taking so long. See http://msdn.microsoft.com/library/en...nethowto04.asp for more information.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Feb 2005
    Location
    Barcelona, Spain
    Posts
    3
    I have read the article you mention and see that the difference between first and all the other executions is the 'physical reads' variable... the first time I execute the SP this variable is high (a lot of reads from physical disk). All the other executions have this variable 0. Does that mean it has all the 1.300.000 rows in memory? If so, is there any way to initialize this table in memory the first time SQL Server executes? (there is an ugly way, which would be executing the SP the first time SQL starts just to make it bring the table to memory...)

  6. #6
    Join Date
    Nov 2004
    Location
    Huddinge, Sweden
    Posts
    283
    You need to make sure that the index is actually used. If the execution plan contains a table scan, it will read the entire table. This could be due to a number of reasons, such as mismatching data types or bad index statistics.

    This could also be one of those cases where you shouldn't have th PK clustered, but rather your helper index.

    Rune
    Last edited by Rune Bivrin; 02-17-2005 at 04:56 AM.
    If you hit a brick wall, you didn't jump high enough!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center