January 14, 2011 at 6:17 am
We uee BizTalk to call a stored procedure and insert records into a table. One of the parameters is XML, so we are shredding it into a table varaible and then loading from the table variable. The seems to be faster than reading the XML in the insert. We tried OpenXML and XQuery and found that OpenXML was much faster. Here's the problem: When BizTalk has about 60000 messages queued up and then starts firing them to SQL, we sometimes get errors about a primary key violation. There are no duplicates being passed in in the XML variable. The table layouts and code are below. The example I am providing is from a trace file where SQL returned the error. The key is the identity column being created in the Header record on the insert (1 record) and then that identity column and the Store coming in in the XML. Are there any memory issues with OpenXML? Anyone have any ideas?
Edit: We are 2008 SP2 on a Windows Server 2003
Tables:
CREATE TABLE [tbl_Header]
(
[DistributionID] [int] NOT NULL IDENTITY(1, 1),
[Division] [int] NOT NULL,
[Department] [int] NOT NULL,
[ClassNum] [int] NOT NULL,
[Category] [int] NOT NULL,
[Seasonality] [char] (1) NOT NULL,
[FromWarehouse] [int] NOT NULL,
[LPMonth] [datetime] NOT NULL,
[Type] [varchar] (1) NOT NULL,
[DistributionDate] [datetime] NOT NULL,
[DistributionDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_Header.DistributionID]
PRIMARY KEY NONCLUSTERED ([DistributionID])
)
go
CREATE TABLE [tbl_Detail]
(
[DistributionID] [int] NOT NULL,
[Zone] [int] NOT NULL,
[Store] [int] NOT NULL,
[TotalUnits] [int] NOT NULL,
[TotalValue] [int] NOT NULL,
[ToWarehouse] [int] NOT NULL,
[ADDate] [datetime] NULL,
CONSTRAINT [PK_tbl_Detail.DistributionIDStore]
PRIMARY KEY NONCLUSTERED ([DistributionID], [Store])
)
go
ALTER TABLE [tbl_Detail] WITH NOCHECK ADD
CONSTRAINT [FK_tbl_Detail_DistributionID_tbl_Header_DistributionID]
FOREIGN KEY ([DistributionID]) REFERENCES [tbl_Header] ([DistributionID])
Go
Procedure:
Create Procedure usp_AddDistributions
(
@divisionId int,
@deptId int,
@catgId int,
@season char(1),
@fromDC int,
@type char(1),
@lpMonth DateTime,
@distroDate DateTime,
@distroDetails xml
)
AS
Begin
declare @vt_Results table (Store int,
Zone smallint,
TotalUnits int,
TotalValue int,
ADDate smalldatetime,
ToWarehouse int)
Declare @justDate varchar(10),
@XMLDocHandle int,
@vs_ErrorMessage varchar(200)
Set @justDate = Convert(varchar(2), DATEPART(month,@distroDate))+'/'+Convert(varchar(2), DATEPART(day,@distroDate))+'/'+Convert(varchar(4), DATEPART(year,@distroDate))
Begin Transaction
begin try
-- Populate table variable with XML variable
EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @distroDetails
insert into @vt_Results (Store, Zone, TotalUnits, TotalValue, ADDate, ToWarehouse)
select Store, Zone, TotalUnits, TotalValue, Case when ADDate = '' then null else cast(AdDate as smalldatetime) end, ToWarehouse
from OPENXML (@XMLDocHandle, '/ArrayOfDistro/Distro', 2)
WITH (Store int, Zone smallint,TotalUnits int,TotalValue decimal(10,2),ADDate varchar(20),ToWarehouse int)
EXEC sp_xml_removedocument @XMLDocHandle
Insert tbl_header (
Division, Department, ClassNum, Category, Seasonality, FromWarehouse, LPMonth,
[Type], DistributionDateTime, DistributionDate)
Values (@divisionId, @deptId, @CatgID/100, @catgId, @season, @fromDC, @lpMonth,
@type, @distroDate, @justDate)
Insert tbl_Detail (
DistributionID, Zone, Store, TotalUnits , TotalValue , ADDate, ToWarehouse)
Select
@@Identity as DistributionID, Zone, Store, TotalUnits, TotalValue, AdDate, ToWarehouse
from @vt_Results
commit Transaction
end try
begin catch
Rollback Transaction
select @vs_ErrorMessage = Error_Message()
RaisError(@vs_ErrorMessage, 16, 1)
return
end catch
end
go
Example:
declare @p11 xml
set @p11=convert(xml,N'<ArrayOfDistro><Distro><Store>49</Store><Zone>2</Zone><TotalUnits>1</TotalUnits>
<TotalValue>13</TotalValue><ADDate>2011-01-01</ADDate><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>63</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>68</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>173</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>242</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>247</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>300</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>351</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>353</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>369</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>394</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>395</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>403</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>422</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>438</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>458</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>463</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>477</Store><Zone>2</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>513</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>563</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>576</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>586</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>592</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>594</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>621</Store><Zone>2</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>644</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>668</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>687</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>715</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>742</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>763</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>768</Store><Zone>2</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>809</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>815</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>827</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>836</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>839</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>856</Store><Zone>2</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>859</Store><Zone>2</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1005</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1021</Store><Zone>2</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>20</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>51</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>55</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>105</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>128</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>141</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>156</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>172</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>193</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>229</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>248</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>277</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>279</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>288</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>294</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>313</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>316</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>322</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>343</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>426</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>428</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>480</Store><Zone>3</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>506</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>509</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>510</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>522</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>574</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>587</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>597</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>602</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>641</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>652</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>662</Store><Zone>3</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>702</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>703</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>712</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>765</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>776</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>777</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>793</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>797</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>804</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>819</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>829</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>843</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>860</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>878</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1004</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1014</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1033</Store><Zone>3</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1035</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1038</Store><Zone>3</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1053</Store><Zone>3</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1073</Store><Zone>3</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>108</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>124</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>145</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>149</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>153</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>167</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>176</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>194</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>196</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>264</Store><Zone>4</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>284</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>320</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>329</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>333</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>350</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>356</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>372</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>388</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>392</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>398</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>399</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>404</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>405</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>406</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>408</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>425</Store><Zone>4</Zone><TotalUnits>7</TotalUnits><TotalValue>91</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>429</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>431</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>464</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>465</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>473</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>486</Store><Zone>4</Zone><TotalUnits>7</TotalUnits><TotalValue>91</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>511</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>512</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>535</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>541</Store><Zone>4</Zone><TotalUnits>6</TotalUnits><TotalValue>78</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>557</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>650</Store><Zone>4</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>651</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>653</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>678</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>771</Store><Zone>4</Zone><TotalUnits>4</TotalUnits><TotalValue>52</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>789</Store><Zone>4</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>791</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>803</Store><Zone>4</Zone><TotalUnits>1</TotalUnits><TotalValue>13</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>813</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>866</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>867</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1003</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1010</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1018</Store><Zone>4</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1019</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1036</Store><Zone>4</Zone><TotalUnits>7</TotalUnits><TotalValue>91</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1042</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1062</Store><Zone>4</Zone><TotalUnits>2</TotalUnits><TotalValue>26</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1077</Store><Zone>4</Zone><TotalUnits>3</TotalUnits><TotalValue>39</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>599</Store><Zone>5</Zone><TotalUnits>6</TotalUnits><TotalValue>78</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>625</Store><Zone>5</Zone><TotalUnits>11</TotalUnits><TotalValue>143</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>627</Store><Zone>5</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>628</Store><Zone>5</Zone><TotalUnits>6</TotalUnits><TotalValue>78</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>629</Store><Zone>5</Zone><TotalUnits>6</TotalUnits><TotalValue>78</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>630</Store><Zone>5</Zone><TotalUnits>10</TotalUnits><TotalValue>130</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>631</Store><Zone>5</Zone><TotalUnits>7</TotalUnits><TotalValue>91</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>632</Store><Zone>5</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>633</Store><Zone>5</Zone><TotalUnits>7</TotalUnits><TotalValue>91</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>636</Store><Zone>5</Zone><TotalUnits>5</TotalUnits><TotalValue>65</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>749</Store><Zone>5</Zone><TotalUnits>10</TotalUnits><TotalValue>130</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>812</Store><Zone>5</Zone><TotalUnits>6</TotalUnits><TotalValue>78</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1030</Store><Zone>5</Zone><TotalUnits>11</TotalUnits><TotalValue>143</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1078</Store><Zone>5</Zone><TotalUnits>11</TotalUnits><TotalValue>143</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1086</Store><Zone>5</Zone><TotalUnits>7</TotalUnits><TotalValue>91</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro><Distro><Store>1092</Store><Zone>5</Zone><TotalUnits>11</TotalUnits><TotalValue>143</TotalValue><ADDate/><ToWarehouse>887</ToWarehouse></Distro></ArrayOfDistro>')
declare @p12 int
set @p12=0
exec sp_executesql N'EXEC @RETURN_VALUE = [usp_AddDistributions] @divisionId = @p0, @deptId = @p1, @catgId = @p2, @season = @p3, @fromDC = @p4, @type = @p5, @lpMonth = @p6, @distroDate = @p7, @distroDetails = @p8',N'@p0 int,@p1 int,@p2 int,@p3 char(1),@p4 int,@p5 char(1),@p6 datetime,@p7 datetime,@p8 xml,@RETURN_VALUE int output',@p0=10,@p1=73,@p2=6120,@p3='B',@p4=887,@p5='H',@p6='2010-12-01 00:00:00',@p7='2011-01-11 14:33:11',@p8=@p11,@RETURN_VALUE=@p12 output
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2011 at 8:14 am
I would use a slightly different approach:
Store the xml data in table with a xml data type column.
Add an xml index.
Shred the data using XQuery instead of OpenXML.
To get an ID value per row I would use ROW_NUMBER() instead of inserting into a staging table. If there would be the need for a staging table then I would use a temp table instead of a table variable.
CREATE TABLE #tbl_xml (id int primary key clustered, data xml)
INSERT INTO #tbl_xml SELECT 1,@p11
CREATE PRIMARY XML INDEX PXML_#tbl_xml_data
ON #tbl_xml (data);
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as id,
c.value('Store[1]','VARCHAR(30)') as Store,
c.value('Zone[1]','VARCHAR(30)') as Zone,
c.value('TotalUnits[1]','VARCHAR(30)') as TotalUnits,
c.value('TotalValue[1]','VARCHAR(30)') as TotalValue
FROM #tbl_xml
CROSS APPLY data.nodes('/ArrayOfDistro/Distro') T(c)
January 14, 2011 at 8:45 am
Thanks, Lutz. I'll try it. I don't need the rownumber. I'm using the identity column from the record just inserted into the header table to stamp each record in the detail table
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2011 at 9:22 am
Lutz, I tried your approach, but it took alot longer than the original. When Using OpenXML and shredding into a table variable the result took .073 ms. When I tried your approach, it took 420 ms. Again, the process works ok when not under significant load, but once we ramped up the load, I started seeing duplicate errors sporadically. It seemed like ot was reading the data in the table variable from one of the other sessions, even though that seems impossible.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2011 at 9:40 am
Instead of using @@Identity, use SCOPE_IDENTITY().
Also, we have also found that openxml was faster that XQuery. If we queried more than 3 nodes on the xml then we found openxml faster.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply