Featured Script
dba3_Alter_ColumnInTable_Demo
Demo: Alter a Column in an existing table using the Alter Table Statement… (read more)
Did You See This? CLR-Based Tools…
Very interesting tools and functions for SQL Server. These folks have released a set of CLR function libraries to help you with finance, statistics, math and string functions for SQL Server. Very cool way to address the need, and by moving it closer to your data (running in SQL Server), you can potentially see some really big benefits and great savings in overhead to process. Definitely worth a look – check it out here.
What is the best Temporary Table Object?
A few years ago I wrote an article titled Table Variables are Faster – Fact or Fiction (http://bitonthewire.wpengine.com/articles/memberarticle.aspx?id=23780).
In that article I tested many things, and # Temp tables were the fastest hands down (CTE were not available at the time of this article); this was running under SQL Server 2000. In all fairness, it was faster because I was working in a fashion where building an index on the temporary table increased performance far beyond that of a table memory variable. Even a unique constraint on an @ (memory table variable) did not perform as well.
Like most things in SQL Server, the real answer is, “it depends.” And so it does. Recently I have been working in a SQL Server 2008 database, breaking down a complex query using temp tables in order to optimize performance. I’m finding this harder to do as the query optimizer in SQL Server gets better and better. Still, in this unique case, I was able to beat the optimizer by about 1,000%. I’m sure if I did some work on statistics or indexes I may have provided the optimizer with more information and thus performance.
Again I tested both methods for performance. The # table again performed best even though I didn’t create any index or primary key constraint. I went with the # table in all of my unit tests and was quite pleased with the results. The query I was working on is a query that gets performed frequently. So I put together a stress test using C# and started testing the query to see how it would scale. In this case, the # table works best for one or two concurrent queries. But when I scale it up to production levels of 25-40 concurrent queries, the # table fell apart. Even at 5 concurrent queries the @ table outperformed considerably better.
With this knowledge in hand, I think I’ll try writing the query using a CTE to see how that compares. Now that I have the little stress test utility, I can tweak it around and see how things perform.
How about you? Have you discovered any particular rule of thumb that directs you to one method over the other/ Drop us a note and share with other members of SSWUG by sending an Email to btaylor@sswug.org.
Webcast: Best practices for Analysis Services administration
This session takes you on a end-to-end tour of management features for Analysis Services. Along the way, you’ll learn important best practices direct from the product team about how to configure, optimize and manage your Analysis Server. We expect this to be an essential session for new and experienced Analysis Services administrators alike.
Presented by: Donald Farmer
> Register Now
> Live date: 6/23/2010 at 12:00 Pacific