Loops in stored proc or webcode? Which is most efficient?

[Roy]

Apologies for the cross-post, but this truly is a two-sided question.

Given the option of creating Looping statements within a stored proc of
sql server or in the code-behind of an .net webpage, which would you
choose and why?

Reason I ask is I created a webpage which essentially runs through a
litany of loops to determine which stored proc to kick off. This is
written in the code-behind. It occurred to me that I could probably
just as easily create the loops in a "master" stored proc which would
then determine which stored proc to kick off. IOW, I'd be passing the
processing from the webpage to sql server. What are the
advantages/disadvantages of this?

Thanks in advance my friends.

 

[Aaron [SQL Server MVP]]

Reason I ask is I created a webpage which essentially runs through a

litany of loops to determine which stored proc to kick off. This is
written in the code-behind. It occurred to me that I could probably
just as easily create the loops in a "master" stored proc which would
then determine which stored proc to kick off. IOW, I'd be passing the
processing from the webpage to sql server. What are the
advantages/disadvantages of this?

Rather than performance, I think it is much more a question of what makes
sense from a management/maintenance perspective. What are the criteria used
to decide which stored procedure to call? Are these related to business
rules, data the client enters, data in the database, ...? Can you show a
sample of the "litany of loops" you are running?

 

[Patrice]

Hard to say without knowing your code and what you try to achieve.

IMO it's useless to call a stored proc on the server whose job is just to
find out which other proc should be called. I would do this client side.

That said you may want to further explain why you need to loop to find out
which stored proc to kick off. For example if you loop on database rows to
call a particular procedure that depends on something in the row, I would do
instead a single stored proc that process all rows accordingly using if
possible a single or several SQL statements...

Patrice

 

[David Portas]

The best solution is usually to avoid using loops at all with data. SQL
is a declarative language rather than a procedural one and most data
manipulation operations have set-based solutions that don't require
loops.

Without understanding what you are doing it's difficult to recommend.
In general if you require access to data it usually pays to do it
through an SP.

 

[Manohar Kamath]

Roy,

This is a good candidate for a "Strategy Pattern"

http://www.dofactory.com/Patterns/PatternStrategy.aspx

Here's how I would divide it:

1. Create one class each for one stored proc. Create a common method for
all -- say "Execute()" This would be your Data Access layer.

2. Within a business logic component, choose what data you want. Depending
on that you create that component, and call the "Execute()" method:

This makes your component transparent to which stored procs are being
called.

 

[Roy]

Thanks all. I'm getting the broad impression that there may be
something inherently wrong with my approach, but darned if I know how
to fix it. In answer to the collective question: basically I have a
webpage displaying database records. Lets say each record has 7
editable fields, every single one of those fields must have a
"date_edited" field, so thus, while a webpage user sees only 7 fields,
there exists 14 in sql server.

Since a user could update "field1" one day (and thus, the corresponding
"field1_date_edited" field would contain that datetime stamp) and then
the user could come back a week later and change a different field in
that same record (and thus a different datetime stamp would be entered
for that other field), I had to create a way whereby the webpage could
determine (and update) only those fields that had actually changed (and
their corresponding datetime field, of course).

What I'm currently doing is setting the original fields to variables
when a user clicks the edit button, then comparing them against the
text in their corresponding textbox when a user clicks the update
button. If you use your imagination, you'll see that I end up with a
LOT of loops because I must essentially have a stored proc for every
possible update combo that a user could enter.

Am I making sense?

 

[David Portas]

Just use one proc and pass the value as NULL if it isn't modified. In
your SP:

UPDATE YourTable
SET col1 = COALESCE(@col1, col1),
col2 = COALESCE(@col2, col2),
col3 = COALESCE(@col3, col3),
...
WHERE ...

You can use triggers to log what data has changed. Typically the
historical changes are preserved as rows in a table rather than as
separate changed dates for each column.

 

[Roy]

Thanks for the tip Dave! I didn't even know the command "coalesce"
existed!

But doesn't this bring up the original point? Since I still need a
series of IF's to determine if the data has changed and modify it
accordingly, would it be more effective to just send all the info to
the SP and have it do all the crunching and setting to NULL?

 

[Patrice]

Another approach is to update all fields regardless if they were updated (or
let's say to have 2 or 3 stored procs for each set of related updated
fields) :
- generally you don't pass so much data on a page (much less than when
retrieving)
- it doesn't matter that much server side as anyway the whole chunk must be
read/updated/committed even if you update a single byte
- it could be more efficient to call a single SP with several fields rather
than to call multiple SPs with a single field

Patrice

 

[Louis Davidson]

What I have always done is to have the program just pass all data to the
stored proc and do the update.

Then for logging changes, I do something like this (well, I have tools to
generate it):

I have a changeLog tables that I use to monitor all columns that I am
concerned with. The parent table is:

changeLog(changeActionType, tableName, identityValue, date, userName)
changeLogItem (changeLogId, fieldName, Value)

Note that I only concern myself with past values, so you only need this for
DELETE and UPDATE, since you have the current values. It is messy, but
quite efficient. It is easily automatable with a little bit of coding in
VB, or I use ERwin macros in my model.


--This is the insert of the header record, that tells us which record was
touched
insert into changeLog(changeActionTypeID, tableName, identityValue, date,
userName)
select 'UPDATE', 'tableName', <integer key of the table>, getdate(),
suser_sname()
from deleted

--and for each column we want to log
insert into changeLogItem (changeLogId, fieldName, Value)
select changeLog.changeLogId, 'column1',
convert(varchar(2000),deleted.column1)
from deleted
join inserted
on inserted.primaryKey = deleted.primaryKey
join changeLog
on changeLog.identityValue = inserted.<integer key of the
table>
and date = @logDate
where inserted.column1 <> deleted.column1
or (inserted.column1 is null and deleted.column1 is not null)
or (inserted.column1 is not null and deleted.column1 is null)
UNION ALL

select changeLog.changeLogId, 'column2',
convert(varchar(2000),deleted.column2)
from deleted
join inserted
on inserted.primaryKey = deleted.primaryKey
join changeLog
on changeLog.identityValue = inserted.<integer key of the
table>
and date = @logDate
where inserted.column2 <> deleted.column2
or (inserted.column2 is null and deleted.column2 is not null)
or (inserted.column2 is not null and deleted.column2 is null)



--
----------------------------------------------------------------------------
Louis Davidson - (e-mail address removed)
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored

 

[Roy]

Updating all fields or groups of fields isn't really an otion because
I'd still need to individually set the field in question's
"date_edited" to the current date.

 

[Guest]

As you can see from the disparate and vocal opinions presented in this
thread, this is a topic on which there is a broad range of opinion.. I sugges
you just try and understand the Pros/COns of the various approaches and form
your own approach...

To add a bit more to your plate, bear in mind the following considerations.
1) Stored Procs are "Harder" to write, debug, extend, and maintain, than
code written in most modern 4GL languages.
1a) But if you have someone on staff who is talented at T-SQL, they can
do just about anything in a Stored Proc that can be done in CLient-code...

2) Once written, debugged, and tested, Changes in Stored Procs are easier
to deploy than client code.

3) It is somewhat easier to organize and manage Stored Procs than client code.

4) It is harder to integrate Stored Proc functionality with other external
systems (email, web services, inventory control, real time stock quotes,
credit pulls, etc. etc.) than when using client code.

5) If the functionality required for each row also requires database
interaction (Updates, Inserts, deletes) Then doing it in CLient Code probably
means multiple round trips, whereas looping in a SP can happen all in one op,
on the server.

6) If the operations necessary for each row need to be transactionalized,
doing it in a SP gives you much better control, and fewer concurrency issues,
than trying to use COM+ or DTC...

 

[Patrice]

Ok, seems I miss your point...

Louis's lloks fine to me...

Patrice



--

 

[Patrice]

Seems I miss something. Louis's answer seems fine to me (i.e handling field
values checking inside a trigger).

Patrice

 

[Hugo Kornelis]

Thanks for the tip Dave! I didn't even know the command "coalesce"
existed!

But doesn't this bring up the original point? Since I still need a
series of IF's to determine if the data has changed and modify it
accordingly, would it be more effective to just send all the info to
the SP and have it do all the crunching and setting to NULL?

Hi Roy,

If storing the last change date/time of each column is a business
requirement, your only option (IMO) is to have the logic on the server,
and to make sure that it can't be bypassed. There are two ways to
achieve this:

1. Use a stored procedure for the update. To prevent direct updates,
bypassing the SP's logic, you'll have to make sure that nobody has right
to update the table, so that it can only be done through the SP.

The SP code would be something like this (using David's post as starting
point):

UPDATE YourTable
SET col1 = COALESCE(@col1, col1),
col1LastChange = CASE WHEN @col1 IS NULL
THEN col1LastChange
ELSE getdate()
END,
col2 = COALESCE(@col2, col2),
col2LastChange = CASE WHEN @col2 IS NULL
THEN col2LastChange
ELSE getdate()
END,
...
WHERE ...

Note that, if your columns allow NULLs, this won't work. It's also quite
hard to adapt this for multi-row updates.


2. Use a trigger to keep track of last changed date/time for each
column. If you list all columns in an UPDATE statement, you can't use
the IF UPDATE() function (since setting a column to it's current value
is still considered an update of the column).

The trigger would look something like this:

CREATE TRIGGER YourTrigger
ON YourTable AFTER UPDATE
AS
IF UPDATE(col1LastChange) -- Prevent recursion
RETURN
IF UPDATE(PKCol1) OR UPDATE (PKCol2)
BEGIN
RAISERROR ('Can''t change the primaary key!', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
UPDATE y
SET col1LastChange = CASE WHEN y.col1 <> d.col1
THEN getdate()
ELSE col1LastChange
END,
col2LastChange = CASE WHEN y.col2 <> d.col2
THEN getdate()
ELSE col2LastChange
END,
...
FROM YourTable AS y
JOIN deleted AS d
ON d.PKCol1 = y.PKCol1
AND d.PKCol2 = y.PKCol2
go



Best, Hugo