To Query, Or not to Query

[perrymans]

Basically my question is simple. Should I convert from queries to SQL? Is SQL faster than queries? For populating Combo Boxes? Select queries? And so forth. I know I can accomplish more with SQL, but right now I am concerned with speed.

Thank you. Sean.

 

[UncleJack]

. . . whether 'tis nobler to suffer the slings and arrows of outrageous SQL or to take arms against named queries and by opposing end them . . . . 'tis a consumation devoutly to be wished!

SQL statements run slower than named queries because Access has to compile the SQL into a query each time it's run. Named queries are compiled the first time they run and remain that way in the database afterwards. When speed is important, such as filling combo boxes that are accessed frequently, it's generally better to use named queries.

Sometimes, however, it's not possible to used named queries such as when the precise SQL depends on parameters developed at run time. Otherwise, it's a balancing act between filling your database with scads of simple queries or burying them as SQL in the code.


Uncle Jack

 

[UncleJack]

Whoops! Guess I typed the response in the wrong place.

whether 'tis nobler to suffer the slings and arrows of outrageous SQL or to take arms against named queries and by opposing end them . . . . 'tis a consumation devoutly to be wished!

SQL statements run slower than named queries because Access has to compile the SQL into a query each time it's run. Named queries are compiled the first time they run and remain that way in the database afterwards. When speed is important, such as filling combo boxes that are accessed frequently, it's generally better to use named queries.

Sometimes, however, it's not possible to used named queries such as when the precise SQL depends on parameters developed at run time. Otherwise, it's a balancing act between filling your database with scads of simple queries or burying them as SQL in the code.


Uncle Jack

 

[perrymans]

Thanks UncleJack, Do you feel it wise to use static lists for combo boxes whenever possible? For things such as the unit of issue (EA, BX, RO) in a stock environment? Or a table, linked to the front end, used to build a query, then populate a combo box. It would seem the static values would be the way to go, but I don't want to miss anything. Sean.

 

[UncleJack]

Static values can be either a value list or a query based on a table. I don't think it really makes any difference. Obviously, if you get above 10 or 12 choices or if the static values are subject to periodic change, then you should use a table based query. If you're splitting your database, make sure the static value tables are in each front end as otherwise you're just adding to network traffic for no benefit.

Uncle Jack

 

[perrymans]

Uncle Jack,

You may have enlightened me to a design error in my dbase. Your statement on making sure the static tables are in all front ends, leads me to believe that it is not always necesary (or wise I should say) to put all tables in the Backend. I currently have all of my tables in the backend. Is this slowing me down unnecessarily? Should I move the static tables to the front end? Converting to .mde doesn't affect having tables in the front end does it? Sean.

 

[UncleJack]

It's best to have the static data in each front end. That way each station doesn't have to use the network to fill combo boxes to select school grades, states, or other stuff that isn't going to change. Conversion to MDE just compiles all the code in the DB and isn't impacted by the location of tables. I'm sure this will help in speeding up your application(s) although don't look for dramatic improvement.

Uncle Jack