Hi

Which one is more optimized or efficient:

Setting the filter propoerty of a table component or use the filter as a WHERE clause in an sql statement with an SQL component?

Also, if a table has the following columns

F1 Varchar(10)  
F2 Boolean  

and has two indexes one for each field

Now which scenario will perform better or more efficiently when querying the table (either by using expression filter or select statement)

with a filter = (F1='<some value>') AND (F2=True)?

Using a single composite index with F1 and F2
or
Using individual and seperate indexes for each F1 and F2?

Any help will be appricitaed?

Many Thanks

Alfred

Alfred


Generally two indices will be more efficient. Unless something's changed and my internal memory hasn't been updated ElevateDB (like DBISAM) only uses the first field of a composite index for filtering.

As far as filter vs sql they should be pretty much the same.

Roy Lambert [Team Elevate]

Dear Roy

Many thanks for your help...

Additional info obtained after the posting:

I checked the time used opening tables. I noticed that filtered tables take much more time to open than non filtered ones even though the
tables themselves have no more than 2 or 3 records.

For example, a table with 3 records and individual indexes on all columns used in filtering:

START >> 22:13:46:343

A10-A:  22:13:47:640 (FILTERED)
A10-B:  22:13:48:218 (FILTERED)
A10-C:  22:13:48:718 (FILTERED)       <<<
A10-L:  22:13:48:718 (NOT FILTERED) <<< Without a filter opend up almost instatntly.

The filter is very simple (Deleted=False) and (Kind <2), but yet it took almost 1.3 seconds to open A10-A.

I have about 23 tables to open and they take about 10 seconds to open in addition to 3 seconds to establish the connection. So the user has to
wait about 13-15 seconds to login.

I'll be very thankful for any additional comments...

Many Thnaks

Alfred

Alfred


That I can't help you with. I posted one myself over the weekend about the length of time to open a table with triggers attached. We'll both have to wait for Tim

Roy Lambert [Team Elevate]

Hi Roy

In addition to above test, I tested again using four TEDBQuery objects to open the tables immidiately after the TEDBTables are opened to
check if there were any performance gain. The following are the results:

NOTE: The tested table has 3 records with a record size of 520 bytes. The test is done over the Internet.

--- Table Objects ----

Start >>> 22:59:11:343

A10-A: 22:59:12:640 (FILTERED)     ~1.30 sec
A10-B: 22:59:13:203 (FILTERED)     ~0.55 sec
A10-C: 22:59:13:703 (FILTERED)     ~0.50 sec
A10-L: 22:59:13:703 (NOT FILTERED) ~0.00 sec

Total >>> ~2.35 sec

--- Query Objects ----

Start >>> 22:59:13:703 (from last entry above)

A10-A: 22:59:14:109 (SELECT * FROM A10 WHERE (F1=False) AND (F2 < 2)) ~0.40 sec
A10-B: 22:59:14:515 (SELECT * FROM A10 WHERE (F1=False) AND (F2 < 2)) ~0.40 sec
A10-C: 22:59:14:921 (SELECT * FROM A10 WHERE (F1=False) AND (F2 < 2)) ~0.40 sec
A10-L: 22:59:15:203 (SELECT * FROM A10) ~0.25 sec

Total >>> ~1.45 sec

Almost a gain of 1 sec... The strange thing though, is the non-filtered table opend instantly (0 sec) but the equivelant query without the WHERE
clause took ~0.25 sec to open.

Kind Regards

Alfred

Hi Roy

Thanks again for your comments.

I'll wait for Tim. He might be able to shed some light on this...

Kind Regards

Alfred

Alfred


If you didn't ask for a sensitive result set ElevateDB will produce a canned one which means writing to the disk. Just creating and opening the files for writing would probably account for most of the time.

Its also worth asking for an execution plan to see what's going on - eg from inside EDBManager

================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 14)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL "companies"."_ID" AS "_ID", "companies"."_Name" AS "_Name",
"companies"."_fkCompanies_Parent" AS "_fkCompanies_Parent", "companies"."_Website" AS
"_Website", "companies"."_fkOrgType" AS "_fkOrgType", "companies"."_fkSICGroup" AS
"_fkSICGroup", "companies"."_fkSICCode" AS "_fkSICCode",
"companies"."_eMailDomain" AS "_eMailDomain", "companies"."_fkMarkets" AS "_fkMarkets",
"companies"."_fkSandT_Turnover" AS "_fkSandT_Turnover", "companies"."_fkSandT_Staffing" AS
"_fkSandT_Staffing", "companies"."_YearEnd" AS "_YearEnd", "companies"."_Status" AS
"_Status", "companies"."_ModDate" AS "_ModDate", "companies"."_Source" AS
"_Source", "companies"."_Created" AS "_Created", "companies"."_Notes" AS "_Notes",
"companies"."_Links" AS "_Links", "companies"."_Profile" AS "_Profile",
"companies"."_Products" AS "_Products", "companies"."_UserFlags" AS "_UserFlags",
"companies"."_URLList" AS "_URLList", "companies"."_MarketPlace" AS "_MarketPlace" FROM
"companies"

Source Tables
-------------

companies: 5903 rows

Result Set
----------

The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the index PK

================================================================================
5903 row(s) returned in 0.031 secs
================================================================================


Roy Lambert [Team Elevate]

Hi Roy

This is the execution plan for sensitive followed by non-sensitive:

================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 11)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL "FDa10"."ID" AS "ID", "FDa10"."Kind" AS "Kind", "FDa10"."Name" AS
"Name", "FDa10"."Zone_RID" AS "Zone_RID", "FDa10"."Telecom_1" AS "Telecom_1",
"FDa10"."Telecom_2" AS "Telecom_2", "FDa10"."Address_1" AS "Address_1",
"FDa10"."Address_2" AS "Address_2", "FDa10"."Address_3" AS "Address_3", "FDa10"."Address_4"
AS "Address_4", "FDa10"."Address_5" AS "Address_5", "FDa10"."Replicate_RID" AS
"Replicate_RID", "FDa10"."Price_List_RID" AS "Price_List_RID",
"FDa10"."Price_List_P1" AS "Price_List_P1", "FDa10"."Price_List_P2" AS "Price_List_P2",
"FDa10"."Price_List_P3" AS "Price_List_P3", "FDa10"."Price_List_P4" AS "Price_List_P4",
"FDa10"."Price_List_P5" AS "Price_List_P5", "FDa10"."Price_List_P6" AS
"Price_List_P6", "FDa10"."Price_List_P7" AS "Price_List_P7", "FDa10"."Price_List_P8" AS
"Price_List_P8", "FDa10"."Commission_Target" AS "Commission_Target",
"FDa10"."Commission_Rate" AS "Commission_Rate", "FDa10"."IP_Address" AS "IP_Address",
"FDa10"."IP_Port" AS "IP_Port", "FDa10"."Login_Name" AS "Login_Name",
"FDa10"."Login_Password" AS "Login_Password", "FDa10"."Active" AS "Active", "FDa10"."RID_Seed" AS
"RID_Seed", "FDa10"."REC_CBY" AS "REC_CBY", "FDa10"."REC_CTS" AS "REC_CTS",
"FDa10"."REC_UBY" AS "REC_UBY", "FDa10"."REC_UTS" AS "REC_UTS", "FDa10"."REC_DEL" AS
"REC_DEL", "FDa10"."REC_IID" AS "REC_IID" FROM "FDa10"

Source Tables
-------------

FDa10: 3 rows

Result Set
----------

The result set was sensitive
The result set consisted of zero or more rows
The result set was ordered using the index Primary_Key

================================================================================
3 row(s) returned in 0.062 secs
================================================================================


----------------------------------------------------------- Non Sensitive --------------------------------------------------------------------


================================================================================
SQL Query (Executed by ElevateDB 2.02 Build 11)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL "FDa10"."ID" AS "ID", "FDa10"."Kind" AS "Kind", "FDa10"."Name" AS
"Name", "FDa10"."Zone_RID" AS "Zone_RID", "FDa10"."Telecom_1" AS "Telecom_1",
"FDa10"."Telecom_2" AS "Telecom_2", "FDa10"."Address_1" AS "Address_1",
"FDa10"."Address_2" AS "Address_2", "FDa10"."Address_3" AS "Address_3", "FDa10"."Address_4"
AS "Address_4", "FDa10"."Address_5" AS "Address_5", "FDa10"."Replicate_RID" AS
"Replicate_RID", "FDa10"."Price_List_RID" AS "Price_List_RID",
"FDa10"."Price_List_P1" AS "Price_List_P1", "FDa10"."Price_List_P2" AS "Price_List_P2",
"FDa10"."Price_List_P3" AS "Price_List_P3", "FDa10"."Price_List_P4" AS "Price_List_P4",
"FDa10"."Price_List_P5" AS "Price_List_P5", "FDa10"."Price_List_P6" AS
"Price_List_P6", "FDa10"."Price_List_P7" AS "Price_List_P7", "FDa10"."Price_List_P8" AS
"Price_List_P8", "FDa10"."Commission_Target" AS "Commission_Target",
"FDa10"."Commission_Rate" AS "Commission_Rate", "FDa10"."IP_Address" AS "IP_Address",
"FDa10"."IP_Port" AS "IP_Port", "FDa10"."Login_Name" AS "Login_Name",
"FDa10"."Login_Password" AS "Login_Password", "FDa10"."Active" AS "Active", "FDa10"."RID_Seed" AS
"RID_Seed", "FDa10"."REC_CBY" AS "REC_CBY", "FDa10"."REC_CTS" AS "REC_CTS",
"FDa10"."REC_UBY" AS "REC_UBY", "FDa10"."REC_UTS" AS "REC_UTS", "FDa10"."REC_DEL" AS
"REC_DEL", "FDa10"."REC_IID" AS "REC_IID" FROM "FDa10"

Source Tables
-------------

FDa10: 3 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows


Result set I/O statistics
-------------------------

Total rows visited: 3

Row buffer manager

Max buffer size: 133120 Buffer size: 1560

Hits: 3   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

Index page buffer manager

Max buffer size: 65536 Buffer size: 4096

Hits: 3   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 0   Bytes written: 0

================================================================================
3 row(s) returned in 0.062 secs
================================================================================

My table has 3 records only and takes double the time of your table with 5000+ records. Are you connecting over the internet? Mine is over the
Internet...

Kind Regards

Alfred

Alfred


Nope I'm running local. If you're operating over the internet then whilst the actual table open time may be good the transport time is anybody's guess.

Second point is you have about twice as many fields as I have so it will take a bit longer to populate its fielddefs. If you don't need all fields in a query just ask for those you do need.

Roy Lambert [Team Elevate]

Hi Roy

True, the Internet definitly has an affect...

I have to test it over the LAN and compare...

Many thanks for your help...

Kind Regards

Alfred