473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Off Topic - Access to Postgres

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
10 2323
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

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
"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
Randy,

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

Hank

Dec 28 '05 #5
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
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
"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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: wlcna | last post by:
mysql v4.0.16: I had been using mysql with innodb and thought that was fine, until i used it for something requiring a few - perhaps slightly involved - joins, and have now seen the performance...
4
by: Bernardo Robelo | last post by:
Hi, I am interested in migrating Microsoft Access database to Postgres database. But I do not have idea of like initiating. Maybe some tool exists for this problem. Thanks you. Bernardo
4
by: Ashwin Kutty | last post by:
Hi all, I sent this message to the jdbc list and received no response there and so am posting to this list hoping I could get some help here. Thanks in advance. ---------- Forwarded message...
0
by: Jesse | last post by:
Hi all, I need some help with Access database and Postgres Database i am trying to link some Acces tables to Postgres tabels but id doesn't seem to work. I have a Postgres running on a server...
1
by: Hank | last post by:
Hello, We are in the process of migrating our Access back end to Postgres. Our current version is Access 2000. Among other issues, the reason for the change is to pick up some speed by way of...
0
by: Zlatko Matiæ | last post by:
I tried to work with postgres temporary tables from MS Access, but unsuccessfully... I was able to create temporary table by pass-through query, also I succeeded in creating linked table through...
18
by: Joe Lester | last post by:
This thread was renamed. It used to be: "shared_buffers Question". The old thread kind of died out. I'm hoping to get some more direction by rephrasing the problem, along with some extra...
9
by: Reid Priedhorsky | last post by:
Hi folks, I would like to access a remote Postgres server from a Python program in a secure way. Postgres doesn't currently listen to the Internet for connections, and I'd prefer to keep it that...
0
by: NM | last post by:
Hello, I've got a problem inserting binary objects into the postgres database. I have binary objects (e.g. images or smth else) of any size which I want to insert into the database. Funny is it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.