Help Optimizing Query

[elDonrico]

Just looking to see if anyone has any pointers on how i can optimize this
query... it is taking about a minute to show the data, and even though its not
terribly long, it would still be better if it was quicker ( i am hearing
reports that people are trying to refresh the page after 30 secs or so. ive
tried to figure out if i can "flush" something, but i dont know if its possible
to flush during a query or even output. i flush it in the begining and display
a message that says "Wait..." which i would love to get rid of after the data
is ready, not sure how to do this but, i think it would need to be a layer or
something that shows and hides the layer. But without further ado... the query.



<cfquery name="rsGetApplicants" datasource="standard">
SELECT * FROM
informix.styhmpf f, OUTER informix.styemplr e
WHERE f.empl_code = e.empl_code
ORDER BY FIRST_NAME, LAST_NAME, hmpfID
</cfquery>

[jdeline]

SELECT * may be the culprit. Do you really need every field from the table? Is your database on the ColdFusion server? If not, what is the bandwidth between the CF server and the Informix server?

[elDonrico]

ok, i can understand that... i need every field from styhmpf (the name is my
way of rebelling against the YAT, yet another table, which i felt was
unnecessary - hmpf) but, i only really need the person's name out of the
styemplr table.

and no, the cf server is a win 2003 zeon and the informix db is a win NT4 on
dual P3 the connection is 100BaseT (is 1000 that much better folks?)

[CF_Oracle]

Not familiar with informix but possibly some of the following could help:
1.Did you check how much time takes for query itself using debagger ot iin
database?
2.Check if empl_code is indexed column in both tables and if not index them.
3.Do you really need to order by FIRST_NAME?

[elDonrico]

sorry to sound like an idiot, but i have no idea how to index a column... i'lve
never used CFDebugger, but, i'll check it out and see what i can come up with
.... and does it really take more time to order it by the first name? i would
suppose than that the ID would be the quickest than?


thanks for the tips.

[Dan Bracuk]

First, get rid of the select * as was mentioned earlier. Second, how may records are there? It could be that the query is quick but you are returning so much data that the browser is slow.

[elDonrico]

i'm already doing this to time it

<cfset tick = GetTickCount()>
<cfquery>... </cfquery>

<cfset tock = GetTickCount()>
<cfset total = (tock - tick)/1000>
<cfif total GTE 60>
<cfset timetook = total/60>
<cfelse>
<cfset timetook = total>
</cfif>

and the #total# is the same as if i had a stopwatch on it. its like once the
data is there, its outputted.

[elDonrico]

so Dan,

is it that even though you are only displaying like 200 on the first page which i am, it has to wait on the other 2000 for the other pages?

[elDonrico]

HOLY $#17

taking out the * moved it to 27.3 sec... thanks guys... thats what i was looking for.

[UpstateWeb]

I am using Access. I have two tables, Categpries and Items. I have 4 categories
and 11 items. The Categories are referenced through the Items table by their
ID. I want the query to pull only the Categories that have active Items
available. I know that I have 4 active Categories and all are referenced in 11
active Items. Thus, the following query pulls 11 records instead of 4. The idea
is that if there are 4 Categories in the Table, but one of them does not have
any active items associated, it will not be a part of the query.

<cfquery name="getCatalogCategories" datasource="#DSN#">
SELECT CatalogCategories.*, CatalogItems.ImageFile AS ItemImageFile FROM
CatalogCategories, CatalogItems WHERE CatalogCategories.Active = yes AND
CatalogItems.Active AND CatalogCategories.CategoryID =
CatalogItems.CategoryReference ORDER BY CatalogCategories.Name
</cfquery>

Thanks in advance for your help.

[jdeline]

Try something like the code below.



<CFQUERY NAME="getCategories" ...>
SELECT DISTINCT CatalogItems.categoryId FROM CatalogItems
</CFQUERY>
<CFSET activeCategories = ValueList(getCategories.categoryId)>
<!--- you can now use activeCategories in the WHERE clause of your query, or
you can include this above query into your WHERE clause. --->

[paross1]

Wouldn't you want to check to see if <b>CatalogItems.Active = Yes</b>? You
aren't doing that in your query, at least as posted:

<cfquery name="getCatalogCategories" datasource="#DSN#">
SELECT CatalogCategories.*,
CatalogItems.ImageFile AS ItemImageFile
FROM CatalogCategories, CatalogItems
WHERE CatalogCategories.Active = yes
AND CatalogItems.Active <b><---------right here</b>
AND CatalogCategories.CategoryID = CatalogItems.CategoryReference
ORDER BY CatalogCategories.Name
</cfquery>

Phil

[UpstateWeb]

I did add the =active and it didn't change the outcome. Thanks any way....

[UpstateWeb]

Well, I guess I was looking for a solution that was more complicated then it
needed to be. Although, I had to run 3 queries to get it to all work. I still
think there is a way to use only one query and optimize it.

Thanks for your help,

David G. Moore, Jr.

[JMGibson3]

Look into LEFT JOINS and use a WHERE right-hand side match-key IS NOT NULL. This will return ONLY left-sides that actually have something on the right.

[Dan Bracuk]

<blockquote>quote:<br><hr><i>Originally posted by: <b><b>UpstateWeb</b></b></i>
Well, I guess I was looking for a solution that was more complicated then it
needed to be. Although, I had to run 3 queries to get it to all work. I still
think there is a way to use only one query and optimize it.
Thanks for your help,
David G. Moore, Jr.<hr></blockquote>
Like this?
select somefields
from catalogcategory -- but not catalogitems
where categoryid in
(select categoryid
from catalogitems)

If you only want one row for each category, do not select anything from the
items table.