Slow Query Initialisation

Greetings All

After following all the excellent advice provided on how to speed up the of the HF/CS I still have the problem of the initial run-through of records after rebooting the system.
One particular file has ~195k records and trying to display/search through after reboot takes upto 8 minutes, but after that initial time it takes less than a second...
I'm using both Windows2003R2 and Windows2008, I have adjusted the registry settings, turned off the indexing service and it is still slow after reboot... any help on this would be greatly appreciated...

Regards C!!!

Happy Holiday's all...

Message forwarded from pcsoft.us.windev

Seeks to use composite indexes and look very good SQL query that generates, I am working on an application that manages a table with 2,040,000 records
and perform a query about her selection of a date: time and customer, besides calculating average values and get the answer in less than a second, and if the consultation takes place when you start your computer, takes about 30 seconds to return the result.

The code I use is as follows:

	HExecuteQuery(QRY_Mediciones_Paciente_fecha,hQueryDefault,EDT_fechaInicial+EDT_HoraInicio,EDT_fechaFinal+ EDT_HoraFinal,nIdPaciente )
		// copiamos el resutado en un fichero externo, no incluido en el analisis
		// este fichero se almacena en el ordenador local del usuario, ya que se debe de poder borrar cada vez que entramos o salimos de la opción
		// por lo que los cambios solo afectan a la terminal en uso
		HToFile(QRY_Mediciones_Paciente_fecha,tr) 
		HDeclareExternal(tr,"tempWD")
		FileToMemoryTable("Table1",tempWD)

Saludos y Feliz Navidad

Message forwarded from pcsoft.us.windev

Thanks for that Salvador, your method helps with something else I was toying around with but unfortunately it still doesn't solve the issue I'm having with the slow initial running of files after reboot... when I use your method it works great from the 2nd time onwards but the first time HQueryExecute runs after the system is rebooted takes about 8 minutes (and for only 195k entries to go through).

Would appreciate any other thoughts/suggestions you may have regarding this issue...

Regards C!!!

Message forwarded from pcsoft.us.windev

Hi Chris...

there are 2 separate problems here:

1. The difference in time between first and second query is due to the hard drive and windows cache, and there is nothing you can do about it (except automatically run the query a first time when your app starts :-)

2. Having SUCH a difference in the execution time means IMO that your query is not optimized (ie using CONTAINS condition by example, or working on a file where the appropriate keys haven't been created), but helping you more on this particular point means that we need to see the query...

By the way, if the query was created independtly, there should be an option in the right clic menu to create optmization keys (if any are possible)

Best regards

Fabrice Harari
WinDev, WebDev, WinDev Mobile consulting

Sorry, have not followed the previous thread...
What about using HOptimizeQuery() ?

If nothing else I would try to run the initial query in a secondary thread; see Farbrice's msg;
hth and a happy new year, bjoern