Post reply

Why is my dynamic "insert select" soooooo slow?

  • July 11, 2005 at 3:40 am

    #67212

    Ive got a sproc that dynamically builds a where clause and executes the results using an insert select statement into a temporary table.

    The first time the fullsproc runs after starting the sql server, it takes 30 seconds+ and thereafter only about 5 seconds. In my cut down version the sproc takes 2 seconds using a dynamic select statement.  But when adding insert temp table to the front of the dynamica sql it takes 17 seconds.

    I dont think I can use a temp table var because I need to exec the dynamic SQL which runs in a separate process.

    Can anybody give me a clue how to speed this up? Ive spent a day looking into this already and dont have any more time!

    Thanks for any suggestions

    Andy

     

    DECLARE @sSQL varchar(8000)

    create table #results(

      crm_entity_id    int,

      crm_unique_key   int identity (1,1),

      crm_selected_entity_id   int)

    --Removing insert #results from the next line saves 15 seconds!!!!!

    SET @sSql = 'INSERT #results

      SELECT ec.crm_entity_id, ec.crm_entity_id

      FROM v_entity_contact ec '

    EXEC (@sSql)

    DROP TABLE #results

  • July 11, 2005 at 6:22 am

    #572844

    Just to get a hint of where the processing time is spent:

    What happens to processing time if you use a permanent table results (same definition), instead of a temp table?

    What happens if you execute the same query non-dynamically?

     

  • July 11, 2005 at 6:26 am

    #572846

    Thanks, it looks like youve hit the nail on the head already!

    Ive just (as in just before you posted) executed the query with a non temporary table and it takes 2 seconds.

    I had already executed the query non dynamically and it was always 2 seconds.

    Guess I'll have to use @@spid to create a non temporary table that will only be used by a single user.  Any idea if thats 100% safe?

     

    Thanks again.

    Andy

  • July 11, 2005 at 7:36 am

    #572871

    I think so - I use this trick with @@SPID once in a while. One problem with temp tables is that you can't index them. In your case, put a clustered index on the spid column, otherwise performance is slow with many users. You may of course include other columns in the clustered index, as long as the first column is the spid column.

    I don't know if it is a general problem that performance is bad when you write to a temp table from dynamically generated code.

  • July 11, 2005 at 7:48 am

    #572877

    Slight misunderstanding there.

    My current fix (which worked) was to create a table inside the sproc with the @@spid as part of the table name so it should be treated exactly the same as for the temp table except its not in tempdb. It obviously has an overhead of creating and dropping a table.

    I will try your method of a permanent table with the spid as a column which could be quicker. As results can easily be 20-30k records in a query I thought this would be slower.

    My concern is whether a single query can be preempted by another query while its part way through executing -   in the same way as preemptive multiprocessing on a processor.  and if it can be preempted, could the other process use the same spid??

     

  • July 11, 2005 at 1:17 pm

    #573096

    Two simoultaneous db connections should have different @@SPIDs, I don't think there is a problem here. On the other hand, there may have been an earlier (now terminated) db connection with the same @@SPID, so be sure to clean up, no matter which solution you choose...

    I don't know which is faster (please let me know), but I would always let spid be a column, as I prefer that the table design (of non-temporary tables) is permanent. But that probably just a matter of taste.

  • July 11, 2005 at 1:24 pm

    #573099

    may I ask, why do you need a temp table ?


    * Noel

  • July 12, 2005 at 1:29 am

    #573243

    I need a temp table because the actual query is hugely complicated and runs as 4 parts (to avoid outer join / unique issues).  I realise I could theoretically use derived tables to perform the first part of the query.  I cant use temp variables because the query is exec'ed.

    However, once Ive collated all of the results, I sort them into a new temp table using a user defined sort column with an identity column in order to pull a page of results eg rows 100-200.

    I will spend some time looking at the query again when I get some time, but it took me too long to find the problem so Ive run out of time for now.

    Thanks to everyone for your help.  I will try and reciprocate.

  • July 12, 2005 at 7:22 am

    #573334

    Have you tried the single select approach??? I'm sure it can be the hell out of this insert select thingy...

Viewing 9 posts - 1 through 8 (of 8 total)

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