Archive for the ‘Performance’ Category

Speeding up aggregates with indexed views

March 25, 2012 by · 3 Comments
Filed under: Performance, TSQL 
Social Tagging: • • • • •

Ever so often, queries include some aggregates data. In a datawarehouse environment, they are frequently used for metric and Key Performance Indicator (KPI) calculations. Aggregates are also used a lot for reporting purposes and for statistical computation. When a query perform poorly, our first instinct is to add an index to speed it up. Creating a […]

Linked servers and distributed queries

October 23, 2011 by · 3 Comments
Filed under: Administration, Performance 
Social Tagging: • • • • • • • • •

Linked servers allows to issue SQL commands against OLE DB providers. Because Microsoft also makes available an “OLE DB Provider for ODBC Drivers”, it is also possible to issue queries against a variety of ODBC drivers. With linked servers and distributed queries, you can query all sorts of data sources and merge them on the […]

Table variable for large tables (vs temporary tables)

September 13, 2011 by · 2 Comments
Filed under: Performance, TSQL 
Social Tagging: • • • • • • • •

The main reason why Microsoft introduced table variable in SQL Server 2000 is to reduce stored procedure recompilations (a recompilation occurs when the stored procedure execution plan is recreated). Table variables also have a well defined scope (the current procedure or function) and they induce less logging and locking (because their transaction last for a […]

Using NOT IN operator with null values

August 18, 2011 by · 7 Comments
Filed under: Performance, TSQL 
Social Tagging: • • • •

The IN operator compares a value with a list of values. It must however be used with care when we are dealing with nulls. Let’s create a table containing three city names and a null value. The goal is check whether a city is in the list or not.

Using local variables in T-SQL queries

July 15, 2011 by · 1 Comment
Filed under: Performance 
Social Tagging: • • • • • • • •

A query plan is a set of steps generated by the database engine to retrieve data. Query plans are produced by the query optimizer from SQL statements. SQL Server automatically caches query plans and try to reuse them whenever possible. For many applications (such as OLTP transactional applications), plan reuse is a very good thing since […]

SSIS Non cached Lookups without a covering index

May 19, 2011 by · Leave a Comment
Filed under: Performance, SSIS 
Social Tagging: • • • • • •

The vast majority of the time, you will use SSIS Lookup component in Full Cache mode. This mode is the fastest because it queries the database only once (before the data flow starts) and apply hashing in order to do high-performance comparisons. Sometimes however you will have to use Non Cached lookups. For example if […]

Heap Tables

April 29, 2011 by · 2 Comments
Filed under: Performance 
Social Tagging: • • • •

Heap tables (tables without a clustered index) are generally not part of a good database design. Unless you never actually query your table, you should always put a clustered index on it. Heap table are generally slower on selects, updates and deletes. They are also generally slower on inserts if you decide to use a […]