One commonly-missed performance tuning option is to ensure that parameters and variables passed into SQL are the same data type as the columns they are referencing.
If you pass in an @nvarchar(25) variable to a varchar(15) column, an implicit conversion is done. This is performed on the entire resultset, which could impact performance.
It shows up often when using .NET apps and with developers unfamiliar with SQL schema.
That was the question I recently got from a customer: they had two systems which send comparable queries to the same SQL Server database, and the system using ADO.Net was 'significantly slower' than the other system.
In SQL 2005+, finding implicit conversions is straightforward with DMVs (and a not so straightforward query).
If you don’t want to rewrite your app, consider rewriting stored procedures (you do use stored procedures?) to convert mistyped variable before the query statement.
Why is ADO.Net so slow? - U2U Blog
This is also a good reason to avoid ORM frameworks (or learn how to use explicitly typed parameters) when dealing with complex SQL queries or large data sets.
More info around LINQ and implicit conversions.
http://www.hanselman.com/blog/ImprovingLINQCodeSmellWithExplicitAndImplicitConversionOperators.aspx
No comments:
Post a Comment