[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

XML string concatenation  very slow in SQL Server 2008

Posted on 2010-08-30
4
Medium Priority
?
641 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 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