Union Operator

  • Thread starter Thread starter Mike Labosh
  • Start date Start date
M

Mike Labosh

Dig this:

SELECT "Software" AS SampleSource, Manufacturer, Prod_name, Prod_version,
Prod_operating_system, Product_ID, Component_ID, Part_num
FROM Software

UNION

SELECT "EIW" AS SampleSource, Manufacturer, Prod_name, Prod_version,
Prod_operating_system, Product_ID, Component_ID, Part_num
FROM SoftwareEIW

UNION

SELECT "Passport" AS SampleSource, Manufacturer, Prod_name, Prod_version,
Prod_operating_system, Product_ID, Component_ID, Part_num
FROM SoftwarePassport

UNION

SELECT "Retain" AS SampleSource, Manufacturer, Prod_name, Prod_version,
Prod_operating_system, Product_ID, Component_ID, Part_num
FROM SoftwareRetain

I'm not using any WHERE clauses or DISTINCT, but Access is showing only 245
records in the datasheet. I'm not talking about the difference between
Union and Union All. I have a record base in these tables that's well over
half a million (there are lots of duplicates though), but I should be
getting something to the tune of at least a couple hundred thousand records
even with the dupes removed.

Is there something unusual that the Access Union operator does differently
from T-SQL? As far as I can see, the Access docs & SQL Server Books Online
topics for Union are pretty much the same, but this should be giving me WAY
more records than 245.

--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"
 
Mike Labosh said:
Dig this:

SELECT "Software" AS SampleSource, Manufacturer, Prod_name,
Prod_version, Prod_operating_system, Product_ID, Component_ID,
Part_num
FROM Software

UNION

SELECT "EIW" AS SampleSource, Manufacturer, Prod_name, Prod_version,
Prod_operating_system, Product_ID, Component_ID, Part_num
FROM SoftwareEIW

UNION

SELECT "Passport" AS SampleSource, Manufacturer, Prod_name,
Prod_version, Prod_operating_system, Product_ID, Component_ID,
Part_num
FROM SoftwarePassport

UNION

SELECT "Retain" AS SampleSource, Manufacturer, Prod_name,
Prod_version, Prod_operating_system, Product_ID, Component_ID,
Part_num
FROM SoftwareRetain

I'm not using any WHERE clauses or DISTINCT, but Access is showing
only 245 records in the datasheet. I'm not talking about the
difference between Union and Union All. I have a record base in
these tables that's well over half a million (there are lots of
duplicates though), but I should be getting something to the tune of
at least a couple hundred thousand records even with the dupes
removed.

Is there something unusual that the Access Union operator does
differently from T-SQL? As far as I can see, the Access docs & SQL
Server Books Online topics for Union are pretty much the same, but
this should be giving me WAY more records than 245.
Click to expand...

Mike -

I don't see anything wrong. If you're right that the difference between
UNION and UNION ALL is not the culprit, then something odd is going on.
Did you try substituting UNION ALL to see what the effect would be?
 
I don't see anything wrong. If you're right that the difference between
UNION and UNION ALL is not the culprit, then something odd is going on.
Did you try substituting UNION ALL to see what the effect would be?
Click to expand...

Yes. With UNION ALL, I got all half million records. The "problem" is
solved, but the "wierdness" remains. If it was going to give me 245
records, there should have been at least two zeros on the end of that 245
:)

Maybe a reboot will clear the bit-rot in distinct-ification part of Union.
I sure can't stop and wait for 8 gigs of MDB files to repair / compact.
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"
 
8GB might be your problem as access still doesn't support more than 2GB...
my guess is that it just stops processing when it nears that limit.
with that kind of data I strongly suggest moving up to MSSQL or Oracle as
your backend

of no help
Pieter
 
8GB might be your problem as access still doesn't support more than
2GB...
my guess is that it just stops processing when it nears that limit.
with that kind of data I strongly suggest moving up to MSSQL or Oracle as
your backend
Click to expand...

Nah, I have 6 or 7 databases each with one giant table, and the one that I'm
working in has those tables as links. This isn't really a "backend" either.
Just a utilitarian dumping ground I made so I analyze giant data files to
understand them better as I write the SQL Server / .NET import program.

Believe me, in this migration project, I have bumped in to almost every
single maximum limit that Access has :)
--
Peace & happy computing,

Mike Labosh, MCSD

"It's 4:30 am. Do you know where your stack pointer is?"
 
Mike Labosh said:
Yes. With UNION ALL, I got all half million records. The "problem"
is solved, but the "wierdness" remains. If it was going to give me
245 records, there should have been at least two zeros on the end of
that 245 :)

Maybe a reboot will clear the bit-rot in distinct-ification part of
Union. I sure can't stop and wait for 8 gigs of MDB files to repair /
compact.
Click to expand...

I'm afraid I don't have any more ideas at the moment. It does occur to
me that you could be blowing out on size, if your query needs more than
2GB of intermediate storage to run. I understand that your tables are
linked, and living in multiple .mdb files, but your query still needs to
run in one particular file. Have you noticed whether anything much
happens to the size of the front-end .mdb file when you run the query?
 
Back
Top