InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:25:21 GMT20http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx[quote][b]Jeff Moden (6/28/2008)[/b][hr][quote]about 300-million rows[/quote]That's kinda what I thought you were going to say.What I was suggesting is that the distance from Point A to Point B is exactly the same as the distance from Point B to Point A... wouldn't that cut your row count about in half if you did it like half a multiplication table for each "group"?[/quote]It's already one-way. The two tables have completely different data in them. No self-join at all. A to B or B to A, it's the same number of calculations in what I'm doing. And I'm already filtering for lat/long outside of the possible distances range, etc. (otherwise, it would be a full cartesian). It's a question of taking a large number of addresses in one list, and finding the four closest addresses in a different list for each one in the first list. There's no overlap in the two lists.Mon, 30 Jun 2008 08:03:02 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx[quote]about 300-million rows[/quote]That's kinda what I thought you were going to say.What I was suggesting is that the distance from Point A to Point B is exactly the same as the distance from Point B to Point A... wouldn't that cut your row count about in half if you did it like half a multiplication table for each "group"?Sat, 28 Jun 2008 12:22:53 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx[quote][b]Jeff Moden (6/26/2008)[/b][hr][quote]The other option was taking each line of the calculation, adding a column to a join table between the two sets, and calculating one column at a time on the whole set of the join table. This was ugly code, and nowhere near so simple to maintain. But it was amazingly fast.[/quote]How many rows did you end up with in that table, Gus? Was it a full Cartesian product or just a triangular join ((2-1)=ABS(1-2))[/quote]Limited cartesian join, depending on business rules. For example, the 5-million to 1,500 join had about 300-million rows. (To be more precise, it was an inner theta join on a range of data, not a full cross join, but definitely many-to-many within pre-specified limits.)Then I broke up each part of a spherical distance calculation into a column. For example, the first thing you calculate is SIN(Latitude1/57.29577951), so I had a SinLat1 column, and had it run that as one calculation; next is SIN(Latitude2/57.29577951), same treatment. Instead of trying to run the whole calculation at once (which I did test), I had it run each piece on each row, and store the data in the column. Doing it as calculated columns ended up being slower in this case, as did trying to run the whole thing at once. Main problem was that 300-million floating point operations takes a LOT of RAM, and there are 12 such operations per distance calculation. That 3.6-billion calculations, and each takes a minimum of 16 bytes (two float data types), plus whatever overhead is used for the actual calculations. Meant it had to push the larger sets onto the hard drive, since I just simply didn't have that much RAM available. (That's a minimum of just about 57 Gig just for this, not including what the OS, SQL Server, etc., need, if I'm calculating it correctly. As opposed to 4.8 Gig per calculation when broken up.)Breaking it up like this meant each calculation one column at a time darn near pegged the RAM, but didn't end up in the swap file/tempdb.Another option would have been doing whole calculations atomically, but on a smaller subset of the rows at a time. That means a cursor/loop, and I just plain don't like messing around with those if I can avoid it.As mentioned, this is a heavy-lifting option that probably doesn't apply in very many cases, but it sure as heck was better than an inline function in this case, which is the point.Fri, 27 Jun 2008 09:05:32 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxHave any of you had a chance to try out some of the stuff Adam Machanic did with Inline TVF's? Just curious, becuase the perf differences are fairly remarkable.I still don't think it gets past all of the circumstances, and I also suspect it's a preciously narrow window where this would be as good or better, but still, interesting read nonetheless....Here's the blog post I had in mind.[url=http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx]http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx[/url]I adapted this to a 10M row test, and the inline BEAT the "straight" group by scenario.Fri, 27 Jun 2008 08:23:48 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxWould you guys share some real examples where you stop using functions in order to increase performance? I mean, could you share the function code and the SQL statement code where you used the proc?I ask this because I also had bad performance using functions, and so I stopped using them. But after more study, I realized I didn't know how to use them. I also know a couple of good and experienced DBAs that were doing my same mistake. After learning the right way to use functions I saw no drop in performance. I'm not saying that the performanced drop just a little bit and it was aceptable. I'm saying that I experienced no drop in performance at all.Thanks a lot,LuizFri, 27 Jun 2008 08:11:33 GMTLuiz-458831http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx[quote][b]TheSQLGuru (6/27/2008)[/b][hr]At each of my last 3 SQL 2005 performance tuning/analysis client gigs I have addressed tremendous performance issues by removing the use of TVFs (and also table variables). It is amazing how inefficient these features can make queries and how frequently they can lead the optimizer to implement an inefficient query! Nested loops on M+ row tables is soooo ugly. :w00t:[/quote]I absolutely agree, Kevin... not so hidden RBAR on steriods!Fri, 27 Jun 2008 08:09:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxAt each of my last 3 SQL 2005 performance tuning/analysis client gigs I have addressed tremendous performance issues by removing the use of TVFs (and also table variables). It is amazing how inefficient these features can make queries and how frequently they can lead the optimizer to implement an inefficient query! Nested loops on M+ row tables is soooo ugly. :w00t:Fri, 27 Jun 2008 07:30:23 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx[quote]The other option was taking each line of the calculation, adding a column to a join table between the two sets, and calculating one column at a time on the whole set of the join table. This was ugly code, and nowhere near so simple to maintain. But it was amazingly fast.[/quote]How many rows did you end up with in that table, Gus? Was it a full Cartesian product or just a triangular join ((2-1)=ABS(1-2))Thu, 26 Jun 2008 17:07:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxI currently use 2 table-valued functions. With those, it's high-performance, many-reuse code, and neither of them uses any table variables. (Inline select functions) That means they can actually access table/index statistics meaningfully, and can be used for complex, set-based solutions. I've tested, and I get a .1% performance increase when I incorporate their code directly into procs instead of calling the function, but that loss in speed in worth it for the ease of code re-use in these two cases.(One of them resolves complex hierarchies, the other splits delimited strings.)In almost all other cases, incorporating the code into a proc directly has been the better option.I don't currently use any multi-select table-value functions. Used to use them a lot, but that was because my Access front-end couldn't deal with procs that did the same thing, but could deal with functions that did. (I never did get a good answer from MS on why that was built that way.) Now, I avoid them as much as possible. They're generally performance killers, and can almost always be replaced with code in the proc and a good temp table.I have a few scalar functions that get used on very small recordsets where the complex calculations can't readily be done in a set-based fashion. All three of these have been tested against multi-step set-based solutions, and found to be better performers, on the size of recordset they get used on.Otherwise, I've found that tearing apart inline scalars into set-based multi-step updates, is generally MUCH faster and better than inline scalars.For example, I have to regularly calculate the distances between all records in two data sets. One set might be anywhere from 5-thousand to 3-million rows, all with latitude and longitude, and the other set might be anywhere from 100 rows to 5000 rows. (Size between the two is generally proportionate, with larger first sets being because of larger second sets.)One option, which was tested, was having an inline scalar that takes the lat and long from list one, and compares it to the lat and long from list two, and gives the distance on a row-by-row basis. It was very convenient and easy to build. And horribly slow.The other option was taking each line of the calculation, adding a column to a join table between the two sets, and calculating one column at a time on the whole set of the join table. This was ugly code, and nowhere near so simple to maintain. But it was amazingly fast.The difference between the two solutions was lists that would take 4-6 hours to run, or lists that would take 30-60 seconds to run, on the largest lists, and 1-2 hours on the smaller versus under 10 seconds.Since as many as five or six of these comparisons need to be done in a day sometimes, the faster solution was necessary. Like I say, ugly, not as easy to read, not as easy to maintain, required adding a whole bunch of columns to an otherwise unnecessary table, and so on. But the speed is worth it.Test various solutions. Try things that may not be obvious. Test them under load and with realistic record set sizes. What works in one case may not work well in another.Thu, 26 Jun 2008 12:48:32 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxThe best thing to do, in most cases, it to avoid any kind of function and any kind of correlated sub-query including Cross Apply. Yeah, I know... I'm going to get the same argument that people make for cursors... sometimes, it's not possible to do otherwise. The problem is, that a lot of folks just don't know what is and what is not possible... ;)Wed, 25 Jun 2008 15:29:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxIt's ultimately going to depend on what the function is and how it does it. Yes, inlining a function can yield some interesting perf gains, but again, they tend to fall into specific circumstances. So - there's not really a decent way to generalize. In both of the cases you mentioned, even if the function execution itself is faster due to inlining, there are lots of opportunities for derived tables to flat out beat this kind of setup (unless the inline function itself is a derived table setup).This is just one of those where you need to test, test and test. And did I mention to test?Wed, 25 Jun 2008 15:13:17 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspx[quote][b]Matt Miller (1/23/2008)[/b][hr]The other place not yet mentioned where this gets even more important to consider is when you use a table-valued function within a CROSS APPLY clause. It's in those cases where it sets up something like a "correlated sub-function". Again - very powerful potential, but will tend to quickly turn into something not at all efficient on the execution plan side.[/quote][quote][b]GilaMonster (1/23/2008)[/b][hr]One of the things to watch for with table-valued functions (especially the multi-statement table valued functions) is that the resultant table, just like a table variable) has no column statistics and no indexes. The optimiser doesn't know how many rows are in the function's result table. That's not an issue for small numbers of rows, but it becomes a very big issue for larger numbers of rows.If the optimiser misjudges the number of rows, it can result in very poor query plans.Note, this is a maybe, possibly, could be, not definitely and always. Test carefully.[/quote]I'm assuming that those and the other statements talk about table functions in general, and I believe they are true for non-Inline table functions.But I'm very interessed to hear what you guys think about Inline Table Functions.if we use inline table functions, would we have performance decrease?As far as I understand, when an SQL statement references an Inline Table Function,the parser and query optimizer analyze the source of both the SQL statement and the Inline Table Function and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the Inline Table Function (please correct me if I'm wrong). If this is true, would we still have any reasons to believe that an Inline Table Function may hurt the performance?Thanks,Luiz.Wed, 25 Jun 2008 13:53:19 GMTLuiz-458831http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxI've bench marked the type of scalar function in Adam's article against correlated sub queries, since they're doing the same type of thing, and they seem to work at about the same efficiency. When the result set is small you don't see much performance problem. When the result set gets into the hundreds or thousands of rows, the performance takes a big dive.I've pretty much always used derived tables when I need to compare a single row to an aggregate value and I usually get good performance.Something like this:SELECT O.CustomerID, OD.ProductID, OD.Quantity, X.MaxQtyFROM Orders OINNER JOIN OrderDetails OD ON O.OrderID = OD.OrderIDINNER JOIN (SELECT ProductID, MAX(Quantity) AS MaxQty FROM Orders O INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID WHERE O.OrderDate BETWEEN '1/1/07' and '1/31/07' GROUP BY OD.ProductID ) AS X ON OD.ProductID = X.ProductIDWHERE O.OrderID BETWEEN '1/1/07' AND '1/31/07'The hard dates are usually just variables in the SProc.I'd have to bench mark a UDF that returns a table against this type of logic to see what happensTodd FifieldThu, 24 Jan 2008 12:31:07 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxThe other place not yet mentioned where this gets even more important to consider is when you use a table-valued function within a CROSS APPLY clause. It's in those cases where it sets up something like a "correlated sub-function". Again - very powerful potential, but will tend to quickly turn into something not at all efficient on the execution plan side.Wed, 23 Jan 2008 08:11:16 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxOne of the things to watch for with table-valued functions (especially the multi-statement table valued functions) is that the resultant table, just like a table variable) has no column statistics and no indexes. The optimiser doesn't know how many rows are in the function's result table. That's not an issue for small numbers of rows, but it becomes a very big issue for larger numbers of rows.If the optimiser misjudges the number of rows, it can result in very poor query plans.Note, this is a maybe, possibly, could be, not definitely and always. Test carefully.Wed, 23 Jan 2008 08:02:32 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxIf you look at the execution plans for the two queries, the scalar function looks like the simpler plan. It simply has a clustered scan and a scalar operation. The scan is apparently the full cost of the execution. The other query shows two scans of the table, a hash match aggregate, and a merge join. Basically the optimizer is able to see into the table valued function and incorporate it's query into the plan for the outer query. Because it's able to generate a plan, it can make good choices in terms of performance. I set up the tests on my copy of AdventureWorks and saw the performance times go from 937ms to 78ms.The reads, according to SQL Trace, go from 374944 to 1253. Again, this has to come from getting an actual plan that seeks out appropriate data & joins it together as opposed to opening the table and checking each and every row, one by one. The only funny thing was, based on estimated plan cost, when the two scripts were run side by side, was 1% and the second plan was 99% even though the actual execution statistics went in exactly the opposite direction.Wed, 23 Jan 2008 07:53:33 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxWhat I derived is:functions can cause Row by row processing and also that would result in a table scan as for each row the function will have to be executed. The analogy which can be taken here is the use of aggregate function in the where clause. Questions: In case of table valued functions how does the processing take place? .... I read that joins can be executed by FunctionReturningTable(parameter) ... In this case the execution plan generated for the SP will include that of Function ... but when the function is in the where clause how does the database engine create a execution plan?how and why in the above example returning a table reduce the logical reads?Wed, 23 Jan 2008 06:37:33 GMTMegha Yadavhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxI think this is a case of buyer beware, your mileage may vary, some assembly required... You get the drift. Scalar functions, as Adam very clearly outlines, unless they're very simple & direct, generally lead to RBAR (row-by-agonizing-row) processing (all due deference to Jeff Moden) because the optimizer can't simply unpack them and add them to the execution plan in a meaningful way. So instead, Adam showed how using a table valued function, in that case, resulted in a query that the optimizer could do something with, hence a radical increase in speed (through a reduction in I/O). It's not the case that ALL table valued functions will perform better than ALL scalar functions, but rather that there are situations where you achieve significant wins, depending on what the different functions are doing. Further, using Adam's example, you probably write a query, no function needed, that would perform even faster than the final UDF.Personall, at my company, we've found functions just too problematic to use them much. Note the keyword, much. We do use them, but we try to very carefully identify places where we can achieve either maintenance & coding improvements at no performance cost, or performance improvements. Other than that, we try to stay away from them because they are very problematic, as Adam outlined.Does that help at all?Wed, 23 Jan 2008 06:01:14 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic446308-360-1.aspxHi All,I read in this particular post (http://sqlblog.com/blogs/adam_machanic/archive/2006/08/04/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post.aspx) that is a scalar valued function is used as table valued function it would perform better ... but I do not really understand it why??Can someone guide me in the right direction ?ThanksWed, 23 Jan 2008 02:50:35 GMTMegha Yadav