Solved

XML string concatenation  very slow in SQL Server 2008

Posted on 2010-08-30
4
636 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:venk_r
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33563683
Maybe you can show us what your loop looks like?
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.
0
 
LVL 8

Author Comment

by:venk_r
ID: 33567029
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,startdatekey,enddatekey,partitionname)
SELECT Accounts_Key,852 AS startdatekey,883 as enddatekey,ltrim(rtrim(convert(varchar(10),AccountsRID))) +'-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=partitionname,@AccountsKey=Accounts_Key,@StartDateKey=startdatekey,
@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</CubeID>
    <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_DataMart</DataSourceID>
        <QueryDefinition>
          select TracksRID,State_Key,MobileUnits_Key,Accounts_Key,Drivers_Key,UtcSatDate_Key,
          Time_Key,Pins_Key,Speed_Key,Zones_Key,Locations_Key,Lat,Lon,Mileage,
          GreatCircleDistance,IdleTimeMinutes from fact_Tracks                WHERE Accounts_Key= '+ convert(varchar(10),@AccountsKey) +'  and UtcSatDate_Key >= '+convert(varchar(10),@StartDateKey) +'  and UtcSatDate_Key < '+convert(varchar(10),@EndDateKey) +'
        </QueryDefinition>
      </Source>
      <StorageMode>Molap</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <Source xsi:type="ProactiveCachingInheritedBinding" />
      </ProactiveCaching>
      <EstimatedRows>249999781</EstimatedRows>
      <AggregationDesignID>AggregationDesign_30pcnt</AggregationDesignID>
    </Partition>
  </ObjectDefinition>
</Create>'


/*insert into Cube_Partition(
AccountsKey,
CubeName,
MeasureGroup,
ProcessedDate,
StartDate,
EndDate,
PartitionName,
PartitionType)
values(@AccountsKey,'Olap Database For Venkat','Fact Tracks',GETDATE(),@StartDateKey,@EndDateKey,@PartitionName,'M')*/

SET @inc=@inc+1
END
SET @STR=@STR+ '</Batch>'
EXEC (@str) AT DASHBOARD
END
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33568119
try the below:
DECLARE @str nvarchar(max)

SET @str='<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">'

SET @str=@str+ (
select '<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
   <ParentObject>
    <DatabaseID>Olap Database For Venkat</DatabaseID>
    <CubeID>Cube_DataMart</CubeID>
    <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_DataMart</DataSourceID>
        <QueryDefinition>
          select TracksRID,State_Key,MobileUnits_Key,Accounts_Key,Drivers_Key,UtcSatDate_Key,
          Time_Key,Pins_Key,Speed_Key,Zones_Key,Locations_Key,Lat,Lon,Mileage, 
          GreatCircleDistance,IdleTimeMinutes from fact_Tracks                WHERE Accounts_Key= '+ convert(varchar(10),Accounts_Key) +'  and UtcSatDate_Key >= '+convert(varchar(10),startdatekey) +'  and UtcSatDate_Key < '+convert(varchar(10),enddatekey) +' 
        </QueryDefinition>
      </Source>
      <StorageMode>Molap</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <Source xsi:type="ProactiveCachingInheritedBinding" />
      </ProactiveCaching>
      <EstimatedRows>249999781</EstimatedRows>
      <AggregationDesignID>AggregationDesign_30pcnt</AggregationDesignID>
    </Partition>
  </ObjectDefinition>
</Create>'
from #tmpAccount for xml path('')
)

SET @STR=@STR+ '</Batch>'
EXEC (@str) AT DASHBOARD
END

Open in new window

0
 
LVL 8

Author Closing Comment

by:venk_r
ID: 33757926
thanks
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question