Speed up Database 1

[SiJP]

Current System:

Windows NT server
Network is TCPIP 100mbps
Sequel 2000
Access mde FE

Currently have a database with 4 linked tables (through ODBC) to the server. Each table has a passthru query run against it.

I then have a fifth query which incorperates all four previously mentioned queries... the query is filtered by ID criteria so may only return 1 record.

The query takes a minute or two to run...

Idea 1 - ditch sequel... IMO its too slow
Idea 2 - gain other opinions from Tek-Tips memebers..!!!

Can you please help?

 

[Smack]

I'm not sure if this will help your application, but last week I read a post where you go to tools, options, general and uncheck the 2 autocrrect boxes. I have a couple of forms that were really, really slow, noe they open up almost intantaneously.

 

[dvannoy]

The first thing I would change is ODBC. if you are using a SQL BE then ADO would be your best bet. Check your indexes in your tables. make sure the correct fields are indexed(this makes a big difference) also hardware is very important SQL "LOVES" memory the more you give it, the more it will use. it also depends on what else your running on your server. SQL should be the only thing running on your NT box(for the best performance)

 

[cmmrfrds]

If you are using sql server as the backend, why are you using an mdb/mde as opposed to an adp/ade. The adp uses an ADO connection and is much cleaner and faster than using linked ODBC tables.

 

[OnTheFly]

Or, if using the linked tables you can still use the ADO connection, do your querying on the SQL server and just return the data through a stored program. Or, a simpler way, create views for 4 other queries on SQL (if they are not using parameters) and link them like you would a table through ODBC. That way the bulk of the work is being done on the SQL server rather than passing back and forth over the network.

Hope this helps,

OnTheFly

 

[JeremyNYC]

Try this:

http://www.fmsinc.com/tpapers/faster/index.html

Hope it helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Onyxpurr]

That was a great article Jeremy!! I can definately use it!