cache it then use the cached copy. This depends, of course, on:
1) How much data will be retrieved? To much to cache? If so perhaps
you could construct temp tables on the DB with the letter queries.
Lacking that you might also construct a simpler query containing only
the keys of the rows needed and cache that: then you can do one big "IN"
statement (which may or may not be faster. but I suspect it would be).
2) How often does the data get updated. If it's once a week then cache
the data for a week and create a page that will refresh it on demand.
3) What's the data freshness requirement? This is related to the above.
If the table is updated what's the requirement for presenting the data
to the user: can the user data be a minute old? 5 minutes? 30 minutes?
8 weeks?
4) What kind of data is being returned? Perhaps you could use Verity
instead?
5) Are you sure that you're using the right index and that it's being
refreshed often enough? Since you don't offer what DB is being used
it's hard to make suggestions but most DBMS offer a query
analyzer/performance tuner of some type. Often a few minutes with it
can yield substantial results. I'm not sure what kind of structure you
have but this seems a perfect place to use a Clustered Index.
6) Although you can't redesign the main table could you create helper
tables? A simple table containing only the first letter and pKey of the
other table would be lightning fast and could be joined to the main
table without using "LIKE". Triggers could be used to easily keep this
helper table up to date.
Those are just some ideas.
Jim Davis
-----Original Message-----
From: admin [mailto:[EMAIL PROTECTED]
Sent: Sunday, October 26, 2003 4:39 PM
To: CF-Talk
Subject: cfquery performance suggestions
I'm using the following query to find all the catagories in a table that
start with a certain letter :-
SELECT distinct CATEGORY
FROM dbo.mainfile
where CATEGORY like 'P%'
order by CATEGORY ASC
I have an index based on Category but it runs slow (it's a v. large
table - and it't can't be redesigned). Any suggestions for a better
query to get the results ? - btw I'm very much a SQL novice
Cheers
Richard
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
