Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Why would this query run slowly?

    Why could the following query be going slowly (over 30 minutes and running)? The ID is the PK and clustered index on the table. The estimated execution plan is a basic clustered index seek.

    Code:
    select * from recipients where id = 3006392307

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Have you looked at the query in Profiler? Try specifying a column list and see if it speeds up. Try UPDATE STATISTICS also. How fragmented is the index?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    I did UPDATE STATISTICS and restarted the query. That didn't change anything. I don't know how fragmented the index is or how to check that.

    Using a more targeted query produces the same result:
    Code:
    SELECT ID From Recipients WHERE ID = 3006392307
    In SQL Profiler, I see a rapid stream of non-stop
    Lock:Acquired
    Lock:Released

    The ObjectID is that of the "Recipients" table. IndexID=1. I don't know how to interpret the BinaryData.

    What does this mean? Why would this happen? How do I investigate the cause of this further?

    There are no other active processes running on this server at the moment.

    In Enterprise Manager, the process info window says its waiting on
    PAGEIOLATCH_SH 7:1:7766803
    82078 1237688
    Last edited by RogerWilco; 01-21-05 at 01:44.

  4. #4
    Join Date
    Oct 2003
    Posts
    268
    coworker figured it out. My id value was out of range.

    -- This returns instantaneously (empty result set)
    SELECT ID FROM Recipients WHERE ID = 2147483647
    -- This runs indefinitely and chews up the db server.
    SELECT ID FROM Recipients WHERE ID = 2147483648

    That's a very bizarre reaction to an out of range value. I never would have guessed that. Now that I know I can avoid it but that was frustrating.

    thanks for the reply, derrickleggett

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    New to me. Thanks for posting the resolution.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This is indicative of high fragmentation (of course it's a guess here, no other info is given). An attempt to retrieve a record based on the non-existing value of a PK should result i as quick of a response as a similar attempt with existing value. The page presumably containing the key is reached and scanned for a match. If the value is not found it should return right away.

    Did you BULK INSERT the data into the table after TRUNCATE?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2005
    Posts
    3
    did you check the table with dbcc checktable?

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Just out of curiosity, is the ID column in the table defined as just int? If so, then SQL Server was probably busy converting all of the entries in the index from int to bigint, in order to do the comparison between the values. I forget the exact rules, but I do know there is an implicit conversion that happens in such cases.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you kidding?

    It wouldn't alter the predicate?

    How effecient
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    How do you alter the predicate to int? The predicate is too big.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good point...still you would think it would know that there was a conversion issue

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int)
    GO
    
    DECLARE @x bigint
    SELECT @x = 3006392307
    
    INSERT INTO myTable99(Col1) SELECT 1
    
    SELECT * FROM myTable99 WHERE Col1 = 3006392307
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    Just gives me a scan
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    Are you kidding?
    It wouldn't alter the predicate?
    How effecient
    quote of the week (so far)

    Quote Originally Posted by MCrowley
    How do you alter the predicate to int?
    like this --
    Code:
    SELECT ID From Recipients WHERE ID = cast(3006392307 as integer)
    this at least prevents the table scan

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, it prevents a table scan. I will give you that. Problem is, it generates a wee error, which prevents any data from coming back, too ;-)

    Code:
    Server: Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type int.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, i know, ain't that sweet?

    i mean, given that it would be impossible for any data to come back with that value anyway?

    saves you waiting around for nothing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by MCrowley
    Just out of curiosity, is the ID column in the table defined as just int? If so, then SQL Server was probably busy converting all of the entries in the index from int to bigint, in order to do the comparison between the values. I forget the exact rules, but I do know there is an implicit conversion that happens in such cases.
    Yes, the ID column is an INT; and the literal value is out of the 2^31 range. Your explanation is probably right; I thought I triggered a bug and put SQL Server into an infinite loop but your explanation makes more sense. And that would explain why running the same out of range query against an empty table like Brett posted completes instantly (no rows to convert).

Posting Permissions

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