July 11, 2007 at 9:00 am
OK, I want to write a more efficient query to compress the results of some heirarchy data, my current solution takes seven seconds to execute, which is not admirable for my tastes. To make this easier to understand for the masses, I'm going to change my context a bit, but I would like it to be known that in my original context, I'm only looking at working with a maximum of 1200 rows and my solution still takes seven seconds to execute.
For this example, I have a set of zips codes, which exist in a state, which exist in a country. Each zip code can belong to one and only one state, and each state can belong to one and only one country. For a set of zips codes, I would like to compress the result set, if for example, I have all zip codes in one state, I would have state.*, if I have all the zip codes in a country, I would have something like the following:
pseudo
zips(xxxx1, xxxx2, ..., xxxxN) -> country1.*.*, country2.state1.*, country2.state3.*, country2.state4.xxxx1, country2.state4.xxxx2
implementation (sql 2k5)
declare @zips table (code char(5) not null, state char(2) not null)
declare @states table ([state] char(2) not null, country char(2) not null)
declare @countries table (country char(2) not null)
declare @setOfZips table (code char(5) not null)
/* initialize tables */
;with
merged as (
select
c.country,
s.state,
z.zip,
n.zip as selected,
from @countries c
inner join @states s
on s.country = c.country
inner join @zips z
on z.state = s.state
left outer join @setOfZips n
on n.zip = z.zip
),
expanded as (
select
country,
case when exists (
select 1 from merged _m
where _m.country = m.country
and _m.selected is null
) then [state]
else '**'
end as [state],
case when exists (
select 1 from merged _m
where _m.state = m.state
and _m.selected is null
) then zip
else '*****'
end as zip
from merged m
)
select
country,
[state],
zip
from expanded
group by
country,
[state],
zip
sample result set
country state zip ------- ----- ----- C1 ** ***** C2 S1 ***** C2 S2 xxxx1 C2 S2 xxxx2
I believe this query takes seven seconds to execute because of the left outer join and exists checks, but I am having difficulty visualizing any other kind of solution, and I thought that while writing this post I might have some more ideas, but I am still clueless, so please, if you have any suggestions, please let me know.
Thanks!
Dave
July 11, 2007 at 9:42 am
July 11, 2007 at 12:13 pm
Ok, I'm feeling a bit perplexed now. While fiddling around with this, I decided to create an actual table which contained my set, therefore eliminating the insert in my batch and the query executes in zero seconds now. However, previously, the exectuion plan indicated that the insert was only 20% of the cost relative to the batch.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply