Analyze interbase slow queries

[mvanrijnen 123]

Something whats bothering me a few months now, 

Is there anybody who knows how to best analyze your application for slow (eg non-indexed or wrong indexed queries), with most databases you can set some switches and they will be logged.

With interbase i never found something like that, yes you can output some lowlevel data with firedac but it does not give a (fast) usable result. 

Anybody some tips, or some tooling for his?

 

[Anders Melander 1782]

http://docwiki.embarcadero.com/InterBase/2020/en/InterBase_Performance_Monitor_Window

[mvanrijnen 123]

2 hours ago, Anders Melander said:

http://docwiki.embarcadero.com/InterBase/2020/en/InterBase_Performance_Monitor_Window

Yes i know that one already, but thats realtime, not exactly what i'm looking for.,  thnx though.

 

[Guest]

6 hours ago, mvanrijnen said:

Anybody some tips, or some tooling for his?

All what i can suggest is DBMonitor from DevArt https://www.devart.com/dbmonitor/download.html , not sure if you suit you but if if you are looking for only a way to compare the duration of any/all queries been executed, then you can download UniDAC or IBDAC and use that monitor with the demos or build a small project to profile your queries.

 

on side note:

I been using that monitor for many years (+10), but never used it with InterBase, and just now noticed that the monitor been last updated 2012.

[Fritzew 51]

I would recommend 
https://www.upscene.com
Using it since years. Love it, but I see now I should really upgrade to the latest Version of the Workbench

[Anders Melander 1782]

1 hour ago, mvanrijnen said:

thats realtime, not exactly what i'm looking for

I haven't used IB in ages but as far as I can tell you can configure it to log query execution to disk. I have no idea what kind of output it produces.

 

What I used to do when I had problems with slow queries was to just look at their plan to see if it matched my expectations. I used Craig Stuntz' free Planalyzer for that but I don't know if it works with current version of IB. I guess all the current commercial tools have similar features. Of course examining the plan requires that you already know which queries to look at but, assuming you can reproduce the problem, you can use the performance monitor to identify those.

[Hans J. Ellingsgaard 21]

You can analyze your queries in IBExpert, but you need the paid version to be able to see all the details.

[Guest]

I am a Firebird user, not Interbase.

 

Anyways, what you call "realtime" monitoring is not bad at all. Try it! At least for Firebird there's no specific API, you just issue your queries and massage the results.

 

For another type of monitoring, as people say, look at the plans. I do not use FireDAC but most full-grown DACs should allow you to log the plans of each statement.

 

You do not need any special software, however some of the tools mentioned will save time because those realtime queries can be a bit convoluted. A good presentation of the plan is also a boon and not simple to code IMHO. For Example, IBExpert (expensive) you can bring up the SQL monitor and just copy the monitoring / meta queries to you own application or platform (check the licensing first!!).

 

I once had a conundrum in a database used by linguist researchers. No plan no nothing could pinpoint the problem, it was in triggers doing a very advanced join. Since we did not have the original documentation, working backwards into the trigger hierarchy was way too time-consuming, even though i had a management tool that could debug triggers. Realtime monitoring pointed in the right direction so much faster!

 

Disclaimer: Firebird and Interbase forked long before monitoring queries was implemented so it is different.