Query Speed

Manuel · External Since: Jun 16, 2006 Posts: 13

Hi,

I have a database which contains tables which are linked (via ODBC) to
tables in my company's data warehouse. The tables vary in size and number of
records, but the larger tables can contain upwards of 2 Million records.

When I query the data, particularly when I join two large tables, or even
one small table with one large table, Access can take up to several minutes
to process the query.

I don't understand why Access is so slow in processing the query when the
tables are linked; I thought linking tables would reduce network traffic and
speed database performance.

Could someone please provide suggestions for how I may be able to speed up
the performance of my queries?

The database itself is only 1.56 MB in size (it’s only a test database as
I’m in the early stages of development. But if the database is this slow
not, I can only imagine when I start adding forms and reports).

Thanks,

Manuel

Jerry Whittle · External Since: Mar 03, 2006 Posts: 1240

Check out SQL pass-through queries. They do the processing out on the
database server and only return the requested records. If you do a standard
select query from within Access, Access needs to get at all the records on
the server then return the proper records.

http://support.microsoft.com/default.aspx/kb/303968
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Manuel" wrote:

> Hi,
>
> I have a database which contains tables which are linked (via ODBC) to
> tables in my company's data warehouse. The tables vary in size and number of
> records, but the larger tables can contain upwards of 2 Million records.
>
> When I query the data, particularly when I join two large tables, or even
> one small table with one large table, Access can take up to several minutes
> to process the query.
>
> I don't understand why Access is so slow in processing the query when the
> tables are linked; I thought linking tables would reduce network traffic and
> speed database performance.
>
> Could someone please provide suggestions for how I may be able to speed up
> the performance of my queries?
>
> The database itself is only 1.56 MB in size (it’s only a test database as
> I’m in the early stages of development. But if the database is this slow
> not, I can only imagine when I start adding forms and reports).
>
> Thanks,
>
> Manuel
>

Dorian · External Since: Jun 09, 2006 Posts: 16

Seeing your queries might help.
Also how fast is your PC? that can be a significant factor.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

"Manuel" wrote:

> Hi,
>
> I have a database which contains tables which are linked (via ODBC) to
> tables in my company's data warehouse. The tables vary in size and number of
> records, but the larger tables can contain upwards of 2 Million records.
>
> When I query the data, particularly when I join two large tables, or even
> one small table with one large table, Access can take up to several minutes
> to process the query.
>
> I don't understand why Access is so slow in processing the query when the
> tables are linked; I thought linking tables would reduce network traffic and
> speed database performance.
>
> Could someone please provide suggestions for how I may be able to speed up
> the performance of my queries?
>
> The database itself is only 1.56 MB in size (it’s only a test database as
> I’m in the early stages of development. But if the database is this slow
> not, I can only imagine when I start adding forms and reports).
>
> Thanks,
>
> Manuel
>

Jeff Boyce · External Since: Nov 04, 2004 Posts: 2342

Manuel

Factors that affect query speed:

* Indexing -- are the underlying tables/views indexed on every field used
in joins, sorts, selection?
* Network -- network speed; NICs; traffic
* functions -- your query involves "local" functions (i.e., functions
that exist in Access but not in your data warehouse)

Any of those apply in your situation?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Manuel" <Manuel.TakeThisOut@discussions.microsoft.com> wrote in message
news:B305C9BB-1BAD-450C-B13D-829660114FEF@microsoft.com...
> Hi,
>
> I have a database which contains tables which are linked (via ODBC) to
> tables in my company's data warehouse. The tables vary in size and number
> of
> records, but the larger tables can contain upwards of 2 Million records.
>
> When I query the data, particularly when I join two large tables, or even
> one small table with one large table, Access can take up to several
> minutes
> to process the query.
>
> I don't understand why Access is so slow in processing the query when the
> tables are linked; I thought linking tables would reduce network traffic
> and
> speed database performance.
>
> Could someone please provide suggestions for how I may be able to speed up
> the performance of my queries?
>
> The database itself is only 1.56 MB in size (it's only a test database as
> I'm in the early stages of development. But if the database is this slow
> not, I can only imagine when I start adding forms and reports).
>
> Thanks,
>
> Manuel
>

Manuel · External Since: Jun 16, 2006 Posts: 13

Thanks for enlightening me in regards to Pass-Through queries; I’d heard
about them before but had never actually used them. Unfortunately, for
whatever reason, the pass-through query is not any quicker than the regular
select query I had. Any other suggestions?

Thanks,
Manuel

"Jerry Whittle" wrote:

> Check out SQL pass-through queries. They do the processing out on the
> database server and only return the requested records. If you do a standard
> select query from within Access, Access needs to get at all the records on
> the server then return the proper records.
>
> http://support.microsoft.com/default.aspx/kb/303968
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Manuel" wrote:
>
> > Hi,
> >
> > I have a database which contains tables which are linked (via ODBC) to
> > tables in my company's data warehouse. The tables vary in size and number of
> > records, but the larger tables can contain upwards of 2 Million records.
> >
> > When I query the data, particularly when I join two large tables, or even
> > one small table with one large table, Access can take up to several minutes
> > to process the query.
> >
> > I don't understand why Access is so slow in processing the query when the
> > tables are linked; I thought linking tables would reduce network traffic and
> > speed database performance.
> >
> > Could someone please provide suggestions for how I may be able to speed up
> > the performance of my queries?
> >
> > The database itself is only 1.56 MB in size (it’s only a test database as
> > I’m in the early stages of development. But if the database is this slow
> > not, I can only imagine when I start adding forms and reports).
> >
> > Thanks,
> >
> > Manuel
> >

Bernard Peek · External Since: Jul 09, 2009 Posts: 3

In message <B305C9BB-1BAD-450C-B13D-829660114FEF RemoveThis @microsoft.com>, Manuel
<Manuel RemoveThis @discussions.microsoft.com> writes
>Hi,
>
>I have a database which contains tables which are linked (via ODBC) to
>tables in my company's data warehouse. The tables vary in size and number of
>records, but the larger tables can contain upwards of 2 Million records.
>
>When I query the data, particularly when I join two large tables, or even
>one small table with one large table, Access can take up to several minutes
>to process the query.

The delay will probably be caused by the way Access queries the central
database, forcing the server to do more work than it should. You are
probably going to have to apologise to the database administrator for
slugging the performance of the database for all of the other users. You
aren't the first person to do this. It is one reason why a lot of
companies don't allow users to connect Access databases to their
production servers.

Others have already mentioned pass-through queries. Bear in mind that
pass-through queries should be written in the dialect of SQL used by the
server and not the version used by Access. You need to understand more
about the server system in order to write code that runs efficiently on
the server.

>
>I don't understand why Access is so slow in processing the query when the
>tables are linked; I thought linking tables would reduce network traffic and
>speed database performance.

It does, mostly. But ODBC uses a subset of SQL that can result in your
Access databases sending commands to the server that it finds difficult
to execute.

What type of database are you connecting to?

--
Bernard Peek