Best practices for query

L

Larry Kahm

I haven't located any doc that describes this, so if those who know would be
good enough to explain, I"d appreciate it.

Which is considered "better:" Code-behind SELECT blah FROM tblX WHERE... or
a parameterized query?

In the "winning" case, where does the temporary recordset exist - the
front-end or the back-end?

Thanks!

Larry
 
J

Jeff Boyce

Define "better".

For whom? In what circumstances?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
L

Larry Kahm

A customer has requested a "copy" button that would take the contents of an
existing record and flash it into a new record that would then be updated
with new information.

There's a list panel that contains the records and I can put the button on
the top, allow them to select an old record and poof, they're in edit of the
new record.

But... I was wondering if I should put the code to pull the old record's
data into storage using a select query in the CBF or in a query. And the
"better" is responsiveness of the application over the network plus ease of
maintenance for anyone who has to maintain this app in the future.

Thanks!

Larry
 
J

Jeff Boyce

Larry

Any/all ideas are predicated on a complete lack of understanding of your
customer's data structure. "How" questions are generally answered only ...
well, generally, when there's no specific information to go on. It all
starts with the data!

My first reaction/question on reading your description was "why is a record
being copied?" This (again, without anything specific to go on) sounds like
it could be a spreadsheet, in which you'd pretty much have to copy the whole
record to start another similar one. Access isn't a spreadsheet.

"Over the network" could describe a multitude of configurations. Wired or
wireless? LAN or WAN? "Split" database design or "single MDB located on a
network share"? Response time can also vary by the speed of the network,
the settings on the NICs, the "horsepower" of the servers and PCs, indexing
on the data, ...?!

I'd like to be able to provide you a simple (and defensible) magic bullet
answer, mainly because I could make a lot of money consulting telling people
the "right way" to do this. But I'm still not clear what "this" is, and I
still haven't found any one-size-fits-all answers.

Feel free to offer more specific descriptions. The folks in the newsgroups
are not particularly shy about offering their ideas/opinions.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
L

Larry Kahm

Jeff,

Thanks for the response. I'll tackle one issue: the record is an
"agreement" and the verbiage in one version suits the needs of another
project. Most of the fields will change, but the text that describes the
work fits the current need - so someone doesn't have to retype 4 memo
fields' worth of text. And, before you start, I can't normalize an existing
database with over 18,000 records - I inherited this project from someone
who didn't know any better.

My main goal in asking the original question was to find out if building a
recordset in VBA (using code-behind) was more or less effective in terms of
overall Access performan and future maintenance than having a parameter
query that got the record's ID from a form. That's all this was ever
about....

Larry
 
J

Jeff Boyce

Larry

There are so many variables (e.g., network performance, PC/Server
"horsepower", Access version, ?"split" design, indexing, ...) that the best
I can offer is that you will have to test your alternatives on your system
to determine your "best" solution to any performance questions.

Rather than copy all the fields, it sounds like you only need to copy the
text held in 4 memo fields. I'm a little curious whether the 4 memo fields
hold different data, or if they hold "the same" data that exceeded the size
limit for one memo field. If all 4 memo fields are "full", your database
may be/may starting suffering some "bloat".

If this were mine, I'd probably start out adding a procedure that created a
new record, then used an update query to "load" the 4 memo fields -- JOPO
(just one person's opinion).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

(OK, OK, I didn't START with this, I ended with it ... Access is a
relational database. You won't get the best use of the features and
functions if you don't feed it well-normalized data. And just because you
were handed 18,000 records, non-normalized, doesn't mean you couldn't
normalize the data structure and cross-load those 18,000 into a structure
that let you use more of Access' strengths. Again, though, JOPO!)
 
R

Rob Hick

Larry

There are so many variables (e.g., network performance, PC/Server
"horsepower",Accessversion, ?"split" design, indexing, ...) that the best
I can offer is that you will have to test your alternatives on your system
to determine your "best" solution to any performance questions.

Rather than copy all the fields, it sounds like you only need to copy the
text held in 4 memo fields. I'm a little curious whether the 4 memo fields
hold different data, or if they hold "the same" data that exceeded the size
limit for one memo field. If all 4 memo fields are "full", your database
may be/may starting suffering some "bloat".

If this were mine, I'd probably start out adding a procedure that created a
new record, then used an updatequeryto "load" the 4 memo fields -- JOPO
(just one person's opinion).

Good luck!

Regards

Jeff Boyce
Microsoft Office/AccessMVP

(OK, OK, I didn't START with this, I ended with it ...Accessis a
relational database. You won't get the best use of the features and
functions if you don't feed it well-normalized data. And just because you
were handed 18,000 records, non-normalized, doesn't mean you couldn't
normalize the data structure and cross-load those 18,000 into a structure
that let you use more ofAccess' strengths. Again, though, JOPO!)
Click to expand...

can i jump in here with a slightly related question - if i've got a
query that runs very slowly, would investing in a better processor
give significant speed gains?

The detail - I've got a dataset of around 100,000 records that's not
normalised so I'm using a union query to do various calculations and
manipulations and spit out a dataset of around 2 million rows (but
only 5 columns!). This is taking around 40 mins on my Dell GX280
which has a pentium 4. I've increased the memory to 2Gb but I'm
wondering if a new processor (new computer) would also increase the
speed.

Thanks for the advice
Rob
 
J

Jeff Boyce

Rob

By posting way "down thread" like this, you are less likely to get many eyes
on your post. The next time, you'll probably find you get a lot more
responses by starting a new thread.

Here are the things I'd try first, before considering a new PC (unless, of
course, I needed a rationale for getting a new PC <G>):

1. indexes - are all fields used to join, select or sort indexed?
2. query "chaining" - sometimes queries work faster if I break them into
smaller steps and "chain" them together
3. normalize the data, then use Access features/functions to query the
normalized data
4. add RAM

(and if this application is "split", the front-end on your PC, the
"back-end" on your LAN, there are additional checks I'd do...)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
R

Rob Hick

Rob

By posting way "down thread" like this, you are less likely to get many eyes
on your post. The next time, you'll probably find you get a lot more
responses by starting a new thread.
Click to expand...

Good point. I may repost, although your advice is clear enough.
Here are the things I'd try first, before considering a new PC (unless, of
course, I needed a rationale for getting a new PC <G>):
Click to expand...

damn, busted!
1. indexes - are all fields used to join, select or sort indexed? yep

2. query"chaining" - sometimes queries work faster if I break them into
smaller steps and "chain" them together
Click to expand...
might try this - i specifically go for the opposite approach to keep
things manageable
3. normalize the data, then useAccessfeatures/functions toquerythe
normalized data
Click to expand...
the query is to create normlised data
4. add RAM
Click to expand...
done this - 2Gb now (as opposed to 512Mb before)
(and if this application is "split", the front-end on your PC, the
"back-end" on your LAN, there are additional checks I'd do...)
Click to expand...
The database is on a network - would it speed up if i took a copy
local to my desktop?


Thanks again,
Rob
 
Top