?
Solved

Union Query Slow

Posted on 2006-11-11
7
Medium Priority
?
362 Views
Last Modified: 2008-01-09
Hey there

This query is running really really slow.  To add fuel to the fire, I have to work on this database via a VPN connection this weekend (the DB was not designed for this type of connectivity as it is grabbing data over the network, and does not have the tables locally stored on my laptop)

select *  From tbl_atl_ospec
union

select * From tbl_bos_ospec
union

select * From tbl_chi_ospec
union

select * From tbl_dal_ospec
union

select * From tbl_lac_ospec
union

select * From tbl_sfc_ospec
UNION select * From tbl_stl_ospec;

Could someone please tell me what I could do to enhance performance?
0
Comment
Question by:cstraim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 1000 total points
ID: 17922647
Connection over a VPN will kill it even if it's fast :-S

It depends on the data that's involved.
If there are a lot of records - or peculiarities of the data - who knows.
But they appear to be tables - rather than other queries - so that's in your favour.

Is the data such that it has to be unique?
Would Union All still give you want you want?
(But should be quicker)

e.g.
select *  From tbl_atl_ospec
union all

select * From tbl_bos_ospec
union all

select * ... etc
0
 

Author Comment

by:cstraim
ID: 17922661
Im going to try it..give me a few minutes....it kills me its so slow just to maneuver around in it.  

The tables are linked to CSV files located over the network (as I said the database is not designed the best to begin with..primarily becuase of corporate beureacracy.)

The tables can contain duplicate data, as all the tables have the same structure just different data from different regions, but account numbers make the data unique on its own, as they are unique on their own.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17922671
Ow... linked CSV files will only help kill your performance even more.

I'm not sure what more there is to suggest.
Unfortunately your situation is a recipe for slowness.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cstraim
ID: 17922732
it did work thank you...heres a question...Since the tables are linked CSV files, and I'm building a union query to place all the data (from several locations) into one location, is there a way to index fields in the union query so that when i run addtional queries against the union query, they will go faster?
0
 

Author Comment

by:cstraim
ID: 17922776
im looking around and I'm not seeing any way to index fields within a query (unless I'm missing something).  I've read that there is a way to create a "temporary" table using the query, I'm not sure what this means or what is involved.  I know that I don't want to have to remember to update the table each time I need to run a query (which will be a lot of times especially becuase I'm linking excel pivot tables to the queries that are based on the union query). Do you know what they are trying to say regarding a "temporary" table, and how I may be able to apply it to what I'm trying to accomplish?
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 200 total points
ID: 17922883
In Access, you cannot create indexes on queries, only on tables.

Access does not have 'temporary' tables. Only REAL tables - and you would need to 'refresh' them in order to see new or revised data.

AW
0
 

Author Comment

by:cstraim
ID: 17922902
thanks folks...I appreciate your help !!!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question