Looking for some insight from the professionals about how they handle
row inserts. Specifically single row inserts through a stored
procedure versus bulk inserts.
One argument are people who say all inserts (and updates and deletions
I guess) should go through stored procedures. The reasoning is that
the developers that code the client side have no reason to understand
HOW the data is stored, just that it is. Another problem is an insert
that deals with multiple tables. It would be very easy for the
developer to forget a step. That last point also applies to business
logic. In my case, adding a security to our SecurityMaster can touch 1
to 4 tables depending on the type of security. Also, certain fields
are required while others are set to null for depending on the type.
Because a stored procedure cannot be passed datasets but only scalar
values, when you need to deal with multiple (i.e. bulk) rows you are
stuck using cursors. This post is NOT about the pros and cons of
cursors. There are plenty of those on the boards (some of them
probably started by me and showing my understanding (or more
correctly, lack of) of the way to do things). Stored procedures also
give you the ability to abort and/or log inserts that cannot happen
because of contraints and/or business rule failures.
Another approach is to write code (not accessible from outside the
database) that handles bulk inserts. You would need to write in rules
to "extract" or "exclude" rows that do not match constraints or
business rules otherwise ALL the inserts would fail because of one bad
row. I guess you could put the "potential" rows into a temp table.
Apply your rules to the temp table and delete / move rows that would
fail. Any rows left can that be bulk inserted. (You could also use the
rows that were moved to another temp table for logging why they
failed.)
So that leaves use with two possible ways to get data into the system.
A single row based approach for client apps and a bulk based for
internal use. But that leaves use with another problem. You now have
business logic in TWO separate areas. You have to remember to modify
code or fix bugs in multiple locations.
For those that are still reading my post, my question is...
How do you handle this? What is the approach you take?
4  2148 
Jay
What we have are a series of core stored procedures for single row
insert/update/deletes. As you point out, this may do extra
validation/processing etc. (Examples include security checking,
auditing, optimistic locking, business rule validation etc). These are
auto-generated.
In addition, we sometimes do bulk inserts from another table or data
source. To ensure we dont duplicate validation/processing AND to
ensure all data goes through the one filter before being committed, we
simply call the exact same stored procedure multiple times, typically
from within a cursor, but can be done from code in our case C#.
Depending on the application, this may be done as a transaction block.
We typically let the stored proc do the validation of the data and log
any rows that failed to insert.
This works like a charm for us and I have used this approach in a
number of successful projects.
However, this bulk insert approach does not scale very well and if you
do a lot of batch style processing, you really need to be looking at
customised scripts/stored procedures to get the best performance. Ja*******@hotma il.com (Jason) wrote in message news:<f0******* *************** ****@posting.go ogle.com>... Looking for some insight from the professionals about how they handle
row inserts. Specifically single row inserts through a stored
procedure versus bulk inserts.
One argument are people who say all inserts (and updates and deletions
I guess) should go through stored procedures. The reasoning is that
the developers that code the client side have no reason to understand
HOW the data is stored, just that it is. Another problem is an insert
that deals with multiple tables. It would be very easy for the
developer to forget a step. That last point also applies to business
logic. In my case, adding a security to our SecurityMaster can touch 1
to 4 tables depending on the type of security. Also, certain fields
are required while others are set to null for depending on the type.
Because a stored procedure cannot be passed datasets but only scalar
values, when you need to deal with multiple (i.e. bulk) rows you are
stuck using cursors. This post is NOT about the pros and cons of
cursors. There are plenty of those on the boards (some of them
probably started by me and showing my understanding (or more
correctly, lack of) of the way to do things). Stored procedures also
give you the ability to abort and/or log inserts that cannot happen
because of contraints and/or business rule failures.
Another approach is to write code (not accessible from outside the
database) that handles bulk inserts. You would need to write in rules
to "extract" or "exclude" rows that do not match constraints or
business rules otherwise ALL the inserts would fail because of one bad
row. I guess you could put the "potential" rows into a temp table.
Apply your rules to the temp table and delete / move rows that would
fail. Any rows left can that be bulk inserted. (You could also use the
rows that were moved to another temp table for logging why they
failed.)
So that leaves use with two possible ways to get data into the system.
A single row based approach for client apps and a bulk based for
internal use. But that leaves use with another problem. You now have
business logic in TWO separate areas. You have to remember to modify
code or fix bugs in multiple locations.
For those that are still reading my post, my question is...
How do you handle this? What is the approach you take?
Jason (Ja*******@hotm ail.com) writes: Because a stored procedure cannot be passed datasets but only scalar
values,
Actually, this is not completely true anymore. With SQL 2000 we got
the rowset provider OPENXML. So you can send in a complex dataset with
data for umpteen tables in one single procedure call.
It is probably still not as effecient as bulk insert, but bulk insert has
its limitations in validation etc. You can use a staging table, but
that comes with a cost of course.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp Pr************@ hotmail.com (Mystery Man) wrote in message news:<87******* *************** ***@posting.goo gle.com>... Jay
What we have are a series of core stored procedures for single row
insert/update/deletes. As you point out, this may do extra
validation/processing etc. (Examples include security checking,
auditing, optimistic locking, business rule validation etc). These are
auto-generated.
I use a combination - depending on what I want to achieve - if I am
loading a routine 'bulk' load then that runs split into adequate
batches (to minimise impact on transactions and logs) - but if I have
a luser wanting to enter data I like the stored proc approach for all
sorts of reasons.
My personal experience is that business logic gets more and more
complicated as a project develops. The users wants to change this,
tweak that, etc. And stored procedures are the only sure-fire way I
can accomplish all of the user's requests. I do try to "modularize "
common code. For example, a user wants to view a dataset. The web
page calls the viewdataSP. Inside the viewdataSP, it calls a
userAccessSP. Erland has a pretty good article about how to call
stored procedures from a stored procedure.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Andy Dingley |
last post by:
I've just had a call from these people,
http://www.browsealoud.com
offering to sell me their wares.
Anyone have an opinion on it ?
I'll post my own thoughts about 24 hours from now. I'm interested in
what others think - wouldn't want to prejudice other's comments.
|
by: bipod.rafique |
last post by:
Hello All,
I need your help in understanding something.
I have a simple class
class test{
};
|
by: Patrick Guio |
last post by:
Dear all,
I have some problem with insertion operator together with namespace.
I have a header file foo.h containing declaration of classes, typedefs and
insertion operators for the typedefs in a named namespace
namespace foo
{
class Foo
|
by: Jack Klein |
last post by:
I'm looking for opinions on a C technique I, and others, have used
successfully in the past. While some people swear by, apparently
others swear at it.
Assume a part of a program too large to fit comfortably in a single
source file, call it a "module". Let's call it "module A".
Also assume for various reasons module A needs a private data store
with static storage duration, accessible from files in more than one
translation unit. ...
|
by: John N. |
last post by:
Hi All,
Here I have a linked list each containing a char and is double linked.
Then I have a pointer to an item in that list which is the current
insertion point.
In this funtion, the user hits the right
and left keys to move this insertion point (cursor)
Here is the problem:
| | |
by: JJ |
last post by:
Hi,
I am working on a proj and was thinking of creating a Document object
that would hold filepath and doc name. And in this Document obj. when
page that is using it loads I would call a document obj method to
initialize the following javascipt:
<code>
if (new FileInfo(String.Format("{0}.pdf", strFile)).Exists)
{
|
by: Peter Michaux |
last post by:
Hi,
I'm playing with dynamic script insertion to make a request to the
server for a JavaScript file to be automatically run when it arrives
in the browser. It works but...
The page caching is too good. When I revisit the page or refresh the
page, and then redo the script insertion, the browser doesn't even hit
the server to check for a newer version of the JavaScript file. The
same old script runs with each insertion.
|
by: Don Li |
last post by:
Hi,
Env is ms sql server 2000.
ddl:
create table srchPool(tid int primary key, taid int, s tynyint, uid
tynyint);
-- and sql server automatically creates a clustered index for the pk
dml:
|
by: python_newbie |
last post by:
I don't know this list is the right place for newbie questions. I try
to implement insertion sort in pyhton. At first code there is no
problem. But the second one ( i code it in the same pattern i think )
doesn't work. Any ideas ?
------------------------------------------------------------
def insertion_sort(aList):
for i in range(len(aList)):
for j in range(i):
|
by: Prisoner at War |
last post by:
Friends, your opinions and advice, please:
I have a very simple JavaScript image-swap which works on my end but
when uploaded to my host at http://buildit.sitesell.com/sunnyside.html
does not work.
To rule out all possible factors, I made up a dummy page for an
index.html to upload, along the lines of <html><head><title></title></
head><body></body></html>.; the image-swap itself is your basic <img
src="blah.png"...
|
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...
| | |
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,...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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...
| | |
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
|
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...
| |
