Hi:
I'm Running Windows XP, Sql Super Server and ODBC.
I have a valid connection string to the EMPLOYEE.FDB and used SQL to

access the content of the CUSTOMER table so I'm connected under the

Liberty Basic language using ODBC.
Now I would like to create my own DataBase programatically under

Liberty Basic and ODBC.
The sample Liberty Basic program I have creates an Access Database

using the SqlConfigDataSource of the OfbgCp32 dll.
First, is this the best method to use with Firebird programatically

under ODBC?
If so, I found the following on google.

char strAttributes[256] "DSN = MyFbDSN;
"
"CREATE_DB = NewFbDatabase.fdb;
"
"UID = SYSDBA;
"
"PWD = masterkey;
"
"CHARSET = NONE;
"
"PAGESIZE = 8192;
"
"DIALECT = 3;
";
SQLConfigDataSource( NULL, ODBC_ADD_DSN, strDriver, strAttributes );

For the above, is it necessary to specify the DSN= parameter in the

Attributes?
What does it do with the Super Server Databases if anything or is it

for Embedded Databases?
I Couldn't find the DSN= information for the EMPLOYEE.FDB in the ISQL

generated EXtract switched output but did find a basic Create Database

command comment.
Last Question, is it time to consider switching to the Embedded Server

or should I continue to develop under Super Server?
I'm asking as each uses a diferent sub-set of SQL ie. Static and

Dynamic.
By the way, I'm asking on this list as the ODBC listas I've read several
threads about using ODBC programatically with FireBird so there are some
folks who might know the answer.
If not, I'll ask on the ODBC Driver Development list but this question is
not really about driver development.
Thanks:
Rick Thomas:
Farmington Mich. USA

----- Original Message -----
From: "Paul Hope" <paulhope@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, May 05, 2006 8:14 AM
Subject: [firebird-support]A query on query performance


Hi

I have the following query

SELECT h.INVOICE_NUMBER,h.INVOICE_DATE,h.CUSTOMER_AC_NO, c.CUSTOMER_NAME
FROM ICC_HEADER h join CUSTOMER c on c.ACCOUNT_NO=h.CUSTOMER_AC_NO AND
h.COMPANY=c.CO
WHERE h.company='A'
and h.INVOICE_DATE>='1.4.2006'
and h.INVOICE_DATE<='30.4.2006'
ORDER BY h.ICC_NO DESC

primary key on ICC_HEADER is ICC_NO, primary key on CUSTOMER is
ACCOUNT_NO,CO.

there are 56148 rows in ICC_HEADER and 3511 rows in customer

with no index on INVOICE_DATE I get

Plan
PLAN SORT (JOIN (H NATURAL,C INDEX (RDB$PRIMARY21)))
------ Performance info ------
Prepare time = 0ms
Execute time = 641ms
Avg fetch time = 45.79 ms
Memory buffers = 2,048
Reads from disk to cache = 368
Writes from cache to disk = 0
Fetches from cache = 122,178

This performance is fine even though it reads all 56148 records. BUT with
an index on INVOICE_DATE I get


Plan
PLAN SORT (JOIN (C INDEX (AC_CO_INDEX),H INDEX (ICC_INVDATE_INDEX)))
------ Performance info ------
Prepare time = 0ms
Execute time = 47s 218ms
Avg fetch time = 3,372.71 ms
Memory buffers = 2,048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 7,815,493

This performance is really bad. It does 3897210 indexed reads on
ICC_HEADER.

The index on INVOICE_DATE is really useful for other queries like SELECT *
FROM ICC_HEADER WHERE INVOICE_DATE='ddd' - why is it so bad in the case
above and why does it need to do 3897210 indexed reads?.

Regards
Paul



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




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links