Verrrry Slow Browse Refresh

[RichBowman]

Running my web app on my local PC with local files, response is adequate. If I point to the dataset on one of our network servers, response time slows down maybe 20 fold. My Win32 browse using the same network server dataset refreshes almost instantly. I've tried using both the UNC pathname for the folder and a mapped drive letter with the same result.

I'm using Pervasive.SQL with the Btrieve driver.

I know this is a bit vague, but does anyone have any ideas?

Thank you,

Rich

[bruce2]

Almost certainly your browse filter is badly formed.
Or possibly the order has no key to use. But I'd check the filter first. Post the filter here so we can take a look. Be sure to point out which fields in the filter are string fields.

Also - if you have a key, that you think the browse will be using, let us know if the key is marked as case sensitive or case insensitive.

Cheers
Bruce

[RichBowman]

Filter is:

[1764] [netTrace][thread=4] filter=ITM:ItemNumber >= 'CED-104-' AND ITM:ItemNumber <= 'CED-104-zzzzzzzzzzzzzzzzzzzzzz' AND ~INSTRING(ITM:Stock,'DX')   

ITM:ItemNumber and ITM:Stock are both string fields and always upper case. ITM:ItemNumber is the primary key. The primary key is selected in the Data / Tables Pad and also in the template as the unchanging unique key. Removing the ~INSTRING portion of the filter has no effect on performance.

Thank you,

Rich

[RichBowman]

Sorry, I forgot. The key is NOT case sensitive.

[Bruce]

first try

UPPER(ITM:ItemNumber) >= 'CED-104-' AND UPPER(ITM:ItemNumber) <= 'CED-104-zzzzzzzzzzzzzzzzzzzzzz' 

then try

UPPER(ITM:ItemNumber) >= 'CED-104-' AND UPPER(ITM:ItemNumber) <= 'CED-104-z' AND ~INSTRING(ITM:Stock,'DX')   

my guess is that the first one will be fast, the second _might_ be fast (but probably wrong) then try

UPPER(ITM:ItemNumber) >= 'CED-104-' AND UPPER(ITM:ItemNumber) <= 'CED-104-z' AND ~INSTRING(ITM:Stock,'DX',1,1)   

which will be the same as the second, but probably more correct. Let's see if this works first, if it does I'll explain why...

cheers
Bruce

[RichBowman]

All 3 seem to work equally well, no observable difference in response. I used this same technique on another browse and it worked equally well. A 3rd browse did not improve. It's filter is

OEH:CustomerNumber >= 'BRO-030' AND OEH:CustomerNumber <= 'BRO-030' AND INSTRING(OEH:TransCode,'ORDRET') AND OEH:DeliveryCode <> 'BK'

The difference here is that the key has 4 fields -- string, date, time and string. The key IS case sensitive. Customer Number is the first field. I tried with just the Customer Number portion of the filter, using UPPER and using just an "=" instead of ">= and <=" all with the same result.
Why did using UPPER make such a difference on the first filter?

Thank you,

Rich

[bruce2]

Because the key is case insensitive, the view engine can only use it if the filter is also case insensitive. Adding the Upper makes the filter case insensitive.

Cheers
Bruce

[RichBowman]

OK, I guess I can accept that. But why does my browse with a case SENSITIVE key refresh in a blink in my Win32 app but is slow in my NetTalk app? Isn't it the same file driver and View Engine? Is this something to do with J-Query, something else? The filters in question are (the first field is case sensitive):

OEH:CustomerNumber >= 'BRO-030' AND OEH:CustomerNumber <= 'BRO-030' AND INSTRING(OEH:TransCode,'ORDRET') AND OEH:DeliveryCode <> 'BK' 

OEH:CustomerNumber = 'BRO-030' AND INSTRING(OEH:TransCode,'ORDRET') AND OEH:DeliveryCode <> 'BK'

Sorry for so many questions,

Thank you,

Rich

[Bruce]

>> OK, I guess I can accept that. But why does my browse with a case SENSITIVE key refresh in a blink in my Win32 app but is slow in my NetTalk app? Isn't it the same file driver and View Engine?

yes, same file driver, same View Engine. The whole question of how View Filters work is discussed in my book, and is too long to duplicate here in full, but the edited summary goes something like this;

a) in a windows app you "bind" a bunch of variables, the construct a filter expression using those variables and the file variables.
b) A more effcient approach (in a web app) is to skip the bind step and create the expression with actual values.

thus in windows you might have
BIND('a',a)
Filter = 'fil:field = a'

whereas in Web you have

Filter = 'fil:field = ' & a

If a is a string you need quotes around it

Filter = 'fil:field = ''' & a & ''''

If a is in a Case Insensitive key you want to UPPER both sides;

Filter = 'upper(fil:field) = ''' & Upper(a) & ''''

If a is NOT in a Case Insensitive key then leave it as;

Filter = 'fil:field = ''' & a & ''''

I can't say use INSTRING in filters a lot, but your use of it appears wrong to me. Specifically I think you are expecting the omitted parameters (3 and 4) to default to ,1,1 - and (at least in normal clarion) they don't=.

As to the speed - the best way to debug that is to build up the filter slowly. ie first try

OEH:CustomerNumber >= 'BRO-030' AND OEH:CustomerNumber <= 'BRO-030

then add

OEH:DeliveryCode <> 'BK' 

then add

INSTRING(OEH:TransCode,'ORDRET')

and report back on which part slows down.

cheers
Bruce

[RichBowman]

I can not explain it!

I changed the filter slightly (instead of using >= and <= I just use =, which is what I really want) and now the browse with a case SENSITIVE key is very fast. Here's the filter that works:

OEH:CustomerNumber = 'BRO-030' AND OEH:DeliveryCode <> 'BK' AND INSTRING(OEH:TransCode,'ORDRET',1,1)

Thank you for your help,

Rich

[Bruce]

I notice the INSTRING now has ,1,1 - are you sure it's not that which is making it faster?

cheers
Bruce

[RichBowman]

I tried the filter with out the "1,1" in the INSTRING and it is still fast. I agree that adding the "1,1" is a good programming practice, however the C8 docs say it is optional and the first parameter defaults to the length of the string and the second defaults to 1.

It would be nice to know what I did, if anything, to speed this up, but I'm not going to try and fix something that isn't broken.

Thank you,

Rich

[Bruce]

>> first parameter defaults to the length of the <sub>string

this is (often) not 1. And in _most_ cases you're probably thinking of them as 1....
Leaving them out becomes somewhat dangerous....

the reason why it could affect the speed is because you want the whole filter to be done on the server. "Clarion" functions have to be translated into whatever the server can do (for SQL backends) so the more "clarionised" the filter the less likely it can translate into SQL.

anyway, it's going fast now, that's the main thing.

cheers
Bruce</sub>