<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Mohd Nizamuddin
»
Sending multiple rows to the Database from an...
53 posts, Page 1 of 6
1
2
3
4
5
»
»»
Sending multiple rows to the Database from an Application: Part II
Rate Topic
Display Mode
Topic Options
Author
Message
mohd.nizamuddin
mohd.nizamuddin
Posted Tuesday, August 11, 2009 4:56 PM
Old Hand
Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318,
Visits: 198
Comments posted to this topic are about the item
Sending multiple rows to the Database from an Application: Part II
Post #769021
aay-462091
aay-462091
Posted Tuesday, August 18, 2009 3:34 PM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 24, 2010 9:46 AM
Points: 1,
Visits: 17
How can I use where clause ??
Post #773105
conradr
conradr
Posted Wednesday, August 19, 2009 2:18 AM
Grasshopper
Group: General Forum Members
Last Login: Tuesday, December 08, 2009 8:23 AM
Points: 11,
Visits: 24
I'm Sorry.. I can see any benefit, ever, from sending muliple rows of data to a database. The mechansimsof transactional behaviour on the client side or destaging mechanisms where transactions are the problem should negate the need for ever doing this. I just think it's bad practice.
Post #773275
mohd.nizamuddin
mohd.nizamuddin
Posted Wednesday, August 19, 2009 2:50 AM
Old Hand
Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318,
Visits: 198
aay (8/18/2009)
How can I use where clause ??
I am not confirmed that you are asking about puting the WHERE clause in SP.
WHERE T.Item.value('@Salary', 'INT') < 10
This will filter records whose salary < 10.
Post #773291
mohd.nizamuddin
mohd.nizamuddin
Posted Wednesday, August 19, 2009 2:56 AM
Old Hand
Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318,
Visits: 198
conradr (8/19/2009)
I'm Sorry.. I can see any benefit, ever, from sending muliple rows of data to a database. The mechansimsof transactional behaviour on the client side or destaging mechanisms where transactions are the problem should negate the need for ever doing this. I just think it's bad practice.
This is always be a debate whether you use this kind of approach (bulk data manipulation) or not.
In my application it required. Moreover, that application is based on cross platform distributed architecture and our end user works in disconnected mode as well (sometime). So we come up this approach.
Post #773296
Richard L. Barnes
Richard L. Barnes
Posted Tuesday, August 25, 2009 7:49 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, October 09, 2009 4:03 AM
Points: 1,
Visits: 12
I prefer OPENXML. I think it is easy to then use WHERE, JOIN, and ORDER BY clauses. Using the data in the article, it would look like this:
Declare @xml XML, @idoc int
SET @xml = N'
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT Name, Salary FROM OPENXML (@idoc, '/ROWS/ROW', 1)
WITH (Name varchar(50), Salary int)
WHERE Salary >= 1500
ORDER BY Name
Post #776691
Jack Corbett
Jack Corbett
Posted Tuesday, August 25, 2009 7:51 AM
SSChampion
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571,
Visits: 11,871
Nice article(s). I'd be interested in seeing 3rd installment that compares performance between delimited strings and XML, you might also want to include an OPENXML implementation as well.
Jack Corbett
Applications Developer
Don't let the good be the enemy of the best. --
Paul Fleming
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #776695
lafleurh
lafleurh
Posted Tuesday, August 25, 2009 7:59 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, September 16, 2010 7:30 PM
Points: 3,
Visits: 15
In the theoretical sense, it should not be necessary to insert multiple rows in one command (as stated above). But in the practical sense, we have had to do this. The number of round trips to the database (for us) greatly affected performance.
Our technique (for MS SQL Server
and
Oracle) is not discussed here. We generate Insert statements and concatenate them into a string and execute them using an ADO.NET command object. It reduces the number of round trips to one--and it works.
Post #776702
Jack Corbett
Jack Corbett
Posted Tuesday, August 25, 2009 12:12 PM
SSChampion
Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571,
Visits: 11,871
lafleurh (8/25/2009)
In the theoretical sense, it should not be necessary to insert multiple rows in one command (as stated above). But in the practical sense, we have had to do this. The number of round trips to the database (for us) greatly affected performance.
Our technique (for MS SQL Server
and
Oracle) is not discussed here. We generate Insert statements and concatenate them into a string and execute them using an ADO.NET command object. It reduces the number of round trips to one--and it works.
Sure that will work, but it also opens up the server (SQL Server and Oracle) for SQL Injection. What are you doing to reduce that risk?
Jack Corbett
Applications Developer
Don't let the good be the enemy of the best. --
Paul Fleming
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #776953
pmohan
pmohan
Posted Tuesday, August 25, 2009 1:01 PM
SSC Journeyman
Group: General Forum Members
Last Login: Wednesday, June 08, 2011 9:47 AM
Points: 95,
Visits: 158
We are using the xml technique mentioned in this article in our system and it works like a charm.
Post #777007
« Prev Topic
|
Next Topic »
53 posts, Page 1 of 6
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
