Performance Issues ?

[canc]

Hi All

I have the following code, after a SQL select statement to populate a repeating section dialog with data from another table and view .. the code and populate works ok , however if I have more than or 20 or 30 records I see performance issues on my alpha server and if a I close the dialog and try and re-open I just get the "working" message unless I close my browser ...., on much smaller queries all appears to work as expected

Does anybody know of a more streamlined way to populate a dialog ?


eval("j.Invoice_number_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("Invoice_number")
eval("j.TC_Invoice_Number_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("TC_Invoice_number")
eval("j.Lead_surname_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("Lead_pax_surname")
eval("j.Date_of_Travel_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("DOT")
eval("j.Invoice_Amount_A5INSTANCE" + alltrim(str(cnt))) = str(rs.Data("Invoice_amount"),10,2)

' invoice_balance = rs.data("Invoice_amount") - rs.data("total_paid")

eval("j.Amount_A5INSTANCE" + alltrim(str(cnt))) = str(rs.Data("Outstanding"),12,2)

eval("j.Clerk_added_A5INSTANCE" + alltrim(str(cnt))) = session.cu
eval("j.date_added_A5INSTANCE" + alltrim(str(cnt))) = date()
eval("j.Agency_A5INSTANCE" + alltrim(str(cnt))) = session.Agency_ID

end while

thanks in advance

john

[TheSmitchell]

Re: Performance Issues ?

Hi All

I have the following code, after a SQL select statement to populate a repeating section dialog with data from another table and view .. the code and populate works ok , however if I have more than or 20 or 30 records I see performance issues on my alpha server and if a I close the dialog and try and re-open I just get the "working" message unless I close my browser ...., on much smaller queries all appears to work as expected

Does anybody know of a more streamlined way to populate a dialog ?


eval("j.Invoice_number_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("Invoice_number")
eval("j.TC_Invoice_Number_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("TC_Invoice_number")
eval("j.Lead_surname_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("Lead_pax_surname")
eval("j.Date_of_Travel_A5INSTANCE" + alltrim(str(cnt))) = rs.Data("DOT")
eval("j.Invoice_Amount_A5INSTANCE" + alltrim(str(cnt))) = str(rs.Data("Invoice_amount"),10,2)

' invoice_balance = rs.data("Invoice_amount") - rs.data("total_paid")

eval("j.Amount_A5INSTANCE" + alltrim(str(cnt))) = str(rs.Data("Outstanding"),12,2)

eval("j.Clerk_added_A5INSTANCE" + alltrim(str(cnt))) = session.cu
eval("j.date_added_A5INSTANCE" + alltrim(str(cnt))) = date()
eval("j.Agency_A5INSTANCE" + alltrim(str(cnt))) = session.Agency_ID

end while

thanks in advance

john

Can you return javascript to the browser instead of running eval? You can use setValue for repeating sections (http://wiki.alphasoftware.com/%7Bdia...Value%20Method).

'Using Javascript to Set Control Values and the Row Count in a Repeating Section -- Recommended For Maximum Efficiency (Advanced users)
'---------------------------------------------------------------------------------------------------------------------------
'When setting the value of a large number of controls, you will improve performance if you generate the Javascript directly,
'rather than setting properties in the e.control object (as described above) and then having Alpha Five generate the Javascript from this object.

I roughly translated what you have above into a template that you could use to generate the javascript in the onDialogInitialize event (that's where the quote above was taken.) It's important to note that you need to define "{dialog.Object}" as something that's inserted, otherwise when you compile and output the template, {dialog.Object} is interpreted as xbasic (eg, a variable named dialog with a property Object) and fails. Read more about compileStringTemplate here: http://wiki.alphasoftware.com/~alpha...plate+Function

***Note: I have modified this because the old version didn't work because there were no rows allocated for the repeating section and the javascript would not do what you expect (it would just set the first row of the repeating section, or last row, if the {Row} was greater than the total rows in the repeating section***

' rs is defined however you defined it earlier

'Define a variable that will insert {dialog.Object} into our generated javascript
dim dlgObj = "{dialog.Object}"

'Define a template that will be used to generate JavaScript to set values
'{ and } are used to define xbasic in the template that will be executed/replaced by local variables
dim tmpl as c =<<%TXT%
{dlgObj}.setValue('INVOICE_NUMBER:{Row}',{rs.Data("Invoice_number")});
{dlgObj}.setValue('TC_INVOICE_NUMBER:{Row}',{rs.Data("TC_Invoice_number")});
{dlgObj}.setValue('LEAD_SURNAME:{Row}','{rs.Data("Lead_pax_surname")}');
{dlgObj}.setValue('DATE_OF_TRAVEL:{Row}',{rs.Data("DOT")});
{dlgObj}.setValue('INVOICE_AMOUNT:{Row}',{str(rs.Data("Invoice_amount"),10,2)});
{dlgObj}.setValue('AMOUNT:{Row}','{str(rs.Data("Outstanding"),12,2)}');
{dlgObj}.setValue('CLERK_ADDED:{Row}','{session.cu}');
{dlgObj}.setValue('DATE_ADDED:{Row}',{date()});
{dlgObj}.setValue('AGENCY:{Row}',{session.Agency_ID});
%TXT%

'Create the template for use in our loop!
dim setVal as p
setVal =  CompileStringTemplate(tmpl)


dim js as c =<<%js%
//This will initialize the repeating section with enough rows so that our 
//setValue function calls we add to js later will work:
var setFocusToFirst = true;
var nullOutExistingData = true;
var repeatingSectionId = 'CONTAINER_1'; //Replace CONTAINER_1 with the name of your repeating section

//Note: We will define totalRows later - when we have a record count - and prepend it to 'js'
{dialog.object}._setRepeatingSectionRowCount(repeatingSectionId, totalRows, setFocusToFirst,nullOutExistingData);
%js%

dim cnt as n = 1
while (your while criteria)
    ' Whatever else you're doing in your while loop here
    ' cnt is defined somewhere, I assume...

    dim Row as n = cnt 
    'Add javascript to set all those values for us for this Row
    *concat(js, setVal.output())

    ' The rest of whatever you do
    cnt = cnt + 1
end while

'Total rows would be cnt - 1 because on the last time through the while, cnt will be incremented for the NEXT iteration - which doesn't happen
dim totalRowsJs as c = "var totalRows = " + (cnt-1) + ";"

'Add the definition for totalRows to js so our repeating section can allocate the appropriate number of records.
'You want to add totalRowsJs before the rest of the javascript - because we define the number of 
'repeating section records BEFORE we add all the data to the repeating section:
js = totalRowsJs + js

e.javascript = js

This code may or may not execute copy-paste. You will have to debug any errors in the above... BUT, the point is, Alpha states that generating the javascript yourself will be faster. So, it's worth trying out.

[canc]

Re: Performance Issues ?

Hi thanks very much for your help, just a quick question thou..

When I try and run the code I get

ReferenceError: rs is not defined

And can't quite work out why ..., code is below any advice greatly appreciated


function populate_container as c (e as p)

dim cn as SQL::Connection

connection = session.db_connection
cn.open(connection)

if cn.open(connection) then

dim dlgObj as c = "{dialog.Object}"

dim js as c = ""

dim args as SQL::Arguments

dim cnt as n = 0

dim sqlCommand as c = <<%sql%
SELECT Invoice_number, tc_invoice_number,Surname,date_of_travel,Amount,
COALESCE(Amount,0)- coalesce(payment_allocated,0)- coalesce(paid_direct, 0) AS Outstanding
FROM Sales_Ledger
where account_number = :Account_number
%sql%

args.add("Account_number",e.datasubmitted.Account)

if cn.Execute(sqlCommand,args) then

dim invoice_balance as n

dim rs as SQL::ResultSet

rs = cn.ResultSet

dim tmpl as c =<<%longstring%
{dlgObj}.setValue('INVOICE_NUMBER:{ROW}',rs.Data("Invoice_number"));
%longstring%

'Create the template for use in our loop!
dim setVal as p
setVal = CompileStringTemplate(tmpl)


while rs.NextRow()
' cnt = cnt + 1

dim Row as n = cnt

Row = cnt
' 'Add javascript to set all those values for us for this Row

*concat(js, setVal.output())

' populate_container = "alert('Support error: " + js_escape(js) + "');"
cnt = cnt + 1
end while
else
populate_container = "alert('Support error: " + js_escape(cn.CallResult.Text) + "');"

end if
end if

populate_container = populate_container + JS


end function

[TheSmitchell]

Re: Performance Issues ?

Hi thanks very much for your help, just a quick question thou..

When I try and run the code I get

ReferenceError: rs is not defined

And can't quite work out why ..., code is below any advice greatly appreciated

function populate_container as c (e as p)
    dim cn as SQL::Connection
    connection = session.db_connection
    cn.open(connection)

    if cn.open(connection) then
        dim dlgObj as c = "{dialog.Object}"
        dim js as c = ""
        dim args as SQL::Arguments
        dim cnt as n = 0
    
        dim sqlCommand as c = <<%sql%
SELECT Invoice_number, tc_invoice_number,Surname,date_of_travel,Amount, 
COALESCE(Amount,0) - COALESCE(payment_allocated,0) - COALESCE(paid_direct, 0) AS Outstanding  
FROM Sales_Ledger 
WHERE account_number = :Account_number 
%sql%
                  
        args.add("Account_number",e.datasubmitted.Account)
 
        if cn.Execute(sqlCommand,args) then	
            dim invoice_balance as n		
            dim rs as SQL::ResultSet
            rs = cn.ResultSet
		
            dim tmpl as c =<<%longstring%
{dlgObj}.setValue('INVOICE_NUMBER:{ROW}',[B][COLOR="#FF0000"]{rs.Data("Invoice_number")}[/COLOR][/B]);
%longstring%

            'Create the template for use in our loop!
            dim setVal as p
            setVal =  CompileStringTemplate(tmpl)

            while rs.NextRow()
                'cnt = cnt + 1
                dim Row as n = cnt

                Row = cnt
                'Add javascript to set all those values for us for this Row
                *concat(js, setVal.output())

                'populate_container = "alert('Support error: " + js_escape(js) + "');"
                cnt = cnt + 1
            end while
        else
            populate_container = "alert('Support error: " + js_escape(cn.CallResult.Text) + "');"
        end if
    end if

    populate_container = populate_container + JS
end function

You're missing curly brackets in your tmpl definition around rs.Data("Invoice_Number").

---
Sarah

[canc]

Re: Performance Issues ?

Hi Sarah

thanks for replying, I get the following using curly brackets
13:00:49.315 POST http://travelcloudsystem.co.uk:84/__a5RunDialog.a5w [HTTP/1.1 200 OK 501ms]
13:00:49.770 SyntaxError: syntax error

thanks in advance

john

[TheSmitchell]

Re: Performance Issues ?

Hi Sarah

thanks for replying, I get the following using curly brackets
13:00:49.315 POST http://travelcloudsystem.co.uk:84/__a5RunDialog.a5w [HTTP/1.1 200 OK 501ms]
13:00:49.770 SyntaxError: syntax error

thanks in advance

john

That's a pretty useless error message. Put a debug(1) at the top of your code and step through it in working preview. That should give you a better idea of what's going on/wrong.