Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    40

    Question Unanswered: Speeding up Union Query

    I import tables from three different tills which I combine via a UNION query to make one huge file from which I then query and generate Invoices. The system works without a hitch except the UNION query (queries actually, their are two of them) take 35 seconds to run on my 1.6Ghz Athlon (yes, they are that big!). The problem is that each time I produce an invoice (Report) these UNION queries run. Since the tables are imported the data doesn't change during a session, I only really need these UNION queries to run once per session, and save that result to a table or something, to which all other queries, subqueries etc can refer. This would really speed things up. My question is, can it be done?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To answer the question: yes.

    Are you using UNION or UNION ALL? You know the latter is MUCH more efficient, right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Posts
    40
    UNION. The the queries are bother essentially the same, just working with different tables. One of the Queries is:

    SELECT 2 AS TillNo, RECHEAD.*
    FROM RECHEAD
    UNION
    SELECT 3 AS TillNo, RECHEAD1.*
    FROM RECHEAD1
    UNION SELECT 4 AS TillNo, RECHEAD2.*
    FROM RECHEAD2;

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    UNION results in the data sets being ORDERED and then duplicates are REMOVED. This is inefficient if you don't need this to happen.
    How does this perform?
    Code:
    SELECT 2 AS TillNo, RECHEAD.*
    FROM RECHEAD
    UNION ALL
    SELECT 3 AS TillNo, RECHEAD1.*
    FROM RECHEAD1
    UNION ALL
    SELECT 4 AS TillNo, RECHEAD2.*
    FROM RECHEAD2;
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Dec 2007
    Posts
    40
    WOW! What a difference that makes! I see what you mean by UNION ALL being MUCH more efficient, the queries now run in about a second.
    Thanks Pootle

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Wow! I am surprised too... 35 seconds to 1... UNION ALL FTW!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •