How to Clear down Query Execution Statistics in SQL Server 2005/2008
DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS
sql-execution-plan
Keep PostgreSQL from sometimes choosing a bad query plan
If the query planner makes bad decisions it’s mostly one of two things: 1. The statistics are inaccurate. Do you run ANALYZE enough? Also popular in its combined form VACUUM ANALYZE. If autovacuum is on (which is the default in modern-day Postgres), ANALYZE is run automatically. But consider: Are regular VACUUM ANALYZE still recommended under … Read more
Is there a way to force MySQL execution order?
Perhaps you need to use STRAIGHT_JOIN. http://dev.mysql.com/doc/refman/5.0/en/join.html STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
How to let SQL Server know not to use Cache in Queries?
DBCC FREEPROCCACHE Will remove all cached procedures execution plans. This would cause all subsequent procedure calls to be recompiled. Adding WITH RECOMPILE to a procedure definition would cause the procedure to be recompiled every time it was called. I do not believe that (in SQL 2005 or earlier) there is any way to clear the … Read more
Reset SQL Server execution plan
For clarity………. Executing sp_recompile will “mark” the given stored procedure for recompilation, which will occur the next time it is executed. Using the WITH RECOMPILE option will result in a new execution plan being generated each time the given stored procedure is executed. To clear the entire procedure cache execute DBCC FREEPROCCACHE
Measure time of query in Mongo
You can add .explain(“executionStats”) at the end of the query.
SQL Server: Table-valued Functions vs. Stored Procedures
An inline table valued function (TVF) is like a macro: it’s expanded into the outer query. It has no plan as such: the calling SQL has a plan. A multi-statement TVF has a plan (will find a reference). TVFs are useful where you want to vary the SELECT list for a parameterised input. Inline TVFs … Read more
What does “Recheck Cond” in Explain result mean?
This was explained by Tom Lane on the mailing list: what is “Recheck condition” and why is it needed? If the bitmap gets too large we convert it to “lossy” style, in which we only remember which pages contain matching tuples instead of remembering each tuple individually. When that happens, the table-visiting phase has to … Read more
How to improve performance on a clustered index seek
I’m generalizing here, but… A clustered index seek is, for the most part, the best-case scenario. The only ways I can think of to improve performance would be: Update the query to return fewer rows/columns, if possible; Defragment or rebuild the index; Partition the index across multiple disks/servers. If it’s only returning 138 rows, and … Read more
Easy way to run “explain” on query sets in django
Well, there seems to be nothing out there except a toolbar so I wrote my own mixin to give me an explain() method on my querysets: from django.db import connections from django.db.models.query import QuerySet class QuerySetExplainMixin: def explain(self): cursor = connections[self.db].cursor() cursor.execute(‘explain %s’ % str(self.query)) return cursor.fetchall() QuerySet.__bases__ += (QuerySetExplainMixin,) Hopefully this is useful to … Read more