--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...>
wrote:

>
> inoffensive_2006 wrote:
> > I've been using dynamic SQL to build my SQL in C++ strings
>
> What does this mean exactly? Are you using parameters, or write
> everything as strings? IOW, are you doing:
>
> st->Prepare("insert into t1 values (?,?)");
> st->Set(1, "this1");
> st->Set(2, "that1");
> st->Execute();
> st->Set(1, "this2");
> st->Set(2, "that2");
> st->Execute();
>
>
> or you are doing this:
>
> st->Prepare("insert into t1 values ('this1','that1')");
> st->Execute();
> st->Prepare("insert into t1 values ('this2','that2')");
> st->Execute();
>
> The former one is much faster.

Thanks Milan and Alexandre:

How much faster? My code is more like the latter.

> > and passing them to Firebird through IBPP's facilities. This
> > has been working well, but I'm looking to speed up performance.
> > In particular, we want to speed up SELECTS.
>
> My example used INSERTs, but the same thing works for selected.
>
> > My partner and friend in this project tells me that using
> > static SQL will improve performance of the SELECTS.
>
> I assume that 'static SQL' is my former example, and 'dynamic SQL'

would

> be my latter example?

No, I think my business partner is talking about
embedding SQL into the C++ source. His experience is
with other SQL database engines and COBOL, he has no
experience with Firebird.

I believe he's talking about what you would find in
the Interbase "Embedded SQL Guide". The stuff that needs
to be preprocessed before the C++ compiler sees it.

This is an example from that document:

EXEC SQL
SELECT FNAME, LNAME, STREET, CITY, STATE, ZIP
INTO :billing_address.fname, :billing_address.lname,
:billing_address.street, :billing_address.city,
:billing_address.state, :billing_address.zip
FROM ADDRESSES WHERE CITY = 'Brighton';

> > I use IBPP's Prepare() and Execute(). Then I call Fetch()
> > for each record.
>
> This sounds correct. Care to post some actuall code?

Sure, here is some. I hope this shows what I'm doing
wrong

Transaction start and commit code is omitted. The
table and column names are C++ constants, for example
DB_CON_KEY_ID is "CON_KEY".

The STL string sql_string, passed to Prepare, is
the following:

SELECT
CON_KEY,
CON_SORT_NAME,
CON_CONTACT_TYPE,
CON_ORG_CONTACT_PERSON,
CON_ORG_NAME,
CON_PHONE_DESC_1,
CON_PHONE_NBR_1,
CON_PHONE_EXT_1
FROM
CON
;

This query has no where clause.

------------

{
tstring sql_string = "SELECT\n ";
sql_string += DB_CON_KEY_ID;
sql_string += ",\n ";
sql_string += DB_CON_SORT_NAME_ID;
sql_string += ",\n ";
sql_string += DB_CON_CONTACT_TYPE_ID;
sql_string += ",\n ";
sql_string += DB_CON_ORG_CONTACT_PERSON_ID;
sql_string += ",\n ";
sql_string += DB_CON_ORG_NAME_ID;
sql_string += ",\n ";
sql_string += DB_CON_PHONE_DESC_1_ID;
sql_string += ",\n ";
sql_string += DB_CON_PHONE_NBR_1_ID;
sql_string += ",\n ";
sql_string += DB_CON_PHONE_EXT_1_ID;
sql_string += "\n";
sql_string += "FROM\n ";

sql_string += DB_CONTACT_TABLE_NAME;
sql_string += "\n";

if (text_has_a_graphic_char(where_clause))
{
sql_string += "WHERE ";
sql_string += where_clause;
sql_string += "\n";
}

sql_string += ";\n";

try
{
IBPP::Statement st =
IBPP::StatementFactory(
database_record_ptr->get_database_id(),
selected_transaction_ptr->transaction);

st->Prepare(sql_string.c_str());

st->Execute();

row_has_been_read = st->Fetch();

while (row_has_been_read && !error_found)
{
{
contact_record_ptr = new CONTACT_RECORD;

contact_record_vector_ptr->
push_back(contact_record_ptr);

// Fill the contact record from the database.
contact_record_ptr->load_from_database(st);

}

row_has_been_read = st->Fetch();
++local_row_count;
}
}
catch (IBPP::Exception& e)
{
// Error report
}
catch (...)
{
// Error report
}
}

bool CONTACT_RECORD::load_from_database(IBPP::Statement st)
{
bool return_val = false;

get_db_val(st, DB_CON_KEY_ID, &key);
get_db_val(st, DB_CON_SORT_NAME_ID, &sort_name);
get_db_val(st, DB_CON_CONTACT_TYPE_ID, &contact_type);
get_db_val(st, DB_CON_ORG_CONTACT_PERSON_ID,
&org_contact_person_key);
get_db_val(st, DB_CON_ORG_NAME_ID, &org_name);

phone_record_vector[0]->load_from_database(st,
DB_CON_PHONE_DESC_1_ID,
DB_CON_PHONE_NBR_1_ID,
DB_CON_PHONE_EXT_1_ID,
NULL);

return return_val;
}

------------

This needs to be sorted punctuation and numbers
first, then by case insensitive alpha, so the select
has no ORDER clause. I run an STL sort on the
vector after the fetches. I'm re-thinking this
approach and will be adding a column with each
record's sort order, and will be declaring an
ascending and descending index on it.

When run as the first query this selects records
at the rate of almost 350 rows / second. I then
test a number of SELECT statements selecting
different columns, including just the key and the
sort name. The table is fairly large, and the
SELECT key and sort name max out at around 425.
But the second and third selects of these columns
in this sample top out at an impressive
8000 rows / second. Firebird's cache is something
I don't understand.

But I'd like to get as much speed as possible
for all of the selects.

> > Would replacing this dynamic SQL with ESQL provide a
> > performance advantage?
>
> You mean, use ESQL instead of IBPP. While I guess it could spare you
> some function calls, IBPP is a very light wrapper. I don't think the
> difference would be noticeable.

That's what I want to hear.

I appreciate the effort both of you have made to help me.

Thanks
Larry