ColdFusion Why is my CF search engine slow on one of my servers?
I have CF installed on 3 servers:
1 with SQL - very fast
1 with Access (1 table without any relationships) - speed is acceptable
1 with Access (5 tables with relationships) - very slow (15 seconds to retrieve query results)
How can I utilize debugging or any other method to find the issue of slow queries (home query page takes just as long, approx. 15412ms).
1 with SQL - very fast
1 with Access (1 table without any relationships) - speed is acceptable
1 with Access (5 tables with relationships) - very slow (15 seconds to retrieve query results)
How can I utilize debugging or any other method to find the issue of slow queries (home query page takes just as long, approx. 15412ms).
_agx_
You mean the database queries are slow? Start with the home page which you say takes 15412ms. I don't know of any tools for Access, but the robust debugging includes query times. That should tell you at a glance whether the problem is a) One or two queries in particular or b) the page is running too many queries (ex. running a query 100 times in a loop). If it's a) can you post the query?
ASKER
Debugging info:
Execution Time
Total Time Avg Time Count Template
15105 ms 15105 ms 1 E:/MSWEB/www/Search.cfm
1 ms STARTUP, PARSING, COMPILING, LOADING, & SHUTDOWN
15106 ms TOTAL EXECUTION TIME
red = over 250 ms average execution time
SQL Queries
Search (Datasource=MSDB, Time=14993ms, Records=21360) in E:\MSWEB\www\Search.cfm @ 14:43:16.016 --We don't have this many records--
SELECT *
FROM TBL_DWG , TBL_PROJECTS
DropDown1 (Datasource=MSDB, Time=2ms, Records=30) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT SH_HULL
FROM TBL_SHIP
DropDown2 (Datasource=MSDB, Time=11ms, Records=231) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT DW_GROUP
FROM TBL_DWG
DropDown4 (Datasource=MSDB, Time=2ms, Records=74) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT SH_HULLNO
FROM TBL_SHIP
WHERE SH_HULLNO IS NOT NULL
ORDER BY SH_HULLNO ASC
DropDown7 (Datasource=MSDB, Time=2ms, Records=4) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT PR_NAME
FROM TBL_PROJECTS
Results (Datasource=MSDB, Time=83ms, Records=85) in E:\MSWEB\www\Search.cfm @ 14:43:17.017
Execution Time
Total Time Avg Time Count Template
15105 ms 15105 ms 1 E:/MSWEB/www/Search.cfm
1 ms STARTUP, PARSING, COMPILING, LOADING, & SHUTDOWN
15106 ms TOTAL EXECUTION TIME
red = over 250 ms average execution time
SQL Queries
Search (Datasource=MSDB, Time=14993ms, Records=21360) in E:\MSWEB\www\Search.cfm @ 14:43:16.016 --We don't have this many records--
SELECT *
FROM TBL_DWG , TBL_PROJECTS
DropDown1 (Datasource=MSDB, Time=2ms, Records=30) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT SH_HULL
FROM TBL_SHIP
DropDown2 (Datasource=MSDB, Time=11ms, Records=231) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT DW_GROUP
FROM TBL_DWG
DropDown4 (Datasource=MSDB, Time=2ms, Records=74) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT SH_HULLNO
FROM TBL_SHIP
WHERE SH_HULLNO IS NOT NULL
ORDER BY SH_HULLNO ASC
DropDown7 (Datasource=MSDB, Time=2ms, Records=4) in E:\MSWEB\www\Search.cfm @ 14:43:16.016
SELECT DISTINCT PR_NAME
FROM TBL_PROJECTS
Results (Datasource=MSDB, Time=83ms, Records=85) in E:\MSWEB\www\Search.cfm @ 14:43:17.017
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks _agx_!