DBServer Performance
in [Visual Objects]
|
From: michael.floh on 4 Jul 2008 00:41 I have a performance-problem with dbServer setFilter. VO-Version 2.7 The DBF has 4500 raws. The less raws are comming from the server (with filter-settings) the worth is the performance. If there is only one user, everthing is OK. If the filter is taking all or nearly all raws from the DBF, the setFilter-method takes about 1 second If the filter is taking only a few raws from the DBF, it takes up to 40 seconds. Here is the code with the setFilter-method: IF .not. SELF:oDCPAR_bBrowser:Server:ClearFilter( ) prcTraceErr( "ClearFilterError" ) ELSE IF .not. SELF:oDCPAR_bBrowser:Server:SETFILTER( uFilterblock, cFilterString ) prcTraceErr( "SetFilterError", cFilterString ) ENDIF ENDIF Is this a known problem and is there a workaround?
From: johan.nel on 4 Jul 2008 01:35 Hi Michael, > Is this a known problem and is there a workaround? Yes it will always be a problem, remember its a file based system. So the whole database needs to get passed to your client's PC so less strict filters will be faster. Have you tried to rather use SetScope(TopScope, BottomScope)? Do you have an index that could help with the filtering of the records? HTH, Johan Nel Pretoria, South Africa.
From: Geoff Schaller on 4 Jul 2008 01:51 Michael. No, there is no workaround, other than to switch over to orders and scopes. It is a fact of life with DBFs that filters can be inherently slow. Clipper, VO, doesn't matter. With cdx there is a degree of optimisation possible so long as the filter expression matches the index expression but hey, that is where scopes come in. Of course in SQL, filters are as fast as scopes <g>. The answer: go scopes. Geoff "michael.floh(a)hotmail.com" <michael.floh(a)hotmail.com> wrote in message news:40074b31-07a3-456d-a5a5-7f50f2d49028(a)8g2000hse.googlegroups.com: > I have a performance-problem with dbServer setFilter. > VO-Version 2.7 > > The DBF has 4500 raws. > > The less raws are comming from the server (with filter-settings) the > worth is the performance. > If there is only one user, everthing is OK. > > If the filter is taking all or nearly all raws from the DBF, the > setFilter-method takes about 1 second > If the filter is taking only a few raws from the DBF, it takes up to > 40 seconds. > > Here is the code with the setFilter-method: > > IF .not. SELF:oDCPAR_bBrowser:Server:ClearFilter( ) > prcTraceErr( "ClearFilterError" ) > ELSE > IF .not. SELF:oDCPAR_bBrowser:Server:SETFILTER( uFilterblock, > cFilterString ) > prcTraceErr( "SetFilterError", cFilterString ) > ENDIF > ENDIF > > Is this a known problem and is there a workaround?
From: richard.townsendrose on 4 Jul 2008 03:50 Michael. if you have advantage client server engine running then as long as all the filter criteria are in one of the index's (as long as you are running with indexbags, i.e. one index file containing many index's) then filtering is speeded up quite a lot. best is scope, and then filter. however often a temporary extract file will work faster.. like this one. firts we scope the table to be examoined, then we evaluare what we want to copy to the extract table, andthen report on that. IF Eval(cbTypeDocs, SELF:oDOCIS:FIELDGET(#DOCSRC)) .AND. ; Eval(cbAck, SELF:oDOCIS:FIELDGET(#RECVVERIFY),SELF:oDOCIS:FIELDGET(#RECVDATE)) .AND. ; Eval(cbReturn, SELF:oDOCIS:FIELDGET(#MUSTRETURN),SELF:oDOCIS:FIELDGET(#RETNDATE),SELF:oDOCIS:FIELDGET(#DOCSRC)) .AND. ; // rgtr 201102 can't return returns ! Eval(cbReseCode, SELF:oDOCIS:FIELDGET(#DOCSRC),SELF:oDOCIS:FIELDGET(#DRGRESE)) .AND. ; Eval(cbReteCode, SELF:oDOCIS:FIELDGET(#DOCSRC),SELF:oDOCIS:FIELDGET(#DRGRESE)) .AND. ; Eval(cbIssueDate,SELF:oDOCIS:FIELDGET(#ISSUDATE)) .AND. ; Eval(cbAdvice, SELF:oDOCIS:FIELDGET(#NOCOPIES)) .AND. ; Eval(cbRecip, SELF:oDOCIS:FIELDGET(#NAMEREF)) .AND. ; Eval(cbMaker, SELF:oDOCIS:FIELDGET(#DOCSRC),SELF:oDOCIS:FIELDGET(#JOBNO),SELF:oDOCIS:FIELDGET(#DRGNO)) .AND. ; Eval(cbGroup, SELF:oDOCIS:FIELDGET(#DOCSRC),SELF:oDOCIS:FIELDGET(#JOBNO),SELF:oDOCIS:FIELDGET(#DRGNO)) may look awesome but it is surpisingly fast as every item is in an index somewhere - even tho one index is never actually used anywhere - except for speeding up this analysis. richard
From: Robert van der Hulst on 4 Jul 2008 07:45
Hi michael, On Thu, 3 Jul 2008, at 21:41:56 [GMT -0700 (PDT)] (which was 6:41 where I live) you wrote about: 'DBServer Performance' > I have a performance-problem with dbServer setFilter. > VO-Version 2.7 > The DBF has 4500 raws. > The less raws are comming from the server (with filter-settings) the > worth is the performance. > If there is only one user, everthing is OK. > If the filter is taking all or nearly all raws from the DBF, the > setFilter-method takes about 1 second > If the filter is taking only a few raws from the DBF, it takes up to > 40 seconds. > Here is the code with the setFilter-method: > IF .not. SELF:oDCPAR_bBrowser:Server:ClearFilter( ) > prcTraceErr( "ClearFilterError" ) > ELSE > IF .not. SELF:oDCPAR_bBrowser:Server:SETFILTER( uFilterblock, > cFilterString ) > prcTraceErr( "SetFilterError", cFilterString ) > ENDIF > ENDIF > Is this a known problem and is there a workaround? If you are using DBFCDX you should try to set the filter _WITHOUT_ the uFilterBlock. DBFCDX will then try to optimize the filter using the available indexes. Quite often that results in a better performance, if one of more parts of the index expression can be resolved using an existing index. -- Robert van der Hulst AKA Mr. Data Vo2Jet & Vo2Ado Support VO & Vulcan.NET Development Team www.heliks.nl |