Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
4
Erster Beitrag:
vor 4 Jahren, 5 Monaten
Letzter Beitrag:
vor 4 Jahren, 5 Monaten
Beteiligte Autoren:
Arnaud Benhamdine, Ned!, David Egan

SQL Question

Startbeitrag von David Egan am 08.08.2013 22:59

Hi
One for the SQL gurus! I'm using HFCS but the question stands for all SQL databases I guess.

Is a query using UNION/UNION ALL more or less efficient than a query giving the same results by using CASE statements. I originally wrote the query using UNION but then started thinking (rather dangerous sometimes!) & came up with the 2nd option, which gives me exactly the same result. My dataset is currently quite small so there is no appreciable difference but the files could end up with several million records. Appropriate indexes etc are in place.

Thanks

David

Original

SELECT RegisterId,userid,'SALES' AS TranType,COUNT(*) AS Qty,SUM(salevalue) AS amount, 1 AS sequence
FROM salhead
WHERE BranchId = '%1'
AND shiftnum BETWEEN '%2' AND '%3'
AND trantype = 'SALE'
AND status 'V'
AND salevalue > 0
GROUP BY RegisterId,userid
UNION
SELECT RegisterId,userid,'VOIDS' AS TranType,COUNT(*) AS Qty,0 AS amount,
2 AS sequence
FROM salhead
WHERE BranchId = '%1'
AND shiftnum BETWEEN '%2' AND '%3'
AND trantype = 'SALE'
AND status = 'V'
GROUP BY RegisterId,userid
UNION
SELECT RegisterID,userid,'RETURNS' AS TranType,COUNT(*) AS Qty,SUM(salevalue) AS amount, 3 AS sequence
FROM salhead
WHERE BranchId = '%1'
AND shiftnum BETWEEN '%2' AND '%3'
AND trantype = 'SALE'
AND status 'V'
AND salevalue < 0
GROUP BY RegisterId,userid


The alternative:

SELECT RegisterId,userid,
CASE
WHEN Status = '' AND SaleValue > 0 THEN 'SALES'
WHEN Status = '' AND SaleValue < 0 THEN 'RETURNS'
WHEN Status = 'V' THEN 'NOSALES'
WHEN status = 'H' THEN 'HELD'
ELSE 'ACTIVE'
END AS TranDesc,
COUNT(*) AS Qty,SUM(salevalue) AS amount,
CASE
WHEN Status = '' AND SaleValue > 0 THEN '1'
WHEN Status = '' AND SaleValue < 0 THEN '3'
WHEN Status = 'V' THEN '2'
WHEN status = 'H' THEN '90'
ELSE '95'
END AS sequence
FROM salhead
WHERE BranchId = '%1'
AND shiftnum BETWEEN '%2' AND '%3'
AND trantype = 'SALE'
AND ABS(salevalue) >
CASE WHEN Status = 'V' THEN (salevalue - 1) ELSE 0 END
GROUP BY RegisterId, userid,Trandesc,sequence

Antworten:

Hi,

UNION queries are expected to be faster then CASE queries, because they can lean on indexes.
So don't hesitate.

von Arnaud Benhamdine - am 09.08.2013 08:59
Hi,

On the other hand... The UNION Query has to go through the table 3 times and then delete any duplicates from the resultset, whereas the CASE query only goes through the table once (albeit checking the values of the some fields on its way).

I'm not saying Arnaud is wrong, but I think the best way to find out is to fill your file with several million records and see which finishes first.

Thanks,
Ned!

von Ned! - am 09.08.2013 12:48
Quote
Ned!
Hi,

On the other hand... The UNION Query has to go through the table 3 times and then delete any duplicates from the resultset, whereas the CASE query only goes through the table once (albeit checking the values of the some fields on its way).

I'm not saying Arnaud is wrong, but I think the best way to find out is to fill your file with several million records and see which finishes first.

Thanks,
Ned!


Yes, the UNION query has to read the table 3 times, but if the relevant fields are indexed, it will be significantly faster.

But you're right, the best way is to perform some ramp up tests.

von Arnaud Benhamdine - am 09.08.2013 16:27
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.