Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: Performance puzzle

    I'm puzzled by the following issue that I'm having on my trusty 2000 sql-server.

    The logging table has 26220 records and has a clustered index on the ID column.
    The update is still running but I expect it to finish in, oh, about 20 minutes.
    Code:
    update logging
      set diff = datediff(ms
                          , (select dt from logging l2 where l2.id = l1.id-1)
                          , (select dt from logging l3 where l3.id = l1.id)
                 )
    from logging l1
    When I change the update to:
    Code:
    update logging
      set diff = datediff(ms
                          , (select dt from logging l2 where l2.id = l1.id)
                          , (select dt from logging l3 where l3.id = l1.id)
                 )
    from logging l1
    it's done in 2 seconds.

    I've compared the actual execution plans but I don't see anything out of the ordinary, the clustered index is used.

    I can't believe the -1 puts such a big load on the update, I'm really anxious finding out why.... anyone?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I guess I didn't need that second select in there, I moved from using the ID to the dt column (added a non-unique clustered index).

    Took about ten minutes:
    Code:
    update logging
      set diff = datediff(ms
                          , (select max(l2.dt) from logging l2 where l2.dt < l1.dt)
                          , l1.dt
                 )
    from logging l1
    go
    Personally, I think ten minutes is too long for 26220 records...

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Agreed. The theta join is probably going to be slower than even a cursor\ loop.

    I admit the original observation is perplexing but I have no time to investigate. Are you certain there are no gaps in the numbers? A non clustered index on (id, dt) will speed things up if there are lots of columns or the there are columns with lots of data (say wide string columns).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Posts
    492
    Yeah, no gaps.

    The table originally started with three varchar fields (50, 100 and 2500). Then I added the dt (datetime, not null) and id. Perhaps it's an issue that the ID isn't the first column of the table.

    It's not that excessive (varchar & length), is it?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No, the order of columns in a table is immaterial. The order of columns in an index is critical.

    Those are potentially large rows. 8 bytes for the datetime. 4 for the id. Up to 2650 bytes for the text columns. Rows like that would fit 3 to a page. Of course since these are VARCHAR they might be much smaller.

    Definitely try the non clustered, covering index.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    ...and you missed off the diff column
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Posts
    492

    Talking

    so ehrm, I ran the query again in 7 seconds!

    Guess I'm officially either a noob or a bum, you choose:
    I installed the 2005 Server Mgmt Studio a while back and used it to create the indexes. I didn't see that, when modifying a column adding an index that column isn't selected...

    Thanks Pootle!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm glad that worked. It suggests your varchars do contain a decent amount of data.

    I always use code to create\ modify objects. Here's some of the reasoning why:
    http://www.dbforums.com/6448156-post16.html

    e.g.
    Code:
    CREATE UNIQUE NONCLUSTERED INDEX ix_your_table_id_dt_u_nc
    ON dbo.your_table (id ASC, dt ASC) 
    WITH    (
            FILLFACTOR = 100
            )
    Last edited by pootle flump; 02-19-10 at 12:15.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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