|
We have some applications that return data, add it to a DataTable and then iterate through that DataTable to return the rows that we want. In the example below the data is being entered into a PDF report. I'm sure there must be a more efficient way of dealing with this rather than repeated calls back to the DB, but as it produced the results... Now the time it's taking to generate a report is getting longer and longer... Using vService As New Service1Client
strSQL = "SELECT Nominal_Code FROM A_Sales_Ledger WHERE Nominal_Code != 0 GROUP BY Nominal_Code ORDER BY Nominal_Code"
Using DS As DataSet = vService.ReturnDataSet(strSQL, Current_HOA_ID)
For Each SubRow As DataRow In DS.Tables(0).Rows
Dim vAmount As Decimal = 0
Dim vTotalAmount As Decimal = 0
Dim vNominal As String = SubRow("Nominal_Code")
strSQL = "SELECT Customer_ID FROM Customers"
Using DS2 As DataSet = vService.ReturnDataSet(strSQL, Current_HOA_ID)
For Each CustRow As DataRow In DS2.Tables(0).Rows
If vNominal = vNormal Then
strSQL = "SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= '" & Format(ReportEndDate, "yyyy-MM-dd") & "' AND (Nominal_Code = '" & vNominal & "' OR Nominal_Code = 0) "
strSQL += "AND Customer_ID = " & CustRow("Customer_ID")
' strSQL += " AND (Paid = 'N' OR Paid_Date >= '" & Format(ReportEndDate, "yyyy-MM-dd") & "')"
Else
strSQL = "SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= '" & Format(ReportEndDate, "yyyy-MM-dd") & "' AND Nominal_Code = '" & vNominal & "' "
strSQL += "AND Customer_ID = " & CustRow("Customer_ID")
' strSQL += " AND (Paid = 'N' OR Paid_Date >= '" & Format(ReportEndDate, "yyyy-MM-dd") & "')"
End If
vAmount = vService.ReturnScalarDouble(strSQL, Current_HOA_ID)
If vAmount > 0 Then
vTotalAmount += vAmount
End If
Next
End Using
If vTotalAmount > 0 Then
Dim NomDescription As String = ""
If vNominal = "0" Then
NomDescription = "Others"
Else
strSQL = "SELECT NL_Description FROM NL_Codes WHERE NL_Code = '" & vNominal & "'"
NomDescription = ReturnText(vService.ReturnScalarString(strSQL, Current_HOA_ID))
End If
vAssetsRunning += vTotalAmount
vAssetsTotal += vTotalAmount
vTable.AddCell(AddNewTextCell(False, "1100 (Split)", 1, ReportFontSize, False))
vTable.AddCell(AddNewTextCell(False, vNominal & " - AR " & NomDescription, 1, ReportFontSize, False))
vTable.AddCell(AddNewCurrencyCell(vTotalAmount, False, False, ReportFontSize, False))
vTable.AddCell(AddNewTextCell(False, "", 1, ReportFontSize, False, False))
End If
Next
End Using
End Using
What would be a better method? Thanks ============================== UPDATE ======================================= The following now returns the Nominal Code, Nominal Description and Total Outstanding in one statement SELECT Nominal_Code as 'NomCode',NL_Codes.NL_Description as 'NomDesc', (SELECT IsNull(SUM(SUBQUERY.Totals), 0) as 'SQ' FROM (SELECT Customer_ID as 'vID', CASE WHEN A_Sales_Ledger.Nominal_Code = 4105 then (SELECT IsNull(SUM(Debit) - SUM(Credit), 0) AS 'Totals' FROM A_Sales_Ledger WHERE Document_Date <= '2020-06-30' AND (Nominal_Code = NomCode OR Nominal_Code = 0) AND Customer_ID = vID) ELSE (SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= '2020-06-30' AND Nominal_Code = NomCode AND Customer_ID = vID) END AS 'Totals' FROM Customers WHERE Totals > 0 GROUP BY Customer_ID) AS SUBQUERY) FROM A_Sales_Ledger JOIN NL_Codes ON NL_Codes.NL_Code = A_Sales_Ledger.Nominal_Code WHERE Nominal_Code != 0 GROUP BY Nominal_Code, NL_Description ORDER BY Nominal_Code BUT 'SQ' (the third column returns quite a few zero amounts. I tried using a derived table by adding SELECT * FROM ( At the top AND ) as TB at the bottom so that I could filter out the zero amounts but it throws the toys out with 'Derived Table has no name for Column 3' Brandy time (the drink not the stripper) |
|
You could create a View in the database with the result set as needed by the PDF report and loop through the view with the ReportEndDate in the where clause. You could also create a stored procedure returning the result set needed by the PDF report and pass the ReportEndDate as a parameter and loop through the returned result Frank - trying, if possible, to just change the front-end SQL. Adding views and stored procedures would mean updating every DB - although we could probably limit it to the problem children.
(14 Jul '20, 11:11)
gchq
Replies hidden
If you're able to create the view, then you would also be able to use the same statement in the application using the same technique as is used for the current solution. It would at least bring down the number of select statements you're using and probalby the performance would improve
(14 Jul '20, 12:46)
Frank Vestjens
Can you pass variables to a view (like a date)? Is there a way to check if that view already exists? (Check to see if it exists, if not create it from the front-end)
(14 Jul '20, 14:37)
gchq
Thinking about it I guess we could use CREATE OR REPLACE VIEW view_name every time the application is run, including the variables, then run SELECT * FROM [View_Name]?
(14 Jul '20, 14:43)
gchq
Replies hidden
1
If you're going to create or replace the view in code. Why then create a view. You could also use the statement used to create the view as the source for the data set.
(15 Jul '20, 02:46)
Frank Vestjens
...particularly as CREATE VIEW (as most DDL statements) will force an implicit commit, which might or might not be desired here...
(15 Jul '20, 03:25)
Volker Barth
Can you show me an example of how I can return value(s) and then use those values to return others all in one SQL request?
(15 Jul '20, 08:38)
gchq
Replies hidden
Here's a simple sample from the SQL Anywhere sample database, see the docs on GROUP BY, somewhat simplified and also enhanced with an IF expression to "describe" the number of orders: SELECT year(OrderDate) AS Year, Quarter(OrderDate) AS Quarter, count(*) AS Orders, IF Orders > 100 THEN 'Wonderful quarter!' ELSE 'Normal business' END IF AS OrderNumberDescription FROM GROUPO.SalesOrders GROUP BY (Year, Quarter) ORDER BY Year, Quarter; It's apparently non-sense but I hope you get the point: You can use calculated values (here like "Orders" and do further calculations on them in the SELECT list and/or in the WHERE, GROUP BY, HAVING and ORDER BY clauses... You can also use CASE instead of IF if there are more branches to distinguish.
(15 Jul '20, 10:23)
Volker Barth
From what I can see from your example it should look something like this: select
"Format(ReportEndDate,"yyyy-mm-dd")" as FilterDate
,A_Sales_Ledger.Nominal_Code
,NL_Codes.NL_Description
,Customers.Customer_ID
,(select IsNull(sum(Debit-Credit),0)
from A_Sales_Ledger as C
where C.Customer_ID = Customers.Customer_ID
and (C.Nominal_Code = A_Sales_Ledger.Nominal_Code or (C.Nominal_Code = "vNormal" and C.Nominal_Code = 0))
and C.Document_Date <= FilterDate
and (C.Paid = 'N' or C.Paid_Date >= FilterDate)
from
A_Sales_Ledger
join NL_Codes on NL_Codes.NL_Code = A_Sales_Ledger.Nominal_Code
,Customers
where
A_Sales_Ledger.Nominal_Code != 0
group by
A_Sales_Ledger.Nominal_Code
order by
A_Sales_Ledger.Nominal_Code
You should only replace the fields in "" with the correct value from the source.
(15 Jul '20, 14:37)
Frank Vestjens
I can actually work this another way. The balance sheet shows some Nominal (GL) totals as assets and prepayments as liabilities If I run SELECT Customer_ID, IsNull(SUM(Debit) - SUM(Credit), 0) AS 'Totals' FROM A_Sales_Ledger WHERE Document_Date <= '2020-06-30' AND (Nominal_Code = 4105 OR Nominal_Code = 0) GROUP BY Customer_ID That returns two columns - Customer_ID and 'Totals' some will be positive amounts, some (prepayments) will be negative amounts. From the above I now need to find a way to return a total of just the positive amounts. I could do that within the application, but I'm sure it would be more efficient to run at the DB level
(16 Jul '20, 14:19)
gchq
Figured it out :-) SELECT SUM(SUBQUERY.Totals) FROM (SELECT Customer_ID as 'vID', (SELECT IsNull(SUM(Debit) - SUM(Credit), 0) AS 'Totals' FROM A_Sales_Ledger WHERE Document_Date <= '2020-06-30' AND (Nominal_Code = 4105 OR Nominal_Code = 0) AND Customer_ID = vID) as 'Totals' FROM Customers WHERE Totals > 0 GROUP BY Customer_ID) AS SUBQUERY
(16 Jul '20, 15:43)
gchq
I have updated the original question
(16 Jul '20, 18:59)
gchq
Are you finding that a lot faster that the procedural approach? (Hopefully the answer is yes!) If not, it might be worth looking at the query plan.
(20 Jul '20, 05:54)
Justin Willey
Justin - between four and sixteen times faster :-)
(20 Jul '20, 08:24)
gchq
1
That's good to hear. Certainly keeping things server side will almost always be the best answer.
(20 Jul '20, 09:58)
Justin Willey
More comments hidden
|
If it works fast, don’t touch it. I don’t like doing a DB job in code (you were working with data in memory of your application), but if it works well, leave it as it is…
But, if you send us a schema, some sample data and what you want to get, people might try to help you. It seems that you want to count some “totals”, but if I know better what is the task, I can try to help you with a SQL statement.
First, I would try to get rid of the two different queries used within the "If vNominal = vNormal" statements - I guess you could find a way to "abstract" the condition into one single query.
And then you would not need a cursor/loop but could certainly sum the amount for all according rows in one go (because that's what SQL is basically for:)).
And in case your "description" is dependent on the total amount (and other conditions), you mighht build that within the SQL statement itself, such as
I can't give any actual hints as I don't understand the logic (and the programming model in use) here.
This technique is mentioned in "How To Make SQL Anywhere Slow":
Try moving all the logic into a stored procedure that returns the final result set the application needs. This will reduce the number of separate data transmissions from server to client, each of which has overhead that increases latency (response time).
Try using set-oriented queries rather than record-oriented (fetch) loops. SQL Anywhere (and all other relational database products) work better on sets than on row-by-row fetch loops. This applies to the code inside stored procedures as well as client-server interactions.
Vlad - that is the issue it's getting very slow. Even processing on one of our servers and just returning the PDF sometimes is so slow it times out, albeit faster than running it locally.
Volker - that could certainly help :-)
Breck - Yup aware that this method is slow (hence the post). Can you explain more about sets? Old dog new tricks 'n' all that
Breck - unless of course you mean DataSets?
Ok, this is not a 100% answer, but these are the selects from your VB code.
SELECT Nominal_Code FROM A_Sales_Ledger WHERE Nominal_Code != 0 GROUP BY Nominal_Code ORDER BY Nominal_Code SELECT Customer_ID FROM Customers for each customer: SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= ? AND (Nominal_Code = ? OR Nominal_Code = 0) AND Customer_ID = ? AND (Paid = 'N' OR Paid_Date >= ?) or SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= ? AND Nominal_Code = ? AND Customer_ID = ? AND (Paid = 'N' OR Paid_Date >= ?)I think the first issue is to avoid GROUP BY and replace it with SELECT DISTINCT, if you want distinct values of Nominal_Code. But if you look at further selects (for each customer), you will notice that you probably want to SELECT ... FROM A_Sales_Ledger GROUP BY Customer_ID and apply some WHERE statements. The result of the grouping is that you want to SUM(Debit) and SUM(Credit) and subtract one from another. If you are afraid of Null, you can apply additional filter to WHERE.
After you get a data as a table, I think you wanted to sum totals, and it will be pretty easy to do...
As I said, without sample data and description I do not want to start guessing what you want to achieve, but I hope you will get a general idea how to use SELECT SUM(...) - SUM(...) ... FROM ... GROUP BY Customer_ID
Syntax error on line 5: Illegal character "?" (63)
This is just a placeholder for the actual values... - you would usually use a parameter here - or add the actual value as string...
Sorry - the actual issue is - Syntax error near 'each' on line 4
Sets, as in sets of rows or result sets, are fundamental to the relational model of data. For example "update t set t.c = expression where ..." tells the server how to update multiple rows in a single operation as opposed to the multi-step record-oriented fetch loop which is exactly like the old "read - edit - write - loop" of bygone days. Set-oriented insert, update and delete statements can involve multiple joins to implement complex operations as single statements that the server is free to optimize if possible. You can build set-oriented SQL in your application and send it to the server, or embed it inside a stored procedure, either way you can take advantage of the server's query optimizer in a way that is not possible if you force it to return ... every... single ... row ... to the application or procedure with a fetch loop. Smart folks can quickly switch to set-oriented thinking, others (like me) take years to "get it". Using sets to manipulate data is like using wheels for travelling... a fetch loop is like walking, they'll get you there eventually :)
nope, this is a text description of what your code is doing... I see you have difficulties with SQL, may I ask you to give us sample schema & data?
The application queries are initially posted to a Web Service that I wrote years ago, so the queries are returned as their data-type (scalar, dataset....)
Here is what I am trying to achieve
This will return about 120 records - we need to loop through these and return a value for each one
This will return on average about 500 records. We need the totals in Sales Ledger for each customer split into Nominal Codes, so we now have an inner loop
There should now be a handful of rows with each Nominal_Code and an amount. Here we need to remove all zero balances, that should reduce it to about 5 or six rows
Next we need to add the description for each Nominal_Code
This should return 3 columns - Nominal Code - Nominal Description - Total Amount for each code
In an actual DB this returned 17 rows in 84 ms
I have updated the original question