Post reply

Query takes too much time to fetch the record

  • August 23, 2010 at 4:06 am

    #226104

    Hi,

    I have 40 gb database which have 5 years transaction record.and i cant delete any single reord. now i m facing the problem , when i write the query to fetch the record then query takes too much time.

    i also try to fetch the record after delete the 3 year data from the database. then it takes little time.

    I have studied out on forum that we can increase performance after specify the file group and partitioning.

    I have the file group but still it takes too much time.

  • August 23, 2010 at 4:12 am

    #1210331

    Please post query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    Is there a supporting index? Are statistics up to date?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 23, 2010 at 4:25 am

    #1210337

    If Index is not present then add index as per your requirement. Also, check statistics and if it is old then update it

    For ex. UPDATE STATISTICS tablename;

    You can also use partition by year/month.

    Thanks

  • August 23, 2010 at 4:45 am

    #1210349

    hi,

    Thanks for reply.

    i have post the script file.

  • August 23, 2010 at 4:49 am

    #1210351

    I don't see any indexes in that script. Did you simply forget to script them?

    You don't tell us what query you are running and you don't supply us the sqlplan file.

    How do you expect us to help you?

  • August 23, 2010 at 5:17 am

    #1210367

    hi,

    Sorry,

    Now i have post the table script and the query to fetch the record.

    data is not possible to Post here.

    thanks

  • August 23, 2010 at 5:59 am

    #1210385

    Not asking for data. Asking for the execution plan please.

    Do you really have absolutely no indexes on these tables? If that's the case, that's almost certainly the cause of the poor performance.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 24, 2010 at 9:44 pm

    #1211428

    Hi,

    Yes i have not create the indexes on these tables.

    I m creating the indexes on these tables. then i will check the performance, if same problem exists then i will take your help. thank you for the help.

    thanks

  • August 26, 2010 at 6:47 am

    #1212251

    I love the smell of no-indexed tables in the morning. Smells like . . . MONEY!! 😀 (wondering how many got that great American movie reference)

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 26, 2010 at 8:19 am

    #1212335

    The OP's database infrastructure certainly sounds like its an Apcalypse Now, 40GB and no indexes?

    Sheesh! :w00t:

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

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

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