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 25, 2009 1:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 16, 2010 12:30 PM
Points: 14, Visits: 24
>Since, delimited string would be passed as a string data type, so limitation of size of string data supported in SQL Server 2005 comes into picture (4000 / 8000 characters max including the delimiter character).

I'm not sure that is true. We use varchar(MAX) to pass comma delimited strings. I believe these are up to 2GB
Post #777038
Posted Tuesday, August 25, 2009 4:28 PM
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
If objects are secured properly and authentication mechanisms are secure, then SQL injection should not be an issue. Remember that you can send any command over ADO.NET as long as the authenticated user has permission to perform that command. Since the code is controlling the SQL that is generated and sent to the server, you have to take measures to make sure the code is secure (appostrophes are escaped, etc.)

This is the same issue if you are using something like NHibernate--it needs CRUD permissions on all tables--versus using stored procedures for all access while locking out table access.

SQL injection is an issue when you are generating DML, so you just have to take proper measures to deal with it and then thoroughly test for known injection methods. This should be part of your coding/testing to start off with. Bunching commands together in one string vs. individual strings is the only difference.

I'm not saying this is the best way to do things, and there are a lot of things that can go wrong if you don't anticipate problems (i.e. injection), but it is another technique.
Post #777157
Posted Tuesday, August 25, 2009 5:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 5:26 PM
Points: 3, Visits: 34
there is another choice:

Update SQL Server Data by Using XML Updategrams

An updategram is a data structure that you can use to express a change in the data. INSERT, UPDATE, and DELETE commands are represented in an updategram by the difference of the image of the data before and the image of the data after a change.

http://support.microsoft.com/kb/316018
Post #777178
Posted Tuesday, August 25, 2009 7:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 8:31 PM
Points: 2, Visits: 48
Very insightful article!

Any idea if UDT/CLR types can be used?

Say I have AddressInfo User Defined Type and table like this:

CREATE TABLE [AddressBook](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](25) NOT NULL,
[FirstName] [varchar](25) NOT NULL,
[Address] [dbo].[AddressInfo] NULL,
)

XML that I am trying to insert looks like this:

<AddressBook>
<Contact>
<LastName>DOE</LastName>
<FirstName>JOHN</FirstName>
<Address>
<Street>123 STREET</Street>
<City>CITY NAME</City>
<State>STATE NAME</State>
</Contact>
</AddressBook>


I tried this stored procedure:
CREATE PROCEDURE [dbo].[P_AddressBook_Insert]
(
@xmlAddressBook XML
)
AS
BEGIN

SET NOCOUNT OFF

INSERT
INTO [AddressBook]
([LastName], [FirstName], [Address])
SELECT
T.Item.query('./LastName').value('.', 'VARCHAR(25)') [LastName],
T.Item.query('./FirstName').value('.', 'VARCHAR(25)') [FirstName],
T.Item.query('./Address').value('.', 'AddressInfo') [Address],
FROM @xmlAddressBook.nodes('/AddressBook/Contact') AS T(Item)

END

However, I am not able to use AddressInfo UDT because it's not one of SQL Server built-in types.
I tried dbo.AddressInfo without single quotes it wouldn't take it either.
I also tried to use OpenXML for this and got the error message "CLR types cannot be used in an OpenXML WITH clause".

Please let me know if there is a syntax that I am missing or there is just no way to use UDTs with XML inserts.

Thank you!
Post #777209
Posted Tuesday, August 25, 2009 8:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 35,262, Visits: 31,745
Need to pass as many delimited strings as the number of parameters.

Not true... please consider the following...
http://www.sqlservercentral.com/articles/T-SQL/63003/

Since, delimited string would be passed as a string data type, so limitation of size of string data supported in SQL Server 2005 comes into picture (4000 / 8000 characters max including the delimiter character).
Definitely not true. Please check Books Online for VARCHAR(MAX) and NVARCHAR(MAX). It's not even true in SQL Server 2000 or SQL Server 7 if you don't mind working with the TEXT or NTEXT datatypes which can be passed as parameter in a stored procedure. There are also a fair number of fairly simple splitters for TEXT and NTEXT.

I would also like to see a performance and resource usage comparison between passing delimited strings and XML strings. It might also be interesting to take a look at what very few people take pause to look at... the impact on the "pipe" and resources. For example...

XML String:
<ROWS> 
<ROW Name="Richard" Salary="1100"/>
<ROW Name="Cliff" Salary="1200"/>
<ROW Name="Donna" Salary="13000"/>
<ROW Name="Ann" Salary="1500"/>
</ROWS>

According to MS-Word, that bit of XML is 151 characters including spaces.

Delimited String:
Richard,1100|Cliff,1200|Donna1,3000|Ann,1500|

According to MS-Word, that bit of delimited computational heaven is only 45 characters including the delimiters.

Let's do a quick bit of math... (151-45)/45 = ~2.356. Translation: You have to pass more than 2-1/3 times more data over the "pipe" AND through the I/O resources of SQL Server (and the underlying Windows Server) for XML to do the same thing as the delimited string. THEN, you have to shred the XML or parse the delimited string. I haven't done the performance/resource usage comparisons between shredding and parsing, but rumor has it that shredding is a bit resource hungry compared to well formed parsing techniques (cteTally or just a Tally table... recurrsion is as bad as most loops).

I've heard all the supposed advantages of passing XML like this... I'm just not finding they're all true especially the parts I listed at the beginning of this post.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #777219
Posted Tuesday, August 25, 2009 10:59 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
First of all thanks to every one for putting your valuable comment.



Jack Corbett (8/25/2009)
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?


I just wanted to add one more point, the application where we implemented these techniques is mainly using JAVA family technology and data can only be handled through SP no Inline query .


Pmohan: Thanks and good to see that someone get benefitted.


mbrunton
I'm not sure that is true. We use varchar(MAX) to pass comma delimited strings. I believe these are up to 2GB

No, even if you use varchar(max). It will hold 4000 / 8000 characters max including the delimiter character.
Please check the Part-I forum, where I posted the testing script.



Post #777258
Posted Wednesday, August 26, 2009 2:03 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
Max Minkov (8/25/2009)
Very insightful article!

  INSERT
INTO [AddressBook]
([LastName], [FirstName], [Address])
SELECT
T.Item.query('./LastName').value('.', 'VARCHAR(25)') [LastName],
T.Item.query('./FirstName').value('.', 'VARCHAR(25)') [FirstName],
T.Item.query('./Address').value('.', 'AddressInfo') [Address],
FROM @xmlAddressBook.nodes('/AddressBook/Contact') AS T(Item)



You can use the following SQL syntax.

SELECT
T.Item.query('./LASTNAME').value('.', 'VARCHAR(25)') [LastName],
T.Item.query('./FIRSTNAME').value('.', 'VARCHAR(25)') [FirstName],
T.Item.query('./ADDRESS/STREET').value('.', 'Varchar(20)') + ','
+ T.Item.query('./ADDRESS/CITY').value('.', 'Varchar(20)') + ','
+ T.Item.query('./ADDRESS/STATE').value('.', 'Varchar(20)') [Address]
FROM @xmlAddressBook.nodes('/ADDRESSBOOK/CONTACT') AS T(Item)

Hope, you could have a Parse function in your CLR type defined, which takes a string type as an parameter and in the function you split the string. Hope you are using the delimiter as "," in the Parse function. (Plese check).

The same delimiter , you have to add in SELECT + ',' , which you are using in the Parse method.

hope this could help you to solve your problem.
Post #777341
Posted Wednesday, August 26, 2009 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 8:31 PM
Points: 2, Visits: 48

Hope, you could have a Parse function in your CLR type defined, which takes a string type as an parameter and in the function you split the string. Hope you are using the delimiter as "," in the Parse function. (Plese check).

The same delimiter , you have to add in SELECT + ',' , which you are using in the Parse method.

hope this could help you to solve your problem.


Thank you very much that did it!
Post #777680
Posted Thursday, August 27, 2009 2:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, October 30, 2010 5:47 AM
Points: 4, Visits: 25
I have used XML to insert multiple rows using a SP for years now. I certainly see the benefits. There is a huge performance boost when inserting data aggregations that need to be placed in multiple related tables. You just hand over a big chunk of XML to the SP, and it can handle all the inserts within a single transaction. The code is not that complex. It usually just boils down to a set of 'INSERT INTO ... SELECT FROM ..' statements.

Post #778109
Posted Thursday, August 27, 2009 3:09 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
dewit.john (8/27/2009)
I have used XML to insert multiple rows using a SP for years now. .... The code is not that complex. It usually just boils down to a set of 'INSERT INTO ... SELECT FROM ..' statements.


Thanks John.
Post #778114
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse