Slow SQL Update

[wasatchconsulting]

I have noticed that saving and deleting records in a MSSQL table is extremely slow. Accessing a record and displaying it is just fine, but saving or deleting records slow down dramatically. In other Clarion apps that I have done, I use PROP:SQL to send SQL code to save and delete records as this improves speed and at times the normal Clarion code does not update the tables correctly if there are calculated fields in the tables. Where would I place SQL code instead of using the Clarion code to update or delete?

Ken Watts
Wasatch Consulting Services

[kevin plummer]

Hi Ken,

I use MSSQL in my apps and I'm not experiencing the problems you mention by leaving the templates to do updates or deletes in clarion code.

I have a lot of hand code and use a combination of prop:sql, clarion file access and sproc's

Make sure you have MARS set (ref docs).

I would also add 2 buttons to a web form and add some server side code to test deleting a record via prop:sql and clarion code. It could be your server setup that is slow.

Cheers,

Kev

[Bruce]

Hi Ken,

If your updates are slow there's a reason for that, and it's probably a good idea for you to figure out what that reason isbefore making hasty decisions about writing custom prop:sql code.

The most likely cause of a slow write is because dictionary validation is slow. And the only cause of that is "Must be in File" validation. If you have one or more fields which are "must be in file" then Clarion will do a Read of that file (or files) in order to assess if the value is indeed in that other file.

There are two solutions to this. Either remove the dictionary validation (which seems to make the most sense, because by hand-coding prop:sql you're doing that anyway) or use ADD and PUT to write the file record, not Access:File.Insert or Access:File.Update as this will also bypass the validation code.

>> at times the normal Clarion code does not update the tables correctly if there are calculated fields in the tables.

I'm not 100% sure what you mean by a calculated field - there are various possible interpretations of this - but it sounds to me that if it's not saving right then you're likely doing something wrong on your side.

Of course you can use prop:sql if you really want to, but I strongly recommend against using that as the line of first resort. It will make your code a lot less portable, and you will have a lot more problems later on especially when it comes to upgrading NetTalk etc. We are using Sql ourselves, and there are plenty of others like Kevin as well, and I've not had reports of Deletes being slow in either web, or normal Clarion apps.

Cheers
Bruce