Which UNION query runs faster - or is better to use as a subquery.

[Darren Bartrup]

EDIT: The heading should just read 'Which UNION query runs faster'

Hi all,

I need to use a subquery to exclude records from the main query. Which way of writing it would be better?

This way unions it all together before checking the ListDate and I need to use DISTINCT to exclude duplicates as the ListDate changes on each record:

SELECT DISTINCT User
FROM    (
         SELECT          User,
                         ListDate
         FROM            Contact_List_CL

         UNION SELECT    User,
                         ListDate
         FROM            Contact_List_IN
                
         UNION SELECT    User,
                         ListDate
         FROM            Contact_List_OL
        )
WHERE   ListDate BETWEEN [Forms].[frmCalendar].[clndr]-1
        AND [Forms].[frmCalendar].[clndr]-6

Or this way, which has to look the calendar form three times:

SELECT          User,
FROM            Contact_List_CL
WHERE           ListDate BETWEEN [Forms].[frmCalendar].[clndr]-1
                AND [Forms].[frmCalendar].[clndr]-6
         
         
UNION SELECT    User,
FROM            Contact_List_IN
WHERE           ListDate BETWEEN [Forms].[frmCalendar].[clndr]-1
                AND [Forms].[frmCalendar].[clndr]-6
                
                
UNION SELECT    User,
FROM            Contact_List_OL
WHERE           ListDate BETWEEN [Forms].[frmCalendar].[clndr]-1
                AND [Forms].[frmCalendar].[clndr]-6

I'm thinking it's probably the first query as there's less calls to the form?

 

[xenou]

I probably would go with the second query on the reasoning that the first selects all the data first, then filters it -- unless the tables aren't very large, as in many hundred thousands of records, in which case it probably doesn't matter. In principle SQL is a fourth generation language meaning you tell the DB what you want and the DB decides how to get it for you, including things like re-arranging your query to optimize it. I wouldn't expect Access to be as sophisticated on this point as an enterprise server system but it does seem to be quite smart most of the time. I.e., in the ideal it will run both queries pretty much the same way.

If you are accessing a form variable 3 times in a query it is quite likely that the value is cached in RAM for quick access so that may not be a concern at all. I think you'd have to time them in a series of repetitions to find out for sure which is fastest.

Note: it may be a help to index ListDate, if you haven't already done so. That can have a big impact on a query - in which case, you notice right away if it made a difference or not.