Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: simple select query takes a very long time

    I have a table tblCustTrans which contains
    custid int
    transid int
    startdate datetime
    value int

    the custid, transid and startid are composite primary key.

    the table contains more than 10 million records. Now i want to fetch record for
    select * from tblcusttrans where startdate > = 10/10/2006 10:00:000 and startdate <= 10/10/2006 11:00:000

    This statement is taking more than 2 hours to fetch the data. is there a way to fetch the record with less time

    Regards

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    so does start date have an index on it?

    Even still a 2 million row scan shouldn't take 2 hours

    Is tthere any locking?
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How long does this query take:
    Code:
    select count(*) from tblcusttrans where startdate > = '10/10/2006 10:00:000' and startdate <= '10/10/2006 11:00:000'
    ...and please post actual code in the future. Your example is not syntactically correct, which tends to make us suspicious that we may be wasting our time chasing red herrings.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Jan 2003
    Posts
    41
    I used with nolock and now i am able to get the value in 10 mints. Earlier it took about 2 hours to get the value. (total no of rows in the table is about 7 million rows.

    select * from tblcusttrans with (nolock) where startdate > = CONVERT(DATETIME,'10/10/2006 10:00:000') and startdate <= CONVERT(DATETIME,'10/10/2006 11:00:000')

  5. #5
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    why don't you put indexes on the date columns you have in the table?

    Date columns definitely hit the performance when used in the WHERE clause in any query. Me too had a table which was used extensively and had almost 5-6 million records. This table was being used every now and then. We used to have the time-outs due to table locks and server busy state..

    We tried the indexes on date column and the things looked good then.

    Hope this helps.
    In GOD we believe. Everything else we Test!

Posting Permissions

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