Solved

XML string concatenation  very slow in SQL Server 2008

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 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