Transactions in VBA

[NauticalGent]

Buongiorno all,

Got a couple of conceptual questions that I would like to hear opinions on if you would care to indulge me.

I am considering changing my text import paradigm from temp table batch processing to temp table loop, in other words parse the text and update the tables one record at a time. I really avoided this because I firmly believe that Access behaves better with batch query updates and that the process is cleaner and more efficient.

However, there are some issues that I did not take into consideration and The solutions are too complex and most of them could be alleviated if I were to do a paradigm shift.

I when the import process runs, 3 tables are updated, all with a PK/FK relationship. Because of this, I am considering using Transactions to accomplish my objective.

Which brings me to my questions:

Should I use stored/parameterized queries or run Parameterized SQL statements to do this?

I have read in many sources that stored queries are much faster. I have also read that if this query is only to be used in once place for one specific purpose, then running an SQL statement is better programming practice and make trouble shooting in the future easier...AND...makes for a tidier navigation pane (??!?)

Any experience/advice would be appreciated. I have no opinion on this, I am just unsure of which method is better.

 

[MarkK]

The main thing to avoid is creating a new query for every line of data you want to insert/update. So avoid this...

   For i = 1 to 1000
      currentdb.execute "INSERT INTO table ( Field1 ) VALUES ( " & i & " )"
   next

...because that SQL is reconstructed from nothing in every iteration of the loop.

An optimization would be...

   With currentdb.createquerydef("", _
      "INSERT INTO table ( Field1 ) VALUES ( p0 )" _
   )
      for i = 1 to 1000
         .parameters(0) = i
         .execute
      next
      .close
   end with

See how that creates a temp parameterized querydef ONCE only, and then just updates the parameter (and executes the query) for every loop?

And then the next optimization is the transaction, which caches the disk writes locally, and only does the final disk write when you commit the transaction, and that code would look like...

   const SQL_INSERT as string = _
      "INSERT INTO table ( Field1 ) " & _
      "VALUES ( p0 ) "

   dbengine.begintrans
   with currentdb.createquerydef("", SQL_INSERT)
      for i = 1 to 1000
         .parameters(0) = i
         .execute
      next
      .close
   end with
   dbengine.committrans

I think that's how that all works.
hth
Mark

 

[NauticalGent]

Sound advice MarkK, always gives me peace of mind when you weigh in. From your answer, I take it that you are an advocate of the SQL vice stored query method?

Thanks again.

 

[MarkK]

Thanks for the kind words.

I don't understand exactly what you mean here...

...the SQL vice stored query method...

...is there an autocorrect word in there somewhere???
Cheers,
Mark

 

[The_Doc_Man]

Gent, you can store an SQL query with a parameter rather than generate one dynamically and it would still work. Therefore I'm maybe slightly confused by your question. Are you talking about an SQL Server backend version of a stored query vice an Access dynamic SQL query or something like that?

 

[Galaxiom]

I think NauticalGent is referring to stored queries in Access.

Once they have been run, they are stored with a query plan and can be run with parameters. Consequently they perform better than an ad hoc VBA query where the values are concatenated into a new query string each time and a new plan generated.

Mark demonstrated an in-between technique by reusing an ad hoc query with parameters one hundred times. In this case the query plan only needs to be regenerated the first time the code runs, then reused for the other 99.

BTW In SQL Server there are other factors. By default each query plan is stored in a cache for reuse when the query runs. The oldest unused stored plans are overwritten when the cache is filled.

A setting called "Optimise for ad hoc queries" causes the server to cache the plan only after its second use, preventing the cache from being filled with once off query plans so the more frequently used plans are not overwritten as often.

 

[NauticalGent]

All, thanks as always for engaging. Galaxiom, as always, you nailed it. My question is coming from two schools of thought:

1. Stored queries perform better because Access doesn't have to convert the SQL statement.

2. If the only purpose of the query is for a specific sub/function then it is a more professional approach to use an SQL statement. The author of the blog I read claims it makes future debugging easier and makes for a cleaner nav pane. Not that it bothers me that much. The only people seeing the nav pane is myself (for now) and anyone worth their stripes can decipher a query.

However, I have come to realize that the more I learn about VBA/programming, the less I know and black and white become shades of gray. (Queue the music!)

MarkK has provided an excellent example that will allow me to satisfy both concerns and Doc has confirmed that if I choose to use the queries I have already made then it will work as well.

Not sure which way I wil go at this point, I have some more reading to do. I have stumbled across some information that says if you do not trap for errors correctly in Transactions, your action queries can give a false return, in other words no records were updated but it did not trigger a "false".

This of course would negate the whole purpose of what I am trying to accomplish so I want to make sure I totally understand (!) what I am doing.

Thanks again guys!