eugene.Kweb developer to web developers

Many times i found myself in trou­ble ( Bea­t­les ???) when i needed to change sql tables design based on some con­di­tional logic so i couldn’t use Insert Select tech­nique sim­ply because it doesn’t sup­port if state­ments when i needed some­thing beyond when con­di­tion abil­i­ties.

real life example :

prob­lem i faced lately was rela­tion­ship icons near each pro­file for com­mu­nity web­site when users had a rela­tion­ships (ie. sent mes­sages), mes­sages were stored in “Mes­sages” table and my require­ment was to show near each user’s pro­file whether logged user had a rela­tion­ship with them and what kind. So you could say just have a JOIN between “Users” and “Mes­sages” sql tables but first of i had 4 kinds of rela­tion­ships and sec­ond of all think what kind of opti­miza­tion would it be to have even one JOIN between two tables with more than hun­dreds of thou­sands records each. This ain’t work­ing unless you are ready to buy super servers to store data­bases on. You could also say cre­ate a func­tion that checks for each returned from query user what kind of rela­tion­ship logged user has with which is fine but i this way i would have many dou­ble records like UserA and UserB could have mes­saged each other, added to friend or blacklist.

So the idea was to cre­ate a Rela­tion­ships sql table which holds User1,User2,Relationship1,Relationship2,Relationship3,Relationship4. When any Rela­tion­ship col­umn was an inte­ger “0” for no rela­tion­ship, 1 for User1’s action, 2 for User2’s action and 3 for mutual action ( both users mes­saged each other).

You  can say now cool, this what i should do in the first place but if you work on web­site opti­miza­tion like i did? What if you are not the orig­i­nal designer?

Now what ? Prob­lem is that there is a mes­sage record for each mes­sage sent between users so every time mes­sage was sent the sender was User1 and the receiver was User2 which cre­ated lots of records for sin­gle rela­tion­ship when i needed cre­ate one field  that con­tains all the rela­tion­ships between two users. This where for state­ment comes handy. First of all i cre­ated a stored pro­ce­dure that had a con­di­tional logic, checked if there is a rela­tion­ship between users and then updated or inserted upon request. Now all i need is a for loop though Mes­sages table so it will send User1 and User2 to my stored pro­ce­dure, this is where cur­sors came to save me because oth­er­wise i would be needed to cre­ate C# func­tion to make the con­di­tional logic which would be many times slower. Cur­sors are not advis­able for real time results as well sim­ply because they are very slow and CPU demand­ing com­pared to more ele­gant solu­tions in sql or just proper design but in off-line changes like these i could allow to have cur­sor for loop but again the are much faster than C# access­ing the data­base and manip­u­lat­ing it.

here is the code

code :

-- setting nocount on so the query will be faster as won't return number of rows affected
set nocount on
-- declaration of two int variables which will hold current userID's
declare @userA int, @userB int
--declaration of the cursor that will loop though table in it's declaration
declare cur cursor for select fieldA,fieldB from tableA
-- opening cursor
open cur
-- get first result from table
fetch next from cur into @userA ,@userB
-- loop until there are no rows left
while @fetch_status = 0
begin
-- stored procedure with current userIDs
exec someStoredProcedure @userA ,@userB
-- get next result
fetch next from cur into @userA ,@userB
end
-- closing cursor
close cur
-- removing it from sql server memory
deallocate cur

Tags: sql

This entry was posted on Friday, September 11th, 2009 at 8:31 PM and is filed under MSSQL. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.