venk_r
asked on
XML string concatenation very slow in SQL Server 2008
XML string concatenation in a for loop is very slow in SQL Server 2008.I am building a dynamic
XML string within a for loop and its very very slow. Please suggest if there is any alternative
XML string within a for loop and its very very slow. Please suggest if there is any alternative
ASKER
Please find the bow script that I use to create partitions in XMLA Batch
BEGIN
create table #tmpAccounT (Accounts_Key int,startdatekey int,enddatekey int,partitionname varchar(50))
insert into #tmpAccounT(Accounts_Key,s tartdateke y,enddatek ey,partiti onname)
SELECT Accounts_Key,852 AS startdatekey,883 as enddatekey,ltrim(rtrim(con vert(varch ar(10),Acc ountsRID)) ) +'-May2010'
as partitionname FROM dim_Accounts
DECLARE @partitionname as VARCHAR(50)
DECLARE @AccountsKey INT
DECLARE @StartDateKey INT
DECLARE @EndDateKey INT
DECLARE @cnt INT
DECLARE @inc INT
DECLARE @str nvarchar(max)
SELECT @cnt=count(*) from #tmpAccounT
SET @inc=1
SET @str='<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">'
WHILE @inc<=@cnt
BEGIN
SELECT @partitionname=partitionna me,@Accoun tsKey=Acco unts_Key,@ StartDateK ey=startda tekey,
@EndDateKey=enddatekey FROM #tmpAccounT WHERE Accounts_Key=@inc
SET @str=@str+ '<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Olap Database For Venkat</DatabaseID>
<CubeID>Cube_DataMart</Cub eID>
<MeasureGroupID>Fact Tracks</MeasureGroupID>
</ParentObject>
<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<ID>'+ @PartitionName +'</ID>
<Name>'+ @PartitionName +'</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>DataSource_D ataMart</D ataSourceI D>
<QueryDefinition>
select TracksRID,State_Key,Mobile Units_Key, Accounts_K ey,Drivers _Key,UtcSa tDate_Key,
Time_Key,Pins_Key,Speed_Ke y,Zones_Ke y,Location s_Key,Lat, Lon,Mileag e,
GreatCircleDistance,IdleTi meMinutes from fact_Tracks WHERE Accounts_Key= '+ convert(varchar(10),@Accou ntsKey) +' and UtcSatDate_Key >= '+convert(varchar(10),@Sta rtDateKey) +' and UtcSatDate_Key < '+convert(varchar(10),@End DateKey) +'
</QueryDefinition>
</Source>
<StorageMode>Molap</Storag eMode>
<ProcessingMode>Regular</P rocessingM ode>
<ProactiveCaching>
<SilenceInterval>-PT1S</Si lenceInter val>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>- PT1S</Sile nceOverrid eInterval>
<ForceRebuildInterval>-PT1 S</ForceRe buildInter val>
<Source xsi:type="ProactiveCaching InheritedB inding" />
</ProactiveCaching>
<EstimatedRows>249999781</ EstimatedR ows>
<AggregationDesignID>Aggre gationDesi gn_30pcnt< /Aggregati onDesignID >
</Partition>
</ObjectDefinition>
</Create>'
/*insert into Cube_Partition(
AccountsKey,
CubeName,
MeasureGroup,
ProcessedDate,
StartDate,
EndDate,
PartitionName,
PartitionType)
values(@AccountsKey,'Olap Database For Venkat','Fact Tracks',GETDATE(),@StartDa teKey,@End DateKey,@P artitionNa me,'M')*/
SET @inc=@inc+1
END
SET @STR=@STR+ '</Batch>'
EXEC (@str) AT DASHBOARD
END
BEGIN
create table #tmpAccounT (Accounts_Key int,startdatekey int,enddatekey int,partitionname varchar(50))
insert into #tmpAccounT(Accounts_Key,s
SELECT Accounts_Key,852 AS startdatekey,883 as enddatekey,ltrim(rtrim(con
as partitionname FROM dim_Accounts
DECLARE @partitionname as VARCHAR(50)
DECLARE @AccountsKey INT
DECLARE @StartDateKey INT
DECLARE @EndDateKey INT
DECLARE @cnt INT
DECLARE @inc INT
DECLARE @str nvarchar(max)
SELECT @cnt=count(*) from #tmpAccounT
SET @inc=1
SET @str='<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">'
WHILE @inc<=@cnt
BEGIN
SELECT @partitionname=partitionna
@EndDateKey=enddatekey FROM #tmpAccounT WHERE Accounts_Key=@inc
SET @str=@str+ '<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>Olap Database For Venkat</DatabaseID>
<CubeID>Cube_DataMart</Cub
<MeasureGroupID>Fact Tracks</MeasureGroupID>
</ParentObject>
<ObjectDefinition>
<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<ID>'+ @PartitionName +'</ID>
<Name>'+ @PartitionName +'</Name>
<Source xsi:type="QueryBinding">
<DataSourceID>DataSource_D
<QueryDefinition>
select TracksRID,State_Key,Mobile
Time_Key,Pins_Key,Speed_Ke
GreatCircleDistance,IdleTi
</QueryDefinition>
</Source>
<StorageMode>Molap</Storag
<ProcessingMode>Regular</P
<ProactiveCaching>
<SilenceInterval>-PT1S</Si
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-
<ForceRebuildInterval>-PT1
<Source xsi:type="ProactiveCaching
</ProactiveCaching>
<EstimatedRows>249999781</
<AggregationDesignID>Aggre
</Partition>
</ObjectDefinition>
</Create>'
/*insert into Cube_Partition(
AccountsKey,
CubeName,
MeasureGroup,
ProcessedDate,
StartDate,
EndDate,
PartitionName,
PartitionType)
values(@AccountsKey,'Olap Database For Venkat','Fact Tracks',GETDATE(),@StartDa
SET @inc=@inc+1
END
SET @STR=@STR+ '</Batch>'
EXEC (@str) AT DASHBOARD
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
In general, if you can do it using a query to line up all the rows of the loop, then run a query using FOR XML, it will be a lot faster.