Re: Help with recursive queries... BIG problem.
From: Daniel P. (danutzp1_at_hotmail.comU)
Date: 03/01/04
- Next message: Rohan: "Re: Using a variable to specify database to SELECT from"
- Previous message: Amin Sobati: "Re: Named Transactions"
- In reply to: Star: "Help with recursive queries... BIG problem."
- Next in thread: Daniel P.: "Re: Help with recursive queries... BIG problem."
- Reply: Daniel P.: "Re: Help with recursive queries... BIG problem."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 1 Mar 2004 14:41:24 -0600
What about something like this. It's not really a final, nice and performant
production code. I just wrote quick and dirty to illustrate the idea:
SET NOCOUNT ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[ASSOC]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ASSOC]
GO
CREATE TABLE ASSOC ( Source VARCHAR(10), Dest VARCHAR(100) )
GO
INSERT INTO ASSOC VALUES ( 'P132', 'V100' )
INSERT INTO ASSOC VALUES ( 'P132', 'V242' )
INSERT INTO ASSOC VALUES ( 'P140', 'V500' )
INSERT INTO ASSOC VALUES ( 'P132', 'P110' )
INSERT INTO ASSOC VALUES ( 'P110', 'P140' )
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[ASSOC2]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [ASSOC2]
GO
CREATE TABLE ASSOC2 ( Source VARCHAR(10), Dest VARCHAR(100) )
GO
INSERT INTO ASSOC2
SELECT * FROM ASSOC
__again__:
IF EXISTS
(
SELECT IN1.Source, IN2.Dest
FROM ASSOC AS IN1
INNER JOIN ASSOC2 AS IN2
ON IN1.Dest = IN2.Source
WHERE IN1.Source + IN2.Dest
NOT IN (SELECT Source + Dest FROM ASSOC2)
) BEGIN
INSERT INTO ASSOC2
SELECT IN1.Source, IN2.Dest
FROM ASSOC AS IN1
INNER JOIN ASSOC2 AS IN2
ON IN1.Dest = IN2.Source
WHERE IN1.Source + IN2.Dest
NOT IN (SELECT Source + Dest FROM ASSOC2)
GOTO __again__
END
ELSE
GOTO __exit__
__exit__:
GO
SELECT * FROM ASSOC2
"Star" <----> wrote in message news:u4N3So8$DHA.2512@TK2MSFTNGP11.phx.gbl...
> Hi
>
> This is a problem that I'm having and I was wondering that maybe you guys
> could come up with some ideas.
> Ok, let's see if I can explain the problem.
>
> I have about 15 tables. Each table has several fields, however each table
> has a common field called 'Code'
>
> ex.
>
> Table Vehicles
> --------------
> Code
> Make
> Color
>
> Table People
> -------------
> Code
> Name
> Age
>
> Table Addresses
> -----------------
> Code
> City
> ZIP
> ...
>
> Now we want to relate records from one table to another. For example, we
> want to say 'John has two cars'
> In order to do that, I have an external table called Links with source an
> destination fields. For the example we would have
>
> Source Dest
> ----------------
> P132 V100
> P132 V242
>
> (P132 is a record from the People table with code 132, V100 a vehicle from
> the Vehicle table with code 100, and V242 a vehicle
> with code 242)
>
> So, with all that, we know that John (P132) is related with two vehicles.
If
> I want to run the query 'People associated with vehicles'
> it would be easy to query that table and get that information.
>
> Now, let's suppose we have this situation:
>
> Source Dest
> ----------------
> P132 V100
> P132 V242
> P140 V500
> P132 P140
>
> In this example, John is related with Michael (P140) and Michael is
related
> with a vehicle (V500).
> If I want to run the same query than before 'People associated with
> vehicles', because of P132 is related with P140 and P140 is related with
> a V500 this implies that P132 is related with V500.
>
> Now, the query is not so simple, because it's like a graph and I need to
> make a Depth First Search to get what I want. Doing that, I will get that
> P132 is
> related with V500 because there is a 'path' to get there.
>
> I have done all this and it works fine. The problem that I have is that I
> need to make that Depth First Search for each people that I have in the
> database
> in order to see if I have vehicles associated with it. If I don't have
many
> records it's ok, but If I have 100,000 people on the database I would have
> to
> make that search 100,000 times just to see if there is a path between
them,
> I mean, if there is a way to get from a person to a vehicle.
> In this case... this solution doesn't work, because it takes forever...
>
>
> What do you guys suggest I can try? I though that one solution would be to
> have a process running on the server that maintains a table with ALL the
> links that we have in the database and the depth of the link. So, in our
> example we'd have a entry "P132 V500 with depth 2"
> Having that table it would be easy to query. Inconvenience: the table is
> going to be HUGE, and also the process has to be smart enough to keep
track
> of
> deleted links, new links...
>
> I'd appreciate if you guys have any idea or suggestions about this and how
I
> can get a better perfomance.
>
> Thanks a lot for reading this whole message!!
>
>
>
>
>
>
>
>
>
>
- Next message: Rohan: "Re: Using a variable to specify database to SELECT from"
- Previous message: Amin Sobati: "Re: Named Transactions"
- In reply to: Star: "Help with recursive queries... BIG problem."
- Next in thread: Daniel P.: "Re: Help with recursive queries... BIG problem."
- Reply: Daniel P.: "Re: Help with recursive queries... BIG problem."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
