Question about Queries with Subqueries

D

DawnTreader

Hello All

i have a query i am working on that draws data from 3 different data
"paths". i am using a query with a union subquery to pull the information i
need. it looks like this:

SELECT
SQ.ProdID,
SQ.Category,
SQ.IMWPNID,
SQ.PartDesc,
SQ.Ref,
SQ.PartBlockQTY,
SQ.ListPrice,
SQ.PLID,
nz([SQ].[PartBlockQTY],0)*nz([SQ].[ListPrice],0) AS SkidValue,
tblMasterPartList.[1000Maint] AS Maint1000Hr,
tblMasterPartList.[5000Rebuild] AS Rebuild5000Hr,
tblMasterPartList.[10000Rebuild] AS Rebuild10000Hr,
tblMasterPartList.[15000Rebuild] AS Rebuild15000Hr,
tblMasterPartList.[20000Rebuild] AS Rebuild20000Hr,
tblMasterPartList.[25000Rebuild] AS Rebuild25000Hr
FROM
[SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"" AS Ref,
tblProductPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
subtblSectionName INNER JOIN (tblProductPartList LEFT JOIN dbo_PART
ON tblProductPartList.IMWPartNumberID = dbo_PART.ID)
ON subtblSectionName.SectionNameID = tblProductPartList.SectionNameID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Not Null))

UNION SELECT
tblProductPartList.ProductID AS ProdID,
tblProductPartList.RequirementCategory AS Category,
dbo_REQUIREMENT.PART_ID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
dbo_REQUIREMENT.REFERENCE AS Ref,
dbo_REQUIREMENT.QTY_PER AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
(tblProductPartList LEFT JOIN dbo_REQUIREMENT
ON tblProductPartList.RequirementID = dbo_REQUIREMENT.ROWID)
LEFT JOIN dbo_PART ON dbo_REQUIREMENT.PART_ID = dbo_PART.ID
WHERE
(((tblProductPartList.RequirementID) Is Not Null))

UNION SELECT
tblProductList.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
CBAPartListID AS PLID
FROM
((tblCBBANumbers LEFT JOIN tblCBAPartList
ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
RIGHT JOIN (tblProductList LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN dbo_PART ON tblCBAPartList.IMWPartNumberID = dbo_PART.ID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Null)) AND
(((tblProductPartList.RequirementID) Is Null))]. AS SQ LEFT JOIN
tblMasterPartList ON SQ.IMWPNID = tblMasterPartList.ID;

i have a question about queries like this. first a little background

i have built this multiple times trying to find the most efficient way to
get the information in the format that i need. originally i had 7 levels of
queries to get the result i am looking for. the problem is the 7th level gets
so complex that it chokes on the "system resources exceeded" error and if i
add another table it stops on "cannot open anymore databases". so i decided
to try "flattening" my queries by using subqueries.

although the SQL above works there are 2 things i need to know before going
further. the first is, how many subqueries can i do in SQL? the second
question, will i hit the same problem "Cannot open anymore databases" with
one query that is built with a few subqueries and subsubqueries?

unrelated, how do i get the third part of the union query to only show those
parts not generated by the other 2 parts of the union query?
 
S

Sylvain Lafontaine

From http://office.microsoft.com/en-us/access/HA100307391033.aspx , the
indicated maximum number of levels for nested queries is 50 but in practice,
probably that you will be hit by resources limits or that Access will crash
on you well before you'ill reach this limit.

Also, I'm not sure but in your case, maybe it will be advantageous for
performance reasons to use UNION ALL instead of UNION.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


DawnTreader said:
Hello All

i have a query i am working on that draws data from 3 different data
"paths". i am using a query with a union subquery to pull the information
i
need. it looks like this:

SELECT
SQ.ProdID,
SQ.Category,
SQ.IMWPNID,
SQ.PartDesc,
SQ.Ref,
SQ.PartBlockQTY,
SQ.ListPrice,
SQ.PLID,
nz([SQ].[PartBlockQTY],0)*nz([SQ].[ListPrice],0) AS SkidValue,
tblMasterPartList.[1000Maint] AS Maint1000Hr,
tblMasterPartList.[5000Rebuild] AS Rebuild5000Hr,
tblMasterPartList.[10000Rebuild] AS Rebuild10000Hr,
tblMasterPartList.[15000Rebuild] AS Rebuild15000Hr,
tblMasterPartList.[20000Rebuild] AS Rebuild20000Hr,
tblMasterPartList.[25000Rebuild] AS Rebuild25000Hr
FROM
[SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"" AS Ref,
tblProductPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
subtblSectionName INNER JOIN (tblProductPartList LEFT JOIN dbo_PART
ON tblProductPartList.IMWPartNumberID = dbo_PART.ID)
ON subtblSectionName.SectionNameID = tblProductPartList.SectionNameID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Not Null))

UNION SELECT
tblProductPartList.ProductID AS ProdID,
tblProductPartList.RequirementCategory AS Category,
dbo_REQUIREMENT.PART_ID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
dbo_REQUIREMENT.REFERENCE AS Ref,
dbo_REQUIREMENT.QTY_PER AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
(tblProductPartList LEFT JOIN dbo_REQUIREMENT
ON tblProductPartList.RequirementID = dbo_REQUIREMENT.ROWID)
LEFT JOIN dbo_PART ON dbo_REQUIREMENT.PART_ID = dbo_PART.ID
WHERE
(((tblProductPartList.RequirementID) Is Not Null))

UNION SELECT
tblProductList.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
CBAPartListID AS PLID
FROM
((tblCBBANumbers LEFT JOIN tblCBAPartList
ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
RIGHT JOIN (tblProductList LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN dbo_PART ON tblCBAPartList.IMWPartNumberID = dbo_PART.ID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Null)) AND
(((tblProductPartList.RequirementID) Is Null))]. AS SQ LEFT JOIN
tblMasterPartList ON SQ.IMWPNID = tblMasterPartList.ID;

i have a question about queries like this. first a little background

i have built this multiple times trying to find the most efficient way to
get the information in the format that i need. originally i had 7 levels
of
queries to get the result i am looking for. the problem is the 7th level
gets
so complex that it chokes on the "system resources exceeded" error and if
i
add another table it stops on "cannot open anymore databases". so i
decided
to try "flattening" my queries by using subqueries.

although the SQL above works there are 2 things i need to know before
going
further. the first is, how many subqueries can i do in SQL? the second
question, will i hit the same problem "Cannot open anymore databases" with
one query that is built with a few subqueries and subsubqueries?

unrelated, how do i get the third part of the union query to only show
those
parts not generated by the other 2 parts of the union query?
Click to expand...
 
D

DawnTreader

Hello Sylvain

i am not using 50 levels of queries, that is for sure.

just looking through the query limitations i dont think i am hitting any of
those unless it is for all levels of quering together. so if:

qryTopLevel gives a cost per interval based links of 6 fields
which is based on
qryOtherSubLevel1
qrySubLevel1
which is based on
tblIntervals
and linked on a field 1000
qrySubLevel1
which is based on
tblIntervals
and linked on a field 5000
qrySubLevel1
which is based on
tblIntervals
and linked on a field 10000
qrySubLevel1
which is based on
tblIntervals
and linked on a field 15000
qrySubLevel1
which is based on
tblIntervals
and linked on a field 20000
qrySubLevel1
which is based on
tblIntervals
and linked on a field 25000

opens qrySubLevel1
which is based on
qrySubLevel2 with a sum on a QTY field so that each group has a total
dollar value

opens qrySubLevel2
which is based on
qrySubLevel 3 to get a grouping based on field value "1000"
and union joins to
qrySubLevel 3 to get a grouping based on field value "5000"
and union joins to
qrySubLevel 3 to get a grouping based on field value "10000"
and union joins to
qrySubLevel 3 to get a grouping based on field value "15000"
and union joins to
qrySubLevel 3 to get a grouping based on field value "20000"
and union joins to
qrySubLevel 3 to get a grouping based on field value "25000"

opens qrySubLevel3
which is based on
qrySubLevel4
dbo_PART
tblMasterPartList

opens qrySubLevel4
which is based on
qrySubLevel5a
and union joins it with
qrySubLevel5b

opens qrySubLevel5a
which is based on
qrySubLevel6
tblProductPartList
subtblSectionName
dbo_REQUIREMENT

and qrySubLevel5b
which is based on
qrySubLevel6
tblCBBANumbers
tblCBAPartList

opens qrySubLevel6
which is based on
tblProductList
subdatatblCompressor
which has some filtering based on a form to limit the results at
the base level

this is a simple explaination of the structure of my querys and subqueries.
does the limit count for each query seperately, or all together? does the
lowest query fire and then remain "open"? or does it give its results and
pass that dataset to the next level up and then "close"?

i dont know how this stuff works in the background.

an idea that i had this morning is to get a temporary table set up that
would "flatten" a lot of the data and then query that instead because the
amount of records it would be going through would be cut considerably and all
the fields necessary could be put in the temp table. would that be a better
solution? or is there something else that would be even better?

any ideas or suggestions hugely appreciated. :)
 
S

Sylvain Lafontaine

Like I said, JET will chokes on resources or make an internal error well
before you reach the theoritical limit of 50 nested queries; which doesn't
mean that you must reach something like 40 levels before it crashes. Only a
few levels can make it crash if your query requires to many ressources;
especially when you are using a combination of Outer Join with subqueries.

You need to switch to something more powerful like SQL-Server is you need
any real serious business with your queries.

You can get some relief by making sure that you have all the necessary
indexes on your tables (using an index instead of a full scan of a table -
if it's possible for JET to do, of course - will decrease the amount of
resources used but beside that, if your queries are too complex for JET but
don't want to switch to SQL-Server, your only other solution would be to use
temporary tables to compute and store intermediate results. There is no
point coming here to say that you are well below the theoritical limit of
JET.
 
D

DawnTreader

Hello Sylvain

thanks for the quick response.

"There is no point coming here to say that you are well below the
theoritical limit of
JET."

i am just asking questions. i am by no means an expert and i am just looking
for help. i dont understand why i get so many hostile answers on this board.
i have heard it said that "the only dumb question is the one that doesnt get
asked." so here i am asking questions because i dont understand and i get
berated because i attempt to explain my situation and get an answer to
questions. i never said that i thought i was below the "theoritical" limit, i
posted a description of what i am dealing with to help you and others
understand what i am dealing with.

i am so glad that soon my company will be moving to a bigger better system
and i wont be the one developing it, because with help like this i am so
tired of building this application. i am also glad that the next job i intend
to be at wont involve building an internation application that upto 50 users
use to do business with, but instead i will using my creative talents doing
video production. i am so tired of "help" like yours and others that have a
high and lofty attitude of "i know more than you do".

if you think that i am to lowly and stupid to deserve your help, why bother?
 
D

DawnTreader

Hello Sylvain

i guess i should have read my own post before responding.

"i am not using 50 levels of queries, that is for sure.

just looking through the query limitations i dont think i am hitting any of
those unless it is for all levels of quering together."

what i meant was i dont think i am hitting any one of the individual limits,
but you could be right about the fact that my queries are hitting a few of
them in an overall fashion.

looking at my description do you think that it is too complex?

i think i have been working on this project too long and been pushed by a
boss that doesnt understand access at all a little too much and too long. i
owe you a bit of an apology, sorry. i think i am being angry at the wrong
person.

my boss expects this to be done last week and doesnt understand the
limitations of access.
 
S

Sylvain Lafontaine

"There is no point coming here to say that you are well below the
theoritical limit of
JET."

By that, I was simply saying that when JET start to begin to crash because
your queries have become too complex, there is no point in trying to
understand why JET is crashing. You might get some relief by:

1- Simplyfing your schema; ie, fusing together some tables in order to have
bigger tables but simpler queries.

2- Use temporary tables to compute intermediary results. Will always work
but the performance will drop like a rock.

3- Add indexes if you are missing some of them. For performance reason,
this should always be done even if JET is not (yet) crashing. In all cases,
this will reduce the use of internal ressources if your database is missing
some indexes. Of course, if none of them are missing, this will have no
effect.

4- Switch to SQL-Server. It's not perfect but its database engine is many
orders of magnitude more powerful than the one running JET.

5- Be lucky: maybe a small change will change everything. In particular,
take a look back at the suggestion #1.

In your case, there is also a final possibility that I've just noticed: you
have used a combination of Right Join with a Left Join. On SQL-Server,
using parenthesis has no effect at all on the order of evuluation for the
join operations and mixing Right with Left join will rarely give you the
result that you are expecting. SQL-Server is giving you the right result as
defined by the SQL-92 standard; however, this definition is probably not
what you are thinking it is. ***If I were you, I would try first to change
the Right Join with a Jeft Join and see what does this give.***

Don't forget that all systems have a tendency to become more complex over
time; so if your system is already making JET crashing; there is a high
probability that the situation will become worse over time.
 
S

Sylvain Lafontaine

Also, don't forget to replace your UNION with UNION ALL if it's possible to
do so; as it will also lower the pressure on the internal ressources.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Heavy Duty Union Subqueries 4
How to use WHERE NOT EXISTS 2
is this query with a subquery, a union query, normal? 1
Read Only Query, need a little help 1
Heavy data munching - is there a more efficient way of doing this? 8
Concatenating the same field from multiple records 16

Top