Union Query Slow

Posted on 2006-11-11
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

select * From tbl_bos_ospec

select * From tbl_chi_ospec

select * From tbl_dal_ospec

select * From tbl_lac_ospec

select * From tbl_sfc_ospec
UNION select * From tbl_stl_ospec;

Could someone please tell me what I could do to enhance performance?
Question by:cstraim
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
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)

select *  From tbl_atl_ospec
union all

select * From tbl_bos_ospec
union all

select * ... etc

Author Comment

ID: 17922661
Im going to try it..give me a few 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.
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.
Independent Software Vendors: 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

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?

Author Comment

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?
LVL 44

Accepted Solution

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.


Author Comment

ID: 17922902
thanks folks...I appreciate your help !!!

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

734 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