long query times 2

[blaine011]

I have an access database, to which I have added a query which queries a table for the city field, using DISTINCT, to return a list of all of the distinct cities. When this is run on the server machine, it runs relatively quickly. But once I run this on any client machine, it takes a painfully long time, like 40 seconds.

What could be causing this?

 

[cmmrfrds]

If it is a large table the time is probably due to loading all the data from the server over the network to your desktop to perform the query. One way is to keep all the processing on the server by a thin client approach. Talk to the network/dba about setting up Terminal Services on the server, or running the app under Citrix if your company is using Citrix.

 

[blaine011]

the table is about 15-20000 records, and it has about 20 fields in it, would that take that long?

 

[blaine011]

Also I'm not sure if that's the reason, only because, if I do a simple query on it displaying all of the records at one, it pops up quickly in 1 to 2 seconds.

 

[blaine011]

oops, type, I meant all the records at once

 

[cmmrfrds]

Okay, then it is probably the query itsself.

Paste in your query perhaps there are some inefficiencies in the structure.

If you just open the table does it pop up quickly?

What fields are indexed?

 

[lespaul]

yes but when you add distinct it increases the time. here's what I think happens.

Look at record 1: Get city 'NEW YORK'
ADD TO RECORDSET
Look at record 2: Get city 'ALBUQUERQUE'
see if record 2 city = recordset 1 city
DOESN'T MATCH - ADD TO RECORDSET
Look at record 3: Get city 'LOS ANGELES'
see if record 3 city = recordset 1 city
see if record 3 city = recordset 2 city
DOESN'T MATCH - ADD TO RECORDSET
Look at record 4: Get city 'ALBUQUERQUE'
see if record 4 city = recordset 1 city
see if record 4 city = recordset 2 city
MATCHES - move to record 5
Look at record 5 city = 'DALLAS'
see if record 5 city = recordset 1 city
see if record 5 city = recordset 2 city
see if record 5 city = recordset 3 city
DOESN'T MATCH - ADD TO RECORDSET

Now that's just what I imagine happens when you use the distinct keyword and you can see why it might take a little while for a client station to show the results

HTH

Leslie

 

[blaine011]

But why would that affect the time when running on the clients, but not on the server?

 

[PLiNk]

You need to make the server do the query for you, and then return only the results to you.
You do this via an ODBC protocol thingie. It makes sense right, to only download the results.
I was struggling with the same problem, but I never got to close it, and somebody else solved it in some peculiar way I can't remember.

 

[blaine011]

unforunately I don't have the database on me, I'm at my daytime work right now, but it goes something like this:

SELECT DISTINCT[Order Information].[pucity]
FROM [Order Information]
ORDER BY [pucity];

 

[blaine011]

do you or anyone have a rough idea as to what needs to be done on the server odbc setup?

 

[cmmrfrds]

Here is a fairly good reference that has links to other references on ODBC. Unfortunately, I do not believe odbc is available for an Access database, but you can check this link. An Access mdb is not a database engine, such as, you find with sql server, oracle, etc... and only works through file sharing protocols not client/server protocols.

http://www.microsoft.com/accessdev/articles/Odbcover.htm

Another approach for the distinct query is to copy the table local, say when you start the app, and then perform the query on the local table.