Tag Info

New answers tagged

2

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong). Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages: The optimizer has ...


0

The query is joining 2 tables (receipts, sales) that both have a many-to-one relationship with product. This creates a kind of cartesian (cross) product and will give wrong results in the SUM() calculations. To avoid that, you need to do the summations for the two tables in two different subqueries to avoid errors. Something like this will work: SELECT ...


0

Depending on the meaning of where not(pe.ThisThing = 1 and se.OtherThing = 0) and the id format you have chosen to use I would try different indexes and something like this: /* http://dba.stackexchange.com/questions/71415/optimize-select-on-subquery-with-coalesce */ /* assuming: where not(pe.ThisThing = 1 and se.OtherThing = 0); or the Id format of ...


0

My intuition would be that this should not be a problem as by the time COALESCE(pe.StaffName, se.StaffName) AS StaffName does anything all the rows from the two sources should have already been pulled in and matched up so the function call is a simple in-memory compare-to-null-and-pick. Obviously this isn't the case so perhaps something in one of the sources ...


0

It looks to me like you don't need the derived table. Is there any reason you couldn't write it like this: SELECT Sum(x) * 0.1, Sum(y), a FROM tx INNER JOIN ty ON tx.a = ty.a WHERE x = 1 GROUP BY a This probably won't solve all of your performance issues, but if you set statistics IO on and look at the logical ...



Top 50 recent answers are included