April 30, 2009 at 4:08 pm
Also, there is very probably a whole lot of other columns that Steve hasn't mentioned because they are unnecessary, unless you are recreating the whole table.
Still, it's always bothered me that it cannot figure out and just do this trick for us in the query plan. Maybe some option or hint like "TABLELOCK" and the optimizer seriously considers the "reCreate-Drop & Rename" strategy.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2009 at 4:14 pm
Paul White (4/30/2009)
I used:
Insert Into dbo.Users (UserName, LastLoggedIn)
SELECT TOP 40000 CHAR(65+FLOOR(RAND(a.column_id*801020 + a.column_id*705020)*12)) + CHAR(65+FLOOR(RAND(a.column_id*6010 + b.object_id)*5)), DATEADD(dd,RAND(a.column_id*600 + b.object_id/1.5)*500,'2008/01/01')
FROM master.sys.columns a CROSS JOIN master.sys.columns b
Dude, what is going on with your code-window font sizes lately? They are showing up in my Firefox browser larger than PowerPoint letters. Makes them really hard to read as it keeps wrapping all of the lines.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 30, 2009 at 4:21 pm
RBarryYoung (4/30/2009)
Also, there is very probably a whole lot of other columns that Steve hasn't mentioned because they are unnecessary, unless you are recreating the whole table.Still, it's always bothered me that it cannot figure out and just do this trick for us in the query plan. Maybe some option or hint like "TABLELOCK" and the optimizer seriously considers the "reCreate-Drop & Rename" strategy.
Yes that's very true. If one was doing this for real, there would be lots of extra things to consider.
For a start, the SELECT...INTO would have to be part of the transaction, and it would need to run at SERIALIZABLE.
At least Steve has plenty to think about!
Not sure about letting the QO decide to drop and re-create tables - would be complicated if things like foreign keys were involved...not to mention the fact that the object_id would change. This could break things in unexpected ways. It's probably just not practical.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 4:24 pm
RBarryYoung (4/30/2009)
Dude, what is going on with your code-window font sizes lately? They are showing up in my Firefox browser larger than PowerPoint letters. Makes them really hard to read as it keeps wrapping all of the lines.
Oh!
I use Chrome, and the standard code=sql thing makes for really quite small text, so I added size=3 which looks good in Chrome.
I'll stop doing that eh?
😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 4:25 pm
Paul White (4/30/2009)
I use Chrome, and the standard code=sql thing makes for really quite small text, so I added size=3 which looks good in Chrome.I'll stop doing that eh?😀
Please. I can read your code from the other side of the room at the moment.
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
April 30, 2009 at 4:29 pm
GilaMonster (4/30/2009)
Please. I can read your code from the other side of the room at the moment.
At least that gives you freedom to roam about while on the forum? :laugh:
Ok everyone, consider it done.
Paul
edit: also went back and modified previous posts on this thread
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 11:58 pm
Paul White (4/30/2009)
RBarryYoung (4/30/2009)
Dude, what is going on with your code-window font sizes lately? They are showing up in my Firefox browser larger than PowerPoint letters. Makes them really hard to read as it keeps wrapping all of the lines.Oh!
I use Chrome, and the standard code=sql thing makes for really quite small text, so I added size=3 which looks good in Chrome.
...
Well I can sympathize with that. I use four different browsers right now (all free, or course) because there isn't even any combination of two that I can use that work right on every site and app that I use.
Firefox 3 is my main one and the best one overall for this site, though Firefox 2 was better (frickin thing auto-upgraded without my permission)!
I really like Chrome but it still seems to have some significant holes (like that lousy font-window problem) so generally I love it for quick & light browsing, but I don't think that its ready for serious work (like this site).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply