Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending multiple rows to the Database from an Application: Part II


Sending multiple rows to the Database from an Application: Part II

Author
Message
mbrunton
mbrunton
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
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
lafleurh
lafleurh
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
bruce lee-206043
bruce lee-206043
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 37
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
Max Minkov
Max Minkov
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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:




DOE
JOHN

123 STREET
CITY NAME
STATE NAME






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!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51319 Visits: 40305
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:
 







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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 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.
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 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.
Max Minkov
Max Minkov
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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!
dewit.john
dewit.john
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
mohd.nizamuddin
mohd.nizamuddin
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search