Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 12 total |
complex crosstab totals |
Mon, Mar 28 2022 4:49 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 ):
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 AM | Permanent Link |
Roy Lambert NLH Associates 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 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 AM | Permanent 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 https://ksuemailprod-my.sharepoint.com/:u:/g/personal/rcasey_ksu_edu/EfOe3L5DItNEqEjDtNEdwxgBWNkhQlLJ_ukoceOHHf8bRw?e=6H6fpK Thanks Bob |
Tue, Mar 29 2022 12:30 PM | Permanent 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
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 AM | Permanent Link |
Roy Lambert NLH Associates 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 so 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 Experts like yourself make it look like I know what I am doing.
Thanks Bob |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, March 28, 2024 at 08:36 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |