November 18, 2009 at 1:34 pm
I'm doing a little bit of cleaning in some stored procedures. These procedures are like 20 different versions of reports. They use the same Temp Tables in the beginning of the process. I changed those temp tables for views. And they work well.
I have been working with some user table-Valued Functions before. I would like to know if functions will be better instead views or temp tables since the information that I need it’s the same for all the stored procedures.
Experienced advice will be appreciated.
November 18, 2009 at 1:50 pm
Start with, I must, it depends.
I guess it would help if you provided us with an example. If it would mean writing a multi-statement TVF, unless you could guaranttee that the number rows would be no more than a few hundred at the most, maybe. I had (and still do have) multi-statement TVF built over some tables in our ODS database. Joining them together to get a picture of data at a specific point of time was, to say the least, expensive. After 30 minutes, I killed the query. Rewrote those as in-line TVF's (and there were 7 separate functions), the same query completed in 4 minutes, including returning data to SSMS.
However, some multi-statement TVF's may be better as stored procedures. This is especially true if the is the potential for a large volume of data being returned and there is processing that needs to be done to the data that can't be accomplished in-line.
November 18, 2009 at 2:28 pm
My first view joins 3 tables and has some condition to join them. During the process of the Report I have to join that view with a temp table (this one can have between 4 and 20 rows the data comes from a view that join 5 tables). As a result, I have 4 columns. The total of records can be between 400 and 3000 rows.
Select distinct v.Id, BId, IDate, Name
From view_GetData v
Join #Version av on v.id = av.id
Where ClientID = 41
And IDate between '2009-09-28 00:00:00.000' and '2009-11-03 23:59:59.997'
and RDate between '2009-09-28 00:00:00.000' and '2009-11-03 23:59:59.997'
I changed the view and the temp table for functions like this:
insert #BTb
Select ins.*
from fn_GetData(41, '2009-09-28 00:00:00.000', '2009-11-03 23:59:59.997') as ins
cross apply (Select distinct id from fn_GetVersion(41, '2009-09-28 00:00:00.000', '2009-11-03 23:59:59.997')) ad
where ins.id = ad.id
This query it’s the one I will like to use as function, this kind of query is used in all my reports and the total of records in this result can be between 500,000 and 2millions rows.
select b.BID
, b.Id
, stbs.BoxID
, c.[P]
, stbs.TID
, b.IDate
From #BTb b <--- this info is coming from the temp table from the functions result
join CB c with(nolock) on b.BreakId = c.BreakId
and b.Id = c.Id
and c.P = 0
and c.F = 0
join #AP a on c.Id = a.Id
and c.[P] = a.[P]
join dbo.SVersion stbsv with(nolock) on stbsv.ID = b.Id
and stbsv.StartDate <= b.IDate
and stbsv.EndDate >= b.IDate
join dbo.BoxS stbs on c.BoxID = stbs.BoxID
and stbs.[P] = c.[P]
and stbs.VID = stbsv.VID
Since you mention is better to have Stored Procedures for a large volume of data. I guess I can use as many stored procedures I need in a Stored Procedure?
November 19, 2009 at 10:09 am
nothing ?
November 20, 2009 at 12:40 pm
I would recommend that you create both versions, and do a side by side to see.
I personal do not like to use views unless for very specific functions, such as adding filtered indexes to tables. I also (try to) never join to a UDF, but instead I insert directly from a UDF into a table variable, and then join to the table variable. From my environment, with small table sets from the UDF, this seemed to work best, particularly since I typically end up selecting from the same "function" multiple times. I use temp tables for very large data sets, but I try and avoid using them across procedures (though the environment may not agree with this rule).
my 1 1/2 cents.
November 20, 2009 at 12:50 pm
Hey,
Thank for the note. Actually I work the same way. I use temp tables for very large data sets. My procedure creates many temp tables to have Report. I want to use function (instead selects) for small data and insert the info into variable tables.
Sounds like this practice is not bad at all. I was planning to change those temp tables for procedures. So, I run procedures in my main procedure.. It this makes sense?
November 20, 2009 at 12:57 pm
Timothy J Hartford (11/20/2009)
I would recommend that you create both versions, and do a side by side to see.I personal do not like to use views unless for very specific functions, such as adding filtered indexes to tables. I also (try to) never join to a UDF, but instead I insert directly from a UDF into a table variable, and then join to the table variable. From my environment, with small table sets from the UDF, this seemed to work best, particularly since I typically end up selecting from the same "function" multiple times. I use temp tables for very large data sets, but I try and avoid using them across procedures (though the environment may not agree with this rule).
my 1 1/2 cents.
I'd be careful about using table variables. If the number of rows is small, a few hundred, not a big deal. The problems start showing up when the number of row increases. The Query Optimizer always assumes that table variables only have 1 row, wether it actually has 1 row or 1,000,000 rows. As the number of rows increases, you may want to consider using temporary tables instead.
November 20, 2009 at 12:59 pm
In my case I always use Var tables for small amout of data..
Thank you for the note
November 20, 2009 at 2:08 pm
Thanks Lynn. I have always used this method for only small data sets as I have noticed degradation, or at least a lack of improvement, when working with larger data sets. Now I know why. 😎
November 21, 2009 at 8:39 am
MTY-1082557 (11/20/2009)
In my case I always use Var tables for small amout of data..Thank you for the note
This is not necessarily a good thing. You can have a SINGLE ROW SINGLE COLUMN table var that will do an index seek and nested loop lookup when joined to another table that hits 500 Million records and performance will be AWFUL!!! This is because table vars cannot have statistics on the columns. So the optimizer cannot interogate that single row table var and see that the join will hit 50% of the billion row table. Temporary tables do not have that limitation, and you will get the correct table scan/hash (or merge) join plan.
I tell my clients to use table variables in only two circumstances: entities that are never joined (and which don't have large amounts of data that is going to be aggregated), or where recompiles are causing a significant and known problem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 21, 2009 at 2:40 pm
I tend to avoid table variables, if you get it wrong and use a temp table when a variable would of been optimal then it costs you milliseconds. If you get it wrong and use a variable table when a temp variable is optimal it will costs you seconds. Just for fun, i have a pretty heavy routine that is central to our system , this normal runs in 500 ms or so, and converted ALL the temp tables to variables and it took 12secs.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply