Here's a couple of things to try:
1) Run your queries in a db tool (SQL Query Analyzer) to compare performance
of different statements AND to see how many records are being returned.
This will make your life easier.
2) Make sure all the fields you are joining on are indexed.
3) Try the TOP X language.
<CFQUERY NAME="prize" DATASOURCE="SALES">
SELECT TOP 5 prize.value, draw.drawdate,
contest.contest_name, contest_type.contest_type,
state.state
FROM state, prize, draw, contest_type, contest, company
WHERE prize.draw_id = draw.draw_id
AND draw.contest_id = contest.contest_id
AND contest_type.contest_type_id = contest.contest_type_id
AND contest.company_id = company.company_id
AND company.state_id = state.state_id
ORDER BY prize.value DESC
</CFQUERY>
4) TRY a subquery to reduce the number of records matched.
<CFQUERY NAME="prize" DATASOURCE="SALES">
SELECT prize.value, draw.drawdate,
contest.contest_name, contest_type.contest_type,
state.state
FROM state, prize, draw, contest_type, contest, company
WHERE prize.draw_id = draw.draw_id
AND draw.contest_id = contest.contest_id
AND contest_type.contest_type_id = contest.contest_type_id
AND contest.company_id = company.company_id
AND company.state_id = state.state_id
AND prize.draw_id IN
(SELECT TOP 5 draw_id
FROM prize
ORDER BY value DESC)
ORDER BY prize.value DESC
</CFQUERY>
In #3, all the records still need to be joined, then a subset is returned.
#4 is better because it limits the number of prize records the db needs to
join with other tables.
-----Original Message-----
From: Joseph Thompson [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 04, 2000 10:55 AM
To: CF-Talk
Subject: normalization problem?
With a fully "normalized" database (MS SQL Server 7 CF 4.51)
Is this the correct way to get the top 5 prize values and
associated details from a database?
It returns an "out of memory/resource" error;
"Error Diagnostic Information Error occurred for unknown cause."
Any help would be appreciated : )
<CFQUERY NAME="prize" DATASOURCE="SALES" MaxRows=5>
SELECT prize.value, draw.drawdate,
contest.contest_name, contest_type.contest_type,
state.state
FROM state, prize, draw, contest_type, contest, company
WHERE prize.draw_id = draw.draw_id
AND draw.contest_id = contest.contest_id
AND contest_type.contest_type_id = contest.contest_type_id
AND contest.company_id = company.company_id
AND company.state_id = state.state_id
ORDER BY prize.value DESC
</CFQUERY>
The Dbase structure is like this:
Prize Table:
-value
-draw_ID
Draw Table:
-drawdate
-draw_ID
-contest_id
Contest Table:
-contest_name
-contest_ID
-contest_type_ID
-company_ID
Contest_type Table:
-contest_type
-contest_type_ID
Company Table:
-company_ID
-state_ID
State Table:
-state
-state_ID
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
