now you ask , concerning the best practice.
How to carry out a reliable SQL query test?
That's now you ask , about optimisation of DB structure and SQL query itself not the machine and DB performance, buffers, caches.
If you have an elaborate query with many different joins etc, eventually you must know how you can optimize it and also you arrived at
EXPLAIN command (mysql::explain, postresql::explain) to review the execution plan.
After tuning the DB structure you execute the query to determine any performance changes but here you are around the pan of multiple degree of optimisation/loading/caching. How to prevent this? I want the pure time for that query execution and make certain it's not affected.
Knowing different practise for various servers please specify clearly: mysql, postgresql, mssql etc.
For Microsoft SQL Server you should use
DBCC FREEPROCCACHE (to decrease put together query plans) and
DBCC DROPCLEANBUFFERS (to get rid of the information cache) to actually are beginning from the completely uncached condition. You'll be able to profile both uncached and cached performance, and see your speed and agility precisely in the two cases.
Nevertheless, many of the time you will get spun sentences at different occasions for the way complex your totally and what else is going on around the server. It's often smart to check performance multiple occasions in various operating situations to be certain you realize exactly what the full performance profile from the totally.
I am sure a number of these general concepts affect other database platforms too.
Within the PostgreSQL world you have to flush the database cache along with the OS cache as PostgreSQL harnesses the OS caching system.
Check this out link for many discussions.
Why do you want pure execution time? It is dependent on a lot of factors and almost meaningless on live server. I would suggest to gather some statistic from live server and evaluate queries execution time using pgfouine tool (it's for postgresql) making choices according to it. You will notice precisely what must you tune and just how effective was your changes on the report.