April 8, 2011 at 9:08 am
I have an algorithm I am converting from a client process in another programming language to run directly on the server as a series of stored procedures or functions. I am able to implement the algorithm just fine on SQL server, but due to my limited knowledge I am not able to take advantage of saving results for use in other algorithms. Liken it to the following situation: the algorithm has a few inputs, some of which are results of another algorithm which in turn uses some of those same inputs. In my client application, I am able to query those reused inputs once and use again and again; in my SQL function, I am not able to figure out a way to do that. This is leading to a situation where the time saved by executing directly on the server is negated by the fact that the server has to repeat a few of the same exact queries, and they can take a good amount of time. The best of both worlds would be if I could run on the server and reuse inputs. Let me get more specific for you.
I have a few calculations.
1) Function/Proc that selects the sum of values meeting a certain criteria
2) Function/Proc that selects the weighted sum of values meeting same certain criteria in #1
3) Function/Proc that calculates a float based on calculating #1 and #2
4) Function/Proc that returns a row containing the result of #1, #2, and #3.
It is a lot more complex than this, which is why I am not posting code just yet. In this setup, #1 and #2 are each called twice - once each by #3 and once each by #4.
In my client app, I am able to save the result of #1 and #2, which each take a good amount of time to compute, and use the results in #3 and #4.
#4 produces a row that corresponds to one month in time. In the end, I need something that will return a result set corresponding to many months.
So, to put this in SQL terms:
Data Calculation SP:
CREATE PROCEDURE OneRow(SelectedMonth datetime) AS BEGIN
Declare @var1 as int
Declare @var2 as int
Declare @var3 as int
set @var1 = (select SUM(RQID) from Requests where RQID < 5)
set @var2 = (select SUM(RQID) from Requests where RQID >= 5)
set @var3 = @var1 + @var2
select @var1 as 'Results1', @var2 as 'Results2', @var3 as 'Results3'
END
Wrapper Procedure (not clear on this part)
CREATE PROCEDURE ListMonths(StartMonth datetime, EndMonth datetime) AS
BEGIN
-- loop through months using some method, such as CTE
-- call OneRow(CurrentMonth)
-- result set goes back
-- client thinks it is a single set
END
I am not the most experienced doing these kind of queries; any help is greatly appreciated!
April 8, 2011 at 2:10 pm
First, you have to remember that SQL is optimized for set-based operations while the language that you are translating from is most likely primarily a procedural language. Translating directly from a procedural language to T-SQL is usually not an efficient approach, but it sounds like that is what you are trying to do.
A big red flag is that you state that parts #1 and #2 have exactly the same criteria, but you have them in separate procedures/functions. If two items have exactly the same criteria, I would look for ways to create them both from a single query. Since you haven't given details, I can't give you the exact formula you need, but it should be something like, which also allows you to calculate #3
SELECT Sum(Field1) AS Simple_Sum
, Sum(Field1*Field2) AS Weighted_Sum
, Sum(Field1)/Sum(Field1*Field2) AS Float_Field
FROM YourTable
WHERE YourCriteria
Since they're all being calculated in the same query, there is no need to store the intermediate results.
For another thing, you mention that you need to loop through months using some method, such as a CTE. A CTE is most definitely NOT A LOOP! It is a SET. With a loop you step through a process one piece at a time, but with a CTE you are processing the whole thing at once. It is the same difference as serial and parallel processing.
Also, you mention that the client thinks that it is a single set. Maybe there is a good reason for that. Maybe it should be a single result set rather than being broken up into smaller pieces. What usually is a FOR EACH loop in procedural languages often best translates as a GROUP BY in T-SQL, especially when there is some sort of aggregate involved.
Since you haven't provided any details about exactly what you are trying to accomplish, I can't be more specific about how to approach your specific problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy