Alexander Kuznetsov : SQL

Override the optimizer or give it an easy task?

Alexander Kuznetsov — Wed, 17 Jun 2009 02:43:00 GMT

When your query must always return in a certain amount of time, trusting the optimizer might not be the best choice. Suppose your select must return in one second no matter what, and it usually returns in 50 ms, but sometimes, once in 100K calls, it takes...(read more)

When correlation between columns fools the optimizer

Alexander Kuznetsov — Fri, 05 Jun 2009 21:28:00 GMT

When two columns are correlated, it may fool the optimizer and cause it to choose a wrong plan. Here is a simple script that demonstrates it. The following script creates a table and populates some sample data: CREATE TABLE dbo.Vehicles ( ID INT NOT NULL...(read more)

Set based calculation of products of several numbers.

Alexander Kuznetsov — Tue, 17 Mar 2009 22:01:00 GMT

You can use SUM to calculate the sum of several numbers, but you cannot directly use set-based logic to calculate a product. Yet there is a very simple trick – you can use EXP(SUM(LOG(…))) and get a product of several numbers without a loop. The trick...(read more)

Defensive database programming: eliminating IF statements.

Alexander Kuznetsov — Thu, 27 Nov 2008 23:27:00 GMT

The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN DO SOMETHING; END When such code runs in high concurrency situations, it may not work as expected. I will provide a repro when such logic fails 40% of the time....(read more)

Performance of inserts and IGNORE_DUP_KEY

Alexander Kuznetsov — Thu, 31 Jul 2008 02:39:00 GMT

If your data has a small percentage of duplicates, then IGNORE_DUP_KEY may speed up your inserts. For larger amounts of duplicates, IGNORE_DUP_KEY may slow them down significantly. I set up two tables, stripping down all the irrelevant details, as follows:...(read more)