| Author |
Topic |
|
phenreid
Starting Member
29 Posts |
 Posted - 2010-11-16 : 06:48:47
|
| I reference a derived table (DT) in a stored procedure in several different places. I would be nice to establish the DT definition once and re-use it throughout the SP just for code readability, not for performance.I don't want to use table variables because the derived table has too many columns to list -- would be too much typing. DT has a Select * from in it.I don't want to use a view because DT is on-the-fly use in stored procedure only - not worth complexity and overhead of creating/dropping view.I definitely do not need a temp table because DT rows are few and I want to keep everything fast and simple -- goal is code readability only.I don't want to use CTE (WITH statement) because that can only be used in one subsequent statement.I think I am left with dynamic SQL, but I think that is confusing and probably doesn't compile with query plan.In short, I want to make code more readable and saving typing but without performance/complxity sacrifice.Have I covered all the possibilities? I guess I just have to copy/paste in a DT over and over? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-11-16 : 07:07:06
|
| So why don't you want to create a view for it? Whast the problem of having a static view, then you would only have to create it the once and it can stay there forever?Unless of course you are saying the fields and table are different each time you run it?!? |
 |
|
|
TimSman
Posting Yak Master
127 Posts |
Posted - 2010-11-16 : 08:48:36
|
| Why are you using SELECT *? Do you really need all the columns? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-11-16 : 08:58:22
|
| In 2008, you can define a datatype as a table. This is seems to be primarily for the purpose of passing datasets as parameters, but you may be able to adapt it for your purpose as well. Perhaps define the datatype with the columns that you need, and then in your sproc you could just create table variables of that datatype.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-11-16 : 09:01:06
|
quote:
Originally posted by phenreidI definitely do not need a temp table because DT rows are few and I want to keep everything fast and simple -- goal is code readability only.
OK, that is just plain bogus. But, whatever...One other option would be to create a temporary table once at the top of your sproc, and then when you want to create new instances just execute SELECT INTO statements using it as the source.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-16 : 12:19:46
|
quote:
Originally posted by phenreid
<snip>Have I covered all the possibilities? I guess I just have to copy/paste in a DT over and over?
Yes, you'll have to cut-n-paste. Unless you want to change your mind and do something that will be more maintainable and, more than likely, have better performance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-16 : 13:46:34
|
| Worth testing if a local @TEMP table is really significantly slower than an inline derived table I reckon. |
|
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2010-11-16 : 14:45:46
|
| I think you are missing the forest from the trees by openly dismissing views, temp tables and table variables from your options. Saying you won't use these options because readability is your primary goal makes no sense when you are willing to copy and paste your DT code multiple times. It's like saying you don't want to get dust on your shoes by walking in the dirt so instead you'll go through a swamp.Reuse aside, you should be using CTEs over DTs if readability and maintenance are important to you. Remember, you can chain CTEs together like this:WITH cte1AS(SELECT 1 AS Number), cte2AS(SELECT 2 as Number), cte3AS(SELECT Number FROM cte1UNION ALLSELECT Number FROM cte2)SELECT *FROM cte3;The benefit of using a CTE over a derived table is that your statements begin to flow sequentially instead of being indented / nested. I have had to optimize my fair share of DT nested queries and I can assure you that it is far easier to follow a query built with CTEs instead of DTs. Plus, it's far easier to break up a chain of CTEs and capture a resultset in a temp table or table variable if you need to down the road. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2010-11-16 : 15:01:27
|
| Also, you should be extremely cautious anytime you nest anything... including views, CTEs and derived tables. The problem with nesting is that it works great with small recordsets, but as data grows linearly, it takes exponentially more resources to process the query (depending on nesting depth). As such, it is not a best practice to nest queries in any application that expects data to grow (unless you want to walk in one day to see your application performing very poorly).In the situations where you have complex logic that requires deep nesting, temp tables, table variables, physical work tables, and even indexed views are your friends! Break up the logic into segments that limit nesting and/or can be reused. On very small datasets, your performance may not be *as* good as a nested query, but in those cases your query will be fast regardless. On moderate to large datasets, there will be no comparison - you'll see performance improvements orders of magnitude better than a nested query.Don't take my word for it, feel free to do your own performance tests. YMMVEDIT: I completely agree with Tara, in this case I would highly recommend a view. |
|
|
|
phenreid
Starting Member
29 Posts |
Posted - 2010-11-17 : 15:13:53
|
Thanks all for the replies. I consider this fully answered.I'm not too lazy to use a view, I was just trying to see if there was an alternative construct in SQL Server I didn't know about.Below is an example of a DT I need to use repeatedly in one stored procedure because there are several steps to be done with the set and each step must complete successfully before the next one can be underaken. This query looks only at unprocessed records, so there are very few of them (maybe a couple of hundred records or less). ( select a.* from AssnOnLineRequests A join Members M on a.memberid=m.memberid join Assns N on a.AssnID=N.oldid join Memberships S on s.AssnID=n.AssnID and a.memberid=s.memberid where Action='C' and A.Procdate is null and S.Status='A' ) C At present, my stored procedure stands alone. Do I really want to turn above into a view? Then the user has to go back to reference the view to see the SELECT? Since performance is not an issue, copy/paste sounds better to me but that is a matter of style -- modularity vs. having all code in one place. If it were a very complex statement or took advantage of a compiled view, that would be different, of course. I want to increase my readability without increasing my complexity -- for example, what if someone changed the view accidentally, the the sproc could fail.I was hoping I could define this query with the DT name of "C" (or whatever) then refer to that repeatedly throughout my sproc -- same as using the WITH except without restriciton of having to follow that immediately with the code that acts on it.But, I conclude from above that such a construct does not exist within the stored procedure language itself (T-SQL) -- but only with views. So I consider this question answered fully. Thanks again. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-11-17 : 15:26:27
|
| my 2 cents...view are very powerful for encapsulating a business requirement..that view is then used throughout all the sprocs...since they all use the same view, they defacto use the same ruleIf the rule changes, you change the view, and you don't have to touch any other code.The same then is why I insist that developers ONLY use sprocs to get access to data...if the sproc needs to change (and the result sets and parameters don't) They don't have to touch a line of code.It's Development Isolationism MOOBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-17 : 15:47:25
|
"Then the user has to go back to reference the view to see the SELECT?"If I am in a query window, writing an Sproc, then I use something equivalent to:sp_help MyViewto see what the colums are, or sp_helptext MyViewif I need to see the actual definition / logic.I do the same if I want to know what Columns are available to me in a table, or I do a:SELECT TOP 10 * FROM MyTablein which case I see some sample data too  For folk more at home with a GUI there are Right-Click routes to the same, of course.Horses-for-courses, and what-you-are-used-to no doubt ... |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-11-18 : 04:19:00
|
| Adding to that, if the view is in the same database, you can get a definition of columns by holding down Alt-F1 while the object name is highlighted if you don't like typing sp_help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-11-18 : 06:49:34
|
| Alt-F1 is good ... but as it happens I don't actually use "sp_help" as I have my own, short-named, sproc that brings back the data in a variety of my preferred formats suitable for cut & paste into queries. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-11-18 : 07:02:54
|
I contract, so sometimes (read most of the time) not allowed to put my own procs on the servers..  |
|
|
|
phenreid
Starting Member
29 Posts |
Posted - 2010-11-18 : 14:51:42
|
| Thanks all for the conventional wisdom! After all, it's not wise to argue with a SQL goddess.. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
Next Page
|
Reuse Derived Table For Readability