Post reply

Query Performance

  • August 25, 2003 at 2:46 am

    #81025

    Hi All,

    I tried running two queries , The First one does an insert into a permanent table using some complex join, and then does a select using a self join on the permanent table, the Second one, does an insert into a temporary table in memory (Using DECLARE @myTemp1 TABLE (SCHEMA)), and then does a select using a self join on this temporary table. The Question is I always find the First query that does the select from the permanent table executes faster than the one that executes using the temporary table, Can somebody explain why is it so?. Because I always thought that the memory table will be faster. Thanks for your answers.

  • August 25, 2003 at 9:33 am

    #471260

    There is no particular reason why a SELECT statement's speed would differ dramatically between the two; INSERT statement is quite different, however, and I am surprised that you note the difference in the SELECT, not the INSERT. Generally, there are speed advantages to using tempdb for large INSERTs into work tables, since the operation is only half-logged (enough for recovery purposes), and so should be faster for the temp table. However, you said you are using a TABLE variable, which should perform decently up to ~10K rows (give or take, depending on row length... Could you post the execution plans, and or some info on the number of rows you are working with?

  • August 25, 2003 at 12:35 pm

    #471295

    Seems like one of the issues relating to the SELECT ought to be a combination of the number of rows and the complexity of the WHERE statement. If there are many rows and the permanent table is well indexed, it will often be faster (unless, of course, you have also indexed the memory table )

    Guarddata-

  • August 26, 2003 at 1:55 am

    #471373

    The queru on the permanent table has to do less actions.

    Doesn't need to allocate memory, free up memory etc.

    A good point made before is the size of the memory table. If it is too large why don't you create a Fixed table for it. add an extra field as an identifier or user id and you are ready to go.

    And another good point are the indexes.....

Viewing 4 posts - 1 through 3 (of 3 total)

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