Hi Jason

As per our Skype conversation this morning I had provided a sample set
of data. I have Skype messaged you download details and an overview of
the issue.

Thanks
Rob

On 8/10/2014 1:29 p.m., Robert Martin rob@... [IBObjects] wrote:
> Hi Jason
>
> Sure sounds great. Skype me when you are ready :)
>
> Thanks
> Rob
>
>
>
> On 8/10/2014 12:23 p.m., 'IBO Support List' supportlist@...
> [IBObjects] wrote:
>> Rob,
>>
>> Perhaps it would be easiest for me to have a look via remote desktop?
>>
>> Jason
>>
>>
>> -----Original Message-----
>> From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
>> Sent: Tuesday, October 7, 2014 4:06 PM
>> To: IBObjects@yahoogroups.com
>> Subject: Re: [IBO] Paradox conversion
>>
>> Hi Jason
>>
>> Sorry about the delay in getting back to you on this but I am finally
>> back on this job!
>>
>> I have set up a test app with the following....
>>
>> 'Ordering'
>> D.TITLE=D.TITLE;D.TITLE DESC
>> DT.DESCRIPTION=DT.DESCRIPTION;DT.DESCRIPTION DESC
>> D.REFCODE=D.REFCODE;D.REFCODE DESC
>>
>> 'OrderingLinks'
>> D.TITLE=ITEM=1
>> DT.DESCRIPTION=ITEM=2
>> D.REFCODE=ITEM=3
>>
>>
>> SQL below
>> SELECT d.DocRef AS DocRef, CASE WHEN tag.TagRef IS NULL THEN NULL ELSE
>> 'X' END AS DocTag_TagRef,
>> CASE WHEN (d.DocFileRef IS NOT NULL AND d.DesignDocFileRef IS NOT NULL)
>> THEN 'B' WHEN (d.DocFileRef IS NOT NULL) THEN 'M' WHEN
>> (d.DesignDocFileRef IS NOT NULL) THEN 'D' ELSE NULL END AS HasFile, CASE
>> WHEN (d.Who IS NOT NULL AND d.Who <> '') THEN 'T' ELSE 'F' END AS BookedOut,
>> d.DocCode AS DocCode, d.RefCode AS RefCode, d.Title AS Title,
>> dt.Description AS DocumentType_Description, d.Sent AS Sent, d.Misc AS
>> Misc, df.FileName AS DocumentFile_FileName,
>> df.FileDirectory AS DocumentFile_FileDirectory, d.Category AS Category,
>> d.ProjectManager AS ProjectManager, d.Project AS Project, d.Location AS
>> Location, d.Who AS Who,
>> d.Issue AS Issue, d.AdminNote AS AdminNote, do.Description AS
>> DocumentOwner_Description, dc.Description AS DocumentCreator_Description,
>> df2.FileExt AS DocumentFile2_FileExt, df2.FileName AS
>> DocumentFile2_FileName, df2.FileDirectory AS DocumentFile2_FileDirectory
>> FROM Document d
>> LEFT JOIN DocTag tag ON (tag.TagRef = 395839 AND tag.DocRef = d.DocRef)
>> LEFT JOIN DocumentType dt ON dt.DocTypeRef = d.DocTypeRef
>> LEFT JOIN DocumentFile df ON df.DocFileRef = d.DocFileRef
>> LEFT JOIN DocumentOwner do ON do.DocOwnerRef = d.DocOwnerRef
>> LEFT JOIN DocumentCreator dc ON dc.DocCreatorRef = d.DocCreatorRef
>> LEFT JOIN DocumentFile df2 ON df2.DocFileRef = d.DesignDocFileRef
>> ORDER BY d.DocCode DESC
>>
>>
>> Sorry about the overkill on the SQL its just one I grabbed from the real
>> app that dynamically creates SQL.
>>
>> Anyway, the orderingItem stuff works and is significantly quicker than
>> rebuilding and refreshing the SQL which is what is currently done, even
>> when the fields don't have 2 indexes to work with. However number 2
>> which works with a linked table is much slower that 1 or 3. The
>> dt.Description field is indexed (ASC and DESC), the D.RefCode field is
>> NOT indexed by ordering on it takes about 500ms whereas dt.Description
>> takes about 2000ms (Table has just over 100,000 records).
>>
>> I wonder if this is due to my OrderningLinks not having ;POS=0 at the
>> end? Trying to add this causes the following error when ordering...
>>
>> '
>> Invalid internal cursor key handling.
>> (C:\Components_XE2\ibo5_3_5_b1996_Source\source\core\IB_Components, line
>> 36489)
>> '
>>
>> It may be that I misunderstand the entry of the ;POS=0.
>>
>> Any suggestions would be appreciated. Although 2s is slow for an
>> ordering change it is still much faster than the current 4-5s query
>> refresh (AutofetchAll=true).
>>
>> Thanks
>> Rob
>>
>>
>> On 8/08/2014 1:35 p.m., 'IBO Support List' supportlist@...
>> [IBObjects] wrote:
>>> You can configure this feature even on a TIBOQuery but it takes a bit of
>>> extra work.
>>> The way you do it is you add an entry to the OrderingLinks property.
>>>
>>> What may have been configured as this:
>>>
>>> MYCOL=1
>>>
>>> Can be entered as this:
>>>
>>> MYCOL=ITEM=1;POS=0
>>>
>>> And, in the OrderingItems, you need to have both the ascending and
>>> descending ORDER BY criteria entered. Also, you should have indexes
>>> supporting those defined in your database.
>>>
>>> Hope this helps,
>>> Jason Wharton
>>> www.ibobjects.com
>>>
>>>
>>> -----Original Message-----
>>> From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
>>> Sent: Thursday, August 7, 2014 8:12 PM
>>> To: IBObjects@yahoogroups.com
>>> Subject: Re: [IBO] Paradox conversion
>>>
>>> Hi
>>>
>>> Thanks for the detailed reply. Unfortunately it must be a query (not a
>>> table) because it the user can add in / remove fields at run time. The
>>> database has been restructured to a 'proper' normalised structure and
>>> now supports functionality the old system could not. I just need to
>>> have it fast enough that the client doesn't feel it is 'slow'.
>>>
>>> I have managed to get some additional performance by removing an index !
>>>
>>> The client will be in in an hour so I will see what happens :)
>>>
>>> Cheers
>>> Rob
>>>
>>>
>>> On 8/08/2014 12:43 p.m., 'IBO Support List' supportlist@...
>>> [IBObjects] wrote:
>>>> Rob,
>>>>
>>>> If you use a TIBOTable and you have both ascending and descending indexes
>>> on
>>>> the IndexFieldNames column entries then you will be able to virtualize
>> the
>>>> dataset and have paradox-like scrolling performance. IBO will
>>> automatically
>>>> employ what is called horizontal dataset refinement.
>>>>
>>>> It is true that they lose the accurate scrollbar, but that is
>> unavoidable.
>>> I
>>>> think they will be ok with that since it does dynamically adjust as
>>> records
>>>> are brought into the buffer. IBO simply buffers all the records they have
>>>> fetched into the dataset. I don't have a way to cycle old records out.
>> You
>>>> simply just call InvalidateRows if you want the buffer purged to reclaim
>>>> memory without interfering with the operations of the dataset. In other
>>>> words, critical buffer entries will be preserved. For example, if you
>> have
>>>> cached updates, those will remain.
>>>>
>>>> When horizontal dataset refinement is in action then your calls to
>>> Locate()
>>>> will be very fast as well. It will not cause an auto-fetch-all type
>>> action.
>>>> It will simply move the record pointer to the middle of the dataset and
>>> you
>>>> will have a cursor to bring in more records if you scroll in either
>>>> direction. If you call the Last method it simply starts fetching records
>>>> towards BOF and doesn't have to fetch in the entire dataset.
>>>>
>>>> IBO gets you as close to flat file performance as is possible so go for
>> it
>>>> and let us know how it goes. I'll be standing by to help you if you get
>>> hung
>>>> up.
>>>>
>>>> Jason
>>>> www.ibobjects.com
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
>>>> Sent: Thursday, August 7, 2014 6:08 PM
>>>> To: IBObjects@yahoogroups.com
>>>> Subject: [IBO] Paradox conversion
>>>>
>>>> Hi
>>>>
>>>> I am re building some extremely old software to use Firebird instead of
>>>> Paradox. The user is obsessed with performance and wants the new system
>>>> to be as good as the old system. The old system was a flat table that
>>>> copied data locally to the HDD at startup to give best performance so it
>>>> is tricky to match that sort of performance.
>>>>
>>>> Anyway the issue I have is that the user wants the scrollbar to
>>>> accurately represent the position in the table. To do this AutoFetchAll
>>>> must be enabled (correct?). Enabling this means that sometimes 110,000
>>>> records need to be populated, which even with FB 2.5 on my local
>>>> machine, takes 2 and a bit seconds. I can massively improve performance
>>>> by disabling the auto fetch all, however when the user tries a
>>>> progressive search (using .locate()) the system has to fetch all the
>>>> records and it is slow for the first search.
>>>>
>>>> I think I can convince the user to do away with the accurate scrollbar
>>>> if I can convince him of the benefits. I am using TIBOQuery.
>>>>
>>>> My questions
>>>>
>>>> 1. Is there a way to run without AutoFetchAll where the data will
>>>> display quickly and then continue downloading the data without stopping
>>>> the app?
>>>>
>>>> 2. Setting RecordCountAccurate to False when AutoFetchAll is false
>>>> gives a a scrollbar that updates with use. Does it also improve
>>>> performance?
>>>>
>>>> 3. Can I increase the amount of records buffered when AutoFetchAll is
>>>> false. If so I could do a halfway house, where not all records are
>>>> buffered but more than default, so that when a progressive search starts
>>>> it is faster.
>>>>
>>>> 4. Any other performance suggestions. I know it is hard for a
>>>> relational db to compete with a flat file db with regards to strait grid
>>>> display?
>>>>
>>>> p.s. The query is in a readOnlyTransaction and is set to readonly. The
>>>> SQL is dynamically created and set in code.
>>>>
>>>> Cheers
>>>> Rob
>>>>
>>>>
>>>>
>>>> ------------------------------------
>>>> Posted by: Robert Martin <rob@...>
>>>> ------------------------------------
>>>>
>>>>
>> ___________________________________________________________________________
>>>> IB Objects - direct, complete, custom connectivity to Firebird or
>>> InterBase
>>>> without the need for BDE, ODBC or any other layer.
>>>>
>> ___________________________________________________________________________
>>>> http://www.ibobjects.com - your IBO community resource for Tech Info
>>> papers,
>>>> keyword-searchable FAQ, community code contributions and more !
>>>> http://tracker.ibobjects.com - your portal to submit and monitor bug
>>> reports
>>>> http://community.ibobjects.com - your portal to purchase and upgrade
>>>> ------------------------------------
>>>>
>>>> Yahoo Groups Links
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> ------------------------------------
>>>> Posted by: "IBO Support List" <supportlist@...>
>>>> ------------------------------------
>>>>
>>>>
>> ___________________________________________________________________________
>>>> IB Objects - direct, complete, custom connectivity to Firebird or
>>> InterBase
>>>> without the need for BDE, ODBC or any other layer.
>>>>
>> ___________________________________________________________________________
>>>> http://www.ibobjects.com - your IBO community resource for Tech Info
>>> papers,
>>>> keyword-searchable FAQ, community code contributions and more !
>>>> http://tracker.ibobjects.com - your portal to submit and monitor bug
>>> reports
>>>> http://community.ibobjects.com - your portal to purchase and upgrade
>>>> ------------------------------------
>>>>
>>>> Yahoo Groups Links
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -----
>>>> No virus found in this message.
>>>> Checked by AVG - www.avg.com
>>>> Version: 2014.0.4744 / Virus Database: 3986/8000 - Release Date: 08/07/14
>>>>
>>>>
>>> ------------------------------------
>>> Posted by: Robert Martin <rob@...>
>>> ------------------------------------
>>>
>>>
>> ___________________________________________________________________________
>>> IB Objects - direct, complete, custom connectivity to Firebird or
>> InterBase
>>> without the need for BDE, ODBC or any other layer.
>>>
>> ___________________________________________________________________________
>>> http://www.ibobjects.com - your IBO community resource for Tech Info
>> papers,
>>> keyword-searchable FAQ, community code contributions and more !
>>> http://tracker.ibobjects.com - your portal to submit and monitor bug
>> reports
>>> http://community.ibobjects.com - your portal to purchase and upgrade
>>> ------------------------------------
>>>
>>> Yahoo Groups Links
>>>
>>>
>>>
>>>
>>>
>>> ------------------------------------
>>> Posted by: "IBO Support List" <supportlist@...>
>>> ------------------------------------
>>>
>>>
>> ___________________________________________________________________________
>>> IB Objects - direct, complete, custom connectivity to Firebird or
>> InterBase
>>> without the need for BDE, ODBC or any other layer.
>>>
>> ___________________________________________________________________________
>>> http://www.ibobjects.com - your IBO community resource for Tech Info
>> papers,
>>> keyword-searchable FAQ, community code contributions and more !
>>> http://tracker.ibobjects.com - your portal to submit and monitor bug
>> reports
>>> http://community.ibobjects.com - your portal to purchase and upgrade
>>> ------------------------------------
>>>
>>> Yahoo Groups Links
>>>
>>>
>>>
>>>
>>>
>>> -----
>>> No virus found in this message.
>>> Checked by AVG - www.avg.com
>>> Version: 2014.0.4744 / Virus Database: 3986/8000 - Release Date: 08/07/14
>>>
>>>
>>
>> ------------------------------------
>> Posted by: Robert Martin <rob@...>
>> ------------------------------------
>>
>> ___________________________________________________________________________
>> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
>> without the need for BDE, ODBC or any other layer.
>> ___________________________________________________________________________
>> http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>> keyword-searchable FAQ, community code contributions and more !
>> http://tracker.ibobjects.com - your portal to submit and monitor bug reports
>> http://community.ibobjects.com - your portal to purchase and upgrade
>> ------------------------------------
>>
>> Yahoo Groups Links
>>
>>
>>
>>
>>
>> ------------------------------------
>> Posted by: "IBO Support List" <supportlist@...>
>> ------------------------------------
>>
>> ___________________________________________________________________________
>> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
>> without the need for BDE, ODBC or any other layer.
>> ___________________________________________________________________________
>> http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>> keyword-searchable FAQ, community code contributions and more !
>> http://tracker.ibobjects.com - your portal to submit and monitor bug reports
>> http://community.ibobjects.com - your portal to purchase and upgrade
>> ------------------------------------
>>
>> Yahoo Groups Links
>>
>>
>>
>>
>>
>> -----
>> No virus found in this message.
>> Checked by AVG - www.avg.com
>> Version: 2014.0.4765 / Virus Database: 4037/8344 - Release Date: 10/07/14
>>
>>
>
>
> ------------------------------------
> Posted by: Robert Martin <rob@...>
> ------------------------------------
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info papers,
> keyword-searchable FAQ, community code contributions and more !
> http://tracker.ibobjects.com - your portal to submit and monitor bug reports
> http://community.ibobjects.com - your portal to purchase and upgrade
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2014.0.4765 / Virus Database: 4037/8344 - Release Date: 10/07/14
>
>