Use Transaction Property

[wm2005]

Afternoon all...

Does anyone have any 'best practices' they would like to share in terms of using action queries?

In other words, when should you, and should you not wrap your query in a transaction by setting this property?

Will

 

[Crowley16]

I never use it... am I doing something wrong?

--------------------
Procrastinate Now!

 

[wm2005]

Hi Crowley.

I couldnt say. Im just interested in getting the maximum performance out of my database.

As such I always run my select queries in 'snapshot' mode wherever I dont need to update the returned records, and set locking to 'no locks' if possible.

And for action queries, Im thinking they only need to be in a transaction where more than one record is affected, and only then where its imperative that all records succeed or none at all. ie. partial success of the action is not an option.

Will

 

[jsteph]

In most cases, it's not a big deal. You can't really 'roll back' a user-interface query anyway unless there's an error, which is apparently what this is for--but whether set to Yes or No, you still get the 'Are you sure...' prompt.

So if transaction was Yes then your benefit would be that if there were an error, there'd be yet another prompt and you'd get the chance to save or not save changes that were made before the error.

If you have a huge amount of records, then of course setting to No will save some time, but for most ad-hoc queries it's just not that important.

Snapshot mode, is important, and you should always use that especially on combobox sources that won't be updated, since I've seen many a slow-responding combobox get a big boost by changing from dynaset to snapshot.
--Jim