SQL Union Query

[avarga82]

Help! I have 2 tables: tblAbsences and tblAbsences_Archive. Each day, 5K records are inserted into tblAbsences, which is why I need to archive the old data. To run the necessary reports, I need both tables combined into one query. I'm using a UNION query, and since there's appr. 3 million records in the archive table, and up to 500,000 in the Absences table, it takes close to 20 min to run in Query Analyzer. This is going to be an Access Jet Query, which is slower. Either way, it's unacceptable. Any ideas?

Query:

Select StudentID, ClassID, Date, Status from tblAbsences
UNION
Select StudentID, ClassID, Date, Status from tblAbsences_Archive

 

[SQLSister]

Let's start with the Access jet query thing. Don't. Call a stored procedure from Access instead, it will be significantly faster.

Now as to your query. Do you really need to look at a all 3,500,000 records every time? Or should you be runnning reports on only a subset of those (those in the last month or those in the last year?) A where clause in your query would significantly speed things up.

What indexing do you have the tables? Covering indexes of the four fields you selected might speed it up.

Questions about posting. See faq183-874

 

[avarga82]

I'll get a little more specific...

Here's the scenario. I work at a school, and we need to store attendance info for each student, for each class, everyday: hence the 5K records/day. My predecessor created the Absences table without a PK and no indexes. When I got here, there were so many duplicate entries, and fixing it was set on the back burner. It's about that time now to fix it.

I have 2 options: Use a UNION query to get distinct values, then drop them into a new table that I can index properly and use here on out, or create these slow UNION queries to be run each time we do reporting.

What do you think?

 

[SQLSister]

Yep fixing the data would be a high priority for me.

Questions about posting. See faq183-874

 

[avarga82]

OK...FINALLY the data is fixed. And by fixed I mean no duplicate records and the table properly indexed. Still, there are 2 tables that need to be UNION-ized. It still takes close to 15 minutes to accomplish this, even using the UNION ALL as opposed to the UNION. Any ideas? I still need the separate tables, and I still need both tables' information. Any ideas??

 

[SQLSister]

What kind of indexing do you have?

What possible reason do you have to show all 3.5 million records when you run this query? Who will be looking at all of them? A where clause would speed things up considerably.

What are you doing with this giant union? are you by chance doing some kind of agrgregate function?

Questions about posting. See faq183-874

 

[avarga82]

Remember, I'm a UNION virgin here...

Anways, my table is as follows:

StudentID (Indexed)
Date (Indexed)
ClassInstanceID (Indexed)
Status

StudentID, Date, and ClassInstanceID are the Primary Key, and the only indexes, as these are the only fields we search on. Both tables are setup identically.

What I'm aiming for is something like:

Select * from BOTHTABLES Where StudentID = X, Date = Y, and ClassInstanceID = Z

Now that's obviously pseudocode, but you get the idea.

Any ideas?

 

[SQLSister]

Select StudentID, ClassID, Date, Status from tblAbsences
UNION ALL
Select StudentID, ClassID, Date, Status from tblAbsences_Archive
Where StudentID = X, Date = Y, and ClassInstanceID = Z
seems to be your only option.

YOu might get some additional speed out of a stored procedure that has an if statment and if the date input is in the last 30 days (or what ever the amount of days you keep in your active table), then it only queries that table and otherwise it queries both. That would at least reduce the number of times you have to use the union. You might try UNion ALL as above so that it doesn;t spend time trying to filter out duplicate records which you should naot have inthis case anyway.

Questions about posting. See faq183-874