Query Performance diff

Hi @.all,

I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer.

Publishing the report and running it from the report server. The query needs 40x the time as in report designer.

Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval.

Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster.

Thanks for any hints

T

The data tab will only retrieve data, so the report design may be the cause for bad performance. How much time does it take to preview the report in your report designer?

Shyam

|||

I'll try that,

but isn't this time which gets reported in ExecutionLog in Cols TimeProcessing and TimeRendering?

Query parameters not recognized by report designer

No matter how I try to enter an automatic query parameter it just isn't recognized as such. I get missing expression errors from the SQL syntax check and no report variables are generated. There must be something incredibly simple that I am missing....

The most recent query string I've entered is

SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = @.Facility

Thanks for anyone who can help..

I am not sure whether this will help but one thing that works is to create the query as a stored procedure in your database and use the Stored Proceudure option rather than Query Text. You simply type in the stored procedure name (no brackets or parameters) and hit the Execute Query (!) button. Reporting Services then creates the required parameters for you and prompts for their values. You have to run the stored procedure at least once in the dataset tab so that you have some columns to choose when you go to the layout tab. Reporting Services doesn't know what columns are returned until you run the query.

You miss out on the Generic Query Designer in Reporting Services but you don't have to remember or type in the parameter names. It's fast becoming my preferred method.

Dick Campbell

|||

I neglected to mention that we were using an Oracle database because I didn't see why it would make any difference. But coworkers now tell me that the automatic query parameter recognition is a SQL Server specific feature. I don't see why this should be, and if that is the case, it certainly should be documented as such.

For Oracle users, the only solution appears to be to enter the SQL query as an expression like...

="SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = '" & parameters.facility.value & "'"