Hi All, I have got a requirement to call a proc with input parameter. The input params are the column values for a table. The params can be for multiple rows. The issue is with the performance. 1. How to send the params as bulk (xml, delimited data, ???) 2. How to handle/split the input params, break it into individual insert statement in a loop in a optimal way
Send the XML string to your proc as varchar(max), INSERT to temp table then do set based operations from the temp table. (or skip the temp table and do direct insert into your production table if that is all you need to do) EDIT: note there is no root tag for this example to work. DECLARE @xml varchar(max) = '1234567892016-01-01 1234567802016-01-02' EXEC [dbo].[prcUpdateMailDates] @xml ------------------------------- CREATE PROC [dbo].[prcUpdateMailDates] @MailDates varchar(max) AS SET NOCOUNT ON DECLARE @MailDatesXML xml SET @MailDatesXML = @MailDates CREATE TABLE #MailDates (CardNumber bigint, MailDate date) INSERT #MailDates SELECT r.n.value('(CardNumber/text())', 'bigint') [CardNumber], r.n.value('(MailDate/text())', 'date') [Date] FROM @MailDatesXML.nodes('/Card')r([n]) UPDATE tmd SET MailDate = md.MailDate FROM #MailDates md JOIN dbo.tblMailDates tmd ON (tmd.CardNumber = md.CardNumber)
The issue with using TVF is, Java jdbc driver doesn't support this. I had to eliminate this option at the beginning itself. At present I have asked the Java developer to call the proc with data like: '(colval1, colval2, colval3, ...), (colval11, colval22, colval33..), ...'. This way I can avoid splitting the dataset for individual rows. I would be able to create a single insert statement like: INSERT INTO (cols...) VALUES (colval1, colval2, colval3, ...), (colval11, colval22, colval33..), .... I would like to explore some other option as well before finalizing the above one.