Solved

Union Query Slow

Posted on 2006-11-11
7
327 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now