1

There's a SQL query joining a temporary table with a view (joining two tables):

select main.*
  from tmp_table_srt sub -- temporary table
  inner join vw_s_ad_conjunct main -- joining tables M_S_AD_CONJUNCTION and M_S_AD
    on sub.I_SRTREF = 94646 and
       sub.O_ID = main.ID
  where ASCJTREF = 1678744 and
        SOURCEADSREF = 1193467 and
        isnodummy(ID) = 1

It has a query plan, that looks good to me:

PLAN JOIN (SUB INDEX (UNQ_TMP_TABLE_SRT), MAIN ADS INDEX (PK_M_S_AD), MAIN ADSCJT INDEX (FK_M_S_AD_CONJUNCTION_SUBADS))

In my database IDE, which is IBExpert, this query is executed fast enough (less than a second). But in the client application this happens: When the query gets executed, the server temporary directory runs completely full. There are about 23 GB free space before. As soon there's no free space left, the application crashes.

At first, I thought the query causes that. But then I examined, that it runs fast (without overflowing the temp directory) when executed via my database IDE and uses a query plan with good indices. Furthermore I recognized that this happens not when the query is opened but when a API call to isc_dsql_sql_info() is made by FIBPlus database component after the query has been opened (in order to get aliases - I presume).

The functions request parameter is filled with this:

InfoRequest[0]:= AnsiChar(isc_info_sql_select);         // 4
InfoRequest[1]:= AnsiChar(isc_info_sql_describe_vars);  // 7
InfoRequest[2]:= AnsiChar(isc_info_sql_sqlda_seq);      // 9
InfoRequest[3]:= AnsiChar(frb_info_sql_relation_alias); // 25
InfoRequest[4]:= AnsiChar(isc_info_sql_describe_end);   // 8

Something about this API call causes Firebird to need a huge amount of temporary space. Unfortunately I found nearly nothing about this function (besides this Interbase API guide, which tells me nothing about the request values).

Maybe there are some Firebird or Interbase experts here, who can help me to find out what causes this problem. I use Firebird (classic server) 2.5.5.26952 and fbclient.dll 2.5.5.26952

René Hoffmann
  • 2,766
  • 2
  • 20
  • 43
  • Assuming this is the real problem, it sounds like a bug to me as this should not do anything other than reporting information available from preparing the statement, please report it to http://tracker.firebirdsql.org/browse/CORE – Mark Rotteveel Mar 30 '16 at 11:52
  • I think you should try using reducing the problem. Can you reproduce with `isql` or just with `FIBPlus`? – EMBarbosa Mar 30 '16 at 12:11
  • @MarkRotteveel - a bug in FB - or in FIB+ (which seems discontinued) ? I think it is wither about `Fetch All Records` or about `Bi-directional cursor`. I suspect he implicitly or explicitly (via db-aware controls) calls Query.RecordCount - which makes fetching all the record to the very end... – Arioch 'The Mar 31 '16 at 20:23
  • 1
    @Rene - I think you should make a minimal database and program (both sources and exe), zip them and send to firebird developers, it is not very hard. And chances are - while you would remove unneeded parts of your program at some moment you would remove the part that triggered that weird behavior. And thus you would have a hint what causes it and how to find it. – Arioch 'The Mar 31 '16 at 20:25
  • `In my database IDE` which is it ? FenixSQL ? IBExpert? FlameRobin? Something paid for? That matters because different IDEs are written over different FB access libs – Arioch 'The Mar 31 '16 at 20:30
  • @Arioch'The As indicated in the question, the problem seems to be triggered by a call to `isc_dsql_sql_info()`, if that is the case then that might be a bug in Firebird (although I have never seen it and I regularly use the wire protocol equivalent of isc_dsql_sql_info). – Mark Rotteveel Apr 01 '16 at 07:12
  • @MarkRotteveel "triggered" means that was a last drop in the sequence of unknown numbers of previously acted factors.... This specific call is regularly done by millions of FIB+ users including, I suspect, IBExpert IDE - but it did not triggered this before... – Arioch 'The Apr 01 '16 at 10:23
  • @Arioch'The The database IDE is IBExpert. I added this information to the question now. – René Hoffmann Apr 01 '16 at 11:19
  • 1
    Since DK landed here i suggest you to communicate with him. The plan you quoted was the plan from ibexpert or from your app? Try to set fibplus monitoring on and log all the real requests your application sends AND their plans. There could be something stupid like "+0" added that makes your app issue a very similar but different request precluding indices use, for example. – Arioch 'The Apr 01 '16 at 12:22
  • There could be other queries causing effects, there could be mismanagement of transactions.... There should be the difference somewhere. My point is that if logging of that particular app query and its plan would not show difference then more vast logging of other actions of your app should be saved and read too... – Arioch 'The Apr 01 '16 at 12:26
  • One more random shoot to the stars. Are you absolutely sure it is the server making the temp files, not your app? DK asked about filenames for example. I'd additionally run SysInternals Process Monitor on the machine with exhausted disk space and filter in all the file i/o with temp dir to check who really creates them – Arioch 'The Apr 01 '16 at 12:32

1 Answers1

1

[these] client API calls can't result temp allocation at server. Firebird allocates temp space in several known cases: - creating index (not your case) - sorting query result (not your case, sinse there is no SORT word in PLAN) - using function LIST (seems not your case) - ...

So, maybe your application in addition executes some another query, that results huge sorting or temp usage. Or, you gave us wrong query :-) Btw, what are the file names (and their size) that allocates your temp? Can you turn on monitoring (using FIBPlus) to check what goes to server with this query?

Dmitry Kuzmenko
  • 36
  • 1
  • The topic starter omitted many crucial information fragments, so it all is largely a guesswork. But if we take it at face value, then "when executed via my database IDE and uses a query plan with good indices" implies that he somehow learned the plan used by his application made query and that query does not use indices. Then, again, if we would take it at face value.... He gives no sources, so it is quite possible that his application actually uses slightly different request.... – Arioch 'The Mar 31 '16 at 22:44
  • On another side, if one would google "frb_info_sql_relation_alias" he would only fins few pirated sources of FIB+ - there is a huge lack in Firebird documentation here I think.... – Arioch 'The Mar 31 '16 at 22:45
  • @Arioch'The that `frb_info_sql_relation_alias` is probably a FIB specific alternative constant for `isc_info_sql_relation_alias` for people who use an fbclient or ibase.h from an older version that didn't have that constant. – Mark Rotteveel Apr 01 '16 at 07:15