Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Union Query Slow

Posted on 2006-11-11
7
Medium Priority
?
371 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 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