increase the speed of the report

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

increase the speed of the report

Author

Topic

urpalshu
Starting Member

21 Posts

Posted - 2006-04-12 : 15:51:30

Hello,

I am working on a report in SQL Server Reporting Services 2000.

[CODE]
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = case when @callDate = '' then [Call Day] else @callDate end
[/CODE]

>> I have a promt for the user to enter the date.
>> If the user does not enter any date, then the report will show all the first 200 records, without the where condition.
>> This query is running too slow.

To increase the speed of the report , could somebody help me build the where clause only when something is in the filters ?

Thank you,

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-04-12 : 16:00:53

What are the datatypes of [call day] and @CallDate?

urpalshu
Starting Member

21 Posts

Posted - 2006-04-12 : 16:09:57

quote:
Originally posted by jsmith8858

What are the datatypes of [call day] and @CallDate?

Data Type is VARCHAR.

I guess this is something to do with the Reporting Services Design.

Thank you,

urpalshu
Starting Member

21 Posts

Posted - 2006-04-12 : 17:15:45

quote:
Originally posted by jsmith8858

What are the datatypes of [call day] and @CallDate?

I changed the CallDate datatype to datetime.

my query looks like this now,

if @callDate IS NULL AND @destNbr = '' AND @origNbr = '' AND @btn = '' AND @invoiceNbr = '' AND @destMobile = ''
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
END
ELSE
BEGIN
SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @callDate OR @callDate IS NULL)
AND [Dest Nbr] = case when @destNbr = '' then [Dest Nbr] else @destNbr end
AND [Orig Nbr] = case when @origNbr = '' then [Orig Nbr] else @origNbr end
AND [BTN] = case when @btn = '' then [BTN] else @btn end
AND [invoice nbr] = case when @invoiceNbr = '' then [invoice nbr] else @invoiceNbr end
AND [dest mobile] = case when @destMobile = '' then [dest mobile] else @destMobile end
END

Can this query be more efficiantly written, to improve the speed.
Build the where clause only when something is there in the filters.

Thank you

urpalshu
Starting Member

21 Posts

Posted - 2006-04-18 : 13:22:06

quote:
Originally posted by urpalshu

[quote]Originally posted by jsmith8858

What are the datatypes of [call day] and @CallDate?

="SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call "

& IIF(Parameters!callDate.Value = "" AND Parameters!destNbr.Value = "" AND Parameters!origNbr.Value = "" AND Parameters!btn.Value = "" AND Parameters!invoiceNbr.Value = "" AND Parameters!destMobile.Value = "", "", " WHERE 1=1 ")

& IIF(Parameters!callDate.Value = "", "", " AND [Call Day] = @callDate ")

& IIF(Parameters!destNbr.Value = "", "", " AND [Dest Nbr] = @destNbr ")

& IIF(Parameters!origNbr.Value = "", "", " AND [Orig Nbr] = @origNbr ")

& IIF(Parameters!btn.Value = "", "", " AND [BTN] = @btn ")

& IIF(Parameters!invoiceNbr.Value = "", "", " AND [invoice nbr] = @invoiceNbr ")

& IIF(Parameters!destMobile.Value = "", "", " AND [dest mobile] = @destMobile ")

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources