Speed up a union query?

[caitlin]

Hello all -
I have a huge union query that serves as the basis of several pages of a web application. It combines the fields of two tables that each have about 66,000 records.

It works fine, but it is dog slow. I need all the fields, so I can't remove any. The numeric fields are indexed.

Any ideas on how to speed up a mega-query like this one?

Thanks!

--Caitlin

 

[dhookom]

Share your SQL next time. Try replace "UNION " with "UNION ALL". A simple "UNION" will attempt to remove duplicate records. "UNION ALL" just runs.

Duane
MS Access MVP

 

[r937]

you have a web page that displays over a TENTH OF A MILLION rows?

whoa

one way to speed it up is to pull only as many records as you intend to display


rudy

http://r937.com/

 

[caitlin]

Here's the SQL:

SELECT * From qryStudents0203
UNION SELECT * FROM qryStudents0304;

The webpage only pulls records for the selected student using an ASP query string (students.asp?Matric=123456), but the user can select any matric number from the list, so they all need to be available.

I'll try Union All and see if it helps. Thanks!

--Caitlin

 

[r937]

> "...so they all need to be available"

they are -- in the database

your asp page should not request 120,000 rows if it's only interested in a handful of them

your query should be something like

SELECT *
From qryStudents0203
where Matric=123456
UNION ALL
SELECT *
FROM qryStudents0304
where Matric=123456

rudy

 

[dhookom]

I think Caitlin's ASP only requests a handful of records from the union query which is perfectly acceptable and should return results quite fast if Matric is indexed and UNION ALL is used. My reaction is "why are there separate queries with the same structure" but I will assume Caitlin either knows the data much better than I or it can't be easily changed.

Duane
MS Access MVP

 

[caitlin]

Is a union query typically going to perform much slower than requesting data directly from a table?

I have 2 tables, each with 60,000+ records, that represent enrollment of a district's students for this year and last year. I could combine them into one huge table of 120,000+, but then I worry that other queries in the system that only use one of the years would bog down.

There are only a few queries in which I need to pull from either year (depending on user selection), which is why I'm using the union.

Thanks for your insights!!!

--Caitlin

 

[dhookom]

Access should not have an issue with 1/2 million records depending on indexing and other considerations. If your application is working fine with separate tables, I would bite my tongue and leave it that way. If however, you need to query the records from more than one year on a regular basis, you should consider combining them.

Why don't you tell us about the UNION ALL performance vs a single table. Make sure you have indexes on fields used to either sort or filter.

Duane
MS Access MVP

 

[caitlin]

Hi folks -
Just wanted to update you on this. Union vs Union All didn't make much difference. I combined the two tables into one table, and it did help speed things up a bit, but it turns out the union query wasn't really the crux of the problem.

The union query was based on two queries that included GROUP BY clauses, since I needed only a subset of the mega-table for that particular query. I created another table that duplicated just that subset of data, and this made a HUGE difference.

My lesson learned - Group By statements can REALLY slow down a query on a large number of records.

Thanks all for your help!

 

[r937]

thanks for the update

yes, GROUP BY is relatively expensive, as it does require sorting by the db engine


rudy