Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 12 total
Thread complex crosstab totals
Mon, Mar 28 2022 4:49 PMPermanent Link

R Casey

I have subscribed to ElevateDB for a few years now and I'm attempting to move all of my old BDE applications.
I've read the manual, read a lot of the sql forum, and spent a lot of time with Tim's Cross-Tab tech article
as I would like to come up with a better solution than I am using now.

Currently, I use a view similar to the below and then query the view also shown below.This works, but it's really slow (faster than the BDE though).  The ElevateDB SQL that generates the monthly transactions for this report is VERY Fast. The Totals are the problem.  I would appreciate any insight/suggestions you might have. Thanks Bob

/* Creates view KSU 86p */
SELECT M."Date",
    SUM(D.Amount) AS Col1,
CAST(0.0 AS Float) AS Col2,
CAST(0.0 AS Float) AS Col3,
FROM  Master_Checks M, Detail_Checks D
WHERE M.TransactionID = D.TransactionID
AND D.Detail_Fund = 'General'
AND D.Class = 'Print/Audit'
GROUP BY M."Date"
UNION ALL
SELECT M."Date",
CAST(0.0 AS Float) AS Col1,
    SUM(D.Amount) AS Col2,
CAST(0.0 AS Float) AS Col3,
FROM  Master_Checks M, Detail_Checks D
WHERE M.TransactionID = D.TransactionID
AND D.Detail_Fund = 'General'
AND D.Class = 'Telephone'
GROUP BY M."Date"
UNION ALL
SELECT  M."Date",
CAST(0.0 AS Float)AS Col1,
CAST(0.0 AS Float) AS Col2,
    SUM(D.Amount) AS Col3
FROM  Master_Checks M, Detail_Checks D
WHERE M.TransactionID = D.TransactionID
AND D.Detail_Fund = 'General'
AND D.Class = 'Rent/Heat/Utilities'
GROUP BY  M."Date"

/* Query that generates totals from view KSU86p */
SELECT CAST('PREVIOUS' AS Char( 20)) AS Previous_Total,
SUM(K.Col1) AS Col1PT, SUM(K.Col2) AS Col2PT, SUM(K.Col3) AS Col3PT
FROM KSU86P K
WHERE EXTRACT(MONTH FROM K."DATE") < :MonthNum
AND EXTRACT(YEAR FROM K."DATE") = :YearNum
AND K.CheckTotal IS NOT NULL
Tue, Mar 29 2022 4:14 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Without reading the sql in detail, and accepting your statement "The ElevateDB SQL that generates the monthly transactions for this report is VERY Fast." I would bet that the necessary indices to improve the performance of the second query are missing.



Try running the queries in EDBManager with Request execution plan ticked - it will tell you what's missing. Looking at the SQL for that statement I'm not sure if indices will help though. The single biggest problem is that you're performing a cartesian join - great in BDE but not ElevateDB. That needs replacing.

I did think this

SELECT CAST('PREVIOUS' AS Char( 20)) AS Previous_Total, SUM(K.Col1) AS Col1PT, SUM(K.Col2) AS Col2PT, SUM(K.Col3) AS Col3PT
FROM KSU86P
JOIN K ON EXTRACT(MONTH FROM K."DATE") < :MonthNum AND EXTRACT(YEAR FROM K."DATE") = :YearNum
WHERE
K.CheckTotal IS NOT NULL


might work, but reading it I realised there's nothing actually linking KSU86P and K so I'm not sure. If it does  work than its also worth having an index on K."DATE" and altering the test to something like

K."DATE" BETWEEN DATE 'yyyy-mm-01' AND 'yyyy-12-31'

You'd need to build the two date constants outside the sql.

My other thought was that even if you need a cartesian join you might be able to use a subselect to "reduce" the size of K prior to making the join. Not sure if it would work but something like


SELECT CAST('PREVIOUS' AS Char( 20)) AS Previous_Total,
SUM(K.Col1) AS Col1PT, SUM(K.Col2) AS Col2PT, SUM(K.Col3) AS Col3PT
FROM KSU86P (SELECT * FROM K WHERE EXTRACT(MONTH FROM K."DATE") < :MonthNum AND EXTRACT(YEAR FROM K."DATE") = :YearNum AND K.CheckTotal IS NOT NULL) X


If you want, and can let me have a copy of the database I'm happy to have a look to see just what can be done.

Roy Lambert
Tue, Mar 29 2022 8:59 AMPermanent Link

R Casey

The EDBquery that builds the body of the report is very fast, astoundingly so compared to the old BDE process it replaced (ONLY 0.109 seconds with Elevate Smile ):

SELECT DISTINCT M.Payee, M."date", M.Check_Number, M.TransactionID,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Print/Audit' Then AMOUNT ELSE Null END) as Col1,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Telephone' Then AMOUNT ELSE Null END) as Col2,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Rent/Heat/Utilities' Then AMOUNT ELSE Null END) as Col3,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Supplies/Postage' Then AMOUNT ELSE Null END) as Col4,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Equipment/Auto Exch' Then AMOUNT ELSE Null END) as Col5,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Miscellaneous' Then AMOUNT ELSE Null END) as Col6,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Travel' Then AMOUNT ELSE Null END) as Col7,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Salaries' Then AMOUNT ELSE Null END) as Col9,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Subsistence' Then AMOUNT ELSE Null END) as col8,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Employer Contribution' Then AMOUNT ELSE Null END) as Col10,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Grant' OR D.Detail_Fund = 'General' AND D.Class = 'Gift' OR D.Detail_Fund = 'General' AND D.Class = 'Gift'
  OR D.Detail_Fund = 'General' AND D.Class = 'Sales' OR D.Detail_Fund = 'General' AND D.Class = 'Sales' Then AMOUNT ELSE Null END) as Col11,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Capital Outlay' Then AMOUNT ELSE Null END) as Col12,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Transfer' Then AMOUNT ELSE Null END) as Col14
from Master_Checks M, Detail_Checks D
WHERE M.TransactionID = D.TransactionID AND M.Check_Number IS NOT NULL AND EXTRACT(MONTH FROM M."Date") = :MonthNum
AND EXTRACT(YEAR FROM M."Date")= :YearNum
Group BY M.Payee, M."Date", M.Check_Number, M.TransactionID
ORDER BY M."Date"


I ran EDBManager with Request execution plan ticked and added indexes on Date and CheckTotal.  The SQL that creates the view KSU86P  took 7 seconds.  

There is no link between KSU86P and K.  K is just the alias for KSU86P


>If you want, and can let me have a copy of the database I'm happy to have a look to see just what can be done.
I really appreciate your help.  I'm not sure how to get the database to you?  Backup the database and attach here? or Binaries?  Also, this database supports an accounting program, there are a lot of tables.
I can't thank you enough for looking at this... Thanks Bob
Tue, Mar 29 2022 10:28 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Bob


My apologies - I should have noticed the fact that K was being used as an alias - it makes a lot more sense now Smiley

To get something to me depends on how big it is, to big and its not sensible to use even the binaries (definitely don't use any of the other NGs) I do have people who have dropbox and they give me access, or OneDrive, basically anywhere it can be downloaded from as long as I don't have to start installing software to do it.

Roy Lambert
Tue, Mar 29 2022 11:10 AMPermanent Link

R Casey

Roy

To get something to me depends on how big it is, to big and its not sensible to use even the binaries (definitely don't use any of the other NGs) I do have people who have dropbox and they give me access, or OneDrive, basically anywhere it can be downloaded from as long as I don't have to start installing software to do it.

My first attempt at sharing one-drive.  I'm what you might call a one man band here Smile

https://ksuemailprod-my.sharepoint.com/:u:/g/personal/rcasey_ksu_edu/EfOe3L5DItNEqEjDtNEdwxgBWNkhQlLJ_ukoceOHHf8bRw?e=6H6fpK

Thanks
Bob
Tue, Mar 29 2022 12:30 PMPermanent Link

R Casey

By using the Execution Plan to identify the problem area and rewriting the SQL to build the view as shown below; the execution time dropped from 7.7 seconds to 1.3 Smile

SELECT DISTINCT  M."date",
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Print/Audit' Then AMOUNT ELSE Null END) as Col1,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Telephone' Then AMOUNT ELSE Null END) as Col2,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Rent/Heat/Utilities' Then AMOUNT ELSE Null END) as Col3,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Supplies/Postage' Then AMOUNT ELSE Null END) as Col4,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Equipment/Auto Exch' Then AMOUNT ELSE Null END) as Col5,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Miscellaneous' Then AMOUNT ELSE Null END) as Col6,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Travel' Then AMOUNT ELSE Null END) as Col7,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Salaries' Then AMOUNT ELSE Null END) as Col9,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Subsistence' Then AMOUNT ELSE Null END) as col8,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Employer Contribution' Then AMOUNT ELSE Null END) as Col10,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Grant' OR D.Detail_Fund = 'General' AND D.Class = 'Gift' OR D.Detail_Fund = 'General' AND D.Class = 'Gift'
 OR D.Detail_Fund = 'General' AND D.Class = 'Sales' OR D.Detail_Fund = 'General' AND D.Class = 'Sales' Then AMOUNT ELSE Null END) as Col11,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Capital Outlay' Then AMOUNT ELSE Null END) as Col12,
Sum(CASE WHEN D.Detail_Fund = 'General' AND D.Class = 'Transfer' Then AMOUNT ELSE Null END) as Col14
from Master_Checks M, Detail_Checks D
WHERE M.TransactionID = D.TransactionID AND M.Check_Number IS NOT NULL
Group BY M."Date"
ORDER BY M."Date"

While I am interested in your thoughts and other options; this is workable and is way better than the prior BDE process.  Thanks  Bob
Wed, Mar 30 2022 2:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Bob


The good news is that your sharing of OneDrive was successful. The bad news is that I need a database to restore a backup into.


Looking at the new code I have a couple of comments

"from Master_Checks M, Detail_Checks D"

I can see the , this time Smileyso I'd suggest replacing with a JOIN. You have the condition in the WHERE clause so

from Master_Checks M
JOIN Detail_Checks D ON M.TransactionID = D.TransactionID
WHERE
M.Check_Number IS NOT NULL


I also notice "D.Detail_Fund = 'General' AND" everywhere so it would become


SELECT DISTINCT  M."date",
Sum(CASE WHEN D.Class = 'Print/Audit' Then AMOUNT ELSE Null END) as Col1,
Sum(CASE WHEN D.Class = 'Telephone' Then AMOUNT ELSE Null END) as Col2,
Sum(CASE WHEN D.Class = 'Rent/Heat/Utilities' Then AMOUNT ELSE Null END) as Col3,
Sum(CASE WHEN D.Class = 'Supplies/Postage' Then AMOUNT ELSE Null END) as Col4,
Sum(CASE WHEN D.Class = 'Equipment/Auto Exch' Then AMOUNT ELSE Null END) as Col5,
Sum(CASE WHEN D.Class = 'Miscellaneous' Then AMOUNT ELSE Null END) as Col6,
Sum(CASE WHEN D.Class = 'Travel' Then AMOUNT ELSE Null END) as Col7,
Sum(CASE WHEN D.Class = 'Salaries' Then AMOUNT ELSE Null END) as Col9,
Sum(CASE WHEN D.Class = 'Subsistence' Then AMOUNT ELSE Null END) as col8,
Sum(CASE WHEN D.Class = 'Employer Contribution' Then AMOUNT ELSE Null END) as Col10,
Sum(CASE WHEN D.Class = 'Grant' OR D.Class = 'Gift' OR D.Class = 'Gift'   OR D.Class = 'Sales' D.Class = 'Sales' Then AMOUNT ELSE Null END) as Col11,
Sum(CASE WHEN D.Class = 'Capital Outlay' Then AMOUNT ELSE Null END) as Col12,
Sum(CASE WHEN D.Class = 'Transfer' Then AMOUNT ELSE Null END) as Col14
from Master_Checks M
JOIN Detail_Checks D ON M.TransactionID = D.TransactionID
WHERE
M.Check_Number IS NOT NULL
AND
D.Detail_Fund = 'General'
Group BY M."Date"
ORDER BY M."Date"

Finally I have no idea if its more efficient but in this instance I'd use IF rather than CASE, and I'd replace the ORs with an IN


SELECT DISTINCT  M."date",
Sum(IF( D.Class = 'Print/Audit' Then AMOUNT ELSE Null)) as Col1,
Sum(IF(D.Class = 'Telephone' Then AMOUNT ELSE Null)) as Col2,
Sum(IF(D.Class = 'Rent/Heat/Utilities' Then AMOUNT ELSE Null)) as Col3,
Sum(IF(D.Class = 'Supplies/Postage' Then AMOUNT ELSE Null)) as Col4,
Sum(IF(D.Class = 'Equipment/Auto Exch' Then AMOUNT ELSE Null)) as Col5,
Sum(IF(D.Class = 'Miscellaneous' Then AMOUNT ELSE Null )) as Col6,
Sum(IF(D.Class = 'Travel' Then AMOUNT ELSE Null )) as Col7,
Sum(IF(D.Class = 'Salaries' Then AMOUNT ELSE Null )) as Col9,
Sum(IF(D.Class = 'Subsistence' Then AMOUNT ELSE Null )) as col8,
Sum(IF(D.Class = 'Employer Contribution' Then AMOUNT ELSE Null )) as Col10,
Sum(IF(D.Class IN('Grant', 'Gift', 'Sales') Then AMOUNT ELSE Null )) as Col11,
Sum(IF(D.Class = 'Capital Outlay' Then AMOUNT ELSE Null )) as Col12,
Sum(IF(D.Class = 'Transfer' Then AMOUNT ELSE Null )) as Col14
from Master_Checks M
JOIN Detail_Checks D ON M.TransactionID = D.TransactionID
WHERE
M.Check_Number IS NOT NULL
AND
D.Detail_Fund = 'General'
Group BY M."Date"
ORDER BY M."Date"




Roy Lambert
Wed, Mar 30 2022 11:05 AMPermanent Link

R Casey

>The good news is that your sharing of OneDrive was successful. The bad news is that I need a database to restore a backup into.
Copying and sending databases might be another thread?  I assume this means you need the EDBDatabase.EDBCat and EDBDatabase.EDBLck files?

Your suggestions made remarkable improvements.  After implementing them and realizing that the 'General' criteria was no longer necessary and indexing the Detail transactionID column, the SumIF version executes in .047 seconds!  Case version .062. seconds.  Simply phenomenal!  ... considering I was at 7 seconds initially.  Also, I now have the tools to address  performance issues going forward.    I really can't thank you enough.  Bob
Thu, Mar 31 2022 2:03 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Bob


One of the advantages to "coming late to the party" is that, if you have a bit of sense, others have done the suffering for you <VBG>

I'm pleased that after my initial misinterpretation I've been able to help. Feel free to ask for more help if/when needed.


Roy Lambert
Fri, Apr 1 2022 10:45 AMPermanent Link

R Casey

Again, I can't thank you enough. I am an accountant/auditor by training and this database supports a simple single entry QB style accounting system I developed for small municipalities; much like your community foundation Smile  Experts like yourself make it look like I know what I am doing.
Thanks
Bob
Page 1 of 2Next Page »
Jump to Page:  1 2
Image