Post reply

insert statement

  • June 18, 2003 at 1:33 pm

    #81071

    I have the following:

    insert into table select column2, column3,column4, column1 from #table order by column1.

    There are only 5000 record, and the process take forever to run. Anyone got any sugesstion how to speed this thing up a bit.

    thanks

  • June 18, 2003 at 1:59 pm

    #461993

    Check if your table has trigger or other constraints which may cause

    Insert and update performance issue.

    I run a test on a server with 1gig mem and a table that has about 1.5 mil.

    records and it takes only 1 sec.

    MW

    Edited by - mworku on 06/18/2003 2:07:56 PM


    MW

  • June 18, 2003 at 2:26 pm

    #461997

    Check whether there are indexes defined in your table too. If you have clustered index on column1 then you don't need 'order by' in your statement.

    Make sure your tempdb's size is set into a reasonable size to avoid the files growth too frequently during the insert.

  • June 18, 2003 at 8:51 pm

    #462027

    look for any process that is blocking your transaction.

  • June 19, 2003 at 8:00 am

    #462130

    can we see the code?

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 19, 2003 at 8:32 am

    #462142

    Any particular reason you need the "order by" in your insert stmt? Try removing it & see if it affects the performance.

    Allen_Cui makes a good point, maybe your tempdb is growing too frequently. Performance may also be affected if tempdb and your user db are both on the same hard disk, though for 5k rows it shouldn't be that bad.

  • June 19, 2003 at 11:19 am

    #462209

    thanks all for your replies. The order by was causing the problem. Why didn't it effect if I used the select statement alone?

    "select column2, column3,column4, column1 from #table order by column1" run fine <1 second, but with insert statement it like dead in the water. 🙁

  • June 19, 2003 at 1:22 pm

    #462259

    Does the table already have an index based on the same sort criteria? I'm taking a guess, but would say that it was because of the index on the table. When some sort algorithms are fed already sorted data, their performance decreases tremendously (ie syncsort, etc). It would end up doing the equivalent of a table scan for each of the 5000 entries.

    Again, this is just a guess as I have not seen the source code 😉 I have seen this in custom implemented sort routines, though.


    Joe Johnson
    NETDIO,LLC.

  • June 19, 2003 at 1:50 pm

    #462270

    Can you run same query in another SQL Server? Could it be hardware problem?

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

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