Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2006
    Posts
    7

    Unanswered: Slow stored procedure when called from report

    I'm hoping I can get some help here since I didn't see a forum dedicated to SQL Server Reports. If I missed it, please direct me there and I'll post my question there.

    I have created a stored procedure that runs in about 7 seconds and returns around 27000 rows. However, when I try to use that stored procedure as a dataset in SSR, it doesn't finish in any normal amount of time (I waited over 10 minutes yesterday - today I put a 60 second timeout on it). I have an older version of the report that uses the same stored procedure and it works fine. Since that version, I have tried adding a parameter to the stored procedure, then removed it, and added a filter to the dataset, then removed it. I feel like I messed up something in the report, but I don't know where to look. Short of completely rewriting the report, can anyone give me some ideas as to what might be going wrong here?

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Have you looked at the execution time of the query in sql server PROFILER. Can you tell me how many milliseconds the query takes when executed by the report?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Sep 2006
    Posts
    7
    After some further testing, I now don't think it necessarily has to do with the report. I tried to execute the stored procedure from SQL Server Managment Studio in a query window and it didn't return in an expected amount of time. I stopped it, then dropped the procedure and recreated it. Then when I ran it in the query window, 28185 rows were returned in 6 seconds. I then ran the report. According to the trace I had running, the same number of rows were returned in about 17 seconds.

    The stored procedure contains a select from 3 selects unioned together. Example:
    select col1, col2
    from (
    select col1, col2
    from table1
    union
    select col1, col2
    from table2
    union
    select col1, col2
    from table3
    ) as temp
    where <conditions>

    Could this be causing some kind of leak that is cleaned up by dropping the procedure? If so, how do I clean it up within the procedure itself? If not, what else could be going on?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I suggest you look into parameter sniffing and execution plan recompilations.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Very often it does, its old execution plan. When you dropped and recreated the procedure a new execution plan was generated on the first execution. You need to turn the profiler on, and configure it to capture XML output of the plans. Then you run the procedure from QA and from the report. At this point your profiler will capture the execution plans from both, that you can save as .SQLPLAN, which you can post here.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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