Troubleshooting Tableupdate()

[stanlyn]

Hi,

Any idea why I'm getting different results for aa when everything is the same. The aa was only added to troubleshoot this. With aa removed (original setup), tableupdate() fails, so I added a 2 second wait with the inkey() and it now returns .T.. Nothing else has changed.

*=INKEY(2) && aa returns .F.
=INKEY(2) && aa returns .T.

aa = Tableupdate(1, .T., 'rv_Table')
messagebox(aa)

= Requery('rv_Domain')

Is there a better way to find what tableupdate() is having trouble with as the aerror() is not specific enough as it only shows the message, program, line and some .null.s, nothing usefull.

When aa = .F., an exception is thrown on the requery() line with message about "table buffer for alias "xyz" contains uncommitted changes", otherwise, no exception. Restart VFP, open form, change name value, and apply.

With a pause, it succeeds, otherwise it fails.

Thanks
Stanley

 

[stanlyn]

Hi,

I have also revisited my similar issue at

https://www.tek-tips.com/viewthread.cfm?qid=1808583

which I believe has the same root problem, as we discussed this issue back then. I still do not see the WHY...

Also note there are no other users, contention, and no record movement as suggested by that article. In this case I am using inkey() instead of the wait x or set step on. Slowing it down makes it work.

Inkey() requires no user input and not setting or losting focus on form objects.

Thanks,
Stanley

 

[vernpace]

A number of things can cause a commit delay. Does the table have a key field that's being used? How many records | fields are being updated? What is the network environment? Are you using CursorAdapters? You are using the lforce parameter which may cause a delay. What buffering mode are you using? Is SET MULTILOCKS set to ON? What is the value of _VFP.AutoYield? You may want to reference VFP Help on the TABLEUPDATE function for more info.

Note that the commit succeeds after a pause. This could be normal in your environment. You should not call Requery unless TableUpdate returns .T.:

IF Tableupdate(1, .T., 'rv_Table')

Requery('rv_Domain')​

ELSE

* handle error​

ENDIF

Are you using SQL Server? If so, there are other things that can cause a commit to delay.

 

[stanlyn]

Hi,

Introducing this "for loop" that takes about 2-3 seconds to complete instead of the inkey() does not fix...

aa = Tableupdate(0, .T., 'rv_Domain', laError)
Messagebox(Transform(aa))

FOR x = 1 TO 10000000
  x=x+1
ENDFOR
	= Requery('rv_Domain')
	Locate For rv_Domain.pk = lnKey

So something else is happening behind the scenes...

Thanks Stanley

 

[stanlyn]

Vernpace,

Does the table have a key field that's being used? YES

How many records | fields are being updated? One record, all fields as this is a form's apply button's code.

What is the network environment? All local... Explain?

Are you using CursorAdapters? NO

You are using the lforce parameter which may cause a delay? Does not matter, .T. or .F.
or even tableupdate(.T.)

What buffering mode are you using? 5

Is SET MULTILOCKS set to ON? YES

What is the value of _VFP.AutoYield? .T.

You may want to reference VFP Help on the TABLEUPDATE function for more info. OK...

Thanks, Stanley

 

[vernpace]

What does aa return? .T. or .F.? If it returns .F., then there is no need for anything else to make it work - it failed on the server side and you investigation narrows. What backend are you using? VFP or SQL Server. You are not providing enough information.

 

[vernpace]

VFP.AutoYield should be set to false. That could be the problem.

 

[stanlyn]

Hi Vernpace,

What does aa return? .T. or .F.? Without the inkey(2), it returns .F. With the inkey(2) it returns .T. and nothing else changes.

Question... So, if there is nothing to update, and tableupdate() is run, you are saying it should return .F.? I always thought the true of false return value was whether it was successful in its update. Am I wrong?

What backend are you using? MSSQL

You are not providing enough information. What else do you need?

_VFP.AutoYield settings changes nothing... Same results...

Thanks, Stanely

 

[vernpace]

Ah, SQL Server. Nothing like the process of elimination.

It seems that the transaction is taking too long to commit on the server side. By creating a delay, you are just waiting for the transaction to commit. Slow commits on SQL Server can happen for a few reasons, the main one being that the SQL Server Transaction Log file (LDF) is getting too big. A few questions to narrow it down:

Are you noticing a gradual slowdown in TableUpdates and queries overall?
Do you know the Recovery Model of the database - FULL or SIMPLE?
Who maintains the database - you or a DBA?

Under a Full Recovery Model, all transactions (Inserts, Updates, Deletes) are stored in the transaction log file (LDF). Without proper maintenance, the LDF file gets too big and everything eventually slows down to a grinding halt. The solution is: (1) To create an SQL Server Maintenance Plan which periodically makes logfile backups (DO NOT truncate LDFs) or (2) Switch from FULL to SIMPLE Recovery Model where transaction logs are not used. The whole purpose of the Full Recovery Model is to provide "point-in-time" recovery for disaster scenarios. As a result, SQL Server becomes a lot more complex internally.

If the Simple Recovery Model is already being used, then there are a few other things to check.

 

[stanlyn]

Vernpace,

Are you noticing a gradual slowdown in TableUpdates and queries overall? NO, and lightening fast, even a 80gb database (unrelated, but on same server)

Do you know the Recovery Model of the database - FULL

Who maintains the database - ME
I regularly do backups. I did another just now, just in case.

Is it a fact that this slowdown on the SQL side causes errors on the VFP side, other than slowness? Is so, that is troubling. How would anyone build a rock solid VFP app with that behavior?

Thanks, Stanley

 

[tomk3]

Hello,

just to add :
You can check the size of DB/LDF in Studio with right click on DB - properties - Files (? , the 2nd one)
And after a backup you can clean it up for testing with right click on DB - tasks - shrink - files
(Maintenance plan is better, but if I remember correctly not available in Express editions)

Best regards
tom

If I understand it correctly, you are running SQL on same machine.
Just for sure you may check whether there are other effects slowing down performance like AV-programs, problems with HD (having to try multiple times to write),
disk nearly full, not enought RAM making Windows swap.

 

[stanlyn]

Hi Tom

MSSQL 2016 Standard, not express

MSSQL in NOT on same machine, but same network.

We have plenty of resources. For this project, it is extremely small. I spend a lot of time in SSMS. A full backup took about 5 seconds. The database and log files are on different NVME 3200x gum-stick SSDs.

Thanks for sharing,
Stanley

 

[Chris Miller]

I haven't read all the discussion, but I think you already checked it's not just a timing problem.

Then there is one offensive value that doesn't save, isn't there?

In the past thread I think we didn't figure out what the problem was, in the end. But I think you fail on the focused control not yet having written back its value to the view, that only happens, when it loses focus.

Do you have a toolbar save button? Then you have to know that clicking it does not remove focus from the current control of the form and thus a conflict can arise. The save button of a toolbar has to set focus to the control having focus. Sounds like it's doing nothing, but it will trigger the whole event chain of valid lostfocus, gotfocus.

In the simplest form you would do _screen.activeform.activecontrol.setfocus() before tableupdate(). The only corner case not working that way is when the currently focused control is within a grid. Then this has to be one step more complicated.

You can, by the way, find out what recno is causing the tableupdate problem, when you let it create an array of the problematic recnos, look into the full parameterizaton of tableupdatein the help.

Chriss

 

[tomk3]

Hello,

ok, hardware too weak seems not to be the problem

As Steve suggested, check how many rows are affected. The 0 (in second source) makes tableupdate update all rows.
Does view rv_domain have joins to other tables which get updated, too ?


Does the problem occur when started from another PC or, in a bigger network, on a pc connected via another switch ?

Maybe odbc logging can help, you can set it up in odbcad32 when using a DSN for connecting (not sure how with sqlstringconnect without DSN)
And sql-profiler (change template or events to tuning or duration)



Best regards
tom

 

[vernpace]

Stanlyn,

When you say you do regular backups, are they database backups? Log backups? Both?

 

[vernpace]

Just to reiterate: Database backups and Transaction Log backups are two different things. Since your database is configured for Full Recovery Model, regular Transaction Log backups are critical for proper functioning. Otherwise the LDFs will grow and bog-down performance.

https://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/

https://docs.microsoft.com/en-us/sq...-log-backups-sql-server?view=sql-server-ver15

Since you are the acting DBA, there is much to learn about SQL Server that goes way beyond VFP skills. For instance, do you have a Disaster Recovery Plan for "point-in-time" recovery? This involves first restoring the lastest full database back-up and then rolling forward all the transaction log backups. I'm not putting you on the spot here - I had to learn all these things.

 

[stanlyn]

Chriss,

I haven't read all the discussion, but I think you already checked it's not just a timing problem.

Actually, I think it IS a timing issue, because all I have to do to make it work is add a inkey(.2) and all is well while using the exact same process to test.

Then there is one offensive value that doesn't save, isn't there?

I do not think there is as no changes are made. Start VFP, load form (all looks good), then click apply. Without the inkey(.2) it error with unsaved cursor. Add the inkey(.2) and no errors...

In the past thread I think we didn't figure out what the problem was, in the end. But I think you fail on the focused control not yet having written back its value to the view, that only happens, when it loses focus.

True, I never figured it out and had to move on to more pressing issues (fires), and now months later, I'm revisiting. Note these two projects are internal tools and not for customers, and both are for me to learn this in addition to the tools purpose.

Do you have a toolbar save button? NO toolbars as the nav controls are part of the form

You can, by the way, find out what recno is causing the tableupdate problem, when you let it create an array of the problematic recnos, look into the full parameterizaton of tableupdatein the help.

What is "it"? Not sure how to implement and/or call it...

It is my understanding that tableupdate(0, .T., ''viewname') only updates the currently selected row and not all. For all, change the 0 to a 1.

The 0 (in second source) makes tableupdate update all rows.

Are you sure, see this post, previous comment to Chriss

Does view rv_domain have joins to other tables which get updated, too? NO, single view form...

Does the problem occur when started from another PC or, in a bigger network, on a pc connected via another switch?

Do not know as I'm doing this from a single workstation. No other issues are going on and a restart daily.

Maybe odbc logging can help, you can set it up in odbcad32 when using a DSN for connecting (not sure how with sqlstringconnect without DSN)

And sql-profiler (change template or events to tuning or duration)

I mainly use DSNless for both remotes and SPT.

When you say you do regular backups, are they database backups? Log backups? Both?

Both as well as Marcium for SQL + Exchange...

Since you are the acting DBA, there is much to learn about SQL Server that goes way beyond VFP skills. For instance, do you have a Disaster Recovery Plan for "point-in-time" recovery? This involves first restoring the lastest full database back-up and then rolling forward all the transaction log backups. I'm not putting you on the spot here - I had to learn all these things.

I'm fairly comfortable in MSSQLs SSMS and some other RedGate tools. I too follow Brent Ozar and Paul Randall pubs.

I hope I provided answers to all the questions...

Thanks, Stanley

 

[vernpace]

Cool. No more questions. I have no idea what the problem is. Good luck.

 

[Chris Miller]

Just for sake of completeness: "It" is TABLEUPDATE(). since you only update one record the possibility to create an array of error numbers is unimportant, of course.

I agree with vernspace. No more questions.

Also good luck. But also some more ideas:

I do agree that looking into some logs of SQL Server would perhaps show the issue. For that you could also use Extended Events -

https://docs.microsoft.com/en-US/sq...-events/extended-events?view=sql-server-ver15

or SQL Server Audit

https://docs.microsoft.com/en-US/sq...r-audit-database-engine?view=sql-server-ver15

A friendlier introduction to these and more than the MS docs give is

https://www.sqlshack.com/various-techniques-to-audit-sql-server-databases/

And when googling what Redgate offers on that subject:

https://documentation.red-gate.com/...ng-monitored-servers/enabling-extended-events

Chriss