V Ne, 15. 02. 2004 v 20:53, harri007et p����e:
Currently there is no possibility to stop running query, so a bad

> query must not allowed to run at all. By "bad" I mean query, that it
> takes 5 minutes or more, when it should take 1 sec. For now, the
> query text(with the execution time) goes into log file and this way
> I will be able to test things later. Message "BAD query plan, try
> again" could be replaced by "hire another programmer", if you
> like :) But it must not run and hang the server for 5 minutes.

OK, this sounds reasonable. The problem is that there's no magic bowl to
look into and see trhe query execution time before it begins...yu just
have to run it (or to think about the plan, i'll get later to that)

> The last problem I had, was a query, consisting of view (from ~15
> joined tables) joined to 2 other tables. Users can't change the
> joins, but they are free to set any filters to any columns - which
> are converted to WHERE clause by program. Just a small example:
> ... WHERE (YLD.LO_ID IS NOT NULL) AND
> ((YLD.KUU IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)) OR
> (YLD.KUU IS NULL)) AND
> (YLD.AASTA > 0) AND
> ((YLD.NADALAPAEV IN (0, 1, 2, 3, 4, 5, 6, 7)) OR (YLD.NADALAPAEV
> IS NULL)) AND
> (OS.ONNETUS_ID IS NOT NULL) AND
> ((OS.OSALEJA_LIIK IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13,
> 14, 15, 16, 18, 19)) OR
> (OS.OSALEJA_LIIK IS NULL)) AND
> (NOT OS.SUGU IS NULL) AND
> ((OS.HARIDUS IN (0, 1, 2, 3, 4, 5, 6)) OR (OS.HARIDUS IS NULL)) AND
> (KAN.ONNETUS_ID IS NOT NULL) AND
> ((KAN.KANNATANU_LIIK IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
> 13, 14, 15, 16, 18, 19))
> OR (KAN.KANNATANU_LIIK IS NULL))
> AND (NOT KAN.SUGU IS NULL)
> This quey took 6 minutes(plan had 2xnatural). Without "where" clause
> it ran ~1 sec(1xnatural). I removed all clauses and started to put
> them back one by one. Would you beleive, that it was the "NOT"
> inside the last clause. Without NOT it used index(1 sec), with NOT
> it didn't(6 min). As changing of the order had no impact(as always)
> to quey plan, I was out of ideas.
>

Good lord, that's 17 tables :/ (joining views does join the views'
original select select queries, doesn't it?) I can't imagine such compex
query and thus i can't hel you with it. Nevertheless, there should be a
way of getting rid of the NOT clause.

BTW, wouldn't BETWEEN be faster than IN(1 ... n) ? (I have little
experience, i admit)

> > You should either recomupute index statistics (which _could_
> improve the
> > situation if the data has drastically changed...)...
>
> Yes, I know that. But I don't now _when_, what is the exact
> criteria? All indexes, that are are not primary/foreign keys, are
> switched off and back on after each data import, backup/restore is
> done once a week ..

OK, shutting mouth :)

> > ...or - in some special cases - itmay be more efficient to employ
> some
> > SPs and cursors (and maybe even The One Mysterious Hidden
> Column...but
> > that's possibly an offshot, try optimizing the query first.)
>
> I'm considering this new FireBird1.5 "execute varchar", but haven't
> decided yet.

I couldn't sleep last night and was partly being consumed by your
problem and so i sat down and worked on a homework. The result is
attached :) It's a simple command line utility compiled with

gcc -o explain test1.c -I/opt/firebird/include -lfbclient -lpthread

with this calling syntax:

explain <usual_db_name> <username> <password> "<select_query>"

Since the query usually does contain spaces ( :), you probably want to
use them arount the fourth parameter. All parameters are mandatory.

You can either:

1) Compile it under VC++ / MinGW and use it as it is, i.e. call it from
your app and parse the results, or

2) Compile it as a DLL in its C form (remove main(), polish it a bit)
and link to your app)

3) Get inspired and write something in Delphi using as much of FIB Plus
as possible. I'm saorry, but i'm a lamer untouched with Delphi except
for a simple school work one year ago. Can't seve with porting to
Pascal, i even don't use windows.

Once again, sorry for the code layout, it was 6 AM and fell on my bed
after writing it and fell asleep...but at least on my machne it works
and retrieves the execution plan.

(And since i'm thinking about writing a Ruby driver for Firebird,
getting familiar with the API is quite a neccessity for me :)

> Thanks for your support.
>
> regards,
> Harri

Not necessary, it seems that you're better in optimizing queries than me
:) (Didn't have to optimize anything more than a 3 tables' join till
now...i guess i'm still an RDBMS novice :)

Hope this helps at least somehow...

Jakub

----------

#include <string.h>
#include <stdio.h>
#include <stdlib.h>
#include <ibase.h>

#define MAX_PLAN_LENGTH 2000
#define MAX_STATEMENT_LENGTH 2000


// I put the db connection routine into a separate function so that the code is structured
// at least a bit. My code is usually EXTREMELY MESSY, you have been warned! Read at your own risk...

ISC_STATUS xconnect(ISC_STATUS *s_a, isc_db_handle *dhnd, char *db_name, char *user_name, char *password) {

/* The connection parameters other than the DB name itself must be prepared in a
so called "Database Parameter Buffer" structure (i.e. DPB). These parameters
include the username and password. There should be some defaults but i don't
trust defaults usually. They surely don't include your production system login
and password :)

The DPB is a buffer of characters, where (except for the firt byte, which must be
isc_dpb_version1) there's a uniform stream of such blocks:

{
{1 byte param ID} - for example isc_dpb_user_name, they're defined in ibase.h
{1 byte telling how many value characters follow} - in our example strlen(user_name)
{n bytes of the param value} - here we copy the user_name
}

Details in ApiGuide.pdf, page 47.

*/

char dpb_array[1000];
char *dpb = dpb_array; // Just to obain a mutable pointer...

*dpb++ = isc_dpb_version1; // Again - this is a must. The other params can follow in any order.

*dpb++ = isc_dpb_user_name; // This way, we include a string param. 1 byte ID, 1 byte strlen, string itself
*dpb++ = strlen(user_name);
dpb = strcpy (dpb, user_name);
dpb += *(dpb-1); // Please note we set the pointer just on the closing \x00 value of the string that gets
// copied into dbp via strcpy - we're going to overwite it. There's no need to keep it since we explicitly
// tell the lenght by the means of the byte preceding the first character of any parameter, not only of a string,
// and i really don't want to try the consequences of not removing it.

*dpb++ = isc_dpb_password; // The same once more for the password...
*dpb++ = strlen(password);
dpb = strcpy (dpb, password);
dpb += *(dpb-1);

*dpb++ = isc_dpb_sql_dialect; // Just for illustration of how to supply a single byte number :)
*dpb++ = 1;
*dpb++ = 3;

// Now that we have the DBP prepared, we can call the isc_attach_database function...
// See API Guide, p. 49

return isc_attach_database(s_a, strlen(db_name), db_name, dhnd, (dpb-dpb_array), dpb_array);

}

char *get_query_plan(char *dbn, char *usr, char *pwd, char *query) {

ISC_STATUS retval; // i'm a good child and i promised parents that i'll never toss functions' return values away :)

ISC_STATUS_ARRAY s_vec; // this is an array of 20 long integers for storing a bit more status info than a single integer
// return value ever could...
isc_db_handle db_handle = NULL ; // we must create our own connection...at least if we're going standalone.
isc_tr_handle tr_handle = NULL; // we also have to start a transaction, we can't issue a command outside a transaction context,
// as far as itis known to me.
isc_stmt_handle stmt_handle = NULL; // Surprise ... the prepared statement also has its handle !
char *plan_buffer = NULL;
char *plan_request_statement = NULL;
char plan_request[1] = {isc_info_sql_get_plan};
char tpb[] = {isc_tpb_version3, isc_tpb_read, isc_tpb_read_committed, isc_tpb_no_rec_version};

// The plan get stored into a char array
if (!(plan_buffer = (char *)malloc(MAX_PLAN_LENGTH))) {
printf("ERROR: Unable to allocate memory!");
exit(1);
}

// For the tedious task of connecting, i've written a helper function:
retval = xconnect(s_vec, &db_handle, dbn, usr, pwd);

if (s_vec[0]==1 && s_vec[1]) {
printf ("ERROR: Something went wrong when attaching the database!\n");
isc_print_status(s_vec);
return NULL; // Something has gone wrong!
}

// We must start a transaction. Much easied due to fixed format TPB. (And we don't reserve tables)
retval = isc_start_transaction (s_vec, &tr_handle, 1, &db_handle, sizeof(tpb), tpb);

if (s_vec[0]==1 && s_vec[1]) {
printf ("ERROR: Something went wrong when starting transaction !\n");
isc_print_status(s_vec);
return NULL; // Something has gone wrong!
}

retval = isc_dsql_alloc_statement2(s_vec, &db_handle, &stmt_handle);

if (s_vec[0]==1 && s_vec[1]) {
printf ("ERROR Something went wrong when allocating statement !\n");
isc_print_status(s_vec);
return NULL; // Something has gone wrong!
}

retval = isc_dsql_prepare(s_vec, &tr_handle, &stmt_handle, 0, query, 3, NULL);

if (s_vec[0]==1 && s_vec[1]) {
printf("ERROR: Something went wrong when preparing the statement! \n");
isc_print_status(s_vec);
return NULL; // Something has gone wrong!
}

// We're going to request the execution plan:
retval = isc_dsql_sql_info(s_vec, &stmt_handle, 1, plan_request, 1000, plan_buffer);

// OK, we can close the transaction.
retval = isc_commit_transaction(s_vec, &tr_handle);

// This is so easy that i won't use any helper function here...
retval = isc_detach_database (s_vec, &db_handle);

if (s_vec[0]==1 && s_vec[1]) {
printf ("ERROR: Something went wrong when detaching from database !");
isc_print_status(s_vec);
return NULL; // Something has gone wrong!
}



return plan_buffer;

}

int main (int argc, char **argv) {

char *rt;
rt = get_query_plan(argv[1], argv[2], argv[3], argv[4]);

printf("%s\n", &rt[3]); // I have been too lazy to look inside the docs why the plan starts at rt[3] :)

free(rt);

return 0;

}




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