Drastically Different Runtimes in BIDS and SSRS Web Portal

sql-server-2008ssrs

I have a report in a BIDS project that has been deployed to our SSRS Web Portal. On the web, it renders within seconds. In BIDS, it takes upwards of seven minutes. I can see that the query runs against the database quickly – what in the world is BIDS doing that is taking so long?

I have seen this happen in a number of different cases…

Best Answer

If your query is returning a lot of rows across a slow WAN link then you might be seeing a slowdown due to network traffic. I have that class of problem where I'm working now.

If you report server is on the same fast LAN as the database server it would be quicker under these circumstances. This might explain the difference in performance.

Also, if you have virus scanning software running on your PC it could interfere with the local processing in a variety of ways - scanning the .data file etc. Often real-time scanning also hooks into memory allocation and scans data segments of applications. This really slows down managed applications.

Those are a couple of possibilities that come to mind. There may be other causes as well.

Related Solutions

SSRS Data Sources – How to Identify Which Data Sources Are Being Used?

Okay, I got it. Found this link which helped: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/581c7068-0abe-49f9-a1a5-93e94f472641/

I distilled that information to this query:

select
  Catalog.name,
  cat1.Name datasource
from
  Catalog
  join DataSource
    on Catalog.ItemID = DataSource.ItemID
  join Catalog cat1
    on DataSource.Link = cat1.ItemID
where
  Catalog.Type = 2

Hope this helps someone else in the future!

Sql-server – Why does this query become drastically slower when wrapped in a TVF

I isolated the problem to one line in the query. Keeping in mind that the query is 160 lines long, and I'm including the relevant tables either way, if I disable this line from the SELECT clause:

COALESCE(V.Visits, 0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00)

...the run time drops from 63 minutes to five seconds (inlining a CTE has made it slightly faster than the original seven-second query). Including either ACS.AvgClickCost or GAAC.AvgAdCost causes the run time to explode. What makes it especially odd is that these fields come from two subqueries which have, respectively, ten rows and three! They each run in zero seconds when run independently, and with the row counts being so short I would expect the join time to be trivial even using nested loops.

Any guesses as to why this seemingly-harmless calculation would throw off a TVF completely, while it runs very quickly as a stand-alone query?

Best Answer

Related Solutions

SSRS Data Sources – How to Identify Which Data Sources Are Being Used?

Sql-server – Why does this query become drastically slower when wrapped in a TVF

Related Question