?
Solved

XML string concatenation  very slow in SQL Server 2008

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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