dbMonitor and Query Plan

dbMonitor and Query Plan

Postby Wade » Sun 11 Jul 2010 02:04

dbMonitor 3.0.2 with dbxida, FB2.5RC2, Delphi2009

Is there any way to get it to report a query plan for each query? Here's why. It's all very well during development ananysing each query to see that the plan is nice, but reality strikes on customer sites when there is significantly more data in some tables than expected (so the plan changes), or index selectivity is not what was anticipated or Firebird simply decides to make a stupid query plan.

dbMonitor with the ability to output query plans would provide a huge boost in functionality.

Thoughts?
Wade
 
Posts: 31
Joined: Sun 03 Jan 2010 06:04

Postby Dimon » Mon 19 Jul 2010 13:24

Query plan is generated by server and it is server specific. Unfortunately сlient can get access to this plan in many cases and therefore dbMonitor can not show it.
Dimon
Devart Team
 
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Postby Wade » Tue 20 Jul 2010 05:57

Yes, of course the plan is generated by the server. What I was asking was that you simpy surface the functionality already in the source code in "function TGDSCommand.GetPlan: string;" by adding a parameter that causes logging to retrieve the server's pan for each query and to add this to the logged data.

I was not asking for dbxida to "invent" a plan on the client side or for it to try to interpret the plan in any way - the plan returned by Firebird is sufficiently readable.

The plan should be able to be retrieved for any select, update, delete, or execute procedure.
Wade
 
Posts: 31
Joined: Sun 03 Jan 2010 06:04

Postby Dimon » Wed 21 Jul 2010 12:13

dbMonitor works with many SQL servers and products that don't support getting of query plan, like SQL Server, MySQL, Oracle etc. Therefore we can't support such functionality.
Dimon
Devart Team
 
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Postby Wade » Thu 19 Aug 2010 19:19

I think this is a very easy thing to achieve. The biggest hurdle is that the protocol between dbxida and the monitor is simple a bunch of data squashed together a sequence known by both ends and contains no identifying tags for each data element.

If you were to fix the protocol between dbxida and the monitor to be structured (i.e. with tags identifying the data that is to follow - whether you do this by bloating the whole thing out to use xml or alternatively you insert binary identifiers before each data element much like the Firebird api uses doesn't matter), the result is that you could easily add new data elements such as the plan that could be sent by a client if appropriate and if they exist then they could be displayed by the monitor.

To say you don't want to enhance dbxida in this way and that you don't acknowledge how useful this would be is one thing, but certainly to say that only common functionality between all database platforms is possible is logical or reasonable.
Wade
 
Posts: 31
Joined: Sun 03 Jan 2010 06:04

Postby Dimon » Fri 20 Aug 2010 07:55

Thank you for your inquiry. We will investigate the possibility of adding this functionality in the future.
Dimon
Devart Team
 
Posts: 2885
Joined: Mon 05 Mar 2007 16:32


Return to dbExpress driver for InterBase & Firebird