Solved

XML string concatenation  very slow in SQL Server 2008

Posted on 2010-08-30
4
629 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
  • 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

785 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