Can I improve HTML code generation?

[czavala]

 Posted by:  Conrado ZAVALA 

Hello Forum,

I have an application where I manually populate columnless child table. The
most expensive query I run in this application returns a result set of 2 660
rows of data (all 2 660 rows are displayed in one shot, --I don't use next
and previous buttons). I'm concern because populating this amount of data
takes 1:09 minutes to get displayed. Through SQLConsole I was able to
confirm that Preparing, Executing and Populating my child table takes 17
seconds (I was able to identify this because as soon as I "commit" by cursor
I disconnect it from the database) however building the necessary HTML to
diplay in the browser takes an additional 52 seconds. Does anyone knows a
way to improve the time it takes to build this HTML? I will appreciate your
answer.

Regards,

Conrado

[Jeff Luther]

 Posted by:  Jeff Luther 

"Does anyone know a way to improve the time...?" - well, maybe, but (as usual
for me) I have to ask a couple questions:
- How is the HTML being composed now?

- Are you including qckwax.apl and if so which function(s) generate the HTML?

- It may not be an issue here, but there are 3 ways to populate a TW:
1 'normal'
2 'fillall'
3 'fillall_background'

'fillall' - if this is your method now, then look at the 3rd way above. This
way--at least on a client PC--will return control to the user faster.

And if all the HTML has to be built before the page can be displayed, then...
- Depending on what you are doing, This might help: sometimes I let SQLBase
do some of the 'work' with string concatenations. For example, this query:
select 'a system table >>> ' || name || ' >> ' || NAME || ' >> SYSCOLAUTH >> SYSCOLUMNS >> SYSCOMMANDS >> ' || sINTOvar || ' ;"

takes a long time to write for each of my DB tables. But, with this query
run against the ISLAND sample database:

select 'update statistics on table ' || creator || '.' || name || ';'
from sysadm.systables where name not like 'SYS%' and type = 'T';

I get:
'UPDATE STATISTICS ON TABLE ' || CREATOR || '.' || NAME || ';'
=========================================================================
update statistics on table SYSADM.COMPANY;
update statistics on table SYSADM.INVOICE_ITEM;
update statistics on table SYSADM.INVOICE;
...

and can then copy/paste just the "update... ;" lines into SQLTalk and run that.

[czavala]

 Posted by:  Conrado ZAVALA 

Hello Jeff,

First of all I want to thanks for your answer. I cannot use TBL_FillNormal,
TBL_FillAll or TBL_FillBackground on my child table populate because I'm
doing it manually. Bellow you will find and idea about what I'm doing after
the user clicks on pbFind.

pbFind:
On SAM_Click
Call SalSendMsg( tblResultSet, PM_Populate, 0, 0 )

tblResultSet:
On PM_Populate
Set sSqlPopulate = "SELECT PO_NUMBER, C_STYLE, COLOR_NO, SIZE,
COUNT( CASE_NO ), SUM( QTY ), SUM( QTY * BLANK_COST), SUM( QTY * SELL_PRICE)
INTO :sPO_Number, :sC_Style, :sColor_No, :sSize, :nTTL_Boxes, :nTTL_Pieces,
:nBlank_Cost, :nFOB FROM VIEW_INVENTORY WHERE C_NAME = 'ABC MANUFACTURING'
GROUP BY 1, 2, 3, 4 ORDER BY 1, 2, 3, 4"
Set SqlDatabase = "LIBDC"
Set SqlUser = "SYSADM"
Set SqlPassword = "SYSADM"

If SqlConnect( hSqlPopulate )
! CREATE child table columns:
Call SalTblCreateColumn( tblResultSet, 1, 1, 20, "ID" )
Call SalTblCreateColumn( tblResultSet, 2, 1, 20, "P.O. Number" )
Call SalTblCreateColumn( tblResultSet, 3, 1, 20, "Style" )
Call SalTblCreateColumn( tblResultSet, 4, 1, 20, "Color No." )
Call SalTblCreateColumn( tblResultSet, 5, 1, 20, "Size" )
Call SalTblCreateColumn( tblResultSet, 6, 1, 20, "TTL. Boxes" )
Call SalTblCreateColumn( tblResultSet, 7, 1, 20, "TTL. Pieces" )
Call SalTblCreateColumn( tblResultSet, 8, 1, 20, "Blank Cost" )
Call SalTblCreateColumn( tblResultSet, 9, 1, 20, "F.O.B." )

Call SqlPrepareAndExecute( hSqlPopulate, sSqlPopulate )
While SqlFetchNext( hSqlPopulate, nSqlPopulateFetch )
nNewRow = SalTblInsertRow( tblResultSet, TBL_MaxRow )

Call SalTblSetColumnText( tblResultSet, 1,
SalNumberToStrX( nNewRow, 0 ) )
Call SalTblSetColumnText( tblResultSet, 2, sPO_Number )
Call SalTblSetColumnText( tblResultSet, 3, sC_Style )
Call SalTblSetColumnText( tblResultSet, 4, sColor_No )
Call SalTblSetColumnText( tblResultSet, 5, sSize )
Call SalTblSetColumnText( tblResultSet, 6,
SalNumberToStrX( nTTL_Boxes, 0 ) )
Call SalTblSetColumnText( tblResultSet, 7,
SalNumberToStrX( nTTL_Pieces, 0 ) )
Call SalTblSetColumnText( tblResultSet, 8,
SalNumberToStrX( nBlank_Cost, 2 ) )
Call SalTblSetColumnText( tblResultSet, 9,
SalNumberToStrX( nFOB, 2 )
SqlCommit( hSqlPopulate )
SqlDisconnect( hSqlPopulate )

Basically this is what I'm doing. I have more code behind that doesn't let
me use SalTblPopulate( ) to populate the table. My problem is that with my
real query I get to the SqlDisconnect( ) in 17 seconds and after this
SQLWindows 52 additional seconds to show the output in my browser (my
understanding is that it is preparing the HTML for the browser).

[Jeff Luther]

 Posted by:  Jeff Luther 

I would try to cut out all those SalTblSetColumnText() calls. If you change your
logic a bit and are able to reference the dyn. columns directly in the query, this
might work:

! It's been a long time since I did this but I think ":#x" is a valid ref.

On PM_Populate
! the INTO clause has changed...
Set sSqlPopulate = "SELECT...
INTO :tblResultSet#1, :tblResultSet#2, ... ETC..

Now, later you:
connect...
create the columns...
Call SqlPrepareAndExecute( hSqlPopulate, sSqlPopulate )

! and here's the next change - add this line
! row has to be in the TW before you can fetch
nNewRow = SalTblInsertRow( tblResultSet, TBL_MaxRow )
...now start the loop...
While SqlFetchNext( hSqlPopulate, nSqlPopulateFetch )
nNewRow = SalTblInsertRow( tblResultSet, TBL_MaxRow )

! don't need anything else - it should just loop and pop. the TW columns

but, when the While ends you will have 1 more TW row than you need, so
you could:
Call SalTblDeleteRow ( tblResultSet, nNewRow, TBLAdjust )
and:
SqlCommit
SqlDisconnect

Try that and let us know if it is faster. Otherwise, I don't see any
'holes' in performance except possibly you don't have an index on the
C_NAME column. But you said you tested the query and that is not where
the bottleneck is.

Best Regards,
Jeff @ PC Design
info: www.JeffLuther.net/gupta/

[czavala]

 Posted by:  Conrado ZAVALA 

Hello Jeff,

I'm sorry but still the same. Producing an output of 2 783 rows took 01:23
minutes. Preparing, Executing and Populating the child table took 21
seconds. Visualizing the data took 01:02 minutes.

:(

Conrado

[Jeff Luther]

 Posted by:  Jeff Luther 

Next, turn off result set mode, if it is on. There was another user here last
week who saw something run fast in SQLTalk, but much slower in TD. The reason
was that RS mode is off in ST, but defaults to on in TD.

- Jeff

[czavala]

 Posted by:  Conrado ZAVALA 

Jeff,

I'm sorry. I changed the SqlResultSet to FALSE and still the same (it did
not improve).

Conrado

[Jeff Luther]

 Posted by:  Jeff Luther 

Set SqlResultSet = FALSE

done BEFORE any SqlConnect() calls???

Try SqlSetResultSet ( hSql, bSet ) as well and look at help for both
the var. and the function. I am surprised there was no improvement (unless
it had already been done somewhere else in your code.)

[czavala]

 Posted by:  Conrado ZAVALA 

Hello Jeff,

I really appreciate all you have done to help me. Thank You. Bellow you
will find how am I setting the value for SqlResultSet.

frmWebManager
Description
...
Functions
Function: ManagerAppInit
...
Actions
Set SqlIsolationLevel = "RL"
Set SqlResultSet = FALSE
...

No cursors are connected to the database before changing it value.

What gets my attention is that the cursor which executes tables population
is disconnected 53 seconds before showing the data in the browser.

Conrado

[Jeff Luther]

 Posted by:  Jeff Luther 

Well, sorry we went down the SQL path then. Then you have to get back to my
original questions:
- How is the HTML being composed now?
- Are you including qckwax.apl and if so which function(s) generate the HTML?

You didn't answer those before.

[czavala]

 Posted by:  Conrado ZAVALA 

Hello Jeff,

The HTML is being composed automatically by WAM (I have no control of it).
I have qckweb.apl as my include file. I really appreciate your concern.
Have an GREAT WEEK!.

Regards,

Conrado