Post reply

SP slows down to a crawl

  • March 16, 2007 at 9:06 am

    #95173

    We have a stored procedure to process some data, if we run it on a database with 400,000 records, it slows down to a crawl. If we run it against a database with the SAME 400,000 records PLUS another 200,000 records, total 600,000 records, it completes in < 2 min. We can't get an execution plan for the SP with the smaller amount of data because it is timing out. We are at wits end in trying to figure out why this is happening. Anyone have any ideas we may be able to try? I don't want to post it here as it is
    around 1470 lines.

  • March 16, 2007 at 9:43 am

    #695499

    Do both databases have the same indexes?  Have you updated statistics and/or rebuilt indexes on the smaller database recently?

    John

  • March 16, 2007 at 9:44 am

    #695500

    Don't really know but will check it out.

  • March 16, 2007 at 3:09 pm

    #695554

    Goo call, John, it looks like the indexes were the culprit. We reworked

    the SP to not use in-memory temp tables, too.

  • March 19, 2007 at 2:43 am

    #695647

    Also make sure statistics are up to date.  Were they on the same server?  In-memory temp tables should only be used for small amounts of data and can cause performance problems in multi-cpu systems.

Viewing 5 posts - 1 through 4 (of 4 total)

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