Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Sending multiple rows to the Database from an Application: Part II Expand / Collapse
Author
Message
Posted Tuesday, August 11, 2009 4:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Tuesday, August 18, 2009 3:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, August 19, 2009 2:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 8, 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
Posted Wednesday, August 19, 2009 2:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Wednesday, August 19, 2009 2:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Tuesday, August 25, 2009 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 9, 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
Posted Tuesday, August 25, 2009 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
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
Posted Tuesday, August 25, 2009 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, August 25, 2009 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Saturday, August 30, 2014 4:51 PM
Points: 11,264, Visits: 13,023
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
Posted Tuesday, August 25, 2009 1:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 8, 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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse