Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zion99
Posting Yak Master
141 Posts |
 Posted - 2008-07-11 : 00:41:43
|
| Hi, i have a table with around 300,000 records. i need to do some manipulation to the records before i get the final output. Hence i create 4 temporary tables. If i use a stored proc, i need to create temp tables, if i use function i need to use TABLE variable. As per the matter available online... temp tables are quicker than TABLE variables. can nebody suggest me whether using a function will be fine with 300,000 records?Note: (i need to use a function because it allows u to select a column... as "select col1, col2 from fn_test" and i m using this in my SSIS scripts. i dont think using a stored procedure, i can do the same. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-07-11 : 12:54:15
|
| the big question: do you really need ALL the 300k rows for your task?As for temp tables vs table variables it depends on how many rows you have.. for a couple of hundred rows table variables may be better..beyond that they are both same.. both get created in tempdb.. the advantage of temp tables is you can create indexes which you cannot in temp tables..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2008-07-11 : 15:05:38
|
| In addition to Dinakar's point about the rowcounts I'd want to know what your function is doing. A simple manipulation of data is probably fine but if you're taking the data referring to other sources and doing some more complex things you might have some issues with 300k rows. Also consider whether that table will always be 300k rows or if it's going to grow. By that I mean will you be asking the question about it being ok on a million rows next year.Mike"oh, that monkey is going to pay" |
|
|
|
|
|
|
Stored proc or Functions dilemma (performance)