CFDIRECTORY Lag

[metaphiz]

I've got a page that is taking 12-15 seconds to load. It turns out the problem is the <cfdirectory> tag. It's searching a folder with 2131 files to see which filenames match the primary key (Property_ID) for a property on a real estate site. The files are named with the primary key. Is there a way to optimize the file query to make it run faster? I've tried caching the query results in the CFQUERY tag and defining the fields in the select statement--neither helped.

Here's the code:
<CFDIRECTORY DIRECTORY="filepath" NAME="DirectoryQuery" SORT="Name ASC">

<CFQUERY DBTYPE="query" NAME="FilesQuery">
SELECT *
FROM DirectoryQuery
WHERE TYPE = 'File' AND NAME LIKE '#Property_ID#.%'</CFQUERY>

 

[mansii]

Check this out: thread232-1169466

 

[metaphiz]

Thanks for your help, but the link doesn't apply to my situation. I'm trying to make a directory query run faster when there are lots of files in the directory, not join two separate directory queries...

 

[philhege]

You may be better off coding an external program that gets this info. There are functions in Windows, for example, that have extremely fast directory search capabilities.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[imstillatwork]

can you use the filter attribute of cfdirectory to help narrow this down a bit?

I know I can do filter="*.cfm"and only return files .cfm at the end.

try filter="#Property_ID#.*" and see if it speeds up.

cfexecute and OS commands will probably be faster.


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[metaphiz]

I could kiss you. I mean, if you weren't still at work...I mean, if you weren't a man...What I really mean to say is...THANKS the Property_ID filter worked. Cut my query time down to 1/4. Please elaborate on cfexecute and OS commands and how they can replace my code.