Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

xml in subselect and performance => alternatives? Expand / Collapse
Author
Message
Posted Wednesday, November 21, 2012 9:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
hi there,

many times there is the requirement of selecting multiple records into one xml-string. Usually you do this using a subselect. Here's a simplified example for showing what i mean (most of you will be familiar with this kind of query):
declare @Parcel table (
ParcelId int,
Shipper varchar(50),
Consignee varchar(50)
);

declare @ParcelDetail table(
ParcelId int,
ScannedAtStation varchar(50),
ScannedAtTime datetime
);

insert into @Parcel values
(1, 'Romeo', 'Julia' ),
(2, 'Donald', 'Daisy' ),
(3, 'someone', 'nobody' )

insert into @ParcelDetail values
( 1, 'Austria', '20120101' ),
( 1, 'Germany', '20120103' ),
( 1, 'UK', '20120110' ),
( 2, 'Spain', '20120101' ),
( 2, 'Portugal', '20120201' ),
( 3, 'Italy', '20120301' ),
( 3, 'Switzerland', '20120310' )

select
p.ParcelId,
p.Shipper,
p.Consignee,
TransportRoute =
(
select *
from @ParcelDetail detail
where detail.ParcelId = p.ParcelId
for xml auto
)
from @Parcel p

The problem in my case is the performance if the number of records raises. Of course the last statement is a loop: make a table scan (or clustered index seek if possible) on the inner table for each outer table.

My target is to eliminate the loop. I think if I scan each table once I have all the data i need to serve the whole query.
My idea is to join the two tables and let the XML be generated by a kind of aggregate. For example I join the two tables and make a group by, so both tables are only scanned once, like this:
select
p.ParcelId,
p.Shipper,
p.Consignee,
TransportRoute =
(
select detail.*
for xml auto
)
from @Parcel p inner join
@ParcelDetail det on
det.ParcelId = p.ParcelId
group by
p.ParcelId,
p.Shipper,
p.Consignee

This doesn't work. The question is: can we realize this query?

Another try would be following: as the "for xml auto" expects a table where the data is selected from I tried to simulate a table using the values-clause. Without success.
select
p.ParcelId,
p.Shipper,
p.Consignee,
(
select *
from (
values(
det.ParcelId,
det.ScannedAtStation,
det.ScannedAtTime
)) as x(col1)
with xml auto
)
from @Parcel p inner join
@ParcelDetail det on
det.ParcelId = p.ParcelId
group by
p.ParcelId,
p.Shipper,
p.Consignee

By the way: the xml is requestet this way, there is no sense in discussing whether there are alternatives to the xml string itself or not only the way getting there is the question

Thank's a lot for any suggestions,
Wolf

Post #1387473
Posted Wednesday, November 21, 2012 11:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
The way you're doing it is pretty standard. You could switch from an inline sub-query to a Cross/Outer Apply in the From clause, but it won't really do much except change how the query reads.

In your actual query, as opposed to the sample provided, is it actually using table variables, or does it use persisted tables? If so, check the indexing on them. If it's done right, that kind of query can be very fast, unless the amount of data being returned is simply huge.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1387534
Posted Wednesday, November 21, 2012 11:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
hello GSquared,

thank's for your answer.

Using the subselect as shown in the example or switching it to a cross apply ends up in exactly the same execution plan.

The table variables are only for making a quick example for understanding.
In our real database the two tables are permanent tables, each containing several 10thousands rows.

The query above is executed several times within a daily process. The outer table gets filtered by an Id and returs just a few rows most of the time. For some cases the outer table returns about 20.000 to 40.000 rows. The inner select does a clustered index seek, which is quite well. But the high number of index seeks results in a query duration of more than 10 seconds. That's too long.

I personally think making 40.000 index seeks is much slower than 1 partial index scan. For this I try to find a solution where each table is scanned only once.
In any other query the optimizer surely would switch to an index scan instead of making that much lookups but with the subquery I force him to loop. And that's the target: eliminate the loop.

Good evening, Wolf
Post #1387557
Posted Monday, November 26, 2012 6:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Can you save the execution plan as a .sqlplan file and post that here? That would definitely help.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1388573
Posted Monday, November 26, 2012 6:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
hi. here is the execution plan. some explanations:

@InsertedCumulatedDataIds:
A table filled at the beginning of the procedures. For this Ids we have to write records in table #tmpProductHistory.

The tables DetailledDataANP and Product are joined to the @InsertedCumulatedDataIds to get all the information necessary for the later insert.
In the subselect the content of table DetailledDataANP and CumulatedData are stored as XML for each of the records.
The #tmpProductHistory is copied to the permanent table later.

This is the query:
insert into #tmpProductHistory( ProductId, IdentCode, PartitionKey, ProductHistoryTypeId, ProductHistoryReasonId, EventContent )
select
anp.ProductId,
anpp.Identcode,
anp.PartitionKey,
1,
1,
(
select *
from billing.DetailledDataANP as Innen join
billing.CumulatedData as f on
f.CumulatedDataId = Innen.CumulatedDataId
Where Innen.DetailledDataANPId = anp.DetailledDataANPId
for XML Auto
) As EventContent
from billing.CumulatedData fact inner join
billing.DetailledDataANP anp on
fact.CumulatedDataId = anp.CumulatedDataId inner join
dbo.Product anpp on
anp.ProductId = anpp.ProductId
where fact.CumulatedDataId in ( Select ids.Id From @InsertedCumulatedDataIds as ids );



  Post Attachments 
execution plan.sqlplan (4 views, 73.64 KB)
Post #1388583
Posted Wednesday, November 28, 2012 6:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Approximately how many rows does that table variable end up having in it when you run this?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1389721
Posted Wednesday, November 28, 2012 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
The table variable contains just a few rows, maybe 200 for the "large" processes. But the join sometimes results in more than 30.000 rows.
Post #1389733
Posted Wednesday, November 28, 2012 6:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
Can you replace the table variable with a temp table and see what that does?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1389735
Posted Wednesday, November 28, 2012 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
not really, restricted access and so on...

But I don't think this makes sense anyway. It won't change the fact that the subselect makes a lookup for any outer row.

Maybe I manage to build a similar example...
Post #1389750
Posted Wednesday, November 28, 2012 7:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 3, 2014 1:55 PM
Points: 199, Visits: 735
ok, I could make an example. As expected, the where- and from-task has changed a little bit, but not the loop join for the subselect.
Post #1389769
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse