December 4, 2003 at 7:24 am
I'm looking for some ideas on some queries that I have to write that are in a way, dynamic.
My table structure is this:
TABLE_DATA: id_1 (int), id_2(int), data1(float), data2, data3, data4, data5, data6, .... data40(float)
The data fields are basically yearly values.
I need to perform different calculations that I have setup in multiple UDF's which take the parms:
Creat Function calcOne(@time_period int, @var1 float, @var2 float, ... @var20 float)
where time_period can vary from 1 to 20 (which is basically years). The udf looks at the time_period and only does calcs based upon the number requested.
Now the part that I'm looking for ideas on is that the data that gets passed to the udf. Now the request for the calc can vary, so someone could request a 5 year period starting at the current year (data1 - data5) or they could request calculated data starting 3 years ago (data3 - data17) for 15 years. The udf handles the timeframes properly, so it doesn't look at the parms that are passed beyond the certain year. I also only want to query the columns that are needed and Null the columns that aren't when passing, otherwise, there is data that is being moved that shouldn't be.
Mainly the queries will be searches against the data via sp's, such as for a 3 year query from year 5
CREATE PROCEDURE uspSearchCalcX(@Timeframe int, @FromYear int, @GreaterThan float) AS
--THIS IS WHERE THE DYNAMIC SQL WOULD NEED TO BE CREATED
--SO IF TIMEFRAME IS 3 FromYear 5
SELECT id1, id2
FROM TABLE_DATA
WHERE dbo.calcOne(@Timeframe, data5, data6, data7, null, null, .... null) > = @GreaterThan
Thanks in advance,
Vanelin
December 4, 2003 at 8:10 am
assining Null to the parameters is NOT going to speed up your functions!!!
so as long as the udf uses the Right Values you should be ok with :
dbo.calcOne(@Timeframe, @FromYear, data1, data2, data3, data4, data5, .... data20)
HTH
* Noel
December 5, 2003 at 6:40 am
In assigning nulls, I'm referring to the query that will pass the data to the udf i.e. if you only needed 5 years worth of data calculated then,
SELECT id_1, id_2
FROM TABLE_DATA
WHERE calcOne(5, data1, data2, data3, data4, data5, null, null, null, etc... null)
is faster than
SELECT id_1, id_2
FROM TABLE_DATA
WHERE calcOne(5, data1, data2, data3, data4, data5, data6, data7, ..... data20)
since you are moving a lot less data around.
December 5, 2003 at 7:31 am
quote:
SELECT id_1, id_2FROM TABLE_DATA
WHERE calcOne(5, data1, data2, data3, data4, data5, null, null, null, etc... null)
is faster than
SELECT id_1, id_2
FROM TABLE_DATA
WHERE calcOne(5, data1, data2, data3, data4, data5, data6, data7, ..... data20)
since you are moving a lot less data around.
Ok Let's see
1. Your Function in both cases is using ALL the parameters and so ALL will be pushed in the Stack. It means same number of Push statements
2. If you tested with the Nulls HARDCODED in the query the optimizer will speed up the query but the Nulls WON'T be DYNAMICALLY assigned
3. If you look at the example I posted I am using ONLY one Function and passing @Timeframe AND @FromYear so you could handle all cases in it
4. if you find a way to assign or build the query with dynamic sql it has some other implications and still doen't mean is going to be faster.
I do have 2 questions for you
1. How big is your table
2. What are the calculations you are performing
* Noel
December 5, 2003 at 8:06 am
Ok.. so im slow hehe
December 5, 2003 at 8:06 am
I'm unclear on what results from including more or less parms. I am assuming there is more than one reason for having them (and hence your question regarding nulls), because you said
quote:
The udf handles the timeframes properly, so it doesn't look at the parms that are passed beyond the certain year
December 5, 2003 at 8:46 am
noeld,
I should of been clearer on what I'm am currently trying. I am using dynamic sql to create my search clause and in the query part where I select the columns, selecting a fewer amount of columns when needed is quicker than selecting all columns by default when all columns are not needed. I.e. in a simple query, SELECT id1, id2, dat1, data2 FROM DATA_TABLE is going to return results quicker than SELECT id1, id2, data1, data2, ... data20 FROM DATA_TABLE
You are correct in that I'm not going to get a benefit by passing nulls to the udf to which I understand.
The current size of my table is 40,000 rows
The calculations that I am performing are financial ones.
Thanks for your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply