October 1, 2008 at 2:01 am
Sandy (9/30/2008)
Gail & Meely,I think Gail can better answer for this,
I wanted to know your thoughts on why table variables were better than temp tables for 'highly transactional' workloads. I assume by that you mean lots of temp tables/table variables been created, used and dropped and having only a few rows in them.
Well, As per my observation and understanding the Table Variable concepts. Its does not make more difference if primary key is define on the table variable as clustered index scan is same as table scan.
No, but the pk gives the chance of a index seek. Not a good chance, seeing as the optimiser thinks there's a single row in the table, but it does sometimes do the seek.
Frankly speaking to all that when you are creating a Temp Table its a additional burden for the server to create a Temp table on Tempdb and maintains the uniqueness between sessions. I mean to say If one Temp table present in your Store Procedure and same time calling by more users then how it will define inside the server memory, just consider this matter too another thing is Temp Table is following the traditional approach which follows the locking and memory from the server itself during the process. and its not upto the scope too. that is why MICROSOFT comes with new concept like "Table Variable and View Variable and CTE....etc....".
Yeah, but table variables are treated the same in terms of the TempDB system tables and memory/disk location.
All you really gain with the table variable is a lack of logging and no recompile.
The recompile may be the biggest advantage, but it has to be weighed up against the missing stats and the poor exec plan that may result.
View Variable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 3:03 am
Yes Gail,
I do agree with you for both the point you specified in above post.
Yeah, but table variables are treated the same in terms of the TempDB system tables and memory/disk location.
Can you show me where it created inside the tempdb when i execute the query which contains a table variable but you can find in temp table.
can you tell me table variable also follows the locking ??
I needs these inputs from you?
Meely check this: http://www.sommarskog.se/share_data.html#prockeyed
gail please go through this links:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=343067
what i believe as per this topic the CTE is implemented by MICROSOFT.
Cheers!
Sandy.
--
October 1, 2008 at 8:49 am
Sandy (10/1/2008)
Yes Gail,I do agree with you for both the point you specified in above post.
Yeah, but table variables are treated the same in terms of the TempDB system tables and memory/disk location.
Can you show me where it created inside the tempdb when i execute the query which contains a table variable but you can find in temp table.
They're visible in sys.tables, like any other table. Run this on a server that's not in use, so that you don't have other connections possible messing up things.
select * from tempdb.sys.tables
go
declare @tbl TABLE (TableVariable_ID int)
select * from tempdb.sys.tables
select * from tempdb.sys.columns where object_id in (select object_id from tempdb.sys.tables)
On my test server, the first query to sys.tables returns 0 rows, the second returns 1 with a table name of #023D5A04
The query to sys.columns shows the column name that was declared in the table variable
gail please go through this links:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=343067
what i believe as per this topic the CTE is implemented by MICROSOFT
The CTE was added in SQL 2005. Is that what you meant by 'view variable'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 9:36 am
Hi Sandy & Gail,
I got so many new concepts from both of your post..thanks you a lot to both of you. but still i am in dark on my topic..what should i use, Gail can you please explain clearly me what your thought on this topic in few words..
Same to you also Sandy...I need my concept need to clear rather than tech fight...
I know Gail you are absolutely correct but still i need your personal inputs on my topic..
at the same time I need Sandy's Personal Experience on this too..
Luvs
Milu.:)
October 1, 2008 at 10:19 am
GilaMonster (9/26/2008)
One other misconception. A clustered index scan is not better than a table scan. It's the same thing. It's called a table scan when run on a heap (table without clustered index) and a clustered index scan when run on a table with a clustered index. It's a complete read of all the pages in the table.
depends on your clustered index. I do a lot importing data and using queries to select certain data, eg only active students which will return ~90% of the rows. This may cause a table scan, but sometimes when done correctly and having to join on another table, I've had a clustered index scan go strait into a merge join because the sorting already lined up and 0% time was spent on sorting the 1mil rows while it spent quite a bit more time sorting if I didn't have the clustered index.
This would be a corner case.. but it fun to be a devli's advocate some times.. 😛
October 1, 2008 at 10:29 am
bcronce,
Can you please clear me what you are trying to say??
luvs,
Milu.:)
October 1, 2008 at 10:30 am
Milu (10/1/2008)
Hi Sandy & Gail,I got so many new concepts from both of your post..thanks you a lot to both of you. but still i am in dark on my topic..what should i use, Gail can you please explain clearly me what your thought on this topic in few words..
It depends on your situation. There's no absolute right or wrong.
Test both ways and see which works better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 10:34 am
Gail,
Can you please Explain with a situation where you wants to use the table variable and temp table..
I need a complete answer please so i can use as per this....
Luvs,
Milu.:)
October 1, 2008 at 10:36 am
bcronce (10/1/2008)
GilaMonster (9/26/2008)
One other misconception. A clustered index scan is not better than a table scan. It's the same thing. It's called a table scan when run on a heap (table without clustered index) and a clustered index scan when run on a table with a clustered index. It's a complete read of all the pages in the table.depends on your clustered index.
Nope.
You cannot get a table scan on a table with a clustered index, just as you cannot get a clustered index scan on a table that does not have a clustered index. The physical operation that scans all of the pages in the table is called a table scan on a heap, and a clustered index scan on a table that has a clustered index.
The clustered index scan will return the data sorted according to the index key, which will make it more optimal for certain operations (merge join, stream aggregate), but the operator itself is the same as a table scan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 10:46 am
Milu (10/1/2008)
Gail,Can you please Explain with a situation where you wants to use the table variable and temp table..
As has been said before in this thread, if the table variable will be storing a large number of rows (> 100), then you are probably better off using a temp table.
That said, test your specific situation and see which performs better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 10:47 am
gail again i got confused ....
clear me now..what u said just now..
luvs
milu
October 1, 2008 at 1:27 pm
If it's less than 100 rows, use a table variable. If it's more, use a temp table. That's the simple version.
Really, it's not quite that simple, and the only way to tell which is best is to test both.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 2, 2008 at 10:15 am
Thanks you all,
A hearty thanks to Gail for saying me correct way,
luvs
Milu.:)
October 2, 2008 at 11:05 pm
Gail,
I do agree with you, hope Meely clear now..
But still also i am not agree with a primary key on a Table variable for highly transactional table. which is gives you very poor performance for the execution.
Cheers!
Sandy.
--
October 3, 2008 at 8:00 am
Milu (10/1/2008)
bcronce,Can you please clear me what you are trying to say??
luvs,
Milu.:)
Merge joins are very effcient for large joins where they are pre-sorted. Many times doing large joins where you return a bunch of data, SQL will do a table scan and then sort the data in a way to allow the merge join to work. I've had a few situations where several of my import queries joined two multi-million row tables in seveeral different queries, but I created a clustered index in such a way that the only time it sorted was during the index build. It just so happened that the clustered index caused an index scan that resulted in much better performance than a table scan because of a one time sort rather than resorting for each query.
But again, this is a corner case.
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply