Subject RE: [firebird-support] Re: Speed issues
Author Louis van Alphen
With “issues” I mean rather I am in sort of the same situation. Firstly, I am not always 100% sure how to read and interpret the query plan. I basically try to eliminate NATURAL (full table scan) in any query. I also try to check the number of indexed and non- indexed reads. But when there is something wrong I am not always sure where to look and how to use the query plan to remedy the situation. Also, when checking the query execution, it seems that my indexes stats are not good and I am not sure how to remedy this.

I don’t want to hijack Zoran’s thread and will post my own in due course.

Thanks for the response Set


From: []
Sent: 09 February 2015 12:00 PM
Subject: RE: [firebird-support] Re: Speed issues

>I am having similar issues. Are there any resources to read up on this?

What do you mean by "similar issues", Louis? Zoran first showed us his SQL and plan, so that we more or less could see that his SQL, plan and indexes seemed good (that's arguably the most common reason for speed problems). Unfortunately I don't know of any generally good resources for fixing speed issues with Firebird (excepting this list and sometimes firebird-devel, possibly The Firebird book by Helen Borrie can have some tips as well, I don't know), and I doubt there exist any good resources focusing particularly on speed problems with big tables (whereas I cannot rule out there could exist some good resources for more general speed problems).

I never answered Zorans question since I've no experience with tables with more than 100 million rows and Seans answers seemed sensible. I noticed two things that they haven't discussed (but that I don't know how relevant would be) and that is that we haven't seen the DDL of his tables (wondering whether any of the tables involved have lots of fields or large CHAR and VARCHAR fields that could affect performance) and that we don't know how many rows would be returned from the same query if DISTINCT was removed.


[Non-text portions of this message have been removed]