May 11, 2012 at 11:29 pm
Hi,
I am having a query containing three unions.
But i want to compulsory execute 1st query
rest two query should be optional based on the
users interest using parameters
because 2nd and 3rd query are time consuming.
But even though data retrieval point of view
it is working very fine but time taken is same
there is no change
for example
/*Parameter Area*/
declare @StartDate as datetime
set @StartDate ='09-MAY-12'
declare @EndDate as datetime
set @EndDate ='09-MAY-12'
declare @With_MonthYear as nvarchar(1)
set @With_MonthYear ='N'
/*Main Query Area*/
select * from a where a.date betwn @startdate and @enddate;
union all --B
select * from a where a.date betwn @startdate and @enddate
and 'Y'=@With_MonthYear;
union all --C
select * from a where a.date betwn @startdate and @enddate
and 'Y'=@With_MonthYear;
i.eee the 2nd n 3rd should only work when user selects 'Y'
but it execute i guess and take same amount of time
approx..more thn 2mins
Please advice
May 12, 2012 at 4:28 am
You're better off having two seperate queries, which are controlled by the parameter you put in.
Something like:-
/*Parameter Area*/
declare @StartDate as datetime
set @StartDate ='09-MAY-12'
declare @EndDate as datetime
set @EndDate ='09-MAY-12'
declare @With_MonthYear as nvarchar(1)
set @With_MonthYear ='N'
/*Main Query Area*/
IF @With_MonthYear = 'Y'
select * from a where a.date betwn @startdate and @enddate
union all --B
select * from a where a.date betwn @startdate and @enddate
union all --C
select * from a where a.date betwn @startdate and @enddate
ELSE
select * from a where a.date betwn @startdate and @enddate
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply