Post reply

query performance in reporting services: why is it SO SLOW??

  • July 9, 2007 at 3:52 am

    #173437

    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:

    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

  • July 9, 2007 at 8:05 am

    #718048

    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.

Viewing 2 posts - 1 through 1 (of 1 total)

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