Topics Search

Slow query - How to speed up?

Slow query - How to speed up?
Views: 17
I have the below query running and it is taking forever. I'm not quite sure why as my other queries run with reasonable speed, so I cannot assume it is a network issue. [LAST_NAME] & [EMPLOYEE_NUMBER] are indexed.

The table does have 945k records in it and is housed in a back end DB. I realize that is a lot of records, but this seems to be running abnormally slow.

Here is why I ask.I have a form that provides a search function which is based on this query. The query is not called upon until the user needs to search for an employee.

However, for some reason unknown to me the query runs every time the form is opened. I DO NOT have the query running at the open/load of the form. The only time the query is to run is when the user clicks on a search button.
Sponsored Links:

More topics

 

Query Speed

Preview
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).
 

Query runs slow

Preview
I up sized my 2003backend to SQL 2008. I kept my queries n access frontend. Now my update and append queries run really slow. What can I do. Should I look into converting to stored proc or pass through queries. Would this speed up performance.
 

Floated between queries with slow speed

Preview
I like to make a query for extracting latest (max) rev of one list like below. But for catching this result I have made 3 queries

that are related
(one by one). So I have floated between queries with slow speed.

LIST:

DocumentNo
title
REV
DATE
TRANS DW-1013
test
12-Dec-09
TT-3799 DW-1013
test
13-May-10
TT-5923 DW-1013
test
15-Apr-11
TT-5943 DW-1013
test
1-Jan-12
TT-6031

Result:

DocumentNo
TITLE
REV
DATE
TRANS DW-1013
TEST
1-Jan-12
TT-6031

For this result if have made 3 queries that are related.

1- First query for finding max rev of the list.(group and aggregate function)

2- Second query for finding max date that has been made with relating first query and list with joining (doc no, rev)

3- Third query for finding max transmittal , that has been made with relating second query and list .

So slow of third query that has my result is slow (some times around 17 second) , please help how I can make better query
 

Forms slow to open

Preview
Some of the forms in my database take up to 5 minutes to open. I do have subforms in some of them (not all of them). Is there anything I can do to speed them up? Once we're in, they move pretty quickly, just opening is so slow.
 

Slow Database

Preview
I have a access database with a MYSQL back end running on our own server. It runs very well on one PC but on another PC in the same office its runs very slow

What can I try to speed it up
 

Inexplicably slow query

Preview
I have a huge query that joins ten different tables and returns around 100 columns.

When I execute the query, it takes less than a minute to run.

If I create a "make table query" with the same, I.e. I just want to store the results of the query in a permanent table, it takes forever (been running for 30 mins, looks like Access is frozen).

I've indexed most of the join fields to speed up the query.
 

Shared Over A Network

Preview
Network Speed

I Have A Access Program Split
2 Pc's One With Fe And Back End

The Other Just Front End Link But The Speed Is Not Good On The Split.

Tried Mapping Still Slow
 

Database Access Speed

Preview
I'm accessing my MS ACCESS 2010 database via a VPN connection. The accessibility of the database and working on it is however very slow. Any suggestions on how to improve the speed of access
 

Slow Query

Preview
I have a couple of Select queries which are based on Tables Linked from 3 other Databases.

The queries take around 2.5 minutes to open! This is too slow.

I have imported the tables into the DB and the same Queries run on imported (not linked) tables take around 5 seconds to open. So huge difference.

The Problem is that those Tables are in the other DB's for a reason and they need to stay there.

Is there anything I can do to improve speed of queries that are based on Linked Tables from other Databases?

it talks about setting the Subdatasheet Name property to [NONE].
 

Very slow Access 2002 query with Windows 7

Preview
I need help troubleshooting consistently slow queries in an Access 2002 database with Windows 7. I’m running a temporary query in a local copy of a back end database with only tables. The identical query will take just 2-3 seconds with Windows XP but 30-60 seconds with Windows 7.

Multiple Windows 7 workstations are all slow and all Windows XP machines run the query quickly.

I even used JetShowPlan on both workstations to compare and the plans are identical. Two tables are involved with rushmore used on the first and a full scan on the second table of about 150,000 records.

Even in XP Mode on the Windows 7 machine the query still takes only about 10 seconds. Anyone have any ideas why Access/Jet on Windows 7 can be so slow?