my personal SQL parser was overwhelmed ;-)
As you probably build that long statement from variables holding identical information like field lists, join conditions and so on, do you have a concat or text..endext that you can post to highlight differences vs. "equal stuff" ?
One thing already puzzling: In your OP you wrote that you UNION different selects and backend processing in many or single steps might evoke longer runtimes - but here you UNION ALL ? As duplicate records are allowed, I see less of a performane problem , as server-side filtering of duplicates is not happening IAC.
OTOH for data analysis I at least start building with single selects / consecutive insert into statements (often with a "addstep" column to signify from which query [part] particular row was added) and only sometimes convert functioning stuff into long statements if perf is enhanced so much that you can feel, not only measure it ;-)
somewhat puzzzled
thomas
>>If you can post the query maybe? things such as LIKE and other things can cause issues too.
>
>Here the query, if I select around 2-3 unions it works, but once it gets more it seems to fail. So the query in itself is OK, just when there are too many unions it will break, just as if it would cut off the string somewhere in the middle.
>I think it would be better to build up the where clause with OR conditions and join with the table that is scanned to get the conditions.
>
>But I would have thought this would be working as well.
>
>
> SELECT
> MFB699.MFBWNr,
> MIN(MFB699.SrtCode) AS SrtCode, /* Not necessary ? */
> MFB699.MFBWOms,
> MFB681.MFBPNr,
> MFB681.MFBProc,
> MFB681.MFBPrR,
>
> MFB690.MFBPDVV,
> MFB690.MFBPOMS,
> MFB690.MFBPWIN,
> MFB690.MFBPWIND,
> MFB690.MFBKNR AS MFBKNr_Start,
>
> MFB691.MFBPNrV,
> MFB691.MFBVNr,
> MFB691.MFBKNr,
> MFB691.MFBPJK,
> MFB691.MFBPJa,
> IFNULL(MFB693_Ja.MFBAJN,'') AS MFBAJN_Ja,
> MFB691.MFBPNK,
> MFB691.MFBPNa,
> IFNULL(MFB693_Na.MFBAJN,'') AS MFBAJN_Na,
>
> MFB692.MFBVOms,
> MFB692.MFBFuWo,
> MFB692.MFBFunNr,
> MFB692.MFBVStJ,
> MFB692.MFBVStJT,
> MFB692.MFBVStN,
> MFB692.MFBVStNT,
> MFB692.MFBVOPER,
> MFB692.MFBVW,
>
> CAST(1 AS Decimal(1,0)) AS Status,
> CAST("58699D49-DBE4-4D2D-B476-E51C76710194" AS Character(36)) AS RecordKey,
> CAST(0 AS Decimal(1,0)) AS Interactie,
> CAST("" AS Character(36)) AS RecordKeyIA,
> CAST("" AS Character(240)) AS Omschr,
> IFNULL(Memofield.MeMemo,"") AS Omschr2,
> CAST(0 AS Decimal(1,0)) AS Result, /* Functie Result: 1 = Ja, 2 = Nee */
> CAST(0 AS Decimal(1,0)) AS Aktie, /* Aktie (only if MFBPJa or MFBPNa): 1 = Ja, 2 = Nee */
> CAST('0000000000' AS Character(10)) AS AktieNr, /* Aktie nummer */
> CAST('0000000000' AS Character(10)) AS AktieKNr, /* next Knooppuntnummer nummer */
> IFNULL(Memofield.MeMemo,"") AS Links
>
> FROM MFB699
> STRAIGHT_JOIN MFB681 ON MFB681.MFBWNr = MFB699.MFBWNr
> STRAIGHT_JOIN MFB690 ON MFB690.MFBPNr = MFB681.MFBPNr
> STRAIGHT_JOIN MFB691 ON MFB691.MFBPNr = MFB681.MFBPNr
> AND MFB691.MFBPNrV = MFB690.MFBPNrV
> STRAIGHT_JOIN MFB692 ON MFB692.MFBVNr = MFB691.MFBVNr
> LEFT JOIN MFB698 ON MFB698.MFBPNr = MFB681.MFBPNr
> AND MFB698.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocol ON MFBSetProtocol.DelFlag = 0
> AND MFBSetProtocol.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocol.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocolPat ON MFBSetProtocolPat.DelFlag = 0
> AND MFBSetProtocolPat.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocolPat.msPaKey = ?pcPaKey
>
> LEFT JOIN MFB693 MFB693_Ja ON MFB693_Ja.MFBANr = MFB691.MFBPJa
> LEFT JOIN MFB693 MFB693_Na ON MFB693_Na.MFBANr = MFB691.MFBPNa
>
> LEFT JOIN Memofield ON 1=2
> WHERE 1=1
> AND MFB690.MFBPWIN = "N"
> AND MFBSetProtocol.DelFlag = 0 AND ( IFNULL(MFBSetProtocolPat.msActive, MFBSetProtocol.msActive) = 1 )
> AND MFB681.MFBPrR = ?pnProcess AND MFB698.MFBLBLNR = 5
> AND (
> (MFB699.CodeNV = 00101214 AND MFB699.SrtCode = 40)
> OR (MFB699.CodeNV = 00047481 AND MFB699.SrtCode = 45)
> OR (MFB699.CodeNV = 01757040 AND MFB699.SrtCode = 50))
> GROUP BY 1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
> UNION ALL
> SELECT
> MFB699.MFBWNr,
> MIN(MFB699.SrtCode) AS SrtCode, /* Not necessary ? */
> MFB699.MFBWOms,
> MFB681.MFBPNr,
> MFB681.MFBProc,
> MFB681.MFBPrR,
>
> MFB690.MFBPDVV,
> MFB690.MFBPOMS,
> MFB690.MFBPWIN,
> MFB690.MFBPWIND,
> MFB690.MFBKNR AS MFBKNr_Start,
>
> MFB691.MFBPNrV,
> MFB691.MFBVNr,
> MFB691.MFBKNr,
> MFB691.MFBPJK,
> MFB691.MFBPJa,
> IFNULL(MFB693_Ja.MFBAJN,'') AS MFBAJN_Ja,
> MFB691.MFBPNK,
> MFB691.MFBPNa,
> IFNULL(MFB693_Na.MFBAJN,'') AS MFBAJN_Na,
>
> MFB692.MFBVOms,
> MFB692.MFBFuWo,
> MFB692.MFBFunNr,
> MFB692.MFBVStJ,
> MFB692.MFBVStJT,
> MFB692.MFBVStN,
> MFB692.MFBVStNT,
> MFB692.MFBVOPER,
> MFB692.MFBVW,
>
> CAST(1 AS Decimal(1,0)) AS Status,
> CAST("D1758CF0-1CDA-481F-AF84-6C5D49A6323D" AS Character(36)) AS RecordKey,
> CAST(0 AS Decimal(1,0)) AS Interactie,
> CAST("" AS Character(36)) AS RecordKeyIA,
> CAST("" AS Character(240)) AS Omschr,
> IFNULL(Memofield.MeMemo,"") AS Omschr2,
> CAST(0 AS Decimal(1,0)) AS Result, /* Functie Result: 1 = Ja, 2 = Nee */
> CAST(0 AS Decimal(1,0)) AS Aktie, /* Aktie (only if MFBPJa or MFBPNa): 1 = Ja, 2 = Nee */
> CAST('0000000000' AS Character(10)) AS AktieNr, /* Aktie nummer */
> CAST('0000000000' AS Character(10)) AS AktieKNr, /* next Knooppuntnummer nummer */
> IFNULL(Memofield.MeMemo,"") AS Links
>
> FROM MFB699
> STRAIGHT_JOIN MFB681 ON MFB681.MFBWNr = MFB699.MFBWNr
> STRAIGHT_JOIN MFB690 ON MFB690.MFBPNr = MFB681.MFBPNr
> STRAIGHT_JOIN MFB691 ON MFB691.MFBPNr = MFB681.MFBPNr
> AND MFB691.MFBPNrV = MFB690.MFBPNrV
> STRAIGHT_JOIN MFB692 ON MFB692.MFBVNr = MFB691.MFBVNr
> LEFT JOIN MFB698 ON MFB698.MFBPNr = MFB681.MFBPNr
> AND MFB698.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocol ON MFBSetProtocol.DelFlag = 0
> AND MFBSetProtocol.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocol.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocolPat ON MFBSetProtocolPat.DelFlag = 0
> AND MFBSetProtocolPat.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocolPat.msPaKey = ?pcPaKey
>
> LEFT JOIN MFB693 MFB693_Ja ON MFB693_Ja.MFBANr = MFB691.MFBPJa
> LEFT JOIN MFB693 MFB693_Na ON MFB693_Na.MFBANr = MFB691.MFBPNa
>
> LEFT JOIN Memofield ON 1=2
> WHERE 1=1
> AND MFB690.MFBPWIN = "N"
> AND MFBSetProtocol.DelFlag = 0 AND ( IFNULL(MFBSetProtocolPat.msActive, MFBSetProtocol.msActive) = 1 )
> AND MFB681.MFBPrR = ?pnProcess AND MFB698.MFBLBLNR = 5
> AND (
> (MFB699.CodeNV = 00093327 AND MFB699.SrtCode = 40)
> OR (MFB699.CodeNV = 00039551 AND MFB699.SrtCode = 45)
> OR (MFB699.CodeNV = 01791583 AND MFB699.SrtCode = 50))
> GROUP BY 1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
> UNION ALL
> SELECT
> MFB699.MFBWNr,
> MIN(MFB699.SrtCode) AS SrtCode, /* Not necessary ? */
> MFB699.MFBWOms,
> MFB681.MFBPNr,
> MFB681.MFBProc,
> MFB681.MFBPrR,
>
> MFB690.MFBPDVV,
> MFB690.MFBPOMS,
> MFB690.MFBPWIN,
> MFB690.MFBPWIND,
> MFB690.MFBKNR AS MFBKNr_Start,
>
> MFB691.MFBPNrV,
> MFB691.MFBVNr,
> MFB691.MFBKNr,
> MFB691.MFBPJK,
> MFB691.MFBPJa,
> IFNULL(MFB693_Ja.MFBAJN,'') AS MFBAJN_Ja,
> MFB691.MFBPNK,
> MFB691.MFBPNa,
> IFNULL(MFB693_Na.MFBAJN,'') AS MFBAJN_Na,
>
> MFB692.MFBVOms,
> MFB692.MFBFuWo,
> MFB692.MFBFunNr,
> MFB692.MFBVStJ,
> MFB692.MFBVStJT,
> MFB692.MFBVStN,
> MFB692.MFBVStNT,
> MFB692.MFBVOPER,
> MFB692.MFBVW,
>
> CAST(2 AS Decimal(1,0)) AS Status,
> CAST("900D38E4-E3A0-4921-9701-66B8987435C9" AS Character(36)) AS RecordKey,
> CAST(0 AS Decimal(1,0)) AS Interactie,
> CAST("" AS Character(36)) AS RecordKeyIA,
> CAST("" AS Character(240)) AS Omschr,
> IFNULL(Memofield.MeMemo,"") AS Omschr2,
> CAST(0 AS Decimal(1,0)) AS Result, /* Functie Result: 1 = Ja, 2 = Nee */
> CAST(0 AS Decimal(1,0)) AS Aktie, /* Aktie (only if MFBPJa or MFBPNa): 1 = Ja, 2 = Nee */
> CAST('0000000000' AS Character(10)) AS AktieNr, /* Aktie nummer */
> CAST('0000000000' AS Character(10)) AS AktieKNr, /* next Knooppuntnummer nummer */
> IFNULL(Memofield.MeMemo,"") AS Links
>
> FROM MFB699
> STRAIGHT_JOIN MFB681 ON MFB681.MFBWNr = MFB699.MFBWNr
> STRAIGHT_JOIN MFB690 ON MFB690.MFBPNr = MFB681.MFBPNr
> STRAIGHT_JOIN MFB691 ON MFB691.MFBPNr = MFB681.MFBPNr
> AND MFB691.MFBPNrV = MFB690.MFBPNrV
> STRAIGHT_JOIN MFB692 ON MFB692.MFBVNr = MFB691.MFBVNr
> LEFT JOIN MFB698 ON MFB698.MFBPNr = MFB681.MFBPNr
> AND MFB698.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocol ON MFBSetProtocol.DelFlag = 0
> AND MFBSetProtocol.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocol.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocolPat ON MFBSetProtocolPat.DelFlag = 0
> AND MFBSetProtocolPat.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocolPat.msPaKey = ?pcPaKey
>
> LEFT JOIN MFB693 MFB693_Ja ON MFB693_Ja.MFBANr = MFB691.MFBPJa
> LEFT JOIN MFB693 MFB693_Na ON MFB693_Na.MFBANr = MFB691.MFBPNa
>
> LEFT JOIN Memofield ON 1=2
> WHERE 1=1
> AND MFB690.MFBPWIN = "N"
> AND MFBSetProtocol.DelFlag = 0 AND ( IFNULL(MFBSetProtocolPat.msActive, MFBSetProtocol.msActive) = 1 )
> AND MFB681.MFBPrR = ?pnProcess AND MFB698.MFBLBLNR = 5
> AND (
> (MFB699.CodeNV = 00101575 AND MFB699.SrtCode = 40)
> OR (MFB699.CodeNV = 00061077 AND MFB699.SrtCode = 45)
> OR (MFB699.CodeNV = 01979566 AND MFB699.SrtCode = 50))
> GROUP BY 1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
> UNION ALL
> SELECT
> MFB699.MFBWNr,
> MIN(MFB699.SrtCode) AS SrtCode, /* Not necessary ? */
> MFB699.MFBWOms,
> MFB681.MFBPNr,
> MFB681.MFBProc,
> MFB681.MFBPrR,
>
> MFB690.MFBPDVV,
> MFB690.MFBPOMS,
> MFB690.MFBPWIN,
> MFB690.MFBPWIND,
> MFB690.MFBKNR AS MFBKNr_Start,
>
> MFB691.MFBPNrV,
> MFB691.MFBVNr,
> MFB691.MFBKNr,
> MFB691.MFBPJK,
> MFB691.MFBPJa,
> IFNULL(MFB693_Ja.MFBAJN,'') AS MFBAJN_Ja,
> MFB691.MFBPNK,
> MFB691.MFBPNa,
> IFNULL(MFB693_Na.MFBAJN,'') AS MFBAJN_Na,
>
> MFB692.MFBVOms,
> MFB692.MFBFuWo,
> MFB692.MFBFunNr,
> MFB692.MFBVStJ,
> MFB692.MFBVStJT,
> MFB692.MFBVStN,
> MFB692.MFBVStNT,
> MFB692.MFBVOPER,
> MFB692.MFBVW,
>
> CAST(2 AS Decimal(1,0)) AS Status,
> CAST("B1D8C693-0F09-4669-BC1E-570A31111F0C" AS Character(36)) AS RecordKey,
> CAST(0 AS Decimal(1,0)) AS Interactie,
> CAST("" AS Character(36)) AS RecordKeyIA,
> CAST("" AS Character(240)) AS Omschr,
> IFNULL(Memofield.MeMemo,"") AS Omschr2,
> CAST(0 AS Decimal(1,0)) AS Result, /* Functie Result: 1 = Ja, 2 = Nee */
> CAST(0 AS Decimal(1,0)) AS Aktie, /* Aktie (only if MFBPJa or MFBPNa): 1 = Ja, 2 = Nee */
> CAST('0000000000' AS Character(10)) AS AktieNr, /* Aktie nummer */
> CAST('0000000000' AS Character(10)) AS AktieKNr, /* next Knooppuntnummer nummer */
> IFNULL(Memofield.MeMemo,"") AS Links
>
> FROM MFB699
> STRAIGHT_JOIN MFB681 ON MFB681.MFBWNr = MFB699.MFBWNr
> STRAIGHT_JOIN MFB690 ON MFB690.MFBPNr = MFB681.MFBPNr
> STRAIGHT_JOIN MFB691 ON MFB691.MFBPNr = MFB681.MFBPNr
> AND MFB691.MFBPNrV = MFB690.MFBPNrV
> STRAIGHT_JOIN MFB692 ON MFB692.MFBVNr = MFB691.MFBVNr
> LEFT JOIN MFB698 ON MFB698.MFBPNr = MFB681.MFBPNr
> AND MFB698.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocol ON MFBSetProtocol.DelFlag = 0
> AND MFBSetProtocol.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocol.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocolPat ON MFBSetProtocolPat.DelFlag = 0
> AND MFBSetProtocolPat.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocolPat.msPaKey = ?pcPaKey
>
> LEFT JOIN MFB693 MFB693_Ja ON MFB693_Ja.MFBANr = MFB691.MFBPJa
> LEFT JOIN MFB693 MFB693_Na ON MFB693_Na.MFBANr = MFB691.MFBPNa
>
> LEFT JOIN Memofield ON 1=2
> WHERE 1=1
> AND MFB690.MFBPWIN = "N"
> AND MFBSetProtocol.DelFlag = 0 AND ( IFNULL(MFBSetProtocolPat.msActive, MFBSetProtocol.msActive) = 1 )
> AND MFB681.MFBPrR = ?pnProcess AND MFB698.MFBLBLNR = 5
> AND (
> (MFB699.CodeNV = 00101575 AND MFB699.SrtCode = 40)
> OR (MFB699.CodeNV = 00061077 AND MFB699.SrtCode = 45)
> OR (MFB699.CodeNV = 01979566 AND MFB699.SrtCode = 50))
> GROUP BY 1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
> UNION ALL
> SELECT
> MFB699.MFBWNr,
> MIN(MFB699.SrtCode) AS SrtCode, /* Not necessary ? */
> MFB699.MFBWOms,
> MFB681.MFBPNr,
> MFB681.MFBProc,
> MFB681.MFBPrR,
>
> MFB690.MFBPDVV,
> MFB690.MFBPOMS,
> MFB690.MFBPWIN,
> MFB690.MFBPWIND,
> MFB690.MFBKNR AS MFBKNr_Start,
>
> MFB691.MFBPNrV,
> MFB691.MFBVNr,
> MFB691.MFBKNr,
> MFB691.MFBPJK,
> MFB691.MFBPJa,
> IFNULL(MFB693_Ja.MFBAJN,'') AS MFBAJN_Ja,
> MFB691.MFBPNK,
> MFB691.MFBPNa,
> IFNULL(MFB693_Na.MFBAJN,'') AS MFBAJN_Na,
>
> MFB692.MFBVOms,
> MFB692.MFBFuWo,
> MFB692.MFBFunNr,
> MFB692.MFBVStJ,
> MFB692.MFBVStJT,
> MFB692.MFBVStN,
> MFB692.MFBVStNT,
> MFB692.MFBVOPER,
> MFB692.MFBVW,
>
> CAST(0 AS Decimal(1,0)) AS Status,
> CAST("BB78EA7E-2575-4936-910F-E18AE26848EC" AS Character(36)) AS RecordKey,
> CAST(0 AS Decimal(1,0)) AS Interactie,
> CAST("" AS Character(36)) AS RecordKeyIA,
> CAST("" AS Character(240)) AS Omschr,
> IFNULL(Memofield.MeMemo,"") AS Omschr2,
> CAST(0 AS Decimal(1,0)) AS Result, /* Functie Result: 1 = Ja, 2 = Nee */
> CAST(0 AS Decimal(1,0)) AS Aktie, /* Aktie (only if MFBPJa or MFBPNa): 1 = Ja, 2 = Nee */
> CAST('0000000000' AS Character(10)) AS AktieNr, /* Aktie nummer */
> CAST('0000000000' AS Character(10)) AS AktieKNr, /* next Knooppuntnummer nummer */
> IFNULL(Memofield.MeMemo,"") AS Links
>
> FROM MFB699
> STRAIGHT_JOIN MFB681 ON MFB681.MFBWNr = MFB699.MFBWNr
> STRAIGHT_JOIN MFB690 ON MFB690.MFBPNr = MFB681.MFBPNr
> STRAIGHT_JOIN MFB691 ON MFB691.MFBPNr = MFB681.MFBPNr
> AND MFB691.MFBPNrV = MFB690.MFBPNrV
> STRAIGHT_JOIN MFB692 ON MFB692.MFBVNr = MFB691.MFBVNr
> LEFT JOIN MFB698 ON MFB698.MFBPNr = MFB681.MFBPNr
> AND MFB698.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocol ON MFBSetProtocol.DelFlag = 0
> AND MFBSetProtocol.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocol.MFBPNrV = MFB690.MFBPNrV
>
> LEFT JOIN MFBSetProtocolPat ON MFBSetProtocolPat.DelFlag = 0
> AND MFBSetProtocolPat.MFBPNr = MFB690.MFBPNr
> AND MFBSetProtocolPat.msPaKey = ?pcPaKey
>
> LEFT JOIN MFB693 MFB693_Ja ON MFB693_Ja.MFBANr = MFB691.MFBPJa
> LEFT JOIN MFB693 MFB693_Na ON MFB693_Na.MFBANr = MFB691.MFBPNa
>
> LEFT JOIN Memofield ON 1=2
> WHERE 1=1
> AND MFB690.MFBPWIN = "N"
> AND MFBSetProtocol.DelFlag = 0 AND ( IFNULL(MFBSetProtocolPat.msActive, MFBSetProtocol.msActive) = 1 )
> AND MFB681.MFBPrR = ?pnProcess AND MFB698.MFBLBLNR = 5
> AND (
> (MFB699.CodeNV = 00002194 AND MFB699.SrtCode = 40)
> OR (MFB699.CodeNV = 00067903 AND MFB699.SrtCode = 45)
> OR (MFB699.CodeNV = 02351595 AND MFB699.SrtCode = 50))
> GROUP BY 1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
> ORDER BY Status, MFBPNr, MFBPNrV, MFBKNr
>
