0

I have a stored procedure that query one of my tables. To that table I want it to add another column with a values that is calculated by a function.

The function is another query with sub queries that is based on user data in another table.

Now since there are a lot of rows to do calculations on, I figure that the same data is being queried over and over for each row by the function.

I thought I might query the needed data for the function in the stored procedure and keep it in a temp table, and pass that table to the function so the function actually queries the in-memory small table instead of making a query to the real table - that way making it faster, and not having too much IO on my real table.

My question is actually 2 parts:

  1. is what I'm saying even makes sense? is it a good solution that will make it faster and more efficient? or is passing table data to a function comes with a high cost?
  2. how do I pass a table to a function?

Thanks

EDIT:

BTW - it's a scalar function

developer82
  • 13,237
  • 21
  • 88
  • 153
  • depends in part on the function; is it scalar, or a table function? If a table function, is it inline or multi-valued? – Stuart Ainsworth Dec 18 '13 at 18:31
  • sorry, guess I should have mentioned it. it's a scalar function. – developer82 Dec 18 '13 at 18:32
  • You question makes more sense when you think in standard sequential coding ways, SQL thinking often requires other ways -- I can't tell from what you said, but a view or even just a better query that can do the same calculations as the function will probably be better and faster. – Hogan Dec 18 '13 at 18:33
  • I was afraid of that :). Can you rewrite it as an inline TVF or a view? That's the first place I'd start... – Stuart Ainsworth Dec 18 '13 at 18:33
  • @StuartAinsworth - why would it make a difference? this function should make a calculation that returns one number that's returned to the column of the select in the stored procedure. – developer82 Dec 18 '13 at 18:35
  • You might want to review SQL's `OUTER APPLY` functionality, as that was designed for this kind of problem. – Philip Kelley Dec 18 '13 at 18:44
  • 1
    short answer is that a scalar (and a multi-valued TVF) needs to be executed once per row, whereas an inline TVF is set-based. http://stackoverflow.com/questions/4447346/testing-performance-of-scalar-vs-table-valued-functions-in-sql-server – Stuart Ainsworth Dec 18 '13 at 18:46
  • @StuartAinsworth - this is exactly why I was asking (I'm glad you were able to understand my line of though - wasn't sure it would be that clear) - because I need the calculation for each table row, the function executes against the other tables over and over - so that's why I thought of passing an already filtered results that the query in the function should run against. – developer82 Dec 18 '13 at 18:49
  • @StuartAinsworth just had another thought about this - there is something I should probably mention - in the table I would be passing to the function - the function select will filter that table as well - so it's no exactly the same view for each function call. does that make a difference? – developer82 Dec 19 '13 at 05:19

1 Answers1

0

You can use Table-Valued Parameters in a stored procedure or function on MS SQL Server 2008 and up:

http://technet.microsoft.com/en-us/library/bb510489.aspx

alex
  • 1,304
  • 12
  • 15