disable sql caching?

Post Reply
tboyer
Posts: 68
Joined: Mon Aug 28, 2006 4:43 pm

disable sql caching?

Post by tboyer »

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.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

disable sql caching?

Post by John »

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.
John Turnbull
Thunderstone Software
tboyer
Posts: 68
Joined: Mon Aug 28, 2006 4:43 pm

disable sql caching?

Post by tboyer »

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?
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

disable sql caching?

Post by mark »

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.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

disable sql caching?

Post by mark »

Do you have proper indices for the query so disk activity is reduced?
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

disable sql caching?

Post by John »

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.
John Turnbull
Thunderstone Software
tboyer
Posts: 68
Joined: Mon Aug 28, 2006 4:43 pm

disable sql caching?

Post by tboyer »

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.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

disable sql caching?

Post by mark »

If you provide examples we may be able to help...
Post Reply