Explain query plans for SQLite

[spikey]

I've been thinking about creating this for a while and got around to it. It's a drop in override for DatabaseQuery which explains query plans to the debug window before executing them when the debugger is active. Just include at the top of your code. This one's for SQLite but it should be straightforward to modify for other databases. Thanks to freak for the orginal tip about overriding built in functions.

An article which explains the explanations can be found at https://www.sqlite.org/eqp.html

Code: Select all

CompilerIf #PB_Compiler_Debugger
  
  Procedure.I _DatabaseQuery(aSource.S, aLine.I, aProcedure.S, aDatabase.I, aRequest.S)
    
    Protected.I lintResult
    Protected.S lstrExplain
    
    lstrExplain = "EXPLAIN QUERY PLAN " + aRequest
    Debug aSource + " - " + StrU(aLine) + " - " + aProcedure 
    Debug lstrExplain 
    
    lintResult = DatabaseQuery(aDatabase, lstrExplain)
    If lintResult 
      While NextDatabaseRow(aDatabase)
        Debug StrU(GetDatabaseLong(aDatabase, 0)) + ", " + StrU(GetDatabaseLong(aDatabase, 1)) + ", " +
              StrU(GetDatabaseLong(aDatabase, 2)) + ", " + GetDatabaseString(aDatabase, 3) + "."
      Wend
    EndIf
    Debug #Empty$ 
    
    lintResult = DatabaseQuery(aDatabase, aRequest)
    
    ProcedureReturn lintResult
    
  EndProcedure
  
  Macro DatabaseQuery(aDatabase, aRequest)
    _DatabaseQuery(#PB_Compiler_File, #PB_Compiler_Line, #PB_Compiler_Procedure, aDatabase, aRequest)
  EndMacro
  
CompilerEndIf

[hujambo]

Thanks Spikey, very useful

[Fangbeast]

Spikey, thanks for the example. It's much the same as Infratec tried to hammer into my thick skull (and he's not the only one LOL!!!)