Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    22

    Unanswered: Performance of a query

    Hi All,

    I am joining three tables each table has got about 1.5 million rows,selecting data from these three tables and inserting into a table,to avoid transaction log issues I am running the query in a batch of size 50,000 rows,it is taking about 5hrs to insert all the 1.5 millions rows.

    All the columns in the where clause have proper indexes,I ran show plan for the query and it is using indexes properly and not doing any table scan.I updated the statistics for all the indexes also.


    query looks some thing like this.

    insert into table d (col1,col2,col3,.............. )
    values (a.col1,b.col2,c.col3 .....................)
    from a,b,c
    where a.id = b.id
    and a.id = c.id
    and a.id in between @minid and @currid

    The @minid starts from 1 and @currid starts from 50000 ,I am running this in a loop, in next iteration @minid will become 50001 and currid 100,000 and so on.

    Any idea why it is taking that much time?.

    Thanks in advance,
    bsr.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by bsr
    where...
    ...and a.id in between @minid and @currid
    "a.id in between"??? Try your copy/paste again...

    Quote Originally Posted by bsr
    Any idea why it is taking that much time?
    Yes:
    Quote Originally Posted by bsr
    ...I am running this in a loop...
    Use set-based operations instead of loops if you want efficiency.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2005
    Posts
    22
    and a.id between @minid and @currid....

    I posted sample query, actually it is nothing do with the syntax,some how query is running very slow..any ideas???

    Thanks in advance
    bsr

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is because you are using loops rather than set-based processing. How many iterations are executed?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    How heavily indexed is the table receiving the inserts? Is it on a RAID-5 disk array? Are you carring blobs to the new table? How many other processes are running? Is blocking occuring on any of the three tables used to generate the source for the data?

    maybe try putting the db into simple recovery mode, do the inserts, then put into full-mode and take a full backup. maybe drop all indexes on the receiving table, do the inserts, and add back the indexes. Maybe migrate to RAID 1+0. Can you use NOLOCK hints to extract data?

    Without more information, or the results of a profile trace, you get general suggestions.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Dec 2005
    Posts
    22
    Thanks a lot tomh53,

    How heavily indexed is the table receiving the inserts?
    A: Receiving table doesn't have any indexes,I am building a index after inserting the data.

    Are you carring blobs to the new table?
    A: No,it doesn't have any blobs (text or images columns).it is a small table with 20 columns,some of the columns are float and some are less than char(50).

    How many other processes are running?
    A: No other process was running at that time except this insert statement.A java program runs against this database but no process was running at that time since that was the first time I was setting up this new database.

    Is blocking occuring on any of the three tables used to generate the source for the data?

    A: No, since my insert statment was the only process running on those three tables there was no blocking or lock contention.

    As I wrote earlier I ran show plan for this insert query it is using indexes properly, not doing any table scan.

    Please advise.

    Thanks in advance
    bsr

  7. #7
    Join Date
    Dec 2004
    Posts
    46

    Performance of a query

    Hows your CPU/IO statistics during the 5 hours. I bet if you run "select * into d from a", it will use the same order of magnitude of time to complete.

  8. #8
    Join Date
    May 2006
    Posts
    16
    1. All id columns in your three tables must be added index
    2. Do not need any loop.
    3. check the excuting time for the query
    Select a.col1,b.col2,c.col3 .....................
    from a,b,c
    where a.id = b.id
    and a.id = c.id
    and a.id in between @minid and @currid

    If excuting time is ok, change your script as the following

    INSERT INTO(col1, col2, col3.....)
    Select a.col1,b.col2,c.col3 .....................
    from a,b,c
    where a.id = b.id
    and a.id = c.id
    4. if still has the problem, try DTS data dump

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •