By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,301 Members | 3,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,301 IT Pros & Developers. It's quick & easy.

Off Topic - Access to Postgres

P: n/a
We have just recently migrated the data from our Access 2000
backend to Postgres. All forms and reports seem to run correctly but,
in many cases, very slowly. We do not want to switch over until we
can speed things up. We would like to start implementing Stored
Procedures so we can do Server-Side processing.
Can anyone recommend a book that would help us learn how to
use sprocs or pass-through queries? I apologize if my terminology is
incorrect. I am in the position of not quite knowing how to ask the
right questions.
Thanks,
Hank Reed

Dec 28 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
On 27 Dec 2005 18:00:46 -0800, "Hank" <ha********@aol.com> wrote:
We have just recently migrated the data from our Access 2000
backend to Postgres. All forms and reports seem to run correctly but,
in many cases, very slowly. We do not want to switch over until we
can speed things up. We would like to start implementing Stored
Procedures so we can do Server-Side processing.
Can anyone recommend a book that would help us learn how to
use sprocs or pass-through queries? I apologize if my terminology is
incorrect. I am in the position of not quite knowing how to ask the
right questions.
Thanks,
Hank Reed


When you have a speed problem with client/server, using pass-through queries
and stored procedures is not usually the best answer, and may not help at all.
Doing that is, however, a good way to make your program 4 or 5 times harder to
develop and maintain.

For Access client/server performance problems, first make sure your
application is designed to work well with client/server and to work well with
how Access works with servers.

General client/server issue:
- Don't return unfiltered, unaggregated results, and don't return more than a
couple hundred result rows at a time.

Access issue:
- Make sure every table has a primary key.

Start with those, and see how it goes.
Dec 28 '05 #2

P: n/a

Steve Jorgensen wrote:
On 27 Dec 2005 18:00:46 -0800, "Hank" <ha********@aol.com> wrote:
We have just recently migrated the data from our Access 2000
backend to Postgres. All forms and reports seem to run correctly but,
in many cases, very slowly. We do not want to switch over until we
can speed things up. We would like to start implementing Stored
Procedures so we can do Server-Side processing.
Can anyone recommend a book that would help us learn how to
use sprocs or pass-through queries? I apologize if my terminology is
incorrect. I am in the position of not quite knowing how to ask the
right questions.
Thanks,
Hank Reed
When you have a speed problem with client/server, using pass-through queries
and stored procedures is not usually the best answer, and may not help at all.
Doing that is, however, a good way to make your program 4 or 5 times harder to
develop and maintain.

Steve,
Thanks for the advice. I have always used PKs in every
table. My novice understanding of a database like Postgres is that we
would ONLY get the filtered records. I have been led to believe (or
convinced myself) that a stored procedure would select and return only
the records of interest, thus saving bandwidth and transfer time, in
general. If sprocs and pass through queries are not the solution then
what would you recomend?
Thanks again,
Hank Reed For Access client/server performance problems, first make sure your
application is designed to work well with client/server and to work well with
how Access works with servers.

General client/server issue:
- Don't return unfiltered, unaggregated results, and don't return more than a
couple hundred result rows at a time.

Access issue:
- Make sure every table has a primary key.

Start with those, and see how it goes.


Dec 28 '05 #3

P: n/a
"Hank" <ha********@aol.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Steve Jorgensen wrote:
On 27 Dec 2005 18:00:46 -0800, "Hank" <ha********@aol.com> wrote:
We have just recently migrated the data from our Access 2000
backend to Postgres. All forms and reports seem to run correctly but,
in many cases, very slowly. We do not want to switch over until we
can speed things up. We would like to start implementing Stored
Procedures so we can do Server-Side processing.
Can anyone recommend a book that would help us learn how to
use sprocs or pass-through queries? I apologize if my terminology is
incorrect. I am in the position of not quite knowing how to ask the
right questions.
Thanks,
Hank Reed


When you have a speed problem with client/server, using pass-through queries and stored procedures is not usually the best answer, and may not help at all. Doing that is, however, a good way to make your program 4 or 5 times harder to develop and maintain.

Steve,
Thanks for the advice. I have always used PKs in every
table. My novice understanding of a database like Postgres is that we
would ONLY get the filtered records. I have been led to believe (or
convinced myself) that a stored procedure would select and return only
the records of interest, thus saving bandwidth and transfer time, in
general. If sprocs and pass through queries are not the solution then
what would you recomend?
Thanks again,
Hank Reed

Frankly, I'm a bit puzzled by that portion of Steve's comments. I certainly
agree with the part about not returning unfiltered, unaggregated results.
However, I find that using views, stored procedures and pass through queries
are among the most effective ways of accomplishing that.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

For Access client/server performance problems, first make sure your
application is designed to work well with client/server and to work well with how Access works with servers.

General client/server issue:
- Don't return unfiltered, unaggregated results, and don't return more than a couple hundred result rows at a time.

Access issue:
- Make sure every table has a primary key.

Start with those, and see how it goes.


Dec 28 '05 #4

P: n/a
Randy,

Thanks for the encouragement.
Do you have any code snippets you could pass along?

Hank

Dec 28 '05 #5

P: n/a
On 28 Dec 2005 03:37:26 -0800, "Hank" <ha********@aol.com> wrote:

Steve Jorgensen wrote:
On 27 Dec 2005 18:00:46 -0800, "Hank" <ha********@aol.com> wrote:
> We have just recently migrated the data from our Access 2000
>backend to Postgres. All forms and reports seem to run correctly but,
>in many cases, very slowly. We do not want to switch over until we
>can speed things up. We would like to start implementing Stored
>Procedures so we can do Server-Side processing.
> Can anyone recommend a book that would help us learn how to
>use sprocs or pass-through queries? I apologize if my terminology is
>incorrect. I am in the position of not quite knowing how to ask the
>right questions.
>Thanks,
>Hank Reed


When you have a speed problem with client/server, using pass-through queries
and stored procedures is not usually the best answer, and may not help at all.
Doing that is, however, a good way to make your program 4 or 5 times harder to
develop and maintain.

Steve,
Thanks for the advice. I have always used PKs in every
table. My novice understanding of a database like Postgres is that we
would ONLY get the filtered records. I have been led to believe (or
convinced myself) that a stored procedure would select and return only
the records of interest, thus saving bandwidth and transfer time, in
general. If sprocs and pass through queries are not the solution then
what would you recomend?
Thanks again,
Hank Reed


All you need is to make sure you use queries to aggregate and-or filter your
results. In some cases, a query may fail to optimize well and pass the SQL
correctly to the back-end for processing, and then you may have to tweak it to
get it right, or you may even have to create a server-side view, but as soon
as you use an SP, you're opening a can of worms, so use that as a last resort.

Note that using form filtering is -NOT- the same as using a query for
filtering, and does not cause the filtering to be done by the server as query
would.
Dec 28 '05 #6

P: n/a
On Wed, 28 Dec 2005 14:02:58 GMT, "Randy Harris" <ra***@SpamFree.com> wrote:
"Hank" <ha********@aol.com> wrote in message
news:11**********************@z14g2000cwz.googleg roups.com...

Steve Jorgensen wrote:
> On 27 Dec 2005 18:00:46 -0800, "Hank" <ha********@aol.com> wrote:
>
> > We have just recently migrated the data from our Access 2000
> >backend to Postgres. All forms and reports seem to run correctly but,
> >in many cases, very slowly. We do not want to switch over until we
> >can speed things up. We would like to start implementing Stored
> >Procedures so we can do Server-Side processing.
> > Can anyone recommend a book that would help us learn how to
> >use sprocs or pass-through queries? I apologize if my terminology is
> >incorrect. I am in the position of not quite knowing how to ask the
> >right questions.
> >Thanks,
> >Hank Reed
>
> When you have a speed problem with client/server, using pass-throughqueries > and stored procedures is not usually the best answer, and may not helpat all. > Doing that is, however, a good way to make your program 4 or 5 timesharder to > develop and maintain.
>

Steve,
Thanks for the advice. I have always used PKs in every
table. My novice understanding of a database like Postgres is that we
would ONLY get the filtered records. I have been led to believe (or
convinced myself) that a stored procedure would select and return only
the records of interest, thus saving bandwidth and transfer time, in
general. If sprocs and pass through queries are not the solution then
what would you recomend?
Thanks again,
Hank Reed

Frankly, I'm a bit puzzled by that portion of Steve's comments. I certainly
agree with the part about not returning unfiltered, unaggregated results.
However, I find that using views, stored procedures and pass through queries
are among the most effective ways of accomplishing that.


The problem is that Access was never designed to work very well with stored
procedures and pass-through queries. Views can be used effectively in
combination with Access queries that add filtering, but make sure to have a
virtual primary key on the view, and specify the "key" when making the link.
Also, with PostgreSQL, the view will not be editable unless you define
"instead of" triggers to make that happen. That's not a problem, it's just
something you need to know and do.

The reason pass-through queries are a problem for Access (and stored
procedures can only be called via pass-through queries) is that Access cannot
implicitly pass arguments to them, nor edit the records they return. This
means that for a single case, instead of just writing one parameter query, you
may have to create a saved pass-through query, and rewrite its definition from
code before each time you use it, and you may furthermore have to add another
add/edit form to add or edit records since you can't edit directly through the
bound form. Now, realize that you'll have to requery the viewing form after
each of these operations to refresh the data. Be careful about that saved
pass-through query. Are you using it from only one place? Are you making
sure only one instance of one copy of the front-end will be used at a time?

Frankly, you just have to use Access in a much less Access-like way and do a
lot more work as soon as you employ stored procedures if they will be used
behind forms, reports, or combo/list controls.
Dec 28 '05 #7

P: n/a
"Randy Harris" <ra***@SpamFree.com> wrote in message
news:mW*******************@newssvr14.news.prodigy. com...
Frankly, I'm a bit puzzled by that portion of Steve's comments. I certainly
agree with the part about not returning unfiltered, unaggregated results.
However, I find that using views, stored procedures and pass through queries
are among the most effective ways of accomplishing that.


This is true. However; it is important to know that Views and Stored Procedures
are not *required* to get the server to process a SQL request and only return
the results. They can be ways to *guarantee* that this happens, but standard
Access queries against ODBC linked tables will usually pass the statement to the
server for processing as well.

The key is to use Views and SPs when there is a benefit and not simply to use
them exclusively believing that it is "the only correct way" to use a server
back end.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Dec 28 '05 #8

P: n/a
On Wed, 28 Dec 2005 07:07:28 -0800, Steve Jorgensen <no****@nospam.nospam>
wrote:

....
Frankly, I'm a bit puzzled by that portion of Steve's comments. I certainly
agree with the part about not returning unfiltered, unaggregated results.
However, I find that using views, stored procedures and pass through queries
are among the most effective ways of accomplishing that.


The problem is that Access was never designed to work very well with stored
procedures and pass-through queries. Views can be used effectively in
combination with Access queries that add filtering, but make sure to have a
virtual primary key on the view, and specify the "key" when making the link.
Also, with PostgreSQL, the view will not be editable unless you define
"instead of" triggers to make that happen. That's not a problem, it's just
something you need to know and do.

The reason pass-through queries are a problem for Access (and stored
procedures can only be called via pass-through queries) is that Access cannot
implicitly pass arguments to them, nor edit the records they return. This
means that for a single case, instead of just writing one parameter query, you
may have to create a saved pass-through query, and rewrite its definition from
code before each time you use it, and you may furthermore have to add another
add/edit form to add or edit records since you can't edit directly through the
bound form. Now, realize that you'll have to requery the viewing form after
each of these operations to refresh the data. Be careful about that saved
pass-through query. Are you using it from only one place? Are you making
sure only one instance of one copy of the front-end will be used at a time?

Frankly, you just have to use Access in a much less Access-like way and do a
lot more work as soon as you employ stored procedures if they will be used
behind forms, reports, or combo/list controls.


....More...

Another problem with pass-throughs:
- Since you're embedding parameters in SQL text yourself, you are now
responsible for doing all the things a query parameter would normally handle
for you behind the scenes like the difference between a value and Null, what
to do with strings that contain quote marks and other special characters, how
the server expects dates values to be delimited, etc."

More issues with server-side objects in general:
- To the extent that your application relies on server-side objects, you have
now hitched your train to that server. You can no longer demo your app using
an MDB back-end nor switch to a different server back-end without some amount
of rewriting. If you can keep all the queries in Access, you're not stuck.

Personally, I always end up using a few stored procedures and views with
Access, but I use them as a surgical tool when necessary, and not just because
they're supposed to be "better" than using Access queries.

There are situations where doing -everything- with stored procedures does make
sense such as when there will be more than one front-end, but Access just
doesn't make the friendliest front-end to those applications for reasons
described above. Also, most such applications would be better designed as
3-tier rather than relying on server-side objects, and for 3-tier
applications, Access is almost no use whatsoever.
Dec 28 '05 #9

P: n/a
Steve Jorgensen wrote:
The reason pass-through queries are a problem for Access (and stored
procedures can only be called via pass-through queries) is that Access cannot
implicitly pass arguments to them, nor edit the records they return. This
means that for a single case, instead of just writing one parameter query, you
may have to create a saved pass-through query, and rewrite its definition from
code before each time you use it


These are certainly limitations, but they are not, for me, anyway, a
problem with Access. With respect to stored procedures, in Oracle
PL/SQL, arguments are passed via the SQL statements. In both Jet and
Oracle back ends, I always write my SQL dynamically anyway (see my last
paragraph here) and contrary to what I think you implied in your initial
response to Hank, I don't think writing Jet SQL (for Jet BE or c/s
linked table approaches) nor PTQ SQL in VBA makes an app more difficult
to manage or increses development time significantly.

Apart from the above disagreement, though, I think your initial advice
to Hank is spot on and as Rick Brandt once scolded me 8) (and you may
have joined him) if you have a well tuned and appropriately developed
Access application as you're encouraging Hank to make sure he has, the
linked table approach should be just as valid method of achieving the
client server application as a PTQ approach. And certainly much easier
with respect from porting the app from a Jet back end to a different
back end.

The problem can be, however, that what works for Jet may not be
optimized in the c/s back end. Though I cannot speak for Postgres, the
issue of something working well in Jet, but not in a c/s environment has
often been my experience with Oracle back ends. Indeed, with very
complex select statements (for example, multiple inline subqueries in
the from clause along with multiple subqueries in the select clause and
corelated and non-corelated where subqueries), I've found it's usually
very much better and faster to perform the selects in the native server
SQL dialect, in my case, Oracle. As well, especially if the developer
is developing an app in support of an existing and well established c/s
(Oracle or other) app and modification of the server tables is not an
option *AND* the existing app may not be particularly well indexed,
linked tables can drop whole columns in certain Jet select queries (I
believe Larry Linson mentions this issue in one of his presentations on
his tripod site, though I think that may have been with MS SQL - it's
been a while since I've looked at his site, so my apologues if I'm
talking out my butt! 8) ).

There may well be similar issues with Postgres, but Hank should be aware
I've not worked with Postgres.

One very positive thing with using PTQ methods is that one needn't worry
about security in the same way one would if linked tables were present.
With respect to making the program more difficult to develop and
maintain, I think that depends on the developer's approach. In both Jet
and Oracle I tend to write VBA to create dynamic SQL, so there's really
not much of a difference. Include the required ODBC connect strings as
constants, so they are written once in a standard module, make sure
connect strings that allow anything other than read only access are not
saved in querydefs then save as an mde and security is not a worry when
you don't have linked tabledefs saved anywhwere.

Additionally, I find writing joins in Oracle's theta join style versus
the Jet Ansi style infinitely easier to do. Unfortunatey, for Hank,
this is probably irelevant as I seem to recall that Postgres uses ansi
joins (my reference for this is the very good overview book, _SQL in a
Nutshell_ by Kline). It really pisses me off that the folks involved
with the various ANS/SQL standards (89, 92 and 99 - is there anything
later?) recommend the ansi joins. Consider the following example (with
no criteria other than join specification) which has an inner and an
outer join. I just find this so, so much easier to write intuitively in
Oracle. Especially so if my SQL writing function in VBA will need to
include a variable number of tables based on a user's choices!

ANSI Join Method (Jet)

SELECT
<snip>
FROM
(tbl1 inner join tbl2 on tbl1.1_pk = tbl2.2_1_fk)
left join tbl3 on tbl1.1_pk = tbl3.3_1_fk

THETA Join Method (Oracle)

SELECT
<snip>
FROM
tbl1, tbl2, tbl3
WHERE
tbl1.1_pk = tbl2.2_1_fk and
tbl1.1_pk = tbl3.3_1_fk (+)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Dec 28 '05 #10

P: n/a
Steve Jorgensen <no****@nospam.nospam> wrote in
news:vc********************************@4ax.com:
Personally, I always end up using a few stored procedures and
views with Access, but I use them as a surgical tool when
necessary, and not just because they're supposed to be "better"
than using Access queries.


And the dirty little secret behind all of this is that Jet is
actually remarkably successful at getting it right, and hands off to
the server almost everything it can.

Microsoft can't promote this aspect of Access/Jet, because it tends
to contradict the party line about Access/Jet/ADO that has been in
place since the introduction of A2K. And it makes the whole idea of
ADPs look as near useless as they actually are.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Dec 28 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.