GRID IS VERY SLOW THE 1ST TIME

[guillermo88]

I have a form with 2 grids
Grid1 contains information about invoices
Grid2 contains transactions related to invoices

This form allows to look information by a column agent and column invoice No
The table Invoice has an index on agent + invoice_no
Table invoices have 23,000 records
Table Transactions is empty

In the init event I set the Search criteria to
Agent = ‘-9999’
cFilter = “Agent = ‘-9999’ ”
cOrderBy = “Agent”
mySQL = “SQL statement is SELECT * FROM invoices WHERE “ + cFilter + “ ORDER BY “ + cOrderBy
Grid1 recordsourcetype = 4
Grid1 recordsource = mySQL

In Init Event of the DE
thisform.Grid1.recordSource= “”
thisform.Grid2.recordSource=””

The problem that I am having is that the first time the SQL statement is run takes a long time. After that response time is very fast.

I tried already to put a valid value in Search Criteria Ex. Agent = ‘XBD’ and still takes time load, I see “running query” window

Again this only happens the first time a load the form.
Once I have the form open and I look for another Agent it runs fast.
Even after releasing the form and I reopen it again there is no problem.
It seems is the very first time.

Any input will be appreciated.

Thanks.

 

[danceman]

check the use of indexes to optimize the select
check out SYS(3054)


maybe switch to views
check out how CURSORSETPROP may help
FetchAsNeeded Attitude is Everything

 

[guillermo88]

I forget to mention that this aplication is running on the network (Executable and data)

 

[guillermo88]

I don't why but seems having the executable on the network slow down loading the grid only the first time. I created another shorcut but this time the executable was in local drive and the data was and the network and it seem that fixed the problem

 

[danceman]

I once had a extremley complex SQL statement to make across the network.

program and data on one server.

the SQL ran slow untill I had indexes created to optimize the select. with this done I ran a hit that ran across 7 tables join with self join against 480,000 records. Never did it take longer then 20 seconds. Attitude is Everything

 

[guillermo88]

I thought I fixed the problem but I didn't.

The new shortcut that I create was reading the data from the local directory and not from the directory in the network.
I create I new directory Test in my C drive and Test\data for my tables in the drive F: and the same behavior when loading the form for the very first time.

 

[danceman]

are you reading anything I have posted about indexes??? Attitude is Everything

 

[Gandalf23]

yes, try indexing the table on what you seek in the where clause of your select statement

 

[guillermo88]

I have and index already. It is on Agent + invoice

 

[danceman]

you may have indexes but not the right ones or not enough

use the SYS(3054) to help you in otomizing your select statement.

Attitude is Everything