Slow Query performance in Reporting server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Slow Query performance in Reporting server

Author

Topic

cornelius
Starting Member

11 Posts

Posted - 2007-07-09 : 08:50:31

Hi

I have a report which is ran through reporting services. When I run the report using SQL Server management studio it takes the report 4 seconds to generate and return results. When ran through reporting services with the same parameters it takes more than an hour! And returns the same results.

Any idea how I can reduce the time and cost of the report??

Here's the query (it's just a regular hierarchical query) :
select
count(in_tbl.SLOT_NUMBER),SLOT_NUMBER,in_tbl.COMPUTER_NAME from (select TBL_PRODUCTS.MAC_ADD,
TBL_TEST_RESULTS.SLOT_NUMBER,
TBL_TEST_RESULTS.COMPUTER_NAME
from TBL_PRODUCTS, TBL_PROFILES_IN_SESSION,
TBL_TEST_RESULTS, TBL_TESTS ,all_res_view
where TBL_PRODUCTS.MAC_ADD = TBL_TEST_RESULTS.MAC_ADD
and TBL_PRODUCTS.Profile_ID =
TBL_PROFILES_IN_SESSION.Profile_ID
and TBL_PROFILES_IN_SESSION.Session_ID =
TBL_TEST_RESULTS.Session_ID
and TBL_TESTS.Test_ID = TBL_TEST_RESULTS.test_ID
and all_res_view.table_type =TBL_TESTS.Test_type
and all_res_view.TEST_RES_ID =
TBL_TEST_RESULTS.TEST_RES_ID
and TBL_TEST_RESULTS.TEST_RES_ID in (select
max(in_tr.TEST_RES_ID) from TBL_TEST_RESULTS in_tr,
TBL_TESTS in_ts
where in_ts.Test_ID =
in_tr.test_ID
and in_tr.MAC_ADD
=TBL_TEST_RESULTS.MAC_ADD
group by MAC_ADD)
and all_res_view.test_status = 'Passed'
and (all_res_view.date_inserted >= cast(@startdate as
datetime)and all_res_view.date_inserted <=
cast(@enddate as datetime))
group by table_type,TBL_PRODUCTS.MAC_ADD,
all_res_view.test_status,TBL_TEST_RESULTS.SLOT_NUMBER,
TBL_TEST_RESULTS.COMPUTER_NAME) as in_tbl
group by in_tbl.SLOT_NUMBER,in_tbl.COMPUTER_NAME

cornelius
Starting Member

11 Posts

Posted - 2007-07-09 : 10:03:19

One more thing...
The report returns results in management studio and from the reports services (visual studio), BUT:
After I deploy the project, from the browser I don't get ANY results, but this error message:

An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the data set DataSet1. (rsErrorReadingNextDataRow)
A severe error occurred on the current command. The results, if any, should be discarded. Operation cancelled by user.

Please help...? Any suggestions?
Thank you.

StevenRao
Starting Member

1 Post

Posted - 2008-09-03 : 09:14:40

Hello Cornelius,
Look I am having exactly same kind of problem. Can you please let me know if you get any useful tip?
Thanks
(email: srao@questrade.com)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 02:53:04

Basically what error message suggest is that you have some problem with data retrived from report at a particular point. Thats the reason why its failing in middle with this error message.are you able to view the full result set in your mangement studio query window?

Pete Otholt
Starting Member

1 Post

Posted - 2008-09-16 : 17:17:26

You need to go into Report Manager properties for that report. Look at the bottom of the Properties page and you will see Report Execution Timeout. Set that to never or longer than you anticipate your query to run.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources