My application instantiates a view into a Firebird table, then uses a SQL Select statement to create a cursor that populates a grid.
The SQL select statement is located in the form refresh method.
In the form Init method, I invoke the view, and then call the form refresh method which populates the grid. The time to execute the SQL Select in the form refresh method the first time takes around 5 minutes! (MessageBox statements before and after the SQL Select allow me to measure the execution time.)
When the form apepars, I can click on a column heading to resort the grid on that column. My code for that sets a grid property to the name of the column on which to sort, and then calls the form refresh to repopulate the grid. This time exectuion of the SQL Select is instantaneous.
Here is the relevant code in my form refresh method:
WITH this.grdPatients
.RecordSource = SPACE(0)
.RecordSourceType = 1
lcCursorName = .cCursorName
lcSQLSortColDirection = .cSQLSortCol + ' ' + .cSQLSortDirection
SELECT USR_NAME, USR_FIRSTNAME, CAST(USR_DATEBIRTH AS DATE), USR_PatientID, USR_CITY, PatientNo ;
FROM vPatients INTO CURSOR (lcCursorName) ORDER BY &lcSQLSortColDirection
.RecordSource = (lcCursorName)
.refresh
ENDWITH
cSQlSortCol and cSQLSortDirection are set initially in the form designer, and updated by the colum header click method.
Invoking the view in the Init method takes around 1 minute. The view doesn't change for the life of the form.
Why is the SQL Select taking so long initially, and later is instantaneous?
Thanks in advance for your help.
Jim