Solved

How do I make this query faster and more MySQL friendly?

Posted on 2010-09-15
74
563 Views
Last Modified: 2012-05-10
I am curious on how do I make this query more MySQL friendly. This query displays the correct information but it takes a long time to display the results. This query will only work on the MySQL Workbench but not in my program because it times out.

What am I able to do to speed up this query?
SELECT    c.LastOffice, c.LNAME, c.FNAME, ms.SIN, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CPBACK, ms.CSCOL AS CLREC, ms.ADJ2, ms.AR FROM         

(SELECT     

SIN, YEAR, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC, SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM('CLIENT.REC2') AS CL, SUM('CLIENT.SHORT2') AS CS, SUM('SPOUSE.SHORT2') AS SS, SUM(ADJUSTMENT2) AS ADJ2, SUM('CLIENT.REC2') + SUM('CLIENT.SHORT2') + SUM('SPOUSE.SHORT2') AS CSCOL, SUM(COALESCE (CSCH1, 0))                                                + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR                        

FROM          

(SELECT     SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, SUM(CASE WHEN 'Adinfo' = 'P' THEN COALESCE (CKAMT, 0) ELSE 0 END) AS PBack2,   SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 FROM Disbursements AS Disbursements_1 WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) GROUP BY SIN, YEAR UNION ALL SELECT     SIN, YEAR, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 FROM Receipts 

WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

GROUP BY SIN, YEAR                                                

UNION ALL                                                

SELECT     SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC2, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT2, SUM(CASE WHEN 'Type' = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0) ELSE 0 END) AS CREVREC, SUM(CASE WHEN 'Type' = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.REC2', SUM(CASE WHEN 'Type' = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.SHORT2', SUM(CASE WHEN 'Type' = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 

FROM         Receipts AS Receipts_1

WHERE     (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                                

GROUP BY SIN, YEAR                                                

UNION ALL                                                

SELECT     strSIN, strYear, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', SUM(decAMT) AS ADJUSTMENT2                                                FROM         tblAdjustments AS tblAdjustments_1                                                

WHERE     (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

GROUP BY strSIN, strYear) AS HI                        

GROUP BY SIN, YEAR                        

HAVING      (SUM(COALESCE (CSCH1, 0)) + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) <> 0))                        AS ms 

INNER JOIN Clients AS c ON c.SIN = ms.SIN 

GROUP BY c.LNAME, c.FNAME, ms.SIN, c.LastOffice, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CINT, ms.CPBACK, ms.CL, ms.CS, ms.SS, ms.ADJ2, ms.CSCOL, ms.AR 

ORDER BY c.LNAME, c.FNAME, ms.SIN;

Open in new window

0
Comment
Question by:JohnnyBCJ
  • 37
  • 21
  • 16
74 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 33681916
Hi John,

The query looks reasonable.

Can you describe it?  How many rows are being processed?  How many returned?  Have you seen an explain plan?  Are the appropriate indexes in place?  Can you add indexes if necessary?


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33682015
The query returns any clients who's balance does not equal zero. There are 25,000+ clients at the moment.  The query returns about 2000 rows but it goes through probably close to 100,000 records (If you include all the tables it goes through to get this result).

I haven't seen an explain plan. I don't have any indexes in place but I can add indexes if needed.





0
 

Author Comment

by:JohnnyBCJ
ID: 33682244
Is this the explain plan you mentioned? It doesn't make a whole lot of sense to me.


1    PRIMARY    <derived2>    ALL                    19828    Using temporary; Using filesort

1    PRIMARY    c    ALL                    23830    Using where; Using join buffer

2    DERIVED    <derived3>    ALL                    61116    Using temporary; Using filesort

3    DERIVED    Disbursements_1    ALL                    22446    Using where; Using temporary; Using filesort

4    UNION    Receipts    ALL                    20724    Using where; Using temporary; Using filesort

5    UNION    Receipts_1    ALL                    20724    Using where; Using temporary; Using filesort

6    UNION    tblAdjustments_1    ALL                    1806    Using where; Using temporary; Using filesort
    UNION RESULT    <union3,4,5,6>    ALL                        

0
 
LVL 45

Expert Comment

by:Kdo
ID: 33682345

Each of the subqueries filters on the date of the record.  If you'll put an index on that column in each of the tables, the query will be able to read only the rows in the correct date range.  As it is now, the query is doing a full table scan on all of the tables.

CREATE INDEX idx001 on Receipts (DATE_REC);
CREATE INDEX idx002 on tblAdjustments (Date);


See if that doesn't help.

Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33682424
Yes, you are correct. Each of the sub queries filters on the date of the record. The only problem is the vast majority of the time, the date filter does nothing. It wasn't until a very rare problem happened that caused me to add in the by date filter.

I did what you suggested and got the result in 164 seconds where I previously got 167 seconds.

0
 

Author Comment

by:JohnnyBCJ
ID: 33682451
You usually look at this report using the current year end date to see what people owe right now. The reason why I had to put the date filter in was if you were to look at what a person owed in the past (last month, year, week, etc).

If I need to explain anything, please ask away.
0
 

Author Comment

by:JohnnyBCJ
ID: 33682800
Would this query work faster if it was a view?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33683731
Making the query a view won't change anything.  The DBMS will run the exact same SQL.

Run each of the 3 subqueries (separated by the UNION ALL statements) as see what the timing is for each of these 3.  I suspect that their timings will be reasonable and that significant time is being spent accessing the derived tables.


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33683786
Good idea. I'll do that now and I'll get back to you.
0
 

Author Comment

by:JohnnyBCJ
ID: 33683838
I did that and each of the sub queries take under 1 second but when I run the entire thing it takes over 3 minutes.
0
 

Author Comment

by:JohnnyBCJ
ID: 33683864
Here are my results:

23734 rows returned           0.015 sec / 0.266 sec
19796 rows returned           0.109 sec / 0.047 sec
  1719 rows returned           0.016 sec / 0.000 sec
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33684146
45,000 rows isn't a lot of data.  Reprocessing those rows shouldn't take very long.

So let's look at the inner join.  Is there a cartesian product being produced?  (Can there be more than 1 row with the same value of SIN?)


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33684184
Every table can have multiple rows except the clients table.
0
 

Author Comment

by:JohnnyBCJ
ID: 33684239
Now hold on. Please let me back track.

The final product of this query is pretty much this:

Last Office, LName, FName, SIN, Year, Sum(Invoices), Sum(Receipts for each type), Sum(Adjustments).

So each of the joins gives me the sum of either invoices, receipts or adjustments, meaning each one gives me back 1 record.


0
 

Author Comment

by:JohnnyBCJ
ID: 33684284
I wasn't able to use a join in this situation because a person may have an invoice but no receipt, or an invoice and a receipt but no adjustment. If a person never had an invoice, receipt and an adjustment, it wouldn't show. It's possible that a person has an adjustment but no invoice. I couldn't figure out how to do the proper join (if there is one in this situation).
Thats the reason why I went with union all. This shows me all the information I need regardless what the client has.






0
 
LVL 45

Expert Comment

by:Kdo
ID: 33684433
You could put it all on one line using OUTER JOIN instead of UNION ALL.

I've taken the liberty of reformatting the SQL just to be easier to read.  If you'll note, all of the aggregated values in the inner subqueries are modified by COALESCE so no NULL values are possible.

When you select the items from the next-outer query, the HAVING clause filters the aggregate data.  That query assumes non-NULL.  Remove the COALESCE function from the HAVING clause.

Also, run the second query below.  What is the count?


Kent

SELECT    

  c.LastOffice, c.LNAME, c.FNAME, ms.SIN, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CPBACK, ms.CSCOL AS CLREC, ms.ADJ2, ms.AR 

FROM         

(

  SELECT     

    SIN, YEAR, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC, SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM('CLIENT.REC2') AS CL, SUM('CLIENT.SHORT2') AS CS, SUM('SPOUSE.SHORT2') AS SS, SUM(ADJUSTMENT2) AS ADJ2, SUM('CLIENT.REC2') + SUM('CLIENT.SHORT2') + SUM('SPOUSE.SHORT2') AS CSCOL, SUM(COALESCE (CSCH1, 0))                                                + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR                        

  FROM          

  (

    SELECT     SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, SUM(CASE WHEN 'Adinfo' = 'P' THEN COALESCE (CKAMT, 0) ELSE 0 END) AS PBack2,   SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 FROM Disbursements AS Disbursements_1 WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) GROUP BY SIN, YEAR UNION ALL SELECT     SIN, YEAR, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 

    FROM Receipts 

    WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY SIN, YEAR                                                



    UNION ALL                                                



    SELECT     SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC2, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT2, SUM(CASE WHEN 'Type' = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0) ELSE 0 END) AS CREVREC, SUM(CASE WHEN 'Type' = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.REC2', SUM(CASE WHEN 'Type' = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.SHORT2', SUM(CASE WHEN 'Type' = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 

    FROM         Receipts AS Receipts_1

    WHERE     (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                                

    GROUP BY SIN, YEAR                                                



    UNION ALL                                                



    SELECT     strSIN, strYear, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', SUM(decAMT) AS ADJUSTMENT2                                                

    FROM         tblAdjustments AS tblAdjustments_1                                                

    WHERE     (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY strSIN, strYear

  ) AS HI

  GROUP BY SIN, YEAR                        

  HAVING      (SUM(COALESCE (CSCH1, 0)) + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) <> 0)

) AS ms 

INNER JOIN Clients AS c 

  ON c.SIN = ms.SIN 

GROUP BY c.LNAME, c.FNAME, ms.SIN, c.LastOffice, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CINT, ms.CPBACK, ms.CL, ms.CS, ms.SS, ms.ADJ2, ms.CSCOL, ms.AR 

ORDER BY c.LNAME, c.FNAME, ms.SIN;



--

--

--



SELECT count(*)

FROM         

(

  SELECT     

    SIN, YEAR, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC, SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM('CLIENT.REC2') AS CL, SUM('CLIENT.SHORT2') AS CS, SUM('SPOUSE.SHORT2') AS SS, SUM(ADJUSTMENT2) AS ADJ2, SUM('CLIENT.REC2') + SUM('CLIENT.SHORT2') + SUM('SPOUSE.SHORT2') AS CSCOL, SUM(COALESCE (CSCH1, 0))                                                + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR                        

  FROM          

  (

    SELECT     SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, SUM(CASE WHEN 'Adinfo' = 'P' THEN COALESCE (CKAMT, 0) ELSE 0 END) AS PBack2,   SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 FROM Disbursements AS Disbursements_1 WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) GROUP BY SIN, YEAR UNION ALL SELECT     SIN, YEAR, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 

    FROM Receipts 

    WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY SIN, YEAR                                                



    UNION ALL                                                



    SELECT     SIN, YEAR, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, SUM(COALESCE (AMT_REC, 0)) AS AMT_REC2, SUM(COALESCE (INT_AMT, 0)) AS INT_AMT2, SUM(CASE WHEN 'Type' = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0) ELSE 0 END) AS CREVREC, SUM(CASE WHEN 'Type' = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.REC2', SUM(CASE WHEN 'Type' = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.SHORT2', SUM(CASE WHEN 'Type' = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'SPOUSE.SHORT2', 0 AS ADJUSTMENT2 

    FROM         Receipts AS Receipts_1

    WHERE     (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                                

    GROUP BY SIN, YEAR                                                



    UNION ALL                                                



    SELECT     strSIN, strYear, 0 AS AMT_REC, 0 AS INT_AMT, 0 AS PBack2, 0 AS CSCH1, 0 AS AMT_REC2, 0 AS INT_AMT2, 0 AS CREVREC, 0 AS 'CLIENT.REC2', 0 AS 'CLIENT.SHORT2', 0 AS 'SPOUSE.SHORT2', SUM(decAMT) AS ADJUSTMENT2                                                

    FROM         tblAdjustments AS tblAdjustments_1                                                

    WHERE     (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY strSIN, strYear

  ) AS HI

  GROUP BY SIN, YEAR                        

  HAVING      (SUM(COALESCE (CSCH1, 0)) + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) <> 0)

) AS ms 

INNER JOIN Clients AS c 

  ON c.SIN = ms.SIN 

Open in new window

0
 

Author Comment

by:JohnnyBCJ
ID: 33684486
I decided to upgrade to the latest version of MySQL WorkBench and it's been a mistake. I haven't got workbench to work properly since. I'll give you your results as soon as I can.
0
 

Author Comment

by:JohnnyBCJ
ID: 33684549
I ran your queries.

19828 rows returned      164.291 sec / 0.188 sec
1 rows returned                 32.609 sec / 0.000 sec

19828 is the count.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33684643
Wow.....

The two queries are essentially the same.  The only difference is that the faster queries counts the objects to be grouped, and the slower one selects, groups, and sorts in 5 times the time it takes to count.  Unbelievable.  132 seconds to group and sort 19,828 rows.

Is the GROUP BY clause necessary?  It looks like a very strange set of conditions would have to be met for the GROUP BY to actually have an effect.


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33684728
I don't know any other way to get the same results without grouping by SIN and Year.

I'm trying to get the balance of all clients (SIN), and by year. I don't want a receipt for the year 2000 to be applied to an 2004 invoice for example. If a client has a balance, we need to know what year it applies to. Also if a client has a balance for more than one year, I need to have multiple rows for that client but with separate years.

Is there something in MySQL that I can configure that may change these results? I'm fairly new with MySQL but I have a background in MS SQL. It doesn't make any sense to me why it would take so long to do this query. I have several queries that are like this (they don't work because they time out). Yes, I'm able to extend the time out but the real issue is why does this take so long to begin with?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33684778
Just going to chime in quickly to second the motion on Kent's last question.  Looking at the query, it would appear that you don't really need all the inner GROUP BY on the UNION since you are using GROUP BY on the other query.

Or even look at changing some of how the query is structured to remove some of the need for UNION.

For example, the first two queries in the UNION go against the same table.  Without deep inspection of why, that would stick out to me as a potential change.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33684833

The outer query doesn't explicitly aggregate any column, but you still perform the GROUP BY.  Just curious...

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33684879
Another good catch, Kent.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33684905
I wouldn't think this would impact performance, but just a side note that SUM() will optimize out NULLs or another way of saying it is not adding anything to 5 or adding 0 is still only 5, so COALESCE on fields involved in a SUM does nothing unless you were defaulting to a non-zero value.  Now with AVG or COUNT this may make a difference , but not a SUM.
0
 

Author Comment

by:JohnnyBCJ
ID: 33684908
Thanks mwvisa1 for chiming in.

The 1st Receipts sums up all the receipts and interest (regardless of type) for the client per year.

By the looks of it, the 2nd one does the same thing the first one does and also splits them into their separate type. I don't think there is a need for the 1st Receipt query?


Kdo, running the query without the 2nd group by clause returns in 164.547 seconds.

0
 
LVL 45

Expert Comment

by:Kdo
ID: 33684928

I remember that you can do that with MySQL (GROUP BY with no specific aggregation), but I don't remember the exact effect.  Perhaps it's a DISTINCT operation?

Oh -- and there are really 4 subqueries that are combined with UNION ALL.  I did a lousy job of reformatting the query.  :(

It appears that 2 of the queries can be made into a single query, but I don't think that that is where the performance issue lies.


Kent

0
 

Author Comment

by:JohnnyBCJ
ID: 33684938
The default value for the fields that are COALESCE are null. So if I remove COALESCE from my query, it should still work the same?

If I run this same query under MS SQL, It displays in under 5 seconds, if that.
0
 

Author Comment

by:JohnnyBCJ
ID: 33684955
You did a good job Kdo. It beats my crappy code post in my question!!
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33684957
And for clarity, here's the query reformatted in the LONG form....


SELECT    

  c.LastOffice, 

  c.LNAME, 

  c.FNAME, 

  ms.SIN, 

  ms.YEAR, 

  ms.CSCH1, 

  ms.CREVREC, 

  ms.CPBACK, 

  ms.CSCOL AS CLREC, 

  ms.ADJ2, 

  ms.AR 

FROM         

(

  SELECT     

    SIN, YEAR, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC, SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM('CLIENT.REC2') AS CL, SUM('CLIENT.SHORT2') AS CS, SUM('SPOUSE.SHORT2') AS SS, SUM(ADJUSTMENT2) AS ADJ2, SUM('CLIENT.REC2') + SUM('CLIENT.SHORT2') + SUM('SPOUSE.SHORT2') AS CSCOL, SUM(COALESCE (CSCH1, 0))                                                + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR                        

  FROM          

  (

    SELECT 

      SIN, 

      YEAR, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      SUM(CASE WHEN 'Adinfo' = 'P' THEN COALESCE (CKAMT, 0) ELSE 0 END) AS PBack2,   

      SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS 'CLIENT.REC2', 

      0 AS 'CLIENT.SHORT2', 

      0 AS 'SPOUSE.SHORT2', 

      0 AS ADJUSTMENT2 

    FROM Disbursements AS Disbursements_1 

    WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) 

    GROUP BY SIN, YEAR 

    

    UNION ALL 

    

    SELECT     

      SIN, 

      YEAR, 

      SUM(COALESCE (AMT_REC, 0)) AS AMT_REC, 

      SUM(COALESCE (INT_AMT, 0)) AS INT_AMT, 

      0 AS PBack2, 

      0 AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS 'CLIENT.REC2', 

      0 AS 'CLIENT.SHORT2', 

      0 AS 'SPOUSE.SHORT2', 

      0 AS ADJUSTMENT2     

    FROM Receipts 

    WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY SIN, YEAR                                                



    UNION ALL                                                



    SELECT     

      SIN, 

      YEAR, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      0 AS PBack2, 

      0 AS CSCH1, 

      SUM(COALESCE (AMT_REC, 0)) AS AMT_REC2, 

      SUM(COALESCE (INT_AMT, 0)) AS INT_AMT2, 

      SUM(CASE WHEN 'Type' = 'RV' THEN COALESCE (AMT_REC, 0) - COALESCE (INT_AMT, 0) ELSE 0 END) AS CREVREC, SUM(CASE WHEN 'Type' = 'CL' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.REC2', 

      SUM(CASE WHEN 'Type' = 'CS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'CLIENT.SHORT2', 

      SUM(CASE WHEN 'Type' = 'SS' THEN COALESCE (AMT_REC, 0) ELSE 0 END) AS 'SPOUSE.SHORT2', 

      0 AS ADJUSTMENT2 

    FROM         Receipts AS Receipts_1

    WHERE     (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                                

    GROUP BY SIN, YEAR                                                



    UNION ALL                                                



    SELECT     

      strSIN, strYear, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      0 AS PBack2, 

      0 AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS 'CLIENT.REC2', 

      0 AS 'CLIENT.SHORT2', 

      0 AS 'SPOUSE.SHORT2', 

      SUM(decAMT) AS ADJUSTMENT2                                                

    FROM         tblAdjustments AS tblAdjustments_1                                                

    WHERE     (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY strSIN, strYear

  ) AS HI

  GROUP BY SIN, YEAR                        

  HAVING      (SUM(COALESCE (CSCH1, 0)) + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) <> 0)

) AS ms 

INNER JOIN Clients AS c 

  ON c.SIN = ms.SIN 

GROUP BY c.LNAME, c.FNAME, ms.SIN, c.LastOffice, ms.YEAR, ms.CSCH1, ms.CREVREC, ms.CINT, ms.CPBACK, ms.CL, ms.CS, ms.SS, ms.ADJ2, ms.CSCOL, ms.AR 

ORDER BY c.LNAME, c.FNAME, ms.SIN;

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 33684979
You also had 'Type' = 'CL' which is basically comparing two string literals that will never equal.  You probably wanted those to be column identifies which is ` in mysql.

The below code snippet would appear to me to do everything that the two different queries you have to the Receipts table is doing just in one statement.
SELECT SIN, YEAR

     , SUM(AMT_REC) AS AMT_REC

     , SUM(INT_AMT) AS INT_AMT

     , 0 AS PBack2, 0 AS CSCH1

     , SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2

     , SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC

     , SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`

     , SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`

     , SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`

     , 0 AS ADJUSTMENT2 

FROM Receipts 

WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

GROUP BY SIN, YEAR 

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685015
Guess I missed a whole bunch of posts while I was analyzing the code snippet for my last posting.  Sorry if it is no longer relevant.
0
 

Author Comment

by:JohnnyBCJ
ID: 33685040
No, your post is still relevant. I missed out on that completely. That is going to help me a lot with the other queries that are giving me problems!


0
 
LVL 45

Expert Comment

by:Kdo
ID: 33685051
Hi John,

I'm wondering if it's a tuning problem.  There's nothing in the query that's that complex and we're not dealing with very much data.

Perhaps MySQL has too little memory available to it and it's doing a lot of paging?

What's your host system and memory configuration?


Kent

0
 

Author Comment

by:JohnnyBCJ
ID: 33685093
Removing the 1st Receipts table query and replacing it with what mwvisa1 said gives me 2229 rows returned in 18.844 seconds.

Unfortunately the real/correct information only gives me 1554 rows.
0
 

Author Comment

by:JohnnyBCJ
ID: 33685125
With that said, it seems like it may only be a syntax error as I'm not getting any results for CPBack or CLRec. Give me a few minutes to figure out where I went wrong.
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33685127
That is very curious, since I looked and you had 0's in place holders of the one query and so simply merged the two.  Since you are grouping by in other query, the SUM of the UNION and the single query should be equivalent.  Just note that previously you had 'Type' = 'CL' which if I am reading correctly would have resulted in also SUM'ing 0 (or your ELSE condition) so it may be possibly that data was being excluded by the HAVING clause previously that should NOT have been.  When you say real/correct information, how are you validating which numbers are correct ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685145
Seems like we are getting somewhere if that dropped you down to 18 seconds, but I actually had same thought as Kent and was hunting down this article link for you.  Especially look at the section on MySQL configuration and setting for join buffer since that is used on one of the bigger joins here.

3 Ways to Speed Up MySQL by gr8gonzo
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 45

Expert Comment

by:Kdo
ID: 33685152
Hi John,

You may be able to combine the two sub-queries of Recipients, but the other items won't combine.

And that will affect the row count as all of the recipient data for a single entity will be on one line.


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33685164
I'm converting my old program that used a different database to MySQL. I know that the numbers in my old program are correct. I just got to make my new program's numbers match my old one.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685182
Got you!
0
 

Author Comment

by:JohnnyBCJ
ID: 33685197
Thanks Kdo. I did that and I am now down to 18 seconds but I'm getting different results from the correct information. I'm sure it's just a matter of a syntax error somewhere.

Right now I'm not getting any results for CPBack or the CLREC columns. I don't think that will be a big deal tho. I'll figure out where I fooled up.
0
 

Author Comment

by:JohnnyBCJ
ID: 33685213
I already found out where I fooled up. It was what Mvisa mentioned about the ' and the `.

I'm now down to 1729 rows when the correct information says I should be down to 1546.
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
ID: 33685222
Did you follow up on mwvisa's comment on the quoted values?  

There's a world of difference between `type` and 'type'.


Kent
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33685250

That would be a 'yes', huh?  :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685253
Here is what I got after spotting the you had more ' around columns in the code.  See if matches up to what you have.
SELECT    

  c.LastOffice, 

  c.LNAME, 

  c.FNAME, 

  ms.SIN, 

  ms.YEAR, 

  ms.CSCH1, 

  ms.CREVREC, 

  ms.CPBACK, 

  ms.CSCOL AS CLREC, 

  ms.ADJ2, 

  ms.AR 

FROM         

(

  SELECT     

    SIN, YEAR, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC

    , SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM(`CLIENT.REC2`) AS CL

    , SUM(`CLIENT.SHORT2`) AS CS, SUM(`SPOUSE.SHORT2`) AS SS, SUM(ADJUSTMENT2) AS ADJ2

    , SUM(`CLIENT.REC2`) + SUM(`CLIENT.SHORT2`) + SUM(`SPOUSE.SHORT2`) AS CSCOL, SUM(CSCH1)                                                + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR                        

  FROM          

  (

    SELECT 

      SIN, 

      YEAR, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,   

      SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS `CLIENT.REC2`, 

      0 AS `CLIENT.SHORT2`, 

      0 AS `SPOUSE.SHORT2`, 

      0 AS ADJUSTMENT2 

    FROM Disbursements AS Disbursements_1 

    WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) 

    GROUP BY SIN, YEAR 

    

    UNION ALL 

    

    SELECT SIN, YEAR

     , SUM(AMT_REC) AS AMT_REC

     , SUM(INT_AMT) AS INT_AMT

     , 0 AS PBack2, 0 AS CSCH1

     , SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2

     , SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC

     , SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`

     , SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`

     , SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`

     , 0 AS ADJUSTMENT2 

   FROM Receipts 

   WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

   GROUP BY SIN, YEAR                                                 



    UNION ALL                                                



    SELECT     

      strSIN, strYear, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      0 AS PBack2, 

      0 AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS `CLIENT.REC2`, 

      0 AS `CLIENT.SHORT2`, 

      0 AS `SPOUSE.SHORT2`, 

      SUM(decAMT) AS ADJUSTMENT2                                                

    FROM         tblAdjustments AS tblAdjustments_1                                                

    WHERE     (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

    GROUP BY strSIN, strYear

  ) AS HI

  GROUP BY SIN, YEAR                        

  HAVING ((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`) 

       - SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) <> 0)

) AS ms 

INNER JOIN Clients AS c 

  ON c.SIN = ms.SIN 

ORDER BY c.LNAME, c.FNAME, ms.SIN;

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
ID: 33685262
That actually occurs in several places in the SQL.  Check all of the sub-queries carefully.


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33685297
Yes indeed Kdo. Thank you! :-)

mwvisa1, I ran your query and it ran in 13.422 seconds and gave me 1542 rows! Not quite 1546 but close!!

This has came a long ways from where I was this morning. You guys made my day!

I can't say thank you enough!
0
 

Author Comment

by:JohnnyBCJ
ID: 33685328
I noticed Kdo. You won't believe how much this is going to help me with the other 100+ Reports I have to fix. Thanks again!!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685333
You are most welcome.  Hopefully, the missing 4 rows is somewhere within the fact that we consolidated some things and hopefully you find what tweaking needs to be done to resolve that final piece of the puzzle.

Best regards,

Kevin
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685405
By the way, I almost forgot, here is what I meant by my earlier thought on the inner GROUP BY -- not sure if my thinking is correct that performing this operation once is better than the 4 times.  Might be gaining efficiency since each of the UNION ALL statements is grouped into smaller result set before the UNION and GROUP BY , but worth the shot to see if it is more efficient to simply select the rows and then aggregate at once.  Sometimes I find this works better as a lot of the processing time often is the collation and ordering, etc. of the data which is why you saw that the COUNT() of your query was dramatically less time to process than the actual results since the results had to be sorted whereas the count only cares about existence.
SELECT    

  c.LastOffice, 

  c.LNAME, 

  c.FNAME, 

  ms.SIN, 

  ms.YEAR, 

  ms.CSCH1, 

  ms.CREVREC, 

  ms.CPBACK, 

  ms.CSCOL AS CLREC, 

  ms.ADJ2, 

  ms.AR 

FROM         

(

  SELECT     

    SIN, YEAR, SUM(CSCH1) AS CSCH1, SUM(CREVREC) + SUM(INT_AMT2) AS CREVREC

    , SUM(INT_AMT2) AS CINT, SUM(PBack2) AS CPBACK, SUM(`CLIENT.REC2`) AS CL

    , SUM(`CLIENT.SHORT2`) AS CS, SUM(`SPOUSE.SHORT2`) AS SS, SUM(ADJUSTMENT2) AS ADJ2

    , SUM(`CLIENT.REC2`) + SUM(`CLIENT.SHORT2`) + SUM(`SPOUSE.SHORT2`) AS CSCOL, SUM(CSCH1)                                                + SUM(COALESCE (PBack2, 0)) - SUM(COALESCE (CREVREC, 0)) - SUM(COALESCE ('CLIENT.REC2', 0)) - SUM(COALESCE ('CLIENT.SHORT2', 0)) - SUM(COALESCE ('SPOUSE.SHORT2', 0)) + SUM(COALESCE (ADJUSTMENT2, 0)) AS AR                        

  FROM          

  (

    SELECT 

      SIN, 

      YEAR, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      CASE `Adinfo` WHEN 'P' THEN CKAMT END AS PBack2,   

      C_SCH1_REFUND AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS `CLIENT.REC2`, 

      0 AS `CLIENT.SHORT2`, 

      0 AS `SPOUSE.SHORT2`, 

      0 AS ADJUSTMENT2 

    FROM Disbursements AS Disbursements_1 

    WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y')) 

    

    UNION ALL 

    

    SELECT SIN, YEAR

     , AMT_REC

     , INT_AMT

     , 0 AS PBack2, 0 AS CSCH1

     , AMT_REC AS AMT_REC2, INT_AMT AS INT_AMT2

     , CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END AS CREVREC

     , CASE `Type` WHEN 'CL' THEN AMT_REC END AS `CLIENT.REC2`

     , CASE `Type` WHEN 'CS' THEN AMT_REC END AS `CLIENT.SHORT2`

     , CASE `Type` WHEN 'SS' THEN AMT_REC END AS `SPOUSE.SHORT2`

     , 0 AS ADJUSTMENT2 

   FROM Receipts 

   WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                                                                             



    UNION ALL                                                



    SELECT     

      strSIN, strYear, 

      0 AS AMT_REC, 

      0 AS INT_AMT, 

      0 AS PBack2, 

      0 AS CSCH1, 

      0 AS AMT_REC2, 

      0 AS INT_AMT2, 

      0 AS CREVREC, 

      0 AS `CLIENT.REC2`, 

      0 AS `CLIENT.SHORT2`, 

      0 AS `SPOUSE.SHORT2`, 

      decAMT AS ADJUSTMENT2                                                

    FROM         tblAdjustments AS tblAdjustments_1                                                

    WHERE     (Date <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))                                               

  ) AS HI

  GROUP BY SIN, YEAR                        

  HAVING ((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`) 

       - SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) <> 0)

) AS ms 

INNER JOIN Clients AS c 

  ON c.SIN = ms.SIN 

ORDER BY c.LNAME, c.FNAME, ms.SIN;

Open in new window

0
 

Author Comment

by:JohnnyBCJ
ID: 33685447
mwvisa1, that gives me 1542 rows in 15.328 seconds vs 13.828 seconds.

Great suggestion tho. I never quite understood what you were trying to say but now I do.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33685662
I am glad and thanks for sharing the results.  Confirms that though less efficient looking from a code maintenance standpoint that the consolidation of records each level has to deal with is slightly more efficiency by having the multiple group by statements.  

Well let us know if we can do anything further to assist in finding the missing rows ...
(might be good to rerun the SQL server version and see if the 4 rows went away their two and it is just a timing thing ... another thought that may help in the migration process is to create a linked server to your MySQL system from MS SQL and then on the MS SQL side you can use EXCEPT keyword to get a quick result set of the rows that are different between the two queries -- http://msdn.microsoft.com/en-us/library/ms188055.aspx)
0
 

Author Comment

by:JohnnyBCJ
ID: 33692461
I can open up another question on this if you like as it's really not the same question as the original but I'm going to ask it because you two have been so unbelievably helpful to me!

I thought that with 'Union all', it would union all the records regardless if there is a record from table A that doesn't have a record in table B. That doesn't seem to be the case.

I found the missing records. They both have Invoices (CSCH1) but they don't have anything else associated with them. I can look them up in the client file and they're both identical.

If I run the following query, I actually find the missing records.

    SELECT  
      SIN,  
      YEAR,  
      0 AS AMT_REC,  
      0 AS INT_AMT,  
      SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,    
      SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1,  
      0 AS AMT_REC2,  
      0 AS INT_AMT2,  
      0 AS CREVREC,  
      0 AS `CLIENT.REC2`,  
      0 AS `CLIENT.SHORT2`,  
      0 AS `SPOUSE.SHORT2`,  
      0 AS ADJUSTMENT2  
    FROM Disbursements AS Disbursements_1  
    WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  
    GROUP BY SIN, YEAR  


but if I run this query, the two records disappear.

SELECT  
      SIN,  
      YEAR,  
      0 AS AMT_REC,  
      0 AS INT_AMT,  
      SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,    
      SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1,  
      0 AS AMT_REC2,  
      0 AS INT_AMT2,  
      0 AS CREVREC,  
      0 AS `CLIENT.REC2`,  
      0 AS `CLIENT.SHORT2`,  
      0 AS `SPOUSE.SHORT2`,  
      0 AS ADJUSTMENT2  
    FROM Disbursements AS Disbursements_1  
    WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  
    GROUP BY SIN, YEAR  
       UNION all  
    SELECT SIN, YEAR
     , SUM(AMT_REC) AS AMT_REC
     , SUM(INT_AMT) AS INT_AMT
     , 0 AS PBack2, 0 AS CSCH1
     , SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2
     , SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC
     , SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`
     , SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`
     , SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`
     , 0 AS ADJUSTMENT2
   FROM Receipts
   WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  
   GROUP BY SIN, YEAR  


Any suggestions?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33692943
Hi John,
This may be a "precedence" issue.  The final GROUP BY clause may not be operating as we think.

Try the query below.  It forces the final GROUP BY to affect only the low sub-query.


Kent

SELECT  

     SIN,  

     YEAR,  

     0 AS AMT_REC,  

     0 AS INT_AMT,  

     SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,    

     SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1,  

     0 AS AMT_REC2,  

     0 AS INT_AMT2,  

     0 AS CREVREC,  

     0 AS `CLIENT.REC2`,  

     0 AS `CLIENT.SHORT2`,  

     0 AS `SPOUSE.SHORT2`,  

     0 AS ADJUSTMENT2  

   FROM Disbursements AS Disbursements_1  

   WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  

   GROUP BY SIN, YEAR  



      UNION all  

SELECT * FROM

(

   SELECT SIN, YEAR

     , SUM(AMT_REC) AS AMT_REC

     , SUM(INT_AMT) AS INT_AMT

     , 0 AS PBack2, 0 AS CSCH1

     , SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2

     , SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC

     , SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`

     , SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`

     , SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`

     , 0 AS ADJUSTMENT2

   FROM Receipts

   WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  

  GROUP BY SIN, YEAR  

) t0

Open in new window

0
 

Author Comment

by:JohnnyBCJ
ID: 33693006
I tested your query. The rows are still missing.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33693202
The GROUP BY operator should take precedence over the UNION ALL, so it may be the GROUP BY on the whole UNION result:

 GROUP BY SIN, YEAR                        
  HAVING ((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`)
       - SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) <> 0)

If you run JUST the UNION portion , do the records still show up and if so are there any other rows for the same SIN, YEAR that could possibly make the formula in the HAVING clause equate to 0 ??
0
 

Author Comment

by:JohnnyBCJ
ID: 33693364
I can't figure out if the records show up without the group by as it's 3 clients that aren't showing up.

I'm going to run the query again with

((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`)
       - SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) = 0)

(instead of <> 0) to see if the 3 records show up there. If they do than we know where our problem is.
0
 

Author Comment

by:JohnnyBCJ
ID: 33693401
The records don't show up when I attempt the query with = 0 instead of <> 0.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33693802
Hi John,

If you take your last query and run the upper and lower queries separately, do the "missing" lines appear from the upper or lower query?


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33693938
If I run the top query it does. If I run the lower query it does not.

The top query is the only part that contains any information on the missing records. The clients have invoices and nothing else.

The lower query doesn't contain any information on the missing records.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33693992
There's no guaranteed sort order of the resulting rows.  Have you piped the output through `grep` or checked it with an editor?


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33694020
I checked the rows by the costs of the invoices (they are unique numbers).

No I haven't piped the output through `grep`. I have no idea what you're referring to unfortunately.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33694075
Ok.  Let's "adjust" the query a bit to guarantee the right order.

All of the rows for every value of SIN should be adjacent.
SELECT * FROM

(

SELECT  

     SIN,  

     YEAR,  

     0 AS AMT_REC,  

     0 AS INT_AMT,  

     SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,    

     SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1,  

     0 AS AMT_REC2,  

     0 AS INT_AMT2,  

     0 AS CREVREC,  

     0 AS `CLIENT.REC2`,  

     0 AS `CLIENT.SHORT2`,  

     0 AS `SPOUSE.SHORT2`,  

     0 AS ADJUSTMENT2  

   FROM Disbursements AS Disbursements_1  

   WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  

   GROUP BY SIN, YEAR  

      UNION all  

   SELECT SIN, YEAR

     , SUM(AMT_REC) AS AMT_REC

     , SUM(INT_AMT) AS INT_AMT

     , 0 AS PBack2, 0 AS CSCH1

     , SUM(AMT_REC) AS AMT_REC2, SUM(INT_AMT) AS INT_AMT2

     , SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) END) AS CREVREC

     , SUM(CASE `Type` WHEN 'CL' THEN AMT_REC END) AS `CLIENT.REC2`

     , SUM(CASE `Type` WHEN 'CS' THEN AMT_REC END) AS `CLIENT.SHORT2`

     , SUM(CASE `Type` WHEN 'SS' THEN AMT_REC END) AS `SPOUSE.SHORT2`

     , 0 AS ADJUSTMENT2

   FROM Receipts

   WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  

  GROUP BY SIN, YEAR  

) t0

order by 1, 2

Open in new window

0
 

Author Comment

by:JohnnyBCJ
ID: 33694117
I used your query and the 3 rows are still missing.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33694193
It is very strange for UNION ALL to removing rows.  Even with UNION, it would only remove duplicates and so if the rows only exist in the first query and not the second then a UNION of the two should still contain those rows.  Something very strange is occurring there...
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
ID: 33694219
Hi John,

Normally I like puzzles, but this is starting to think that it can whup me....  (it's wrong....)

This shouldn't have any effect on what we're seeing now, but this line:

  SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,

probably should be:

  SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT ELSE 0 END) AS PBack2,


What Adinfo <> 'P' the row should return NULL, and I think that you want 0.


But that line doesn't effect the returning of the correct rows when the UNION ALL is absent.


Kent
0
 

Author Comment

by:JohnnyBCJ
ID: 33694312
I agree. This certainly is a challenge that is pretty odd to figure out.

All 3 rows that are missing only have invoices but no receipts associated with them. That is the only common factor that I can see.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33694354
Kent,

That was probably my fault, I think I confused SUM() and COUNT(), but probably because the assumption is that there is at least one row with a valid value.  So adding 5 and 0 resulted the same as 5 and null where null was optimized out.  However, if all the rows are null then the result is indeed null, so I may have been wrong in recommendation of that -- again I figured the outer group by doing aggregate on the NULL and the 0 literals of the other UNION'd queries would result in 0.

This shouldn't cause the issue, but in your formula where you check :
((SUM(CSCH1) + SUM(PBack2) - SUM(CREVREC) - SUM(`CLIENT.REC2`)
       - SUM(`CLIENT.SHORT2`) - SUM(`SPOUSE.SHORT2`) + SUM(ADJUSTMENT2)) = 0)

It could make a difference as NULL + anything will be NULL which can't be directly compared and so will be excluded from the final result set.

Kevin
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33694393
I would recommend putting this on the outside instead though :

((COALESCE(SUM(CSCH1), 0) + COALESCE(SUM(PBack2), 0)
       - COALESCE(SUM(CREVREC), 0) - COALESCE(SUM(`CLIENT.REC2`) , 0)
       - COALESCE(SUM(`CLIENT.SHORT2`), 0) - COALESCE(SUM(`SPOUSE.SHORT2`), 0)
      + COALESCE(SUM(ADJUSTMENT2), 0)) = 0)
0
 

Author Comment

by:JohnnyBCJ
ID: 33694407
Kdo, you solved the 2nd mystery.

I did the changes as you suggested and the 3 records decided to show themselves.

THANK YOU VERY MUCH!!

I hope to keep in contact with you two if I have any more questions on this. It's pretty amazing how you guys can 'pick and poke' at the problem until you solve it. I may have spent a week trying to figure out this problem on my own.

THANK YOU AGAIN!!
0
 
LVL 45

Expert Comment

by:Kdo
ID: 33694423
Actually, when I reformat the lower query to match the upper,4 more rows have the same potential to return NULL in certain data columns.

I'm probably chasing our tails, but NULLS do have weird effects on things.



Kent

SELECT * FROM

(

SELECT  

     SIN,  

     YEAR,  

     0 AS AMT_REC,  

     0 AS INT_AMT,  

     SUM(CASE `Adinfo` WHEN 'P' THEN CKAMT END) AS PBack2,    

     SUM(COALESCE (C_SCH1_REFUND, 0)) AS CSCH1,  

     0 AS AMT_REC2,  

     0 AS INT_AMT2,  

     0 AS CREVREC,  

     0 AS `CLIENT.REC2`,  

     0 AS `CLIENT.SHORT2`,  

     0 AS `SPOUSE.SHORT2`,  

     0 AS ADJUSTMENT2  

   FROM Disbursements AS Disbursements_1  

   WHERE (CKDATE <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  

   GROUP BY SIN, YEAR  

      UNION all  

   SELECT 

     SIN, 

     YEAR, 

     SUM(AMT_REC) AS AMT_REC, 

     SUM(INT_AMT) AS INT_AMT, 

     0 AS PBack2, 

     0 AS CSCH1, 

     SUM(AMT_REC) AS AMT_REC2, 

     SUM(INT_AMT) AS INT_AMT2, 

     SUM(CASE `Type` WHEN 'RV' THEN COALESCE(AMT_REC, 0) - COALESCE(INT_AMT, 0) ELSE 0 END) AS CREVREC, 

     SUM(CASE `Type` WHEN 'CL' THEN AMT_REC ELSE 0 END) AS `CLIENT.REC2`, 

     SUM(CASE `Type` WHEN 'CS' THEN AMT_REC ELSE 0 END) AS `CLIENT.SHORT2`, 

     SUM(CASE `Type` WHEN 'SS' THEN AMT_REC ELSE 0 END) AS `SPOUSE.SHORT2`, 

     0 AS ADJUSTMENT2

   FROM Receipts

   WHERE (DATE_REC <= STR_TO_DATE('10/31/2010', '%m/%d/%Y'))  

  GROUP BY SIN, YEAR  

) t0

order by 1, 2

Open in new window

0
 
LVL 45

Expert Comment

by:Kdo
ID: 33694433
Yep.  NULLS do have weird behavior.....


0
 

Author Comment

by:JohnnyBCJ
ID: 33694455
I never ever thought in all of my programming career I would have so much troubles with nothing at all.

This isn't my first battle with nulls, nor is it my last.

Thank you again!!
0
 

Author Closing Comment

by:JohnnyBCJ
ID: 33694502
Thank you very much!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now