Performance issue when using UNION SELECT ?

[juvi]

Hello,

I have no experience for 100000 records and above, so I am not sure if this
is really a performance problem or not.

I have a subform with a table as datasource. When the form is openned then
it works fine. The form opens for 100000 records immediately .

But I need to get also the list of materials from a second table. I did this
by:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION
SELECT [Column1], [Column2]... FROM tbl_Material_B

It works, but there is a performance difference (form opens after 2sec, and
without union immediately for 100000 records)

Thank you.

juvi

 

[bcap]

By default a UNION query returns DISTINCT rows. With 100,000 rows it's
gonna take a measurable amount of time to plough through them to find and
eliminate duplicates.

If you don't want/need DISTINCT rows, then this might help:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION ALL
SELECT [Column1], [Column2]... FROM tbl_Material_B

 

[Rob Parker]

A standard Union query eliminates duplicates, and checking for possible
duplicates may be what's making it slower. You can use UNION ALL to allow
duplicates, so that might run faster.

HTH,

Rob

 

[juvi]

Yes if I use UNION ALL then it is fast again but I am loosing ordering.

I have also a search possibility on my form so this query now is only for
first listing of materials. What would you say: should I order by column
material-id or not? (because of performance?)

thx
juvi

A standard Union query eliminates duplicates, and checking for possible
duplicates may be what's making it slower. You can use UNION ALL to allow
duplicates, so that might run faster.

HTH,

Rob

Hello,

I have no experience for 100000 records and above, so I am not sure if
this
is really a performance problem or not.

I have a subform with a table as datasource. When the form is openned then
it works fine. The form opens for 100000 records immediately .

But I need to get also the list of materials from a second table. I did
this
by:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION
SELECT [Column1], [Column2]... FROM tbl_Material_B

It works, but there is a performance difference (form opens after 2sec,
and
without union immediately for 100000 records)

Thank you.

juvi

 

[Rob Parker]

You may be able to get around the ordering problem in the query by setting
the Order By property for the subform itself (where the data is displayed).
I'm surprised that an Order By affects the speed in the query itself (but I
don't have first-hand experience with very large recordsets); I assume that
you have an Index set on the field you are ordering by.

HTH,

Rob

Yes if I use UNION ALL then it is fast again but I am loosing ordering.

I have also a search possibility on my form so this query now is only for
first listing of materials. What would you say: should I order by column
material-id or not? (because of performance?)

thx
juvi

A standard Union query eliminates duplicates, and checking for possible
duplicates may be what's making it slower. You can use UNION ALL to
allow
duplicates, so that might run faster.

HTH,

Rob

Hello,

I have no experience for 100000 records and above, so I am not sure if
this
is really a performance problem or not.

I have a subform with a table as datasource. When the form is openned
then
it works fine. The form opens for 100000 records immediately .

But I need to get also the list of materials from a second table. I did
this
by:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION
SELECT [Column1], [Column2]... FROM tbl_Material_B

It works, but there is a performance difference (form opens after 2sec,
and
without union immediately for 100000 records)

Thank you.

juvi

 

[John W. Vinson]

Hello,

I have no experience for 100000 records and above, so I am not sure if this
is really a performance problem or not.

I have a subform with a table as datasource. When the form is openned then
it works fine. The form opens for 100000 records immediately .

Well... actually not. It OPENS but only the first record or few records will
be shown. If you immediately click the button to go to the last record you'll
see a delay.

But I need to get also the list of materials from a second table. I did this
by:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION
SELECT [Column1], [Column2]... FROM tbl_Material_B

It works, but there is a performance difference (form opens after 2sec, and
without union immediately for 100000 records)

It's trimming dups. Try putting an ORDER BY in the *last* SELECT and using
UNION ALL:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION ALL
SELECT [Column1], [Column2]... FROM tbl_Material_B
ORDER BY [Column1];

If Column1 is indexed in both tables, that should give better performance
(though it will still be slower than the table, since it must interleave the
two recordsets).

Any chance you could apply criteria to the query (both SELECTS of the union)
to filter the records? 100000 records is about 95000 more than should ever be
displayed on a form...

 

[Bob Quintal]

You may be able to get around the ordering problem in the query by
setting the Order By property for the subform itself (where the
data is displayed). I'm surprised that an Order By affects the
speed in the query itself (but I don't have first-hand experience
with very large recordsets); I assume that you have an Index set
on the field you are ordering by.

HTH,

Rob

The process of removing duplicates from the dataset requres the
ordering of the rows, and this reordering is actually what slows
down the UNION SELECT DISTINCT version of the query, in part because
an index does not exist for both source tables.

With UNION SELECT ALL, Access can just grab the next x records and
immediately put them to the screen,

Q

Yes if I use UNION ALL then it is fast again but I am loosing
ordering.

I have also a search possibility on my form so this query now is
only for first listing of materials. What would you say: should I
order by column material-id or not? (because of performance?)

thx
juvi

A standard Union query eliminates duplicates, and checking for
possible duplicates may be what's making it slower. You can use
UNION ALL to allow
duplicates, so that might run faster.

HTH,

Rob

Hello,

I have no experience for 100000 records and above, so I am not
sure if this
is really a performance problem or not.

I have a subform with a table as datasource. When the form is
openned then
it works fine. The form opens for 100000 records immediately .

But I need to get also the list of materials from a second
table. I did this
by:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION
SELECT [Column1], [Column2]... FROM tbl_Material_B

It works, but there is a performance difference (form opens
after 2sec, and
without union immediately for 100000 records)

Thank you.

juvi

 

[juvi]

hello,

not sure about criteria question (depends on demands of the company; we will
see)
but now I used UNION ALL .... without any ordering - the ordering is in the
query for searching for criteria.

Thank you

Hello,

I have no experience for 100000 records and above, so I am not sure if this
is really a performance problem or not.

I have a subform with a table as datasource. When the form is openned then
it works fine. The form opens for 100000 records immediately .

Well... actually not. It OPENS but only the first record or few records will
be shown. If you immediately click the button to go to the last record you'll
see a delay.

But I need to get also the list of materials from a second table. I did this
by:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION
SELECT [Column1], [Column2]... FROM tbl_Material_B

It works, but there is a performance difference (form opens after 2sec, and
without union immediately for 100000 records)

It's trimming dups. Try putting an ORDER BY in the *last* SELECT and using
UNION ALL:

SELECT [Column1], [Column2]... FROM tbl_Material_A
UNION ALL
SELECT [Column1], [Column2]... FROM tbl_Material_B
ORDER BY [Column1];

If Column1 is indexed in both tables, that should give better performance
(though it will still be slower than the table, since it must interleave the
two recordsets).

Any chance you could apply criteria to the query (both SELECTS of the union)
to filter the records? 100000 records is about 95000 more than should ever be
displayed on a form...