Unions slow in IDS 7.30

Posted in 1999

Topics: Versions, Editions & End-of-Life

Willem Roos — Fri, 24 Sep 1999 00:27:28 +0200 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF0612.D712FEAC Content-Type: text/plain; charset="iso-8859-1" All, One of our users is running a query with a union consisting of 2 subselects. The two subselects run fine on their own (15 seconds). With a UNION they take more than a minute, but with a UNION ALL they run their usual 15 seconds again. The union vs. union all have identical query execution plans - only the est. # of rows differs by a few hundred, but the same cost. This used to be OK on 7.23 but seems to have broken on 7.30. I've run update stats until blue in the face. Has anyone seen this before. TIA /* ----------------------------------------------------- * * Willem Roos (+27) 21 980 4941 * * Forced to use M$ Outlook ## * * ----------------------------------------------------- */ ------_=_NextPart_001_01BF0612.D712FEAC Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>Unions slow in IDS 7.30</TITLE> </HEAD> <BODY> All, One of our users is running a query with a union = consisting of 2 subselects. The two subselects run fine on = their own (15 seconds). With a UNION they take more than a = minute, but with a UNION ALL they run their usual 15 seconds = again. The union vs. union all have identical query = execution plans - only the est. # of rows differs by a few = hundred, but the same cost. This used to be OK on 7.23 but seems to have broken = on 7.30. I've run update stats until blue in the face. Has = anyone seen this before. TIA /* = ----------------------------------------------------- *  * Willem = Roos           &n= bsp;           &n= bsp; (+27) 21 980 4941 *  * Forced to use M$ Outlook = ##           &nbs= p;           &nbs= p;   *  * = ----------------------------------------------------- */ </BODY> </HTML> ------_=_NextPart_001_01BF0612.D712FEAC--

↪ replying to Willem Roos

Leo Cardoso — Thu, 23 Sep 1999 22:27:53 -0300 — source: Informix-list mailing list archive (1991-1998)

Hi... >(15 seconds). With a UNION they take more than a minute, >but with a UNION ALL they run their usual 15 seconds again. I hope that u know the difference between UNION and UNION ALL. With the first one, the server get the results from the individual queries and make an "order by" to delete duplicate rows. UNION ALL must be faster than a simple UNION with a big number of returning rows. If u need a UNION, try to cut some fields in the queries, making it with less width. Sorry for the English! []s from Brazil LEO Cardoso ---

Related threads

Glossary

IDS (Informix Dynamic Server)