From unprepared you get the figures below. Subsequent runs without the Fetchall parameter EDBManager quotes 0 with the parameter 0.047 secs


SELECT
_ID,
_fkContacts,
_fkStaff,
_InOutInd,
_ELNtype,
IF(_aList IS NOT NULL,TRUE,FALSE) AS _HasAttachments,
_Subject,
_Comments,
_Timestamp
FROM ELN
WHERE _fkContacts = :IDToMatch  
AND
(:FetchAll OR(_TimeStamp   >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH))
ORDER BY _timestamp DESC


SQL Query (Executed by ElevateDB 2.03 Build 5)

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
"_ID" AS "_ID",
"_fkContacts" AS "_fkContacts",
"_fkStaff" AS "_fkStaff",
"_InOutInd" AS "_InOutInd",
"_ELNtype" AS "_ELNtype",
IF("_aList" IS NOT NULL, TRUE, FALSE) AS "_HasAttachments",
"_Subject" AS "_Subject",
"_Comments" AS "_Comments",
"_Timestamp" AS "_Timestamp"
FROM "ELN"
WHERE "_fkContacts" = 1007387 AND (FALSE OR ("_TimeStamp" >=
CURRENT_TIMESTAMP() - INTERVAL '6' MONTH))
ORDER BY "_Timestamp" DESC

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

ELN: 19371 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 Timestamp

Filtering
---------

The following filter condition was applied to the Query table:

"_fkContacts" = 1007387 [Index scan (ELN.Contact):, 989 keys, 16384 bytes
estimated cost] AND (FALSE OR ("_TimeStamp" >= CURRENT_TIMESTAMP() - INTERVAL '6'
MONTH [Index scan (ELN.Timestamp):, 274 keys, 12288 bytes estimated cost]))

================================================================================
36 row(s) returned in 1.045 secs
================================================================================


SELECT
_ID,
_fkContacts,
_fkStaff,
_InOutInd,
_ELNtype,
IF(_aList IS NOT NULL,TRUE,FALSE) AS _HasAttachments,
_Subject,
_Comments,
_Timestamp
FROM ELN
WHERE _fkContacts = :IDToMatch  
AND
_TimeStamp   >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
ORDER BY _timestamp DESC



================================================================================
SQL Query (Executed by ElevateDB 2.03 Build 5)

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
"_ID" AS "_ID",
"_fkContacts" AS "_fkContacts",
"_fkStaff" AS "_fkStaff",
"_InOutInd" AS "_InOutInd",
"_ELNtype" AS "_ELNtype",
IF("_aList" IS NOT NULL, TRUE, FALSE) AS "_HasAttachments",
"_Subject" AS "_Subject",
"_Comments" AS "_Comments",
"_Timestamp" AS "_Timestamp"
FROM "ELN"
WHERE "_fkContacts" = 1007387 AND "_TimeStamp" >= CURRENT_TIMESTAMP() -
INTERVAL '6' MONTH
ORDER BY "_Timestamp" DESC

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

ELN: 19371 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 Timestamp

Filtering
---------

The following filter condition was applied to the Query table:

"_TimeStamp" >= CURRENT_TIMESTAMP() - INTERVAL '6' MONTH [Index scan
(ELN.Timestamp):, 274 keys, 12288 bytes estimated cost] AND "_fkContacts" = 1007387 [Index
scan (ELN.Contact):, 989 keys, 16384 bytes estimated cost]

================================================================================
36 row(s) returned in 0.187 secs
================================================================================

Roy,

<< From unprepared you get the figures below. Subsequent runs without the
Fetchall parameter EDBManager quotes 0 with the parameter 0.047 secs >>

You'll notice that the second version of the WHERE clause is rewritten by
the optimizer so that the TIMESTAMP condition executes first.  For some
reason, the parameter is messing up the ability of the optimizer to rewrite
the expression.  If you want to send me the database catalog and table, I'll
give it a run here and see what I can find.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>You'll notice that the second version of the WHERE clause is rewritten by
>the optimizer so that the TIMESTAMP condition executes first. For some
>reason, the parameter is messing up the ability of the optimizer to rewrite
>the expression. If you want to send me the database catalog and table, I'll
>give it a run here and see what I can find.

I was looking at the amount of data scanned. I didn't realise the order was that important. I thought it would run both then combine.

If you keep the stuff I send its the same catalog and the ELN table (c 1Gb now)

Roy Lambert

Roy,

<< If you keep the stuff I send its the same catalog and the ELN table (c
1Gb now) >>

I have your recent catalog, but no copy of the ELN table that isn't empty.
Send me an email and I'll give you instructions on how to send me a .zip of
the table files via EDB stores.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

I set up my app for export and import so that when I totally trash the development db I can re-import the live one so I'll email you a zip of the export file. I'll also send the latest catalog.



I'll be interested on you comments on these stats

ELN.EDBBlb, 707,116,544 bytes
ELN.EDBIdx, 39,124,992 bytes
ELN.EDBTbl, 9,203,584 bytes

ELN.TfRE, 110,032,651 bytes

and out of interest

ELN.7z, 16,528,550 bytes

My guess is I have the blob block size wrong


Roy Lambert

Roy,

<< I'll be interested on you comments on these stats

ELN.EDBBlb, 707,116,544 bytes
ELN.EDBIdx, 39,124,992 bytes
ELN.EDBTbl, 9,203,584 bytes

ELN.TfRE, 110,032,651 bytes

and out of interest

ELN.7z, 16,528,550 bytes

My guess is I have the blob block size wrong >>

I wouldn't even hazard a guess without looking at the actual data and doing
an analysis of the BLOBs.  Comparing an export file to the binary files is
an exercise in futility due to the extra formatting in the export file.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>the extra formatting in the export file.

That was my point. I would have expected the export file to be bigger not 1/8th of the size

Roy Lambert

Roy,

<< That was my point. I would have expected the export file to be bigger not
1/8th of the size >>

Yes, but that wasn't my point.  My point was that the extra formatting is
just another issue in making the comparison impossible.  I would actually
expect the EDB table files to be much bigger, namely because of the
fixed-length rows, BLOB blocks, and index pages.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Yes, but that wasn't my point. My point was that the extra formatting is
>just another issue in making the comparison impossible. I would actually
>expect the EDB table files to be much bigger, namely because of the
>fixed-length rows, BLOB blocks, and index pages.

Fixed length fields - yep no problem and expected,
index pages - red herring cos they aren't exported
blob blocks - this is the real kicker; I think I need a tool to see what the utilisation is like. I did write one for DBISAM I'll have to dust it off unless there's one built in somewhere

Roy Lambert