March 5, 2015 at 3:09 pm
Hi,
I'm hoping there is a better approach, as I'm concerned about possible performance issues. I have a stored procedure that accepts an EquipID and a CoverageDate. The stored procedure calculates a Price for that based on the CoverageDate that was passed in.
I have a second stored procedure with a large data pull where I need to bring in the Price for each EquipID. I can call this stored procedure by passing in the EquipID and CoverageDate to get the appropriate Price, but I'm concerned about possible performance issues when repeatedly calling a stored procedure in your query. What alternate approaches do I have at my disposal?
I realize that isn't much detail. If need be, I can whip up some simple mock sprocs to simulate what I'm referring to.
I greatly appreciate any assistance.
March 5, 2015 at 3:17 pm
If the result set returned by the second procedure is more than a few rows, the performance impact is likely to be large, since you would be processing a row at a time loop.
Depending on how complex the Price calculation is, you might be able to include that logic in the second stored procedure. Without seeing the actual code that is being executed, it is not really possible to say how hard that would be.
March 8, 2015 at 7:16 pm
I'm going to go out on a limb here and suggest that, since calculating a product price probably requires no data updates, that should be done using an in-line Table Valued Function if possible.
That would remove the problematic nature of calling an SP to calculate the price.
I realize that some "calculate price" calculations don't lend themselves to be done in a single line of SQL (have one of those unfortunate monsters myself), but in this case even a TVF (not strictly in-line) should help the situation out.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 9, 2015 at 6:37 am
Assuming no data manipulation, you might also be able to use a scalar function. Due caution, testing, your mileage may vary, etc. But, it'll make the code a lot easier to write and maintain. Test, test, test.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2015 at 5:57 pm
CELKO (3/9/2015)
I'm going to go out on a limb here and suggest that, since calculating a product price probably requires no data updates, that should be done using an in-line Table Valued Function if possible.
I would make that suggestion into a look-up table
https://www.simple-talk.com/sql/t-sql-programming/look-up-tables-in-sql-/
People still think of doing computations in SQL when it is a database language. But it stinks for computations! A million row table is nothing these days.
Look up tables are great as far as they go. The Student's T-test example you gave in that article is highly appropriate, and in many cases when a direct lookup is all that's required it's going to be a lot faster.
There are cases however where that really isn't practical. T-SQL may not be suitable for calculations, but it is perfectly capable of doing them. I have had to write several instances where complex calculations were implemented in a T-SQL function and it whooped seriously on any attempts to do the same in C#.Net. In those cases, it was because the calculation usually involved quite a few table rows, possibly from different tables that achieved an initial lookup with some calculational gymnastics added to complete the algorithm.
The trick is more along the lines of correctly determining where it is best to do something.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply