query runs slow

[smithrd6]

I have queries based upon unions of many tables. Here is an example of the sql:

TABLE [tblWG113A1514-2] UNION ALL TABLE [tblWG113A1714-2] UNION ALL TABLE [tblWG113N1147-3] UNION ALL TABLE [tblWG113N1222-3] UNION ALL TABLE [tblWG113N1303-3] UNION ALL TABLE [tblWG113N1323-3] UNION ALL TABLE [tblWG113N3102-1] UNION ALL TABLE [tblWG113N3152-1] UNION ALL TABLE [tblWG113U1000-5] UNION ALL TABLE [tblWG113U1000-6] UNION ALL TABLE [tblWG113U1000-7] UNION ALL TABLE [tblWG113U1000-8] UNION ALL TABLE [tblWG115A1120-1] UNION ALL TABLE [tblWG115U8201-32] UNION ALL TABLE [tblWG115U8201-33] UNION ALL TABLE [tblWG115W1120-5] UNION ALL TABLE [tblWG65B13174-1] UNION ALL TABLE [tblWG65B13472-2] UNION ALL TABLE [tblWG65B13472-3] UNION ALL TABLE [tblWG65B13473-1] UNION ALL TABLE [tblWG65B13473-3] UNION ALL TABLE [tblWG65B17057-1] UNION ALL SELECT *



from [tblWG113A1312-2];

From this, another query is run which compiles several more unions.....

for example:

SELECT * FROM [TW Union]

UNION ALL

SELECT * FROM [VSM Union]

UNION ALL

SELECT * FROM [WG Union]

UNION ALL SELECT * FROM [WM Union];

UNION ALL SELECT * FROM [FT UNION];

 

[Joe4]

Union queries, by nature, are typically slower than other queries (maybe because there is no relationship defined between data sources).

So the more Union queries you have, the slower it will run. You might want to take a step back at your database design. If you have that many Union queries, it may be a sign that the database isn't designed as efficiently as it should be (sometimes that may be out of your control).

Instead of having all these tables with similar layouts, is it possible to just have one large table where all this information is imported into? You can add a new field that designates whatever is different between these tables.

In the past, I have had situations where I will write the query data back to a new table via a Make-Table Query or Append Query. This works well for things like pass-through databases, especially if I automate the whole process through Macros/VBA.

 

[smithrd6]

the make-table query is a "leap" in performance..........I assume the table must be updated with an new query as the other tables change?

 

[Joe4]

That is the draw-back to the make-table approach, you kind of lose the dynamic nature of the database, as changes to the underlying tables will not automatically be reflected in this new table.

What I would probably do is create a macro that deletes the data/table and then recreates it every time you need to access the data, so you are sure that your data is "refereshed" every time you need to access it.

If we knew a little bit about all these tables (where the data is coming from, is it imported or linked, how it is updated), we might be able to offer some other solutions.

 

[smithrd6]

thanks

each table is populated from a multitude of user forms.

 

[Joe4]

Why don't all the user forms write back to the same table (or just a few linked tables), instead of each having its own separate table?

 

[smithrd6]

each table contains unique serial numbers

 

[Joe4]

Not sure what you mean.

If you just had this one master table, couldn't you just add a Serial Number field to it, and populate the record with that specific Serial Number when the data is entered through the Form (different forms could write different Serial Numbers to this field, though if all your Forms are similar, I would look into just creating one form also and having a drop-down or something where they indicate the specific serial number).

 

[smithrd6]

your confusion is justified as I am THE rookie at all this.....what I have is a multitude of vendors who produce parts. A vendor can never produce a part with the same serial number. First attempt was to have tables for each part number; however, as some vendors will produce the same part, some will use the same serial number as well. So the part number tables did not work as each table used serial number as the primary key.

 

[Joe4]

What I would recommend doing is having one table with a part number field. Since this part number is not unique, you obviously don't want to have that as your primary key field (though you probably do want it as an indexed field).

There are various different options with primary key fields if you do not have one unique field for every single record:

1. Have a multi-field primary key field. Maybe one field alone doesn't create a unique value, but maybe multiple fields will, like serial number along with vendor id create a unique key.

2. Use an AutoNumber primary key field. This is really just an automated incrementer. The values, in and of themselves, really don't have any meaning as they relate to the data, but ensure a unique primary key.

3. Have no primary key field. Primary keys are nice to have, but not always necessary (you can have tables with no primary keys).