| Subject | RE: [IBO] Paradox conversion |
|---|---|
| Author | IBO Support List |
| Post date | 2014-10-07T23:23:22Z |
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:
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
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 ofthe
> 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
>> dataset and have paradox-like scrolling performance. IBO willunavoidable.
> automatically
>> employ what is called horizontal dataset refinement.
>>
>> It is true that they lose the accurate scrollbar, but that is
> IYou
>> 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.
>> simply just call InvalidateRows if you want the buffer purged to reclaimhave
>> memory without interfering with the operations of the dataset. In other
>> words, critical buffer entries will be preserved. For example, if you
>> cached updates, those will remain.it
>>
>> 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
>> 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 orInterBase
> without the need for BDE, ODBC or any other layer.___________________________________________________________________________
>
> http://www.ibobjects.com - your IBO community resource for Tech Infopapers,
> keyword-searchable FAQ, community code contributions and more !reports
> http://tracker.ibobjects.com - your portal to submit and monitor bug
> 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 orInterBase
> without the need for BDE, ODBC or any other layer.___________________________________________________________________________
>
> http://www.ibobjects.com - your IBO community resource for Tech Infopapers,
> keyword-searchable FAQ, community code contributions and more !reports
> http://tracker.ibobjects.com - your portal to submit and monitor bug
> 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