A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query Speed



 
 
Thread Tools Display Modes
  #1  
Old October 22nd, 2009, 05:48 PM posted to microsoft.public.access.tablesdbdesign
Manuel
external usenet poster
 
Posts: 46
Default Query Speed

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

  #2  
Old October 22nd, 2009, 06:24 PM posted to microsoft.public.access.tablesdbdesign
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Query Speed

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

  #3  
Old October 22nd, 2009, 07:24 PM posted to microsoft.public.access.tablesdbdesign
Dorian
external usenet poster
 
Posts: 542
Default Query Speed

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

  #4  
Old October 22nd, 2009, 07:34 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Query Speed

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" wrote in message
...
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



  #5  
Old October 22nd, 2009, 07:56 PM posted to microsoft.public.access.tablesdbdesign
Manuel
external usenet poster
 
Posts: 46
Default Query Speed

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

  #6  
Old October 25th, 2009, 09:42 PM posted to microsoft.public.access.tablesdbdesign
Bernard Peek[_3_]
external usenet poster
 
Posts: 42
Default Query Speed

In message , Manuel
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.