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
>
>