Access Database Horror

[wtodd]

PLEASE HELP ME. It takes about 21 seconds to bring up a form which has 24
input fields (7 of which are drop down fields that brings up selection items
from the access database such as mfg, vendor, bldg #, etc.). The access
database is not big at all.

I'm running CFMX 6.1 on a stand alone W2K Server and connecting to MS Access
2000 database, which is also on the server. I added my datasource from the
CFMX Administrator with Access as my datasource and everything checked fine. I
have 7 drop down fields on my form such what you see below: Can someone help
me with this incredibly slow performance. By the way my server has plenty of
space and ram.

<cfquery name=?mfg_name? datasource=?eeeproj?>
SELECT distinct manufacturer
FROM JWST_Table
ORDER BY manufacturer
</cfquery>

<SELECT NAME = ?manufacturer?>
<OPTION SELECTED VALUE = ??> <CFOUTPUT QUERY=?mfg_name?>
<OPTION VALUE = ?#manufacturer#?>#manufacturer#
</cfoutput>
</select>
:clock;:frown;

[Stressed_Simon]

Well that is quite a basic query so maybe your db is not indexed very well. If
this query changes rarely then you could always cache it, which will help
immensely.

<cfquery name=?mfg_name? datasource=?eeeproj? cachedwithin="#CreateTimeSpan(0,
0, 30, 0)#">
SELECT distinct manufacturer
FROM JWST_Table
ORDER BY manufacturer
</cfquery>

The bit I have highlighted in bold will save the query to memory for 30
minutes before running it again. This means that the query is run the first
time and then not checked again for 30 minutes. This will definitely make your
page run quicker!

[wtodd]

Thank you Stressed_Simon. The 'cachedwithin' did speed it up immensely. The
database is new and perhaps you're right on it not being indexed correctly. So
I will check with the creator. THANKS AGAIN.

[mxstu]

You should also check your database design. If the [JWST_Table] table
contains a unique list of manufacturers, then you do not need to use SELECT
DISTINCT, which is typically slower than a regular SELECT. If the table does
not contain unique manufacturers, then you would probably be better off having
a separate table for Manufacturer information [Manufacturers]. Your selects on
this table would be faster and you could use the ManufacturerID (numeric key)
as a foreign key other related tables.

[jorgepino]

if you really don't want a access db horror story
you may want to upgrade your data base to MSsql or MYSQL