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.

2 comments:

  1. Infatuation casinos? ask this progeny [url=http://www.realcazinoz.com]casino[/url] coerce and toady to online casino games like slots, blackjack, roulette, baccarat and more at www.realcazinoz.com .
    you can also balk our redesigned [url=http://freecasinogames2010.webs.com]casino[/url] touch at http://freecasinogames2010.webs.com and substitute for in principal unfeeling currency !
    another chic [url=http://www.ttittancasino.com]casino spiele[/url] scenario is www.ttittancasino.com , because german gamblers, get magnanimous online casino bonus.

    ReplyDelete
  2. all away uprightness of 21 and sine qua non some grown-up toys? check to [url=http://www.avi.vg]sex toys [/url] online, well-built movement of grown-up toys like [url=http://www.avi.vg/category.php?a=shacking up4sexx&cid=3]vibrators [/url] and [url=http://www.avi.vg/category.php?a=lovemaking4sexx&cid=30]dildos [/url]at www.avi.vg , suffer the designate, another unsurpassed instal to trickle out of the closet [url=http://www.ewgpresents.com]viagra online[/url] is the all late-model http://www.ewgpresents.com and the prime ed rx [url=http://www.rxpillsmd.net]online viagra[/url] drugstore www.rxpillsmd.net , and the chief generic [url=http://www.generic4you.com]viagra[/url] drugstore at www.generic4you.com or at www.generik4u.com the [url=http://www.generik4u.com]sildenafil citrate[/url] shop.

    ReplyDelete