Filtering slow on Network Machines

[mstrcmtr]

1) TTLFLTR6 = ALLTRIM(S1.DESC) = && Fltr items staring with Letter S

2) TTLFLTR6 = ALLTRIM(S1.DESC) = [SH] && Filter items staring with Letter SH

Step by Step

lcOB12 = 'S1.Desc'

SELECT S1.Desc,S1.Acitno,S1.Prtno,S1.On_hndq,S1.Bn,S1.Slgrte,S1.Prcrte,;
S2.Pckg,S2.Grs_Wght,S2.Net_Wght,S2.CBM ;
FROM Stk1 S1 ;
LEFT JOIN Stk2 S2 ON S1.Acitno = S2.Acitno ;
WHERE &TTLFLTR6 ;
ORDER BY &lcOB12 ;
INTO CURSOR Lst_As ;
READWRITE

thisform.List1.RowSource = lcRowSource

SELECT Lst_As

INDEX ON DESC TAG DESC
INDEX ON PRTNO TAG PRTNO
INDEX ON VNDRCD TAG VNDRCD
INDEX ON ACITNO TAG ACITNO
INDEX ON SETCODE TAG SETCODE
INDEX ON LINKPNO TAG LINKPNO

SET ORDER TO Desc IN Lst_As

List1 RowSourceType = 6-Fields

Above Coding is calling in text box text1 InteractiveChange Event this is for users to search there relevant items in stock table while making invoice

Stock table has records up to 15000

It is okay while working on Server Machine but on Network machine Filtering of items are very slow what are the reasons of slowness on network machines

 

[Mike Lewis]

Above Coding is calling in text box text1 InteractiveChange Event

That means that, every time the user presses a key in the text box, you are running code that creates a cursor, and then creates six indexes for that cursor. If the cursor has more than a few records, it's bound to be slow. You need to re-think your strategy here.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Looking at your code in a bit more detail, I suggest you create a cursor at the outset - in the form's Init, perhaps. Leave out the WHERE and ORDER BY clauses.

Then, in the InteractiveChange, run a SELECT on that cursor, using yout test on ALLTRIM(S1.DESC) as your WHERE clause; use the Desc field in the ORDER BY clause. Then use the result of that SELECT to populate your list box.

Going further, do you really need the listbox to be updated whenever the user presses a key in the textbox? If not, move the code from the InteractiveChange to the LostFocus.

In any case, you definitely don't need to create those six indexes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mstrcmtr]

One another thing noticed if on a network machine only 1 user is working on that particular form then the above query is taking no time and resulting very fast and quick but if another user start using this form at same time then the above query slow down.

removed creating of indexes after getting query

getting Query from free table stk1 open in Form Data Environment and setting of Buffermodeoverride = 5

Can adding With (Buffering = .T.) in SQL query solve the speed issue in multi user working environment

 

[Olaf Doschke]

>Can adding With (Buffering = .T.) in SQL query solve the speed issue
No, buffering=.t. doesn't mean caching, it means SQL reads from buffered changes, too.
That only happens with ediuted data, it's not happening with once read data in the second read, that happens anyway with VFPs caching.

The phenomenon you descirbe is a well known symtom of oplocks. That mechanism is bad for VFP and also Acesss and other file dbs.

Bye, Olaf.

 

[Olaf Doschke]

Besides the most probable oplock problem (also in regard of often index corruptions you indicate in your other current thread) you don't have an optimizable where condition:

The conditions you have are using ALLTRIM(). If you're unsure, whether field values have leading spaces and shouldn't have, simply update all data once and in your forms make sure you only save alltrimmed values. Besides you should know indexing the result won't speed up the query. An index on Desc will not be used to optimize ALLTRIM(Desc)=value. An index on ALLTRIM(Desc) will be used, but will need you to use Alltrim() in query conditions and will nat make smaller indexes. The index nodea always have same key sizes.

Cautious with trimming in queries:
1. If you have trimmed expressions in the select field list the first expression result determines the result field size, it might even end up as C(0).
2. Indexes on trimmed expressions in the simple case still have the length of the whole field, if VFP can't decide what size a complex expression might have at max, it may create a too wide or too narrow index.
3. If you alltrim(field) while you have an index on field alone, this isn't used for rushmore. Simple rule of thumb: Use the index expressions in queries or vice versa index on the expressions you use in queries, but avoid complex expressions, where simple also do.

You better keep C fields with n=240 at max and index on the field, maybe on UPPER(field) to get case insensitive index (but then also query with UPPER(field), store data trimmed and you get all records with field starting with "S". If you really want to make the tags smaller, then index on LEFT(Desc,10), but then searching and sorting also will only consider the 10 left chars.

Bye, Olaf.

 

[mstrcmtr]

Is there any solution for oplocks ?

 

[Olaf Doschke]

Turning them off. To be able to do so, switch rom SMB2 to SMB1.
Google it.

Bye, Olaf.

 

[newtofoxpro]

Above Coding is calling in text box text1 InteractiveChange Event this is for users to search there relevant items in stock table while making invoice
Stock table has records up to 15000

You are running supplied sample code in testbox text1 InteractiveChange event, may be time consuming.
Please Try for

1. Move this code in textbox init without where clause. You will get cursor "Lst_As"
INDEX ON UPPER(desc) TAG ONE && Add this if needed
2. In TextBox InteractiveChange event,
SET FILTER TO UPPER(desc)=UPPER(this.value) in Lst_As
refresh listbox/grid

 

[mstrcmtr]

Switch rom SMB2 to SMB1. goolge also only found Link of Game Mario nothing else

Please help to solve this oplock issue

 

[Mike Lewis]

This article might help:

http://www.hexcentral.com/articles/troubleshoot.htm

(scroll down to "Problem #2").

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

from, not rom.

Well, search in conjunction with VFP and you find VFP related things about the SMB protocol instead of mario bros stuff.

You also already have other key words, ie oplocks.
You surely find something else even without being pointed to a specific site. There is much on this topic.

http://fox.wikis.com/wc.dll?Wiki~OpportunisticLocking

http://blogs.msdn.com/b/robmar/archive/2009/09/23/get-microsoft-fix-it-for-smb2-issue.aspx

Bye, Olaf.