Monday, December 17, 2007

Effective SQL Mindset: Cursors VS Joins



In the past I've dealt a lot with cursors. My experience was mostly negative. SQL code written using them was terribly slow. Thus our team was transforming SQL code with cursors into code without them.

Sure, cursors have their advantages. But, after conversion SQL query performance can be much better then with cursors.

Now, I'll show you how to accomplish the task using SQL with and without cursors.
There are two tables: regions and values. For example, regions represents departments while values is used to store arbitrary int data about the specific region.

SQL tables:
create table regions (id int, name varchar(10))
create table [values] (id int identity(1, 1), regid int, value int)


Table regions contains records for 100 regions, while values contains data only for region with id = 1 (only for the first region). We need to populate values table using data from the first region.

For exaple if table regions contains data





idname
1Reg 1
2Reg 2

And table values contains data only for first region




idReg Idvalue
1130
2135


So we need to populate table values for every region from regions table.

There are three ways how to do this:
  • write a computer program that will insert values into values table
  • write SQL query with cursors
  • write SQL query without cursors
I will not touch how to write a program to do this, its too ineffective.

But last two options seem to be interesting. Cursor solution is quick and dirty - you can code it almost in no time. Cursor code will be similar to the computer program's one.

select id,regionid,value into #Valuestmp from [values]

DECLARE RegionsCursor CURSOR FOR SELECT id FROM regions
declare @id int

OPEN RegionsCursor;
FETCH NEXT FROM RegionsCursor into @id;
WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE #Valuestmp SET RegionId=@id
INSERT INTO [values](regionId, value) SELECT regionId, value FROM #Valuestmp
FETCH NEXT FROM RegionsCursor into @id;
END;
CLOSE RegionsCursor;
DEALLOCATE RegionsCursor;

drop table #Valuestmp
Here we store initial values from values table, and then for each region we insert data into values table.

This elegant script will accomplish the above:
insert into val (regid, value) select r.id as regid, v.value as value from reg r, val v where r.id <> 1

Central point in this script is Cartesian product - we select from two tables (bold font), joining every row of the values table with the rows in regions table.

SQL code above shows how cursor can be avoided. Every time you're tempted to use cursor - stop and think that nearly everything can be done without them. All you have to do is to think little bit.

Thursday, December 06, 2007

Here Comes Another Bubble

Some people say that there won't be .com like bubble any more. However, there are signs out there that indicate the opposite :)



Thanks Alena C++ for the link