#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    6
    Rep Power
    0

    Slow query when using UNION and VIEWs.


    Hi guys.

    I am having such query:

    SELECT * FROM
    (SELECT * FROM Archive_data
    LEFT JOIN MyView
    ON Archive_Data.ID = MyView.ID)
    UNION ALL
    SELECT * FROM Data
    LEFT JOIN MyView
    ON Data.ID = MyView.ID

    The query is executing in 45 sec when using UNION and MyView VIEW.

    If I separate query onto two queries then each of the queries is executing below one second.

    Why UNION has such impact on the query when I am using views?

    Thanks for the answer.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    74
    Rep Power
    5
    Because it has to search * data

    What is the size of your database?

    Are your indexes OK?

    In my opinion a SELECT * FROM X is always slow in case of huge amount of data.

    Simply the difference between a cheap query and an expensive one, right?.

    Try to limit your output:

    http://scott.yang.id.au/2004/01/limit-in-select-statements-in-firebird/
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by rapgame
    Because it has to search * data

    What is the size of your database?

    Are your indexes OK?

    In my opinion a SELECT * FROM X is always slow in case of huge amount of data.

    Simply the difference between a cheap query and an expensive one, right?.

    Try to limit your output:

    http://scott.yang.id.au/2004/01/limit-in-select-statements-in-firebird/
    Thanks rapgame, but I do not think that selecting ALL columns is the problem here. (I could be always wrong). But if separated queries are executing 1 second each and UNION is simply adding one result set to the other than how can it slow down the whole operation 20 times? This is simply not logical. Unless UNION is diong something more that adding one result to the other...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Originally Posted by Unspoken
    Thanks rapgame, but I do not think that selecting ALL columns is the problem here. (I could be always wrong). But if separated queries are executing 1 second each and UNION is simply adding one result set to the other than how can it slow down the whole operation 20 times? This is simply not logical. Unless UNION is diong something more that adding one result to the other...
    UNION removes the duplicate records from the both queries. So it's like GROUP BY all of your fields. Just for the test - how long takes if you use UNION ALL( UNION ALL doesn't remove duplicates)
    Last edited by mIRCata; November 21st, 2011 at 03:55 AM.

IMN logo majestic logo threadwatch logo seochat tools logo