Jump to content
mvanrijnen

Analyze interbase slow queries

Recommended Posts

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?

 

Share this post


Link to post
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.

Share this post


Link to post

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

Share this post


Link to post
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.

Share this post


Link to post

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×