Is there a way I can disable sql caching? I'm timing some queries and I need to know how they perform non-cached.
There are references in the documentation to a cache table so I could delete from cache where id > '-10 minutes' -- but in our databases I don't see a cache table.
There isn't any caching by default unless you implemented it. Vortex will cache SQL handles in the process, but that shouldn't affect it.
The cache with the biggest impact will be the systems disk cache, which will cause the query not to need to go to the physical disk the second time through, and that is under operating system control, not Texis.
Thanks John. We have queries that will run 30 seconds the first time and 2 seconds when they're repeated. In real world use, performance will be like the first time -- but I can't recreate it in testing because it's fast the second time. I guess you're saying there's nothing I can do to make my tests resemble first-time performance?
You would have to force the host OS to clear the disk cache. Maybe by a command of some kind but usually by pushing the queried data out of cache by doing enough other disk I/O to other files.
Usually the normal flow of queries on a system will keep things cached, so that is closer to the real world view of things, you should rarely see uncached performance unless there has been a long period with not queries.
If you are seeing a regular flow of queries, but still seeing close to uncached performance it is possible that you either need indices as Mark suggests, or possibly a little rearchitecting to reduce the amount of data needing to be read to resolve the query.
Thank you both. I'm fairly confident I can get better performance with better tuned queries and maybe better use of compound indices. What's difficult is identifying the bottlenecks, because even poorly optimized queries will run fast if the data is cached.