Question on Pass-through Query


P

Please Help

Hello all,

I am in the process of creating a SQL Server (SQL) as a back-end database
and an Access database as a front-end database. Access will have all the
queries, and SQL will have all the tables.

From what I learned, by creating a pass-through query in Access, it will
force to have the transactions processed in SQL Server, instead of in Access,
which will make the query runs faster.

Do I have to make every query in Access as pass-through query, or I just
have to make a pass-through query on the linked SQL table. Then the
subsequent queries that use the pass-through query will be the same idea as
processing in SQL Server.

I am still new to creating a FE Access with SQL BE. If you have other
suggestions to improve the processing in Access, please feel free to share
with me.

Thanks.
 
Ad

Advertisements

B

Bob Barrows

Please said:
Hello all,

I am in the process of creating a SQL Server (SQL) as a back-end
database and an Access database as a front-end database. Access will
have all the queries, and SQL will have all the tables.

From what I learned, by creating a pass-through query in Access, it
will force to have the transactions processed in SQL Server, instead
of in Access, which will make the query runs faster.

Do I have to make every query in Access as pass-through query, or I
just have to make a pass-through query on the linked SQL table.

That's an oxymoron.
No linked tables can be involved in a pass-through query. With a
pass-through, you are passing a sql statement to the remote database
where it runs in that database. This means that the sql statement can
only refer to tables, fields, views, stored procedures, etc that exist
in that remote database. A linked table exists in the local database. If
you tried to create a pass-through query referring to the linked table's
name (as seen in the Access database window - it will usually look
something like dbo_tablename), the remote database will raise an error.
Then
the subsequent queries that use the pass-through query will be the
same idea as processing in SQL Server.

I don't understand this sentence.
I am still new to creating a FE Access with SQL BE. If you have other
suggestions to improve the processing in Access, please feel free to
share with me.
There are some recommendations in online help as I recall, but perhaps
you should read Mary Chipman's book to gain a better understanding. I
can't remember the title, but it's easily found at Amazon.
 
A

Albert D. Kallal

From what I learned, by creating a pass-through query in Access, it will
force to have the transactions processed in SQL Server, instead of in
Access,
which will make the query runs faster.

Correct. However, for just a plain jane table, for the most part you will
not see a increase in perforamnce.

However, if there is joins, or aggruate stuff (like group by, sums etc),
then the peformance savings can be significant as the totaling and sum
values are done server side, and THEN the results are send down the network
pipe.
Do I have to make every query in Access as pass-through query

No, in fact any form or reocrdSet that needs to edit data cannot be
pass-though. Pass-though query are read only.

So,for things reports, or perahps a seach type screen, then these work well
as a pass-though.
, or I just
have to make a pass-through query on the linked SQL table. Then the
subsequent queries that use the pass-through query will be the same idea
as
processing in SQL Server.

You can make a query on a pass-through query, but as mentioned, it will
still be read only. Because of this, it really don't make a whole lot of
sense to build a query on a query.....you might as well just make that 2n
query pass-though also.
I am still new to creating a FE Access with SQL BE. If you have other
suggestions to improve the processing in Access, please feel free to share
with me.

SQL server is indeed a high performance system, and also a system that can
scale to many many users.

If you write your application in c++, or VB or in your case with ms-access,
in GENERAL the performance of all of these tools will BE THE SAME.

In other words...sql server is rather nice, and is a standard system used in
the IT industry.

However, before you convert..how well does your applciton run now?

We often see posts here that a application is too slow with one user. If the
application is too slow with one user..then what can one expect when they
try and run 10 users. That is now 10 times the requirements..

The other issue is how well is the database setup?

Further..how well are the forms designed?

How well does the application work with 5 users..and then when you jump to
10 users...how much a slow down do you notice?

A few things:

Having a table with 75k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), and no sql server, then
the performance of that system should really have screamed.

Before Microsoft started "really" selling sql server, they rated JET could
handle easily 50 users. We have credible reports here of people
running 100 users. however, in those cases everything must be
"perfect".

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I dont think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 75k records range.

So, with my 5 users..I see no reason why I cant scale to 15 users with
such small tables in the 75,000 record range.

If the application did not perform with such small tables of only 75k
records..then upsizing to sql server will do absolute nothing to fix
performance issues. In fact, in the sql server newsgroups you see weekly
posts by people who find that upgrading to sql actually slowed things down.
I even seem some very cool numbers showing that some queries where actually
MORE EFFICIENT in terms of network use by JET then sql server.

My point here is that technology will NOT solve performance problems.
However, good designs that make careful use of limited bandwidth resources
is the key here. So, if the application was not written with good
performance in mind..then you kind are stuck with a poor design!

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record). So, at this point, you
really will NOT notice any performance difference by upgrading to sql
server. There is no magic here.

Sql server is a robust and more scalable product then is JET. And, security,
backup and host of other reasons make sql server a good choice.
However, sql server will NOT solve a performance problem with dealing
with such small tables as 75k records

Of course, when efforts are made to utilize sql server, then
significant advances in performance can be realized
(more so then when using jet)

I will give a few tips...these apply when using ms-access as a file
share (without a server), or even odbc to sql server:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.members.shaw.ca/AlbertKallal/Search/index.html

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records dont apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Dont
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

* Use caution with quires that require more then one linked table

This ONLY applies to odbc to sql server...you CAN and are FREE to do this
with a mdb JET file share..and also with ADP projects to sql server.

When you use
ODBC, one table could be on the corporate server, and the other ODBC might
be a FoxPro table link 3 computers from the left of you. As a result..JET
has a real difficult time joining these tables together..and JET can not
assume that the two tables are on the same box..and thus have the "box" join
the tables. Thus,while jet does it best..these types of joins can often be
real slow. In fact, joins on linked talbes to sql server useally works quite
well. However, some might be slow when you test them. The simple
solution in these cases is to change the query to
a view..and link to that. This is the least amount of work, and means the
joins occur on the server side. This also applies to combo boxes. Most
combos boxes has sql embedded in them. That sql has to be processed, and
then thrown to a linked odbc table. This is a bit sluggish. (a form can have
maybe one, or 2-3 combos..but after that ..it will start to load slow). So,
remove the sql from the combo box, build a view..and link the combo box
direct to that view

If you are using a ADP access project, the above points about the joins
with more then one table does NOT apply..since all queries execute
on the sql server side. (perhaps you could consider converting the
application to a ADP project. It would at least force you to make
most sql run on the server side. However, ODBC is just fine
and is usually EQUAL in performance if you do things right).

(and, for existing applications mdb linked to sql server is
less work.)

Of course, if you do have sql with more then one table..then pass-though
is the best here.


* You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice,a and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right. If you use a "where" clause on a bound form, then
only the records in the "where" clause come down the pipe So, views
or pass-though will NOT help this speed issue (and, you don't want
pass-through for a form...as it will be read-only).

* Large loading of combo boxes. A combo box is good for about 100
entries. After that..you are torturing the user (what..they got to look
through 100s of entries). So, keep things like combo boxes down
to a min size. This is both faster..and MORE importantly it is
kinder to your users.

After all, at the end of the day..what we really want is to make
things easy for the users...and treat them well.. It seems that
treating the users well, and reducfing the bandwith
(amount of data) goes hand in hand. So, better applications
treat the usres well..and run faster! (this is good news!)
 
P

Please Help

Hi Albert,

Thanks for taking the time to provide me the valuable information.

The reason that I am creating a SQL Server database (let’s call it “SQLâ€),
instead of Access database, is because of the size of data. The size of data
is at least 10 GB and 50 millions of records. As far as the # of users will
be using, at this point, I don’t know how many yet.

In the past, I created a front-end (FE) Access database that links to the
SQL that created by others. That SQL did not have much data as the one that
I am creating. Therefore, I use Access queries for everything. In addition,
since the SQL was created by others, I didn’t have privileges as I am now to
create View, Stored Procedures, etc. in SQL.

Since I am still new to SQL and connection between SQL and Access, I would
like to learn the ways of improving the performance when the users use the
Access FE.

From your experience, what are the best ways of designing my Access FE for
better performance? Basically, Access FE is where all the actions will
happen, and SQL BE is mainly for data storage. Based on what I read, I
should create Views in SQL, in place of queries in Access. Then I link the
Views in Access for everything.

Again, thanks for the information. Any additional information will be
appreciated.
 
Ad

Advertisements

A

Albert D. Kallal

Based on what I read, I
should create Views in SQL, in place of queries in Access. Then I link
the
Views in Access for everything.


I much summed it up in my post. For the most part you not need to just out
of the blue start converting queries in ms-access to views. It not needed
and for a good many quires it will not help performance.

As mentioned, when the query has aggregate functions, and joins, then a view
can really help. Think of a database having 200,000 products (records), and
you want a query that groups sales buy the north part of the country, and
the south. If you do a group by with a count for north and sound you going
to get two rows returned. That is VERY little data.

In some cases query built in ms-access will pull too many records and thus
you want to use a view. So as mentioned, complex joins and queries with
aggregate functions (sums/counts) are good candidates for views. However,
most sql queries based on linked tables will be just fine and you not see
any improvements in performance because you convert them to a view. So, no,
you don't have to just wholesale change queries to views, and often it will
not make any difference at all.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top