CODEWITHSUNDEEP
sqlms-access
Vred
Vred

Reputation: 21

Optimising a clumsy query

I work in a school and have built a database to help out with the nightmare of checking which kids have gone to which after school clubs.

I have attempted to do this by building a databse which draws from each of the 42 registers (excel documents) using link-tables. I've then setup several queries which search for pupils from specific forms which I then generate a report from so that each form group can see if the children in their form have gone to a club in a particular week.

My knowledge of SQL is basic: I can write queries to do various things but do not know how to optimise large queries.

The issue I am having is that each report takes 2 minutes 30 seconds to generate now I have added all 42 registers to the query. Before, when I had only 31, the report generated in 30 seconds.

Here is a sample of the query:

SELECT [UID], [Name], [Group], [Week 1], [Week 2], [Week 3],
    [Week 4], [Week 5], [Week 6]
FROM [club 1]
WHERE ((([Club 1].[Group])="group name"))

UNION ALL 

SELECT [UID], [Name], [Group], [Week 1], [Week 2], [Week 3],
    [Week 4], [Week 5], [Week 6]
FROM [club 42]
WHERE ((([Club 42].[Group])="group name"));

The query has 41 UNION ALL SELECT's after the initial SELECT and is most likely the most inificient, eye-hurtingly badly written query but, as I said, my knowledge of SQL is basic and I am learning as I go.

Some more info: There are 480 students on these registers and one student can go to more than one club in a week so the query will need to find each instance a pupil has attended a club.

Options outside of using Access with Excel are very, very limited. I have spoken with some friends and looked at the MySQL approach and setting up a local server on the network but our network is a manged service and the company who manages it are touchy to say the least about something like that being setup on their servers (understandably so).

I imagine that, for what I need, this is probably the only way to do it and having a query take 2 minutes 30 seconds is better than the 30 minutes it did take to manaully check each register every Friday. The reason I am trying to sort through this is:

A) I want to expand my knowledge of programming further and have, so far, managed to miss SQL
B) The teachers accessing the database are often impatient and click randomly when the report is loading in an attempt to make it work faster or out of frustration, often crashing Access

Upvotes: 2

Views: 276

Answers (3)

Walter Mitty
Walter Mitty

Reputation: 18950

Your basic problem is that you are trying to write good queries against a terrible database.

If all 42 clubs provide the same data about students, then they should all be in the same table. There should be an additonal column, "ClubId" to indicate which club the data pertains to. In order for ClubId to make sense, the has to be a table of clubs, with data about the club, like the name of the club. This table has a column named "Id" that provides a reference point for ClubId in your attendance table.

There's more to good design than the above, but this is miles ahead of where you are now.

Learn how to coalesce the tables you get from excel registers into a single table. Learn how to use a join to combine data from registers with data about clubs. The write a good query. It should take a few seconds to run with your volume of data.

Upvotes: 0

Joel Brown
Joel Brown

Reputation: 14418

The fundamental problem that you have is that you are pulling data from 42 distinct sources through what is ultimately an awkward mechanism. This isn't something that you've done wrong, it's just a fact of life given your Excel to Access system architecture.

If the users on your network have a license to use Access, then you could replace your 42 Excel spreadsheets with a single Access database which users connect to in shared mode. Keeping all of the data in a single table instead of 42 external linked data sources would give you sub-second response times for generting your reports.

Back in the heydey of VB3, I built VB and Access applications that were shared by many people over the network. Your network provider couldn't possibly object to a single file being stored on their server.

If your users don't have a license to use Access, then you could create a simple ASP.NET front end to the shared Access database.

Once you've taken the decision to move to a single shared database, you should also make sure that the design of your database is normalized so that your queries can be simple and efficient and your data maintenance code can be simple. Don't think in terms of spreadsheet designs! If you are going to go this route and need advice for a schema design, post a comment to let me know.

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

My advice would be not to have a table per club but only one table with an additional column [Club_Name] or an ID to later join the results to a Clubs table.

This way the query would be something like:

SELECT [UID], [Name], [Group], [Club_Name], [Week 1], [Week 2], [Week 3],
    [Week 4], [Week 5], [Week 6]
FROM [Clubs]
WHERE ((([Clubs].[Group])="group name"));

Do you think it would be possible to do that?

Upvotes: 3

Related Questions