SolidQ Training in NYC and Las Vegas!
In partnership with SolidQ, SSWUG announces two new seminars in March of this year!
- March 7-8, a fast-paced, in-person seminar on configuring and securing SharePoint 2010 and business intelligence services will be taught by BI expert Carl Rabeler in Las Vegas, NV.
- March 13-14, practical SSRS design techniques based on industry best practices and real-world reporting applications will be taught by SQL Server MVP Paul Turley in New York City.
To register for one of these sessions, or view the full list of upcoming SSWUG/SolidQ training opportunities, click here.
SQL Server Data Types Impact Performance
Any time you have to convert from one data type to another, regardless if the conversion is implicit or explicit, performance is degraded for your queries. It makes sense. One of the first things you try to do in most programming languages is to reduce Boxing (type casting) to improve performance.
I found this fact to be true when I optimized a query for a company in SQL Server 2000 for an ETL process using staging tables. The process created staging tables dynamically; all character data types were NVARCHAR. However, the permanent tables used VARCHAR data types. As a result, merging data from the staging table into the permanent table was horribly slow due to the constant Boxing of the data.
I came across a blog this morning regarding the impact of Collation to performance in SQL Server. It turns out that using ANSI Collation results in UNICODE (NVARCHAR) comparisons for operations and sorting. Therefore, the data is once again Boxed when you may not think Boxing is required. Moreover, comparisons using ANSI Collation are much more complicated than the standard Latin SQL shipping with SQL Server.
The blog points you to http://support.microsoft.com/kb/322112 for more information.
On a similar note, using a Case Sensitive Collation also speeds up the performance of your application. With a Case Insensitive Collation the system must convert data all to one case prior to comparison in order to match all possible permutations. As a result, the simplicity of comparison results in additional work for all character comparisons.
Lesson learned: in any database engine you gain performance with simpler data types and Collations. Therefore, if performance is a big concern, think about extending your data types to the most flexible data types when you are sure your system requires that flexibility.
Share your feedback by sending an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Office 365 SharePoint Online – what does it mean to my organization as a CIO?
Written by AvePoint
The goal of this white paper is to clarify the disjoint… (read more)
Featured Script
dba3_Column_And_Table_Constraints_And_ColumnDefaults.SQL
Constraints, Column & Table also Column Defaults… (read more)