|
|
You're in the
sql server programming
group:Naming of temporary tables fro sessions
sql server programming:
Hi Guys,
For the creation of a final output tbale for a report , I need to extract data from three tables temporarily. But to run the programme (written in VB) in a multiuser environment i need saperate tables for each session. How I can do this Thanks in Advance.
Temprry tables are specific to session if they are prefixed with single #.
(double ## referes to global). if you want to name uniquely then concatenate with sesson specific system functions like SUSER_SNAME( check BOL for the right one). and execute dynamically. -- Regards R.D --Knowledge gets doubled when shared [quoted text, click to view] "Subhash" wrote:
> Hi Guys, > For the creation of a final output tbale for a report , I need to extract > data from three tables temporarily. But to run the programme (written in VB) > in a multiuser environment i need saperate tables for each session. How I can > do this > > Thanks in Advance.
You can use a temporary table (#results) for instance.
The table will exist in while in the scope you created it, and will be unique to your session. create table #result ( mycol int not null ) or select ... into #result from yourtable... Tony. -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials [quoted text, click to view] "Subhash" <Subhash@discussions.microsoft.com> wrote in message news:FF7F0E39-7F4A-46DB-B7BA-5061EA2CBFA1@microsoft.com... > Hi Guys, > For the creation of a final output tbale for a report , I need to > extract > data from three tables temporarily. But to run the programme (written in > VB) > in a multiuser environment i need saperate tables for each session. How I > can > do this > > Thanks in Advance. > Subhash
Just how is a temp table not a set orientated approach.
You push the results [set] from a query [set] into a temporary storage [set] and are then able to query that. Sometimes your answer just baffle me. -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials [quoted text, click to view] "--CELKO--" <jcelko212@earthlink.net> wrote in message news:1130090862.805534.138100@f14g2000cwb.googlegroups.com... >>> , I need to extract data from three tables temporarily. << > > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, data types, etc. in > your schema are. Sample data is also a good idea, along with clear > specifications. It is very hard to debug code when you do not let us > see it. > > My guess, based on nothing you posted,, you would use a derived table > or a VIEW. Temp tables usually mimic scratch tapes rather than > implement a set oriented approach. >
[quoted text, click to view]
>> , I need to extract data from three tables temporarily. << Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. My guess, based on nothing you posted,, you would use a derived table or a VIEW. Temp tables usually mimic scratch tapes rather than implement a set oriented approach.
[quoted text, click to view]
>> Just how is a temp table not a set orientated approach? << Did you ever write code for mag tape systems? One procedure writes intermediate results to a scratch tape, which feeds it to the next step and so on down the pipeline until you get a final result. The modules have procedural coupling. As opposed to a single module that consructs a result set all at once. Almost all temp tables mimic a mag tape system design. You see things like this: BEGIN SELECT a, b, c, d INTO #scratch_tape FROM Sales WHERE .. SELECT a, x, y, z FROM Inventory WHERE a IN (SELECT a FROM #scratch_tape); END; Instead of: SELECT a, b, c, d FROM Inventory WHERE a IN (SELECT a, b, c, d FROM Sales WHERE ..); Or you see lots of cursors with temp tables.
For local temp tables, each user gets their own copy for the session.
Say you create the following: CREATE TABLE myTemp (id INT IDENTITY(1, 1) NOT NULL ,name VARCHAR(30) NOT NULL ) You can then query it like this: SELECT * FROM myTemp If you execute the following, you will see that the actual name has been uniquely assigned to the session even though you do not qualify the useage in this manner. SELECT * FROM tempdb.dbo.sysobjects WITH(NOLOCK) WHERE [name] LIKE 'myTemp%' One drawback here is constraints. Even though the table name itself has been "sessionized," constraint names are not and must be uniquely generated. In cases like these, you can create the constraint without naming it. The system will generate the name for you, and since the system uses NEWID in the definition, you will not run into naming conflicts. Hope this helps. Sincerely, Anthony Thomas -- [quoted text, click to view] "Subhash" <Subhash@discussions.microsoft.com> wrote in message news:FF7F0E39-7F4A-46DB-B7BA-5061EA2CBFA1@microsoft.com... > Hi Guys, > For the creation of a final output tbale for a report , I need to extract > data from three tables temporarily. But to run the programme (written in VB) > in a multiuser environment i need saperate tables for each session. How I can > do this > > Thanks in Advance. > Subhash
Yes, in the early part of my career I programmed using PL/1 with eith DL/1
or VSAM as the store but that was 15 years ago and is un-important now. Temporary tables are used for interim steps in order to simplfy complex queries, it is better to simply into a couple of steps then have a 100 line complex query with lots of sub-queries that may well perform badly. So you advocate a single complex query instead of simplicity? -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials [quoted text, click to view] "--CELKO--" <jcelko212@earthlink.net> wrote in message news:1130095538.907489.215010@o13g2000cwo.googlegroups.com... >>> Just how is a temp table not a set orientated approach? << > > Did you ever write code for mag tape systems? One procedure writes > intermediate results to a scratch tape, which feeds it to the next step > and so on down the pipeline until you get a final result. The modules > have procedural coupling. As opposed to a single module that consructs > a result set all at once. > > Almost all temp tables mimic a mag tape system design. You see things > like this: > > BEGIN > SELECT a, b, c, d > INTO #scratch_tape > FROM Sales > WHERE .. > > SELECT a, x, y, z > FROM Inventory > WHERE a IN (SELECT a FROM #scratch_tape); > END; > > Instead of: > > SELECT a, b, c, d > FROM Inventory > WHERE a IN > (SELECT a, b, c, d > FROM Sales > WHERE ..); > > Or you see lots of cursors with temp tables. >
Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
[quoted text, click to view] > Yes, in the early part of my career I programmed using PL/1 with eith DL/1 > or VSAM as the store but that was 15 years ago and is un-important now. > > Temporary tables are used for interim steps in order to simplfy complex > queries, it is better to simply into a couple of steps then have a 100 > line complex query with lots of sub-queries that may well perform > badly. > > So you advocate a single complex query instead of simplicity? From a development and maintenance point of view, breaking up a complex task by storing intermediate results into temp tables or table variables may be a good thing. For performance, it may be detrimnetal. One problem with temp tables in SQL 2000 is that they often lead to recompiles, which often cost more than the gains you get in better query plans. With table variables you escape this, but then you may get the opposite effect: since the procedure did not recompile, you got an inferior query plan, since SQL Server only guess what was in that table variable. I've been doing some performance monitoring on our system lately, and one procedure I rewrote had a base INSERT into a temp table, and then there were a couple of updates. I replaced those, by making the INSERT statement more complex - and certainly it looks deterring now!. But since the recompiles went away, the response time dropped from five seconds to sub-second. Since this is a procedure that is run from an interactive function frequently, I can tell you that the users noticed! Of course, your mileage may vary. Sometimes using a temp table for intermediate results is better for performance, because the optimizer goes astray. Also, notice this: WITH CTE (....) SELECT ... FROM CTE JOIN CTE ... JOIN CTE ... CTE = Common Table Expression, a new feature in SQL 2005. Syntactically it looks neat, but the CTE is computed each it appears in the query, so here a temp table may be better. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Hi Erland,
I usually convert any temporary tables i see into derived ones and it usually gives a big performance benefit purely because the optimiser can give me a better plan. There are still occaisions where that doesn't happen and I need to use a temporary table. You are correct, i tend to use a table variable instead because its usually used as the source and no indexing is needed. You do need to watch recompiles which is why you should put any CREATE TABLE # at the start of your stored procedure. But recompiles usually only tend to affect oltp queries that require quick response and are being executed by multiple concurrent connections, you get blocking because of the recompile lock placed on the plan. I'm not fond of CTE's because the whole query is executed, i'm thinking using them for paging - i a bit disappointed. -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns96F92E8490FEYazorman@127.0.0.1... > Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes: >> Yes, in the early part of my career I programmed using PL/1 with eith >> DL/1 >> or VSAM as the store but that was 15 years ago and is un-important now. >> >> Temporary tables are used for interim steps in order to simplfy complex >> queries, it is better to simply into a couple of steps then have a 100 >> line complex query with lots of sub-queries that may well perform >> badly. >> >> So you advocate a single complex query instead of simplicity? > > From a development and maintenance point of view, breaking up a complex > task by storing intermediate results into temp tables or table variables > may be a good thing. For performance, it may be detrimnetal. > > One problem with temp tables in SQL 2000 is that they often lead to > recompiles, which often cost more than the gains you get in better > query plans. With table variables you escape this, but then you may get > the opposite effect: since the procedure did not recompile, you got an > inferior query plan, since SQL Server only guess what was in that table > variable. > > I've been doing some performance monitoring on our system lately, and > one procedure I rewrote had a base INSERT into a temp table, and then > there were a couple of updates. I replaced those, by making the INSERT > statement more complex - and certainly it looks deterring now!. But since > the recompiles went away, the response time dropped from five seconds > to sub-second. Since this is a procedure that is run from an interactive > function frequently, I can tell you that the users noticed! > > Of course, your mileage may vary. Sometimes using a temp table for > intermediate results is better for performance, because the optimizer > goes astray. Also, notice this: > > WITH CTE (....) > SELECT ... > FROM CTE > JOIN CTE ... > JOIN CTE ... > > CTE = Common Table Expression, a new feature in SQL 2005. Syntactically > it looks neat, but the CTE is computed each it appears in the query, so > here a temp table may be better. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >
Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes:
[quoted text, click to view] > You do need to watch recompiles which is why you should put any CREATE > TABLE # at the start of your stored procedure. > > But recompiles usually only tend to affect oltp queries that require > quick response and are being executed by multiple concurrent > connections, you get blocking because of the recompile lock placed on > the plan. It's a very devilish game. In the performance quest that I mentioned, I found a stored procedure that only ran once per day - but then it ran for one hour and a half, and that was a single SELECT that inolved a table variable. When I research it, I found that the table variable was likely to hold 250000 rows. So my fix to this complicated procedure was to make that table variable a temp table. It's now down on ten seconds. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Hi Anthony,
They don't actually work like that. Under the covers they are actually temporary tables but without logging (they are not affected by begin tran/commit/rollback), SQL Server can bind them to the plan where # tables it can't. True, temporary tables are logged, but not as much logging as a normal database; you don't need redo records to be logged in tempdb because its cleared down on each restart. Tony. -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials [quoted text, click to view] "Anthony Thomas" <ALThomas@kc.rr.com> wrote in message news:uty%2358i2FHA.3420@TK2MSFTNGP15.phx.gbl... > So you know, as all variables, even table variables must be stored in > memory. If large enough to no longer be able to completely live in > memory, > SQL Server will create a tempdb table structure to back the variable. > Then > you get into a case of structures on structures. > > By creating the temp table structure directly, you bypassed the additional > overhead and management. That is the drawback of table variables. > > Sincerely, > > > Anthony Thomas > > > -- > > "Erland Sommarskog" <esquel@sommarskog.se> wrote in message > news:Xns96FA6C714701Yazorman@127.0.0.1... >> Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes: >> > You do need to watch recompiles which is why you should put any CREATE >> > TABLE # at the start of your stored procedure. >> > >> > But recompiles usually only tend to affect oltp queries that require >> > quick response and are being executed by multiple concurrent >> > connections, you get blocking because of the recompile lock placed on >> > the plan. >> >> It's a very devilish game. In the performance quest that I mentioned, I >> found a stored procedure that only ran once per day - but then it ran >> for one hour and a half, and that was a single SELECT that inolved a >> table variable. When I research it, I found that the table variable was >> likely to hold 250000 rows. So my fix to this complicated procedure was >> to make that table variable a temp table. It's now down on ten seconds. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >> >> Books Online for SQL Server SP3 at >> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >> > >
Hi Tony!
Table variables are also logged, but just a wee bit less than temp tables. I did a test on this perhaps a year or two ago (using fn_db_log...), you should be able to search ng archive for it. I'm in a bit of a hurry now, lemme know if you can't find it. :-) -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Tony Rogerson" <tonyrogerson@sqlserverfaq.com> wrote in message
news:eZVm4ek2FHA.632@TK2MSFTNGP10.phx.gbl... > Hi Anthony, > > They don't actually work like that. > > Under the covers they are actually temporary tables but without logging (they are not affected by > begin tran/commit/rollback), SQL Server can bind them to the plan where # tables it can't. > > True, temporary tables are logged, but not as much logging as a normal database; you don't need > redo records to be logged in tempdb because its cleared down on each restart. > > Tony. > > -- > Tony Rogerson > SQL Server MVP > http://sqlserverfaq.com - free video tutorials > > > "Anthony Thomas" <ALThomas@kc.rr.com> wrote in message > news:uty%2358i2FHA.3420@TK2MSFTNGP15.phx.gbl... >> So you know, as all variables, even table variables must be stored in >> memory. If large enough to no longer be able to completely live in memory, >> SQL Server will create a tempdb table structure to back the variable. Then >> you get into a case of structures on structures. >> >> By creating the temp table structure directly, you bypassed the additional >> overhead and management. That is the drawback of table variables. >> >> Sincerely, >> >> >> Anthony Thomas >> >> >> -- >> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message >> news:Xns96FA6C714701Yazorman@127.0.0.1... >>> Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes: >>> > You do need to watch recompiles which is why you should put any CREATE >>> > TABLE # at the start of your stored procedure. >>> > >>> > But recompiles usually only tend to affect oltp queries that require >>> > quick response and are being executed by multiple concurrent >>> > connections, you get blocking because of the recompile lock placed on >>> > the plan. >>> >>> It's a very devilish game. In the performance quest that I mentioned, I >>> found a stored procedure that only ran once per day - but then it ran >>> for one hour and a half, and that was a single SELECT that inolved a >>> table variable. When I research it, I found that the table variable was >>> likely to hold 250000 rows. So my fix to this complicated procedure was >>> to make that table variable a temp table. It's now down on ten seconds. >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >>> >>> Books Online for SQL Server SP3 at >>> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >>> >> >> > >
So you know, as all variables, even table variables must be stored in
memory. If large enough to no longer be able to completely live in memory, SQL Server will create a tempdb table structure to back the variable. Then you get into a case of structures on structures. By creating the temp table structure directly, you bypassed the additional overhead and management. That is the drawback of table variables. Sincerely, Anthony Thomas -- [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns96FA6C714701Yazorman@127.0.0.1... > Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes: > > You do need to watch recompiles which is why you should put any CREATE > > TABLE # at the start of your stored procedure. > > > > But recompiles usually only tend to affect oltp queries that require > > quick response and are being executed by multiple concurrent > > connections, you get blocking because of the recompile lock placed on > > the plan. > > It's a very devilish game. In the performance quest that I mentioned, I > found a stored procedure that only ran once per day - but then it ran > for one hour and a half, and that was a single SELECT that inolved a > table variable. When I research it, I found that the table variable was > likely to hold 250000 rows. So my fix to this complicated procedure was > to make that table variable a temp table. It's now down on ten seconds. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >
Anthony Thomas (ALThomas@kc.rr.com) writes:
[quoted text, click to view] > So you know, as all variables, even table variables must be stored in > memory. If large enough to no longer be able to completely live in > memory, SQL Server will create a tempdb table structure to back the > variable. Then you get into a case of structures on structures. > > By creating the temp table structure directly, you bypassed the additional > overhead and management. That is the drawback of table variables. As Tony said, table variables are created in tempdb directly. I had a case where data is being inserted one-by-one by calling a very complex stored procedure. Since it was called many times, hunting milliseconds was meaningful. In some triggers I copied inserted/deleted into table variables, and then there were some other table variables. I found out that there was a 7 ms overhead to start this trigger. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
So, along with Tony's comments, I think I have been corrected in lower
technical architecture, but that the basis of my comments are still accurate? That table variables tend to be as or more useful for smaller data sets; however, as the size of the sets grow, it is more efficient to deal with the temp table structures directly. Is this not the case or is everyone in agreement? Thanks. Sincerely, Anthony Thomas -- [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns96FBEB93E5240Yazorman@127.0.0.1... > Anthony Thomas (ALThomas@kc.rr.com) writes: > > So you know, as all variables, even table variables must be stored in > > memory. If large enough to no longer be able to completely live in > > memory, SQL Server will create a tempdb table structure to back the > > variable. Then you get into a case of structures on structures. > > > > By creating the temp table structure directly, you bypassed the additional > > overhead and management. That is the drawback of table variables. > > As Tony said, table variables are created in tempdb directly. I had a case > where data is being inserted one-by-one by calling a very complex stored > procedure. Since it was called many times, hunting milliseconds was > meaningful. In some triggers I copied inserted/deleted into table variables, > and then there were some other table variables. I found out that there > was a 7 ms overhead to start this trigger. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >
Anthony Thomas (ALThomas@kc.rr.com) writes:
[quoted text, click to view] > So, along with Tony's comments, I think I have been corrected in lower > technical architecture, but that the basis of my comments are still > accurate? That table variables tend to be as or more useful for smaller > data sets; however, as the size of the sets grow, it is more efficient to > deal with the temp table structures directly. Is this not the case or is > everyone in agreement? Yes, for a simple description on when to use what, this is a decent rule of thumb. (There are other issues as well. For instance if you want parallelism to be used when you insert into your temporary data store, you need to use a temp table; you don't get parallelism with table variables.) -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
I'll have a look - is it row level or just the extents? It would make sense
the extent usage being logged but not the rows. -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:edD$Lmk2FHA.2316@tk2msftngp13.phx.gbl... [quoted text, click to view] > Hi Tony! > > Table variables are also logged, but just a wee bit less than temp tables. > I did a test on this perhaps a year or two ago (using fn_db_log...), you > should be able to search ng archive for it. I'm in a bit of a hurry now, > lemme know if you can't find it. :-) > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Tony Rogerson" <tonyrogerson@sqlserverfaq.com> wrote in message > news:eZVm4ek2FHA.632@TK2MSFTNGP10.phx.gbl... >> Hi Anthony, >> >> They don't actually work like that. >> >> Under the covers they are actually temporary tables but without logging >> (they are not affected by begin tran/commit/rollback), SQL Server can >> bind them to the plan where # tables it can't. >> >> True, temporary tables are logged, but not as much logging as a normal >> database; you don't need redo records to be logged in tempdb because its >> cleared down on each restart. >> >> Tony. >> >> -- >> Tony Rogerson >> SQL Server MVP >> http://sqlserverfaq.com - free video tutorials >> >> >> "Anthony Thomas" <ALThomas@kc.rr.com> wrote in message >> news:uty%2358i2FHA.3420@TK2MSFTNGP15.phx.gbl... >>> So you know, as all variables, even table variables must be stored in >>> memory. If large enough to no longer be able to completely live in >>> memory, >>> SQL Server will create a tempdb table structure to back the variable. >>> Then >>> you get into a case of structures on structures. >>> >>> By creating the temp table structure directly, you bypassed the >>> additional >>> overhead and management. That is the drawback of table variables. >>> >>> Sincerely, >>> >>> >>> Anthony Thomas >>> >>> >>> -- >>> >>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message >>> news:Xns96FA6C714701Yazorman@127.0.0.1... >>>> Tony Rogerson (tonyrogerson@sqlserverfaq.com) writes: >>>> > You do need to watch recompiles which is why you should put any >>>> > CREATE >>>> > TABLE # at the start of your stored procedure. >>>> > >>>> > But recompiles usually only tend to affect oltp queries that require >>>> > quick response and are being executed by multiple concurrent >>>> > connections, you get blocking because of the recompile lock placed on >>>> > the plan. >>>> >>>> It's a very devilish game. In the performance quest that I mentioned, I >>>> found a stored procedure that only ran once per day - but then it ran >>>> for one hour and a half, and that was a single SELECT that inolved a >>>> table variable. When I research it, I found that the table variable was >>>> likely to hold 250000 rows. So my fix to this complicated procedure was >>>> to make that table variable a temp table. It's now down on ten seconds. >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >>>> >>>> Books Online for SQL Server SP3 at >>>> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp >>>> >>> >>> >> >> >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |