|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Executing stored procedure against openxml rowsetwould define user account info to create a set of new accounts against one database call. Something like this.. <accounts> <account user="testAcct1" pass="testPass1" type="LN"/> <account user="testAcct2" pass="testPass2" type="LN"/> .... <account user="testAcctN" pass="testPassN" type="LN"/> </accounts> Im executing a stored procedure in asp.net and passing this xml into sql server (2000). Id then like to take the rowset created by openxml on this account data to run another stored procedure that actually creates the accounts. this second stored proc runs the transaction and does several other things to enforce some rules against the account creation process the first stored proc (called by ado.net) ******************************************************************************************************** CREATE PROCEDURE dbo.usp_importAccountsViaOpenXML @inputXML varchar(2000) AS DECLARE @iDoc int EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @inputXML SELECT userName, userPass, userAcctType INTO tempTable --(is this needed?) FROM OPENXML(@iDoc,'/accounts/account',2) WITH (userName NVARCHAR(20) '@user', userPass NVARCHAR(50) '@pass', userAcctType CHAR(10) '@type') -- Id like to run the second stored procedure here against every row in the openxml query EXEC dbo.usp_createNewUserAccount @params pulled from openxml rowset EXECUTE sp_xml_removedocument @iDoc GO ******************************************************************************************************** Would this involve a cursor or is there a better way to do this? The total batch size is limited to 300 accounts so there is no need to worry about the solution getting out of hand. Any suggestions would be a huge help! Thanks in advance! CJ,
Try inserting the result set into a table variable and use a while loop to traverse it. declare @t table (c1 int not null identity unique, ...) declare @rc int declare @error int declare @i int insert into @t (c2, c3, ...) select ... from openxml ... select @rc = @@rowcount, @error = @@error EXECUTE sp_xml_removedocument @iDoc if @error = 0 and @rc > 0 begin select @i = min(c1) from @t while @i is not null begin select @p1 = c2, ..., @pn = cn from @t where c1 = @i EXEC dbo.usp_createNewUserAccount @p1, ..., @pn select @i = min(c1) from @t where c1 > @i end end .... AMB Show quote "CJ" wrote: > Im trying to run a batch user account process where a chunk of xml > would define user account info to create a set of new accounts against > one database call. Something like this.. > > <accounts> > <account user="testAcct1" pass="testPass1" type="LN"/> > <account user="testAcct2" pass="testPass2" type="LN"/> > .... > <account user="testAcctN" pass="testPassN" type="LN"/> > </accounts> > > Im executing a stored procedure in asp.net and passing this xml into > sql server (2000). > Id then like to take the rowset created by openxml on this account data > to run another stored procedure that actually creates the accounts. > this second stored proc runs the transaction and does several other > things to enforce some rules against the account creation process > > the first stored proc (called by ado.net) > > ******************************************************************************************************** > CREATE PROCEDURE dbo.usp_importAccountsViaOpenXML > > @inputXML varchar(2000) > > AS > DECLARE @iDoc int > > EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @inputXML > > SELECT userName, userPass, userAcctType > INTO tempTable --(is this needed?) > FROM OPENXML(@iDoc,'/accounts/account',2) > WITH (userName NVARCHAR(20) '@user', > userPass NVARCHAR(50) '@pass', > userAcctType CHAR(10) '@type') > > -- Id like to run the second stored procedure here against every row in > the openxml query > EXEC dbo.usp_createNewUserAccount @params pulled from openxml rowset > > > EXECUTE sp_xml_removedocument @iDoc > GO > > ******************************************************************************************************** > > Would this involve a cursor or is there a better way to do this? The > total batch size is limited to 300 accounts so there is no need to > worry about the solution getting out of hand. Any suggestions would be > a huge help! > > Thanks in advance! > > Many thanks Alejandro! Strangely, I implemented this code and also
implemented a cursor based implementation and the cursor was actually about 20-30% faster at 300 accounts. Not sure why though. CJ (Charles.Deis***@gmail.com) writes:
> Many thanks Alejandro! Strangely, I implemented this code and also That's not strange at all. Alejandro's solution is a good example when> implemented a cursor based implementation and the cursor was actually > about 20-30% faster at 300 accounts. Not sure why though. the mantra "Don't use cursors!" backfires and leads to even poorer solutions. If you need to iterate, use a cursor. Preferably one that is INSENSITIVE. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland,
With all due respect your messages here appear to contradict themselves. Cursors or not? If you have a better way of doing this, drop me a hint as personally i'd prefer not to offer this type of functionality in a web application. My idea of making it more 'scalable' is to do this import via xml instead of hammering the database with 300 calls. The reason behind using a cursor to execute the stored proc is because the results from the insert are actually hitting sevral other tables. Any thoughts? Regards- CJ CJ (Charles.Deis***@gmail.com) writes:
> With all due respect your messages here appear to contradict Contradiction? Nah, what I said that if you absolutely must iterate> themselves. Cursors or not? If you have a better way of doing this, > drop me a hint as personally i'd prefer not to offer this type of > functionality in a web application. My idea of making it more > 'scalable' is to do this import via xml instead of hammering the > database with 300 calls. The reason behind using a cursor to execute > the stored proc is because the results from the insert are actually > hitting sevral other tables. Any thoughts? it, do it through cursors. I understand that if you already have a stored procedure which inserts data for one account, that you want to reuse that procedure. And it might be the right thing. And it might even be the right thing, even if you have to write this stored procedure from scratch. It canont be denied that working with scalar variables are easier. But the obvious trade-off is that it comes with a performance cost. And while for 300 rows, you are not likely to suffer any serious performance issue, I still like to encourage you to do this all set-based as an exercise for future tasks. I am not talking out of the blue about this. Right now at work I'm working with rewriting a stored procedure that performs a set of important updates for one single instance of an item. I'm now writing a new version that will be able to perform this update at many instances of this item at the same time. At some of our customer sites, they perform about 18000 such updates daily, so they will see a drastic change in execution time from more han half an hour down to a minute or so. The time estimate for this task is more than two weeks of work - and we have an even more complex process that we will need to tackle in the future. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx CJ (Charles.Deis***@gmail.com) writes:
Show quote > **************************** Calling a cursor is one option. However, better is to teach yourself> CREATE PROCEDURE dbo.usp_importAccountsViaOpenXML > > @inputXML varchar(2000) > > AS > DECLARE @iDoc int > > EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @inputXML > > SELECT userName, userPass, userAcctType > INTO tempTable --(is this needed?) > FROM OPENXML(@iDoc,'/accounts/account',2) > WITH (userName NVARCHAR(20) '@user', > userPass NVARCHAR(50) '@pass', > userAcctType CHAR(10) '@type') > > -- Id like to run the second stored procedure here against every row in > the openxml query > EXEC dbo.usp_createNewUserAccount @params pulled from openxml rowset >... > > Would this involve a cursor or is there a better way to do this? The > total batch size is limited to 300 accounts so there is no need to > worry about the solution getting out of hand. Any suggestions would be > a huge help! to write scalable solutions, and and insert all acounts in one go. Today you got 300 accounts - tomorrow maybe you get 3000 accounts, and users are starting to complain about performance. If you still want to create the new accounts in a different procedure, I discuss a couple of techniques in an article on my web site: http://www.sommarskog.se/share_data.html. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics