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)

asked 13 Jul, 16:44

gchq's gravatar image

gchq
296162032
accept rate: 33%

edited 16 Jul, 18:58

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.

(14 Jul, 02:23) Vlad

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

SELECT SUM(IsNull(SUM(Debit) - SUM(Credit), 0)) AS TotalAmount,
   IF TotalAmount > 0 THEN ... ELSE ... END IF AS MyDescription
FROM A_Sales_Ledger...

I can't give any actual hints as I don't understand the logic (and the programming model in use) here.

(14 Jul, 03:13) Volker Barth
3

This technique is mentioned in "How To Make SQL Anywhere Slow":

21. Do joins in the application.

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.

(14 Jul, 08:23) Breck Carter

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

(14 Jul, 11:07) gchq
Replies hidden

Breck - unless of course you mean DataSets?

(14 Jul, 11:15) gchq
1

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

(15 Jul, 08:45) Vlad

Syntax error on line 5: Illegal character "?" (63)

(15 Jul, 09:54) gchq

This is just a placeholder for the actual values... - you would usually use a parameter here - or add the actual value as string...

(15 Jul, 10:13) Volker Barth

Sorry - the actual issue is - Syntax error near 'each' on line 4

(15 Jul, 10:17) gchq

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 :)

(15 Jul, 11:03) Breck Carter

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?

(15 Jul, 11:06) Vlad

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

DECLARE @EndDate DATE = '2020-06-30';
DECLARE @Normal INTEGER = 4500;
SELECT Nominal_Code FROM A_Sales_Ledger WHERE Nominal_Code != 0 GROUP BY Nominal_Code ORDER BY Nominal_Code

This will return about 120 records - we need to loop through these and return a value for each one

SELECT Customer_ID FROM Customers

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

CASE WHEN Nominal_Code = @Normal THEN
SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE 
Document_Date <= @EndDate AND (Nominal_Code = @Normal OR Nominal_Code = 0) AND Customer_ID = customer AND (Paid = 'N' OR Paid_Date >= @EndDate)
ELSE
SELECT IsNull(SUM(Debit) - SUM(Credit), 0) FROM A_Sales_Ledger WHERE Document_Date <= @EndDate AND  Nominal_Code = Nominal_Code AND Customer_ID = customer AND (Paid = 'N' OR Paid_Date >= @EndDate)
END

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

SELECT NL_Description FROM NL_Codes WHERE NL_Code = Nominal_Code

This should return 3 columns - Nominal Code - Nominal Description - Total Amount for each code

(15 Jul, 12:35) gchq

In an actual DB this returned 17 rows in 84 ms

SELECT A_Sales_Ledger.Nominal_Code As 'NomCode',
NL_Codes.NL_Description as 'NomDesc'
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
(15 Jul, 12:48) gchq

I have updated the original question

(16 Jul, 19:00) gchq
More comments hidden
showing 5 of 14 show all flat view

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

permanent link

answered 14 Jul, 03:07

Frank's gravatar image

Frank
524111833
accept rate: 18%

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, 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, 12:46) Frank

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, 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, 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, 02:46) Frank

...particularly as CREATE VIEW (as most DDL statements) will force an implicit commit, which might or might not be desired here...

(15 Jul, 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, 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, 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, 14:37) Frank

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, 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, 15:43) gchq

I have updated the original question

(16 Jul, 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, 05:54) Justin Willey

Justin - between four and sixteen times faster :-)

(20 Jul, 08:24) gchq
1

That's good to hear. Certainly keeping things server side will almost always be the best answer.

(20 Jul, 09:58) Justin Willey
More comments hidden
showing 5 of 15 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×171

question asked: 13 Jul, 16:44

question was seen: 175 times

last updated: 20 Jul, 09:58