NetTalk Central

Author Topic: Slow filtering  (Read 7838 times)

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Slow filtering
« on: March 16, 2011, 04:50:34 AM »
I have couple of browses with filters. Displaying this browse(s) is very slow, lets say about 15-20 seconds to display first browse. And another pain in the... you know what... i have child browse on that browse, both with filters. All is so slow that I cannot put it on the wild.
Any help...
Regards, Ozren.
C7.2
Nettalk 4.53

Gordon Holfelder

  • Jr. Member
  • **
  • Posts: 92
    • View Profile
    • Email
Re: Slow filtering
« Reply #1 on: March 16, 2011, 08:53:34 AM »
Are these tps files or SQL tables?

Typically browses in Nettalk will work the same as a regular clarion app. Any slowness can be improved by adding the proper indexes to the table.

HTH,
Gordon

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #2 on: March 16, 2011, 10:00:38 AM »
This is regulary tps browses... All work fine on offline version, but on Nettalk version displaying browses is soo slow..
All browses had unique keys on tables, but when comes to filtering, all slowed down...
I was stopwatched 2 examples: first browse, page loaded, without filtering, 5 seconds (this tps is about 30.000 entries),
second example, same browse, but filtered, page loaded, 28 seconds...

kevin plummer

  • Hero Member
  • *****
  • Posts: 1195
    • View Profile
    • Production Accounting and Software Payroll
Re: Slow filtering
« Reply #3 on: March 16, 2011, 02:43:35 PM »
check for case sensitivity on your filter

Bruce

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11245
    • View Profile
Re: Slow filtering
« Reply #4 on: March 16, 2011, 10:03:58 PM »
yes, post your filter here, sounds like it's not quite right.
It's easy to get the filter wrong.

cheers
Bruce

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #5 on: March 16, 2011, 10:44:04 PM »
My filter is:

loc:FilterWas =  'MRS:SIF_POD = '''&clip(MRZ:SIF_POD)&''' ' & ' AND ' &  'MRS:GDOK = ' & MRZ:GDOK & ' AND ' &  'MRS:SIF_DOK = '''&clip(MRZ:SIF_DOK)&''' ' & ' AND ' &  'MRS:SIF_PJ = '''&clip(MRZ:SIF_PJ)&''' ' & ' AND ' &  'MRS:BRDOK = '''&clip(MRZ:BRDOK)&''' '
ThisView{prop:Filter} = loc:FilterWas

Bruce

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11245
    • View Profile
Re: Slow filtering
« Reply #6 on: March 16, 2011, 11:37:09 PM »
Hi Oggy,

'MRS:SIF_POD = '''&clip(MRZ:SIF_POD)&''' ' & ' AND ' &  'MRS:GDOK = ' & MRZ:GDOK & ' AND ' &  'MRS:SIF_DOK = '''&clip(MRZ:SIF_DOK)&''' ' & ' AND ' &  'MRS:SIF_PJ = '''&clip(MRZ:SIF_PJ)&''' ' & ' AND ' &  'MRS:BRDOK = '''&clip(MRZ:BRDOK)&''' '

you should not be using file fields in the filter, but Session values. For example,

'MRS:SIF_POD = '''&clip(MRZ:SIF_POD)&'''

should be

'MRS:SIF_POD = '''&p_web.GSV('MRZ:SIF_POD')&'''

clip is replaced with p_web.GSV
Note the quotes around MRZ:SIF_POD

GSV is a shorthand call to GetSessionValue

Cheers
Bruce

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #7 on: March 17, 2011, 01:29:40 AM »
Only one word: WOW (but not World of Warcraft )
Almost fast as in offline application...
I change filter to your suggestion and now its really fast... OK, going all the web application and changing all filters...
Thank you very much...
Regards, Ozren

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #8 on: March 17, 2011, 05:21:13 AM »
One more question: filtering on parent browse is ok, fast as I want it, but filtered child browse is still slow... I use same filtering syntax as in parent, but this child procedure is slow.
Any help on this?
Thanks.

Bruce

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11245
    • View Profile
Re: Slow filtering
« Reply #9 on: March 17, 2011, 06:05:58 AM »
what is the filter you have on the child?
ie - what is the exact setting as you have it in the template?

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #10 on: March 17, 2011, 06:16:09 AM »
This is my filter on child browse, I use local variables and then this variables concatenating in one string, loc:FilterWas, this work fine, but kinda slowly ;)
LOK:FILTER1='MRS:SIF_POD=''001'''
LOK:FILTER2=' AND '
LOK:FILTER3='MRS:GDOK='
LOK:FILTER4=p_web.gsv('GodinaRada')
LOK:FILTER5='MRS:SIF_DOK=''095'''
LOK:FILTER6='MRS:SIF_PJ='
LOK:FILTER7=p_web.gsv('SifraPJ')
LOK:FILTER8='MRS:BRDOK='
LOK:FILTER9=p_web.gsv('MRZ:BRDOK')
loc:FilterWas = LOK:FILTER1&LOK:FILTER2&LOK:FILTER3&LOK:FILTER4&LOK:FILTER2&LOK:FILTER5&LOK:FILTER2&LOK:FILTER6&LOK:FILTER7&LOK:FILTER2&LOK:FILTER8&LOK:FILTER9 
ThisView{prop:Filter} = loc:FilterWas


On parent child, children tab, I insert child procedure... And that is all. On web25 example, its the same scheme parent-child...
Oh, just one more information, I look today on my MRS data (child). It is 74506 records.... ;)
Opening the parent browse is almost split of the seconds (MRZ database, cca 3000 records), but child browse took about 5 seconds now to display. Is it OK?
Regards, Ozren

Bruce

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11245
    • View Profile
Re: Slow filtering
« Reply #11 on: March 17, 2011, 07:08:08 AM »
ok, so I'm guessing you have a key
MRS:SomeKey   Key(MRS:SIF_POD, MRS:GDOK, MRS:SIF_DOK, MRS:SIF_PJ, MRS:BRDOK)
(or at least on the first 3 or 4 fields there) - right?

But I'm also guessing your key has the NOCASE attribute. Which means it's case in-sensitive. Which means you need to UPPER your strings. As in;

LOK:FILTER1='Upper(MRS:SIF_POD)=''001'''
LOK:FILTER2=' AND '
LOK:FILTER3='Upper(MRS:GDOK)='
LOK:FILTER4=Upper(p_web.gsv('GodinaRada'))
LOK:FILTER5='Upper(MRS:SIF_DOK)=''095'''

and so on. Not the position of the Upper command relative to the quotes - it's _inside_ for the "left hand side" and outside for the GSV.

Cheers
Bruce


oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #12 on: March 18, 2011, 12:01:44 AM »
I try this, but now filter did not work at all ?!?

Bruce

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11245
    • View Profile
Re: Slow filtering
« Reply #13 on: March 18, 2011, 12:03:21 AM »
Then something is wrong.

Post your filter here, and also the key you think the client table will use.

cheers
Bruce

oggy

  • Full Member
  • ***
  • Posts: 219
    • View Profile
    • Email
Re: Slow filtering
« Reply #14 on: March 18, 2011, 12:44:10 AM »
!poRbrs KEY(MRS:SIF_POD,MRS:GDOK,MRS:SIF_DOK,MRS:SIF_PJ,MRS:BRDOK,MRS:RBRS),PRIMARY,NOCASE ! this is my referential key...

LOK:FILTER1='Upper(MRS:SIF_POD)=''001'''
LOK:FILTER2=' AND '
LOK:FILTER3='Upper(MRS:GDOK)='
LOK:FILTER4=Upper(p_web.gsv('GodinaRada'))
LOK:FILTER5='Upper(MRS:SIF_DOK)=''095'''
LOK:FILTER6='Upper(MRS:SIF_PJ)='
LOK:FILTER7=Upper(p_web.gsv('SifraPJ'))
LOK:FILTER8='Upper(MRS:BRDOK)='
LOK:FILTER9=Upper(p_web.gsv('MRZ:BRDOK'))

And my strings for concatenated filterstring....