twisted query

[Corticus]

Hi all,

I've got a twisted question for you.
I made a query that does what I need, but its really slow, so there must be a better way.
I'm interested in 2 columns of data. One is a part number, and one is a component number. You might see data like this:

Part
10A
10A
10A
10B
10B
1034
1034
1000A01

Component
1034
1035
1036
A5454
A5454
1000A01
1000A02
L16545


I need a list of every part for a 10A, which would be:

Part
1034
1035
1036
1000A01
1000A02
L16545

As you can see, the trick is that a component can also be a part, which can have more components, and those components also can have components...

My nasty solution for the example would implement SQL as follows:

part I

QryParts

this makes a list of all the parts and relationships
first it gets filtered by a combo so it just shows one Part

SELECT
FROM ((TblItem LEFT JOIN TblItem AS TblItem_1 ON TblItem.Component = TblItem_1.Part) LEFT JOIN TblItem AS TblItem_2 ON TblItem_1.Component = TblItem_2.Part) LEFT JOIN TblItem AS TblItem_3 ON TblItem_2.Component = TblItem_3.Part;

Ugh...

part II

this gives the actual list of components for the part

SELECT DISTINCT QryParts.TblItem.Part
FROM QryParts

UNION SELECT DISTINCT QryParts.TblItem_1.Part
FROM QryParts;

UNION SELECT DISTINCT QryParts.TblItem_2.Part
FROM QryParts

UNION SELECT DISTINCT QryParts.TblItem_3.Component
FROM QryParts;

I just don't understand why its slow.....

Thanks for any help,
Corticus

 

[Dugantrain]

Yeah, that's a real pickle there, Corticus. I have some pretty messy queries in my current project, too, but nothing quite like that one. I wonder if the slowness comes from calling three separate Union Queries. If you haven't already done so, you should go to
http://www.access-programmers.co.uk/
This is easily the best resource I've found for MS Access.

 

[Corticus]

Thanks for the help Dugantrain,

My example isn't all that slow, but the real one is. What kills it is how I have three instances of the table in question, and in each one, the 'child' field becomes the 'parent' in the next table. I only drill down three levels in my example. In the actual data set, some parts have components lists that go 5-6 deep, which requires 6 instances of the table in my query. The table has ~250,000 records. The unions don't help, they just pull distinct values from the results of each round of the drill down.

The problem is, the query drills down 6 rounds even if the item only has components 3 rounds deep (I apologize for my awful terminology, each round would be one instance of the 'child' of one instance becoming the 'parent' of another, or my 6 instances of the table in the query). Maybe I could use VBA to loop the query till I hit an empty recordset. Like have the query go one deep, test for results, if there are, go 2 deep etc.

Who knows, this thing is nasty, yet so simple on the surface.

Thanks again!

Corticus

 

[Corticus]

solution one, more to come!

Okay,

Here's one SQL solution thats faster, and is all in one query:
SELECT A.Component
FROM TblItem AS A
WHERE A.Part=[ENTER PART]
OR A.Component In (select B.Component from TblItem as B where B.Part in (SELECT C.Component FROM TblItem as C WHERE C.Part=[ENTER PART]))
OR A.COMPONENT IN (SELECT B.COMPONENT FROM TBLITEM AS B WHERE B.PART IN (SELECT C.COMPONENT FROM TBLITEM AS C WHERE C.PART IN(SELECT D.COMPONENT FROM TBLITEM AS D WHERE D.PART=[ENTER PART])));

Nice, eh?

-Corticus

 

[Dugantrain]

Yes, that does seem to work. The only thing is that you'd still have to know your maximum number of parent-child levels in order to capture them all (and redo your SQL statement to match accordingly). I've toyed around with this problem and, aside from splitting tables (which you probably don't want to do since you have several 100,000 records), I got nuthin'.

 

[Corticus]

Thanks Dugantrain,

Determining how many layers to drill down is the real trick to this thing. Its really tough because we never know exactly how many layers down we may need to go, and this drastically affects how fsat the query runs. Yuck.
I'm looking over another example that uses append querries and is supposed to be even better (faster), something to do with a "treeview" module. I'll post back when I figure out.

BTW, the problem I'm dealing with is called 'transitive closure', I found out.

Thanks for your help, looks like the end is in sight!

-Corticus

 

[Corticus]

YEAH!!!

Hey all!

I had a major breakthrough. By changing:

QryParts 

this makes a list of all the parts and relationships 
first it gets filtered by a combo so it just shows one Part 

SELECT 
FROM ((TblItem LEFT JOIN TblItem AS TblItem_1 ON TblItem.Component = TblItem_1.Part) LEFT JOIN TblItem AS TblItem_2 ON TblItem_1.Component = TblItem_2.Part) LEFT JOIN TblItem AS TblItem_3 ON TblItem_2.Component = TblItem_3.Part;

from a select to a make table query, I was able to run the query 10 layers deep in <20 seconds on a 250,000 record dataset. This took at least 10, sometimes more minutes as a select query.

Thanks for your help!

-Corticus

 

[Dugantrain]

Really?! How is your Actions Query faster than a Select Query? I have a command button on my Switchboard which does the following:
-Deletes all items out of a temp table
-Appends 10's of 1000's of records from two different tables into this table (which I have appropriately named tblSquash).
-Select Query from the Temp Table
-Report from Select Query
-Delete all items again.

Everything runs quickly except for the two Append Queries. On our shared network, these two queries sometimes take, like, 5 minutes to load. When I paste the db locally, they only take about 30 seconds or so, but I'm still curious how your Appends run faster than your Select. Congrats either way, though.

 

[Corticus]

In my case, the union query was running QryParts and selecting distinct out of it for each instance of a union (one column of QryParts). By having the union query look to a table, instead of a query, it allows QryParts to only have to be run once do make a table. The union query then runs a unique values query on each column of the table. This means QryParts (which goes 10 layers deep now) only has to get run once to make a table to which every instance of a union in the union query refers.

I got lucky on this one, but the difference is dramatic, I haven't even tried with the cleaned up code

SELECT A.Component 
FROM TblItem AS A 
WHERE A.Part=[ENTER PART] 
OR A.Component In (select B.Component from TblItem as B where B.Part in (SELECT C.Component FROM TblItem as C WHERE C.Part=[ENTER PART])) 
OR A.COMPONENT IN (SELECT B.COMPONENT FROM TBLITEM AS B WHERE B.PART IN (SELECT C.COMPONENT FROM TBLITEM AS C WHERE C.PART IN(SELECT D.COMPONENT FROM TBLITEM AS D WHERE D.PART=[ENTER PART])));

I'm still using my original method.

Thanks,
Corticus