Post reply

Slow queries in reporting services! Bug or feature?

  • July 7, 2008 at 12:24 am

    #190236

    Hi all together,

    in my company we are using reporting services to provide some statistics and reports to our customers. In this special case we wanted to offer a report that displays some information that is grouped by day of week - date - hour. The report is based on a single table that contains around 40 million entries at the moment.

    First the report took 5 minutes to finish and that is to slow in the opinion of our customer. We tested the query in the management studio and saw that collecting the data took around 2 minutes there. We created a suitable index on the table and then the statement was finished in 3 seconds, a good result!

    Now we checked the report again, but it took still more than 2 minutes to finish. In the execution plan table of the reporting services it was clearly visible that most of the time was spent in fetching the data (~2 minutes) and that rendering the report only took some seconds.

    Further investigations showed, that the report does a full table scan to collect the data, even if a perfect index for the query exists. After some more hours of work we added a hint to the query that this special index should be used. Now the report is rendered in around 55 seconds. In the execution plan we see around 45 seconds for collection the data. That is much better.

    But here are the questions:

    1. Why do we have to give a hint to use the index. What is the difference between a query inside the reporting services and inside the management studio.

    2. Why does the query still take so long? It is around 15 times slower than expected.

    3. Any more ideas to speed the process up a little bit more?

  • July 7, 2008 at 2:38 am

    #838728

    Hi again,

    now I am confused a little bit more! I did the tests on Saturday and wanted to repeat them and verify the results today. Today I can remove the index hint from the query and the report has the same speed as with hint. On Sunday the index wad rebuilt automatically. Did the Reporting Services recognize it now!?!

  • July 7, 2008 at 3:47 am

    #838744

    Could be fragmentation, could be out of date statistics. There are reasons for SQL not to use an index that looks perfect.

    Edit: Sounds like it could also be parameter sniffing.

    Could you post the proc, the table design and the indexes that you have on it if you want suggestions on it?

    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

  • July 7, 2008 at 4:39 am

    #838765

    Thanks for your answer GilaMonster!

    I am afraid I can tell you no details, but I think this isn't necessary. The index was built just before the tests on Saturday and no new data was inserted during the tests, so the index was up to date.

    I built it close to the statement the report uses. I took all the attributes from the where clause and made them the columns for the index. I also took the queried values and added them as additional attributes. So the query could take all data from the (non clustered) index.

    I think it was proven that the index works, because the statement speed up in the management studio as expected. I also took a look in the execution plan the management studio displayed and I saw that the index was used as expected.

    I am just wondering why the reporting services need much more time to read the data. What do you mean with parameter sniffing? I do not use a stored procedure in the report, it is a plain SELECT statement.

    Thanks

  • July 8, 2008 at 2:34 am

    #839314

    Rebuilding indexes also updates statistics. My guess is that your statistics weren't up to date and the query optimizer therefore did not use the index.

    Regards,

    Martin.

  • July 8, 2008 at 3:26 am

    #839321

    Hi,

    you are right, but in my opinion it does not explain why it is fast in the Management Studio (3 seconds) and slow in the reporting services (60 seconds). The execution plan displayed in the management studio shows that everything is fine and the index is used.

    At the moment the report server takes always the same time (~60 seconds), with or without hint.

    Regards

    Oliver

  • July 8, 2008 at 3:30 am

    #839322

    Are you using an ODBC connection in Reporting Services rather than the native OLEDB connection? That could be the reason...

  • July 8, 2008 at 3:46 am

    #839324

    Thanks for the good idea, but no, I am using the direct SQL Server connection (OLEDB). I also played around with the other connection types, but it did not change anything.

    Regards

    Oliver

  • July 8, 2008 at 3:51 am

    #839330

    That's pretty weird...

    For consistency I would log into Management Studio with the same user that Reporting Services use (unless you are already using Windows Authentication).

    The only other alternative I can think of is to create a stored proc and call that rather than running the native query through Reporting Services.

    Hope this helps...

  • July 8, 2008 at 4:18 am

    #839346

    Can you run profiler against the server and look at exactly what reporting servces is doing and how long the queries take?

    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

  • July 8, 2008 at 7:05 am

    #839461

    Thanks again for all the good ideas and hints.

    I took a look at the statements with the profiler, but I can see no difference between the statements. I also took a look at the system with Filemon and Procmon and I can see, that the query in the management studio only causes minor drive access and the query in the reporting services causes much more drive access. To me it still looks like doing an index scan vs. a full table access, but I am not sure about it. I will know try to switch of all indexes and look if the queries take the same time...

    Regards

    Oliver

  • July 8, 2008 at 7:54 am

    #839502

    And you see the one from reporting services with duration = 60 sec?

    Can you perhaps post the query?

    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

  • July 8, 2008 at 8:46 am

    #839538

    Hi,

    here are the results. First picture (ManagementStudio.jpg) shows statement and result for direct execution in the management studio. The second picture shows the result for the reporting services. You can clearly see the difference 3,2s vs. 49,5s.

    I also took a look at the index usage statistics and I can see that the index is used by the reporting services... Where are the missing 46 seconds... 😎

    Regards

    Oliver

  • July 8, 2008 at 9:32 am

    #839571

    Hi,

    As you can see from both images the reporting service is using the dynamic SQL. I hope if you create stored procedure will increase the performance.

  • July 8, 2008 at 10:01 am

    #839606

    Hi,

    Yes, you've got it! I am impressed, the report is now finished in 7 seconds! Collecting the data now only takes 0,3s in reporting services, 10 times faster than it is in management studio. I am glad to have this result, but I must confess, that I do not really understand it. Do you have more information about this topic or some good links?

    I think I will use stored procedures more often in the future...

    Regards

    Oliver

Viewing 15 posts - 1 through 15 (of 21 total)

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