473,692 Members | 2,103 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 1829
> 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
3452
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 selects and the dba's want to use a stored proc. >From my reading it seems that sp_executesql has a...
0
7143
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 manually call the stored proc, and the external trigger is created without any errors. However, when I do...
6
1436
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
5999
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 proc? TIA Andrew. Dim retstr As String = ""
28
72492
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 accessing views from stored procedures?
12
2528
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
2050
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 problem with that proc is that whether I can retun the result set from the proc without closing the cursor...
3
4377
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 procedure its created in so unless I create the temporary table in every stored
0
1986
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
8608
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8540
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8803
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
8806
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6459
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4324
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2974
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2241
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1959
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.