How to collapse a recursive query

Question

Post reply

How to collapse a recursive query

davrose

Right there with Babe

Points: 728
More actions
September 8, 2010 at 3:26 am

#237970

Hi All,
in the picture attached is a screenshot of a table.
I usually query the table via software. The process starts with the IP and i get the dnsname having the ip as alias.
(select dnsname from t where zone='Zone001' and alias='10.130.2.26')
Then a second iteration takes place and the query retieves the dnsname having as alias the result of the first query and zone='Zone001'.
(select dnsname from t where zone='Zone001' and alias='w2ksrv')
This process continues untill the query returns an empty set, then i add '.com' to the last result and check for rows having as alias the modified result and zone='Zone000'
(select dnsname from t where zone='Zone000' and alias='std-w2ksrv'+'.com')
As you may notice, the number of iterations span the range 1 to 4.
I'm now having performance issues on the software and decided to delegate this iteration to sql. My idea is to create a query that, given the ip, returns the last dnsname.
Any help is welcome

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please adjust either the third query or the sample data so that the third query returns a row:-

IF OBJECT_ID( 'tempdb..#Sample') IS NOT NULL DROP TABLE #Sample

CREATE TABLE #Sample (RowID INT, dnsname VARCHAR(20), Alias varchar(20), Zone VARCHAR(20))

INSERT INTO #Sample (RowID, dnsname, Alias, Zone)

SELECT 1, 'w2ksrv', '10.130.2.26', 'zone001' UNION ALL

SELECT 2, 'std-w2ksrv', 'w2ksrv', 'zone001' UNION ALL

SELECT 3, 'app_w2ksrv', 'std-w2ksrv', 'zone000' UNION ALL

SELECT 4, 'w2ksvrtst', '10.131.129.2', 'zone001' UNION ALL

SELECT 5, 'csm_w2ksvrtst', 'w2ksvrtst.com', 'zone000' UNION ALL

SELECT 6, 'w2k3appdb', '10.130.168.8', 'zone001' UNION ALL

SELECT 7, 'std-w2k3appdb', 'w2k3appdb', 'zone001' UNION ALL

SELECT 8, 'w2k3appdbms', 'std-w2k3appdb.com', 'zone000'

SELECT * FROM #Sample

select dnsname from #Sample where zone='Zone001' and alias='10.130.2.26'

select dnsname from #Sample where zone='Zone001' and alias='w2ksrv'

select dnsname from #Sample where zone='Zone000' and alias='std-w2ksrv'+'.com' -- no data returned

This will give folks something to code against.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Cowne One Orange Chip Points: 26969 More actions · Answer 2

If you know the maximum number of iterations, you can use a series of joins as below, otherwise you'll have to use a recursive query.

DECLARE @t TABLE(dnsname VARCHAR(30),alias VARCHAR(30),zone VARCHAR(30));

INSERT INTO @t(dnsname,alias,zone)

SELECT 'w2ksrv','10.130.2.26','zone001' UNION ALL

SELECT 'std-w2ksrv','w2ksrv','zone001' UNION ALL

SELECT 'app_w2ksrv','std-w2ksrv.com','zone000' UNION ALL

SELECT 'w2ksrvtst','10.131.129.2','zone001' UNION ALL

SELECT 'csm_w2ksrvtst','w2ksrvtst.com','zone000' UNION ALL

SELECT 'w2k3appdb','10.130.168.8','zone001' UNION ALL

SELECT 'std-w2k3appdb','w2k3appdb','zone001' UNION ALL

SELECT 'w2k3appdbms','std-w2k3appdb.com','zone000';

SELECT t1.alias,COALESCE(t3.dnsname,t2.dnsname,t1.dnsname) AS dnsname

FROM @t t1

LEFT OUTER JOIN @t t2 ON t2.alias=t1.dnsname AND t2.zone='Zone001'

LEFT OUTER JOIN @t t3 ON t3.alias=t2.dnsname+'.com' AND t3.zone='Zone000'

WHERE t1.zone='Zone001'

AND t1.alias LIKE '%.%.%.%';

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Mike01 SSChampion Points: 11412 More actions · Answer 3

how about a CTE?

DECLARE @t TABLE(dnsname VARCHAR(30),alias VARCHAR(30),zone VARCHAR(30));

INSERT INTO @t(dnsname,alias,zone)

SELECT 'w2ksrv','10.130.2.26','zone001' UNION ALL

SELECT 'std-w2ksrv','w2ksrv','zone001' UNION ALL

SELECT 'app_w2ksrv','std-w2ksrv.com','zone000' UNION ALL

SELECT 'w2ksrvtst','10.131.129.2','zone001' UNION ALL

SELECT 'csm_w2ksrvtst','w2ksrvtst.com','zone000' UNION ALL

SELECT 'w2k3appdb','10.130.168.8','zone001' UNION ALL

SELECT 'std-w2k3appdb','w2k3appdb','zone001' UNION ALL

SELECT 'w2k3appdbms','std-w2k3appdb.com','zone000';

--select * from @t

;with cte as

(select dnsname, alias, zone, 1 as RowNum

from @t

where zone='Zone001' and alias='10.130.2.26'

union all

select a.dnsname, a.alias, a.zone, b.RowNum + 1

from @t a

join cte b

on b.dnsname = case when CHARINDEX('.com', a.alias,1) > 0 then

SUBSTRING(a.alias, 1, CHARINDEX('.com', a.alias,1) - 1)

else a.alias

end)

select a.dnsname, a.alias, a.zone

from cte a inner join (select max(RowNum) RowNum from cte) b

on a.RowNum = b.RowNum

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

davrose Right there with Babe Points: 728 More actions · Answer 4

CTE re-creates the hierarchic structure. I tried this one

WITH CTEdns AS

(

select dns.dnsname, dns.alias, left(dns.alias, charindex('.', dns.alias)-1) as dns from dbo.dnsname as dns where zona='eni.it'

union all

select rec.dnsname, rec.alias, rec.alias as dns from dbo.dnsname as rec

inner join CTEdns as cte on cte.dns=rec.dnsname

) --option (maxrecursion 5)

select * from CTEdns

but to no avail 😛

anyway... i'm going to test the solution you mentioned as soon as possible.

i have 2 ideas on my mind

1) view

2) sp

both cases end up with a table/view like

ip - last_dnsname_in_chain

so that in my software the query woulb be as simple as

select dnsname from whatever where ip=variable

which one would you suggest?