Are the posted questions getting worse?
-
It's fair use.
I pinged Eric, and not sure what he'll do. I did let him know that some people see the facts as incorrect.
-
CirquedeSQLeil (4/1/2010)
I enjoyed the blog post. I felt silly that I hadn't thought of a great idea like that (or blogging for April Fools Day). I had nothing last night to put out - for a joke.I quite enjoyed your blog post Jason. I think I'll pull out my code and put yours in! Thanks for all that work cleaning it up and testing it.
Chad
-
Chad Crawford (4/1/2010)
CirquedeSQLeil (4/1/2010)
I enjoyed the blog post. I felt silly that I hadn't thought of a great idea like that (or blogging for April Fools Day). I had nothing last night to put out - for a joke.I quite enjoyed your blog post Jason. I think I'll pull out my code and put yours in! Thanks for all that work cleaning it up and testing it.
Chad
That is actually a different query that I am referencing than yours. They are similar but different. Your query didn't need that kind of work.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Crud. Alright, I'll ask it. Why wouldn't a table stored in memory be faster as long as it was small? I can see that a large table in memory wouldn't be as efficient, but a very small table might be. Or is it how the table variables and temp tables are stored that you are disagreeing with?
Chad
-
The original that Chad is referencing can be found here[/url].
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Chad Crawford (4/1/2010)
Crud. Alright, I'll ask it. Why wouldn't a table stored in memory be faster as long as it was small? I can see that a large table in memory wouldn't be as efficient, but a very small table might be. Or is it how the table variables and temp tables are stored that you are disagreeing with?Chad
It is how they are stored. Both temp tables and table variables can be stored both in memory and on disk.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
In short, table variables are handled VERY much like temp tables. A couple of major differences follow:
Table variables aren't subject to logging.
Statistics aren't kept on table variables, which can be very bad for performance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
Looks like Paul has improved his daily post average.
He is now at 100.9333 posts a day.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
CirquedeSQLeil (4/1/2010)
Trey Staker (4/1/2010)
I can't believe of few of the whiney comments in the editorial discussion. I guess some people are born without a sense of humor.I fell for one this morning. The copier had an instruction sheet for a new voice activated module. It was on HP letter head and looked official. I didn't really follow the instructions but I did comment out loud, "Why would anyone ever need a voice activated module on a copier." It made a few people laugh at my expense but I don't mind being the fool on occasion.
Can you forward that to me? I know several copy machines throughout my building.
Sorry...I've left work already. Just after I posted that work got hectic.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum -
Trey Staker (4/1/2010)
CirquedeSQLeil (4/1/2010)
Trey Staker (4/1/2010)
I can't believe of few of the whiney comments in the editorial discussion. I guess some people are born without a sense of humor.I fell for one this morning. The copier had an instruction sheet for a new voice activated module. It was on HP letter head and looked official. I didn't really follow the instructions but I did comment out loud, "Why would anyone ever need a voice activated module on a copier." It made a few people laugh at my expense but I don't mind being the fool on occasion.
Can you forward that to me? I know several copy machines throughout my building.
Sorry...I've left work already. Just after I posted that work got hectic.
That's fine. Thanks anyway.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
The Dixie Flatline (4/1/2010)
Table variables aren't subject to logging.Well...I know what you mean, but it's not literally true:
-- For the demo
USE tempdb;
-- Truncate the log
CHECKPOINT;
-- Show log records
-- (just LOP_BEGIN_CKPT and LOP_END_CKPT)
SELECT *
FROM fn_dblog(NULL, NULL);
DECLARE @NotLogged
TABLE (col1 INT NULL);
INSERT @NotLogged (col1) DEFAULT VALUES;
-- Show log generated by the table variable
SELECT [Current LSN],
Operation,
Context,
Description,
[Log Record Length],
[Log Reserve],
AllocUnitId,
AllocUnitName,
[Page ID],
[Slot ID],
[End Time],
NewAllocUnitId
FROM fn_dblog(NULL, NULL);
Interesting, right? Note especially the AllocUnitName, which gives the 'hidden' temporary table associated with the table variable.
-
The Dixie Flatline (4/1/2010)
Table variables aren't subject to logging.It's perhaps better to say that they don't participate in transactions.
Table variable changes are still logged, they have to be (all data changes are, even in tempDB), because, although table variables aren't affected by a ROLLBACK TRANSACTION, they still have to be able to roll back in the case of a data modification failing part way through and that requires (and uses) the transaction log.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (4/2/2010)
It's perhaps better to say that they don't participate in transactions.Or locking...that's something else that is often forgotten 🙂
Table variable changes are still logged, they have to be (all data changes are, even in tempDB), because, although table variables aren't affected by a ROLLBACK TRANSACTION, they still have to be able to roll back in the case of a data modification failing part way through and that requires (and uses) the transaction log.
Concerning tempdb...
Operations on internal objects (e.g. worktables, spool operations) are never logged.
UNDO information is always logged otherwise, but REDO information may not be.
At one point, skipping the REDO logging was only available on heaps for user tables created in tempdb, though I think that has now been extended to the more general case. I'll have to check on that.
-
GilaMonster (4/1/2010)
Now I have someone whining about my april fool's blog post. 🙁I liked the last comment (thus far). "Optimize for Nested Cursors" option! I know shops that need that one!
- 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
-
I think the statement that table variables don't participate in transactions is clearer. It implies that they can be exempt from a rollback, which is handy. I worry about marking them as "not logging" when that could imply something else to less experienced developers.
Viewing 15 posts - 13,756 through 13,770 (of 66,815 total)
You must be logged in to reply to this topic. Login to reply