Solved

Union Query Slow

Posted on 2006-11-11
7
344 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
  • 4
  • 2
7 Comments
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 250 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 50 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 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