473,574 Members | 3,052 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Nov 19 '05 #1
14 1822
> 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?

--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Nov 19 '05 #2
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

--

"Roy" <ro**********@g mail.com> a écrit dans le message de
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
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.

Nov 19 '05 #3
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.

--
David Portas
SQL Server MVP
--

Nov 19 '05 #4
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.

--
Manohar Kamath
Editor, .netWire
www.dotnetwire.com
"Roy" <ro**********@g mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
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.

Nov 19 '05 #5
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_edite d" 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_ed ited" 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?

Nov 19 '05 #6
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.

--
David Portas
SQL Server MVP
--

Nov 19 '05 #7
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?

Nov 19 '05 #8
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
--

"Roy" <ro**********@g mail.com> a écrit dans le message de
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .
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?

Nov 19 '05 #9
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_edite d" to the current date.

Nov 19 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3438
by: LineVoltageHalogen | last post by:
Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects. Background: 1.) Simple Application that uses sql server for backened db. 2.) The application is only inserting and selecting data from the db. 3.) The developers want to use sp_executesql for simple...
0
7131
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can...
6
1434
by: Roy | last post by:
Hey all, I'm a relative newcomer to asp.net and have 2 simple code snippets below. Everything works fine, I'm just curious if there is a more efficient way to do the job as the update takes quite a while. Here's the proc: **************************************** CREATE PROCEDURE @Recon char(10), @Book nvarchar(50), @Van nvarchar(5),
4
5994
by: Andrew Baker | last post by:
I have the following code that calles a stored proc in SQLServer. When the output parameter @custref is null (System.DBNull) I cant seem to find a test for this and I get an exception. I know I could coalesce the stored proc, but I would like to know if the value is null. How do you test for null in the returned parameter of a stored...
28
72399
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. What is the purpose of a view if I can just copy the vode from a view and put it into a stored procedure? Should I be...
12
2526
by: Sheldon | last post by:
Hi, I have two arrays that are of the same dimension but having 3 different values: 255, 1 or 2. I would like to set all the positions in both arrays having 255 to be equal, i.e., where one array has 255, I set the same elements in the other array to 255 and visa versa. Does anyone know how to do this without using for loops? Sincerely,
0
2044
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i don't know how to return the rows the cursor has opened and I don't know how to handle the return set from the proc in my java code. My main...
3
4370
by: codefragment | last post by:
Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using nested stored procedures and returning the result in a temporary table. However the scope of the temporary table seems to be limited to the stored...
0
1981
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the contents of which comprise the call to a stored proc
0
7762
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8105
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8272
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7859
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5657
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3774
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3793
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1369
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1101
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.