September 23, 2011 at 1:54 pm
I have a large stored procedure that populates about 6 tables. It creates, populates, and uses many variables, table variables, and temp tables to accomplish this. I need to break it down into separate stored procedures for each one. I was wondering if I create the table variables or temp tables from a "master" stored procedure and just execute the individual 6 from it, would these sub-procedures be able to access these objects? If not, what would be the best way to go? I don't really want to muck up our database with a bunch of tables whose sole purpose is to pose as temporary storage for the procedures, if possible. Also, would the same rules apply for a SQL job? (Create the objects in step 1, execute the stored procedures in step 2, etc?). I know there are several options, ie: table variables, local temp tables #tblLocal, global temp ##tblGlobal, and I think you can just create them in tempdb (a normal table). I would like to know if any of these would work and be recommended.
September 23, 2011 at 2:14 pm
duanecwilson (9/23/2011)
I have a large stored procedure that populates about 6 tables. It creates, populates, and uses many variables, table variables, and temp tables to accomplish this. I need to break it down into separate stored procedures for each one. I was wondering if I create the table variables or temp tables from a "master" stored procedure and just execute the individual 6 from it, would these sub-procedures be able to access these objects? If not, what would be the best way to go? I don't really want to muck up our database with a bunch of tables whose sole purpose is to pose as temporary storage for the procedures, if possible. Also, would the same rules apply for a SQL job? (Create the objects in step 1, execute the stored procedures in step 2, etc?). I know there are several options, ie: table variables, local temp tables #tblLocal, global temp ##tblGlobal, and I think you can just create them in tempdb (a normal table). I would like to know if any of these would work and be recommended.
There are a lot of questions in here. i will do my best to answer some (or most) of them.
A table variable's scope is within the current batch. The only way you would be able to get to this is if you pass it to another procedure. It really is nothing more than an int or varchar (it just holds most complex information).
#tblLocal is a temp table and the scope is that it will only be visible to the current database connection. This could be a problem with your process if you encounter multiple executions at the same time because this object is created in tempdb. In other words, if you have a single connection that establishes a temp table another connection cannot create a temp table with the same name. To prove this open a query window in SSMS and create a #tempTable. Then open another window (keeping the original one open) and try to create another temp table with the same name.
##tblLocal the major difference here is that this temp table is now a global temp table and can be accessed from ANY connection. The same contention issue as the previous is the same.
I would suggest that given what it sounds like you are doing you would be better off to use a table variable and pass them to each stored proc as needed. That will mean you have don't worry about checking for existence of a temp table and coming up with some sort of complicated temp table naming convention to allow passing table information between procedures.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 3:02 pm
Sean,
I think you have part of that wrong, table variable scrope from BOL is "A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in." What this really means is that a table variable is essentially scoped like a regular variable. You CAN pass table variables into sub sprocs but it is NOT dynamic like passing a variable into a sproc. You have to create a type with CREATE TYPE to pass a table variable.
Now this is different from temp tables in that they are available to the connection that created them and ALL sub processess. And they don't have to be passed, the are just "available". What this means is that sproc A creates temp table #t, sproc A calls sproc B, sproc B can use #t without any trouble, and if sproc B calls sproc C then sproc C can access #t as well.
I wouldn't use global temp tables ## without thinking about it a LOT first, concurrency can be a problem.
Clear?
CEWII
September 23, 2011 at 3:10 pm
Elliott Whitlow (9/23/2011)
Sean,I think you have part of that wrong, table variable scrope from BOL is "A table variable behaves like a local variable. It has a well-defined scope. This is the function, stored procedure, or batch that it is declared in." What this really means is that a table variable is essentially scoped like a regular variable. You CAN pass table variables into sub sprocs but it is NOT dynamic like passing a variable into a sproc. You have to create a type with CREATE TYPE to pass a table variable.
Now this is different from temp tables in that they are available to the connection that created them and ALL sub processess. And they don't have to be passed, the are just "available". What this means is that sproc A creates temp table #t, sproc A calls sproc B, sproc B can use #t without any trouble, and if sproc B calls sproc C then sproc C can access #t as well.
I wouldn't use global temp tables ## without thinking about it a LOT first, concurrency can be a problem.
Clear?
CEWII
Elliott - Thanks for clarifying my butchered (or at least partially incomplete) explanation about table variables . I sort of skipped over the part about creating the type to pass. :blush:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2011 at 3:13 pm
no problem..
CEWII
September 23, 2011 at 3:34 pm
First of all let me state that without seeing your T-SQL statements I can not be sure I properly understand you question. That said I will suggest you read this material:
http://sqlserverpedia.com/wiki/Temporary_Objects.
Note that # (local temp table prefix) is uniquely named in Temp DB. Temp DB appends a series of ....... and some numeric value to the temp (#) name to allow it to uniquely identify each local temp table.
In the case of a global (## temp table) Temp DB does not append nor alter the ## table name, however its scope is limited. (See above link for explanation)
Second, remember that the Temp DB is created each time SQL Server is started and is based on the Model Database.
Now if persistence is required .. create a permanent table or tables in the Temp DB just as you would in a regular production DB.
Do your processing in Temp DB. To clean up drop the specific tables you created in Temp DB.
Now if more than one instance of your T-SQL is running you will have to figure out a method of uniquely naming the permanent table(s) created in TempDB.
Either way temp or permanent tables in Temp each should work.
September 24, 2011 at 9:45 am
CELKO (9/23/2011)
That is not a bad code smell; that is a code stink in SQL. Local variables say this is COBOL written in SQL; good SQL nests expressions into one statement. The use of table variables, and temp tables screams COBOL magnetic tape scratch files written in SQL.
Although I very much appreciate the spirit of "Set Based" and "Good" programming that you imply, I have to disagree with the quote above and I guess you knew I would :-).
If a set of data has to be used more than once, for one reason or another, it's frequently much more efficient to quickly isolate just the data you need to work with in a Temp Table. It all has to do with some of the basic rules of performance... work only with the data that you actually need and "touch"/isolate the data as few times as possible to get the job done. It worked very well with COBOL magnetic tape scratch files and it works well with any modern large data system.
An excellent example of this is a self-joined CTE. Although it only needs to be "coded" once, every reference to the CTE in the FROM clause of the outer queries or cascaded CTE's will cause the CTE to execute, in full, additional times just like it would if a View were used. It is frequently much better to execute the statement just once and store the relatively miniscule result set in a temporary structure, such as a Temp Table, than it is to execute the same query more than once.
Even if a set of data is only going to be used once, it is frequently much more performant and much less resource intensive to quickly isolate the "base set" (which may come from more than 1 table) of data into a Temp Table and join to it rather than executing a query with a large number of joins even when proper normalization of all the tables exists. Such "Divide'n'Conquer" methods work very well in all 3 related areas of concern for COBOL, SQL, or any data language and those 3 areas are performance, resource usage, and readability for troubleshooting/modification purposes. Of course, the primary concern of any language is accuracy followed very closely by the other 3.
Even you must agree that "scratch tapes" are sometimes necessary. In Section 4.10 of your famous "Trees and Hierarchies in SQL for Smarties" (Morgan Kaufmann, 2004, ISBN 1-55860-920-2) book, you use a proverbial "COBOL scratch tape" in the form of a table that you call "Stack" to convert an "Adjacency List" to a "Nested Set" hierarchical structure. You also use the very variables and methods you've just condemned in a While Loop to get the job done. Sure, in theory, it's a "one off" task but it does use all of the methods and objects that you've just condemned.
Since those working tables can have keys and constraints, they will perform better than table variables or ad hoc temp tables. But that does not solve the fundamental problem – you are writing COBOL in SQL.
"It Depends". There are times where "all-in-one" queries work very well and other times where either Table Variables or Temp Tables used as "interim storage" do a much better job because of "Divide'n'Conquer". Further, Temp Tables can be custom indexed to meet many needs. Even Table Variables can be indexed (PK and unique indexes created by constraints) provided they are done in the table definition.
The goal in a declarative language is to do it all in one statement and not in a set of process steps. Step 1 writes to scratch tape X, step 2 reads tape X to write scratch tape Y, then step 3, etc.
If such a goal exists as a "Best Practice" recommendation somewhere, it is patently incorrect for all of the reasons previously stated and more. Yes, individual SQL statements should be written in a "Set Based" manner but no one should make the terrible mistake of thinking that "Set Based" or "Good Code" means "All-in-one" statements to accomplish a complex task regardless of the number of tables it may need to address including just 1 table.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2011 at 3:20 pm
Thank you all for the input here. There is a lot to learn and digest. A few comments are in order.
First of all, I didn't include code because I designed it to be a very general question of where these objects can be accessed. I didn't know how many tables because I didn't have the procedure in front of me and it wasn't important. Same answer for virtually any number of tables or objects.
Secondly, I only have limited authority to change things. I don't have sufficient knowledge of the data or processes to change the entire design nor the time to do it. Maybe in phase 2. I am attempting a migration with some changes, but not a complete rewrite. There is a lot of this type of code here and I wanted to understand the scope as it pertains a single connection at a time, as in a procedure executing other procedures. I will attempt to get more fancy or at least more refined later. Most of these procedures I am not writing from scratch.
I did learn a lot from this post and it gives me a number of ideas for the future. I also can see how even the experts don't always agree, even about writing COBOL in SQL.:-D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply