July 8, 2005 at 5:45 pm
Lets say I'm writing a complex stored procedure that goes like this.
CREATE PROCEDURE p_complex
@a INT,
@b-2 INT
AS
select * from table_1
where ( -- just some complex condition below
C <= D
and
(
( X = Y and S = T )
or
W = Z
)
)
OR (
C > D
and
(
( P = Q and M = N )
or
K = L
)
)
select * from table_2
where ( -- the same complex condition
C <= D
and
(
( X = Y and S = T )
or
W = Z
)
)
OR (
C > D
and
(
( P = Q and M = N )
or
K = L
)
)
select * from table_3
where ( -- the same complex condition
C <= D
and
(
( X = Y and S = T )
or
W = Z
)
)
OR (
C > D
and
(
( P = Q and M = N )
or
K = L
)
)
Based on the values of @a and @b-2, I do a select statement on one of three tables but the where clause is the same for each select statement. Is it possible in T-SQL to some kind of macro or #define where I specify like this:
#define MY_CONDITION
(
C <= D
and
(
( X = Y and S = T )
or
W = Z
)
)
OR (
C > D
and
(
( P = Q and M = N )
or
K = L
)
)
Then simply write the stored procedure as
select * from table_1
where MY_CONDITION
select * from table_2
where MY_CONDITION
select * from table_3
where MY_CONDITION
Then in a pre-parsing stage, the query compiler would simply do text substitution of MY_CONDITION with the defined condition. I don't want to use dynamic SQL as the condition actually doesn't change dynamically. I just want something to reduce the size of the code to help it be more readable and if I needed to go change the condition I can do it in one place.
July 8, 2005 at 6:45 pm
This seems to me like this is more of a design problem... If the three tables are containing the same information, why are they separated?
July 8, 2005 at 7:39 pm
I guess my example should have been better. My query could also look like this:
select Number from table_1
where MY_CONDITION
select SUM(Number) from table_1
where MY_CONDITION
select AVG(Number) from table_1
where MY_CONDITION
This is just one more example. But the problem remains the same that I need to repeat the huge condition in every IF portion.
July 8, 2005 at 10:49 pm
If the condition is always exactly the same, you can create a view with that condition included, then you just have to select from that view, flushing the statement in the proc. If the where contains parameters you can use a table function.
July 9, 2005 at 11:22 am
Remi,
Thanks for your suggestion. Could you please post an example of using a view versus using a table function for this purpose?
July 9, 2005 at 12:20 pm
View,the values are hard coded. The table function allows to have them dynamic. Look in books online in you need the syntax for the function.
July 11, 2005 at 2:57 pm
Remi,
Thanks for your response. The actual condition I'm using goes something like this ..
WHERE DATEPART(HOUR, CurDate) > @StartHour
AND DATEPART(MINUTE, CurDate) > @StartMinute
AND DATEPART(HOUR, CurDate) < @EndHour
AND DATEPART(MINUTE, CurDate) > @EndMinute
CurDate is one of the fields of the table that I'm querying from. I tried using a scalar function for this condition with parameters @CurDate, @StartHour, @StartMinute, @EndHour, @EndMinute and returning 1 if the condition was satisfied and 0 if it wasn't. So my condition in the actual query became
WHERE dbo.fn_Valid(CurDate, @StartHour, @StartMinute, @EndHour, @EndMinute) = 1
The query thus becomes more readable and manageable as the condition is used in different IF clauses but using the function really slowed it down when compared to the same query without using the function (from 5 sec to 30 sec). Is it expected that using a scalar function will slow things down?
July 11, 2005 at 3:03 pm
Forget the function and use this where condition in the main proc :
Select col from dbo.MyTable where DateCol >= @DateStart and DateCol <[=] @dateEnd
Place an index on the data column (clustered if it fits the design) and see how fast it runs.
The reason that the query slows with the function is that it is executed once for each line. And if you have a select in the function it's gonna REALLY slow it down.
July 11, 2005 at 3:41 pm
I already have the date limitation in place, which is how we got the query to run in 5 sec as the table is pretty big with about 300 million rows. I was trying to introduce some modularity in the code but it looks like it will cost in terms of performance. I guess i'm gonna have to stick to not using a function but just have the repititive code.
July 11, 2005 at 5:12 pm
All you have to do is put the time constraints in the variables.
Can you post some sample of the constraints so that I can be sure it's possible to do?
July 11, 2005 at 6:29 pm
My final query reads like this. I'm not using any functions and the BETWEEN clauses should filter out most of the records from the large table making it run faster.
CREATE PROCEDURE p_MyProc
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME,
@StartHour INT,
@EndHour INT,
@StartMinute INT,
@EndMinute INT,
@Option INT
AS
IF @Option = 1
SELECT table1.CurDate, SUM(table1.Value)
FROM table1
WHERE CurDate between @StartDate AND @EndDate
AND DATEPART(HOUR, CurDate) > @StartHour
AND DATEPART(MINUTE, CurDate) > @StartMinute
AND DATEPART(HOUR, CurDate) < @EndHour
AND DATEPART(MINUTE, CurDate) < @EndMinute
GROUP BY CurDate
ELSE IF @Option = 2
SELECT table1.CurDate, AVG(table1.Value)
FROM table1
WHERE CurDate between @StartDate AND @EndDate
AND DATEPART(HOUR, CurDate) > @StartHour
AND DATEPART(MINUTE, CurDate) > @StartMinute
AND DATEPART(HOUR, CurDate) < @EndHour
AND DATEPART(MINUTE, CurDate) < @EndMinute
GROUP BY CurDate
ELSE IF @Option = 3
SELECT table1.CurDate, table1.Value
FROM table1
WHERE CurDate between @StartDate AND @EndDate
AND DATEPART(HOUR, CurDate) > @StartHour
AND DATEPART(MINUTE, CurDate) > @StartMinute
AND DATEPART(HOUR, CurDate) < @EndHour
AND DATEPART(MINUTE, CurDate) < @EndMinute
July 11, 2005 at 7:37 pm
Do you need data spanned on multiple days between a small range of hours/minutes for each day (from 2005/01/01 to 2005/01/03 AND only from 12:30 to 15:55 for each day) or only a simple from 2005/01/01 12:30.000 to 2005/01/03 15:55.000??
July 11, 2005 at 7:46 pm
the former case ... from 2005/01/01 to 2005/01/03 AND only from 12:30 to 15:55 for each day ...
July 11, 2005 at 7:50 pm
but the timespan can be large, ie. from 2005/01/01 to 2005/01/03 AND from 12:00am to 11:59pm is also a possibility
July 11, 2005 at 8:21 pm
You can move that select with the parameters in a function.
CREATE FUNCTION dbo.fnName (params list)
RETURNS TABLE
AS
RETURN Select statement with params
GO
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply