increase the speed of the report

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 apromt 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.
>> 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,

This should do what you want:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

|||

I tried using

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

>> Output is blank.

>> I need the top 200 records to be returned by default. If the @.callday is blank.

Thank you

|||

urpalshu wrote:

I tried using

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE [Call Day] = @.callDate

>> Output is blank.

>> I need the top 200 records to be returned by default. If the @.callday is blank.

Thank you

You need to use boolean logic here, you stated in some cases no date is entered.

By the way I hope call day is of type date time...

In any even if you sometimes have a value for @.callDate and other times it is null the sproc should be this:

@.callDate datetime= NULL --do you need a default ?

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)

Make sure Call Day is of the right type (datetime). If it is varchar, you will need to change the data type. You can strip the day time month year using various date functions.

Jon

|||

Thank you,

I changed the Call Date to datetime,

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 we improve the speed on this query?

Please help

|||

This is a quite common type of query requirement when coding queries that do searches.

What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...

Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).

If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...

sluggy

|||

sluggy wrote:

This is a quite common type of query requirement when coding queries that do searches.

What you want to do is replicate the exact same logic you used for the @.callDate parameter for all the other parameters too:

SELECT TOP 200 * FROM necc.dbo.vw_rop_report_profit_per_call
WHERE ([Call Day] = @.callDate OR @.callDate IS NULL)
AND (@.destNbr is null or @.destNbr = '' or [Dest Nbr] = @.destNbr)
AND (@.origNbr is null or @.origNbr = '' or [Orig Nbr] = @.origNbr)
... etc...

Notice the bracketing to group the expressions (it won't work correctly without it), and the ordering of the expressions: we are relying on shortcutting to ensure that the field vs. parameter check is only evaluated if the parameter contains a legitimate value (ie isn't empty).

If you do it this way then you can also get rid of the outer if @.callDate is null and @.destNbr = '' etc...

sluggy

Its amazing how people dont listen, did I not just post this like the third post ?

|||

You sure did, but the original poster was still stuck, so i expanded upon it for him. You will see i mentioned "what he had already done with the @.callDate parameter" - this acknowledges your post.

But this is not the place for a flame war, so let it go.

sluggy