April 29, 2008 at 11:23 am
Matt Minnis (4/29/2008)
What about matrix type calculations?How can they be accelerated?
What I mean is you have a bunch (about a dozen) of tables with charts of tax rates,freight rates, etc.
Each row will have different values from each table and then they get computed for the final result.
We have functions for various lookups and calculations, some will be repeated, some won't
How can you improve this type of process?
I can see your technique for the 1 function, but I'm not sure how it will scale in a large stored proc with lots of function calls and computations.
Pre-compute the values as much as possible, put them in tables, and join to them. Using inline scalar functions makes the proc operate row-by-row, which is inherently slow. Joining to pre-computed values is inherently fast.
If the calculation is too complex for that, then move it out of the database into something that's more efficient at it, or move it to a CLR UDF, which will (if built correctly), be better at it than SQL is.
But it's very, very rare that building tables for these things can't get you the right results, and do it fast.
Let's take tax rates for example. Maybe sales tax.
You could build a function that would calculate sales tax based on state, county, city of merchant, and state, county, city of customer, and then use a function to look this up. Or you could build a table of state-to-state rates, including counties and cities, and then just join to that. Do you end up with a larger table to join to? Yep. Does your update code have to do more work when tax rates are changed or tax laws are modified? Yep. But the cost of those things is tiny compared to the fact that querying by joining to that table will be potentially thousands of times faster than an inline UDF. You have to build the table once. You have to update it every few years (in most cases). You might have to query it a few thousand times per hour. Put your speed on the query, in that case.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 2:37 pm
Confucius247 (4/29/2008)
My attitude with functions is avoid like the plague, as they break the rule of thinking in sets while programming SQL.
Table-Valued Functions.
April 29, 2008 at 10:10 pm
There are definite places where functions are of value, but it is important that they be written carefully to avoid replicating work. In one of the examples listed, the function runs a select statement against the table and then is used inside of a select statement against the same table. It is doing vastly more reads than it needs to. On the other hand, there are places like complex manipulation of a single string that will be done the same way regardless of the strings source can usefully be put into a function with minimal performance hits and increased testing possibilities and code reuse.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
April 30, 2008 at 3:58 am
Mike C (4/29/2008)
Table-Valued Functions.
And if that were what we were talking about...
April 30, 2008 at 7:35 am
Confucius247 (4/30/2008)
Mike C (4/29/2008)
Table-Valued Functions.And if that were what we were talking about...
My attitude with functions is avoid like the plague, as they break the rule of thinking in sets while programming SQL.
I thought we were talking about SQL Server User-Defined Functions, which you "avoid like the plague" because they "break the rule of thinking in sets". Maybe it would help to add a little specificity to generalized blanket assertions about functions, since SQL Server actually supports 4 different types of User-Defined Function?
May 9, 2008 at 3:30 am
This looks fine.
Thanks
Pralin
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply