Scan Loop working Slow in MultiUser

[mstrcmtr]

Table Buffering set to Buffermodeoverride = 5 for both Header and Detail table for Sale Invoice

Header Index set to Key_Pk Autoincrement I

Detail Index set to Key_Fk I

SELECT Header

Set Relation To Key_pk into Detail

Set Skip To Detail

thisform.pp_atincvh = GETAUTOINCVALUE()

SELECT Detail

lnatincvh = thisform.pp_atincvh

IF INDEXSEEK(lnatincvh,.T.,'Detail','Key_Fk')
ELSE
GO BOTTOM IN Detail
ENDIF

SCAN FOR Detail.KEY_FK = thisform.pp_atincvh

repl Qty with lnQty , Rate with lnRate , Amt with lnAmt

EndScan

When more than 1 user working on Sale Invoice above Loop speed become very slow

Above Scan Loop working absolutely Fast in Purchase Invoice ,Sale Return Invoice , Purchase Return Invoice in Multi user Environment Except <B>Sale Invoice</B>

Through Set coverage on tell the story that scan loop taking long time in Sale Invoice Check all the coding but found nothing that why scan loop taking time in Sale Invoice and same Scan Loop with same coding working fast in other type of Invoices.

Check all the setting but found nothing different

 

[Olaf Doschke]

Instead of this:

SCAN FOR Detail.KEY_FK = thisform.pp_atincvh 

repl Qty with lnQty , Rate with lnRate , Amt with lnAmt

EndScan

Use SQL Update!

Update Detail Set Qty = lnQty , Rate = lnRate , Amt = lnAmt WHERE KEY_FK = thisform.pp_atincvh

Your having a big misunderstanding of SCAN, as you do this in advance to the scan loop

IF INDEXSEEK(lnatincvh,.T.,'Detail','Key_Fk')
ELSE 
GO BOTTOM IN Detail	
ENDIF

You think your scan begins at the record you set here, but a scan loop begins at the top record, unless you add REST to it!
So if you didn't find the Key you GO BOTTOM, but SCAN FOR Detail.KEY_FK = thisform.pp_atincvh then goes back TOP.

You woould need SCAN [highlight #CC0000]REST[/highlight] FOR Detail.KEY_FK = thisform.pp_atincvh

Anyway, welcome to 2015, use SQL.

Bye, Olaf.

 

[Olaf Doschke]

Even SCAN REST is bad with FOR, you'd need SCAN REST WHILE. Or SET KEY.

But use SQL! It optimizes much better than you think you can do manually.

Bye, Olaf.

 

[mstrcmtr]

Thanks for your valuable reply i will check by using Scan Rest & While

Still Using Win-Xp-Sp3 on Designing Machine you may say Edict of XP no major reasons

What SQL Free version should i choose and how shift Database to SQL & All Coding in All Forms according to Free Tables any easiest way to reset coding according to SQL in Win-Xp-Sp3

 

[Mike Lewis]

You have misunderstood Olaf's suggestion. He said you should use SQL - not a SQL database.

SQL is a language used to access and manipulate data. The language is supported directly by VFP, and as such it works with VFP native data. You don't need to use any other software to make that happen. And you certainly don't need "SQL Free" - whatever that is.

Just check the VFP Help for the UPDATE command. Then use that command like any other command in VFP.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Is an 'update' approach quicker than a 'replace' one?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

An update surely is quicker than a series of single replaces.
When in doubt, make a test run and measure, but measure correctly.
Often what you do as second test will win, as it profits from caching.

It's only logical, that a single update can optimize all record changes needed to be done via where clause better than the FOR clause optimization and several replaces done now.

Bye, Olaf.

 

[GriffMG]

Hi Olaf

You CAN replace many fields in a native replace, although I don't (normally).

I suppose using the update is a good way to prep for SQL though



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Olaf Doschke]

>You CAN replace many fields in a native replace, although I don't (normally).
Yes, and the REPLACE command used actually does so. Look closer:

repl Qty with lnQty , Rate with lnRate , Amt with lnAmt

What a REPLACE can also do is replace several records at once, if used with ALL or FOR clause, but this is not the case here, instead this replace is done in a scan loop. And both scan loop and replace can be done with a single UPDATE-SQL statement in this case.

Bye, Olaf.

 

[GriffMG]

I see what you mean, the update is more elegant in that context, particularly as you don't have to build a SQL string with parameters
to execute it.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Mike Lewis]

I guess a REPLACE and a basic UPDATE are pretty similar in functionality. They can both update a single field or multiple fields; they can both operate on a single record or a group of records meeting some criteria. But a REPLACE can also operate on records within a scope, such as REST or NEXT N.

Where UPDATE really comes into its own is when used with FROM and JOIN clauses, and/or subqueries, to obtain data from other tables. Off-hand, I can't think of an easy equivalent in REPLACE.

My own preference is to use REPLACE for simple, single-record updates, and to use UPDATE in all other cases. But don't take that as a hard and fast rule.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

Once you set the relation, you can do the whole thing with a single REPLACE:

repl Qty with lnQty , Rate with lnRate , Amt with lnAmt ;
  FOR Detail.KEY_FK = thisform.pp_atincvh

Tamar

 

[Olaf Doschke]

Yes, as said you can also use the FOR clause of REPLACE, I'd still prefer UPDATE, and I'd also opt out of using GETAUTOINCVALUE. You know you want the latest value in the id field of the header table, so read it from there.

lnLatestID = Header.KEY_PK && right after adding a header record you are at the new record and can read the PK from there, no matter if using APPEND or INSERT INTO.
Update Detail Set Qty = lm.nQty , Rate = m.lnRate , Amt = m.lnAmt WHERE KEY_FK = m.lnLatestID

Will be the whole code needed to replace your original code and doesn't need setting an index order or a relation from header to detail at all.

On the other side, I ask myself why you don't insert detail records with the right values of Qty, Rate and Amt in the first place. You have records having the foreign key with empty Qty, Rate and Amt fields, if you already inserted the PK into these records, you can also insert Qty, Rate and Amt at the same place and won't need this update at all.

Bye, Olaf.

 

[Olaf Doschke]

...and the last question is, why all detail records for a certain header will need the same Qty, Rate and Amt values. If we'd talk about orders and order items, the quantity, rate and amount of each order item would normally vary per order item. If something is equal per detail, it should better be in the header data. If it's just a preset make it the default value of the detail table.

Bye, Olaf.