Have you tried adding CachedWithin=#CreateTimeStamp(0,0,10,0)#
Add this to your query and then it will cache the query when it runs so it
does not have to requery again.
Larry Juncker
Senior Cold Fusion Programmer
Heartland Internet
-----Original Message-----
From: Jon Hall [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 29, 2000 5:39 PM
To: CF-Talk
Subject: query caching?
This is a multi-part message in MIME format.
------=_NextPart_000_03AA_01C02A44.87489EC0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ok, maybe someone could point me in the right direction here. I am =
working with a database with 750,000 diferent products (yes 750,000). =
The database has been tweaked so that even the most complicated search =
only takes 10 seconds to return data on the development server.This wil =
lspeed up when it is live. That is fairly acceptable...
The problem is that each page takes 10 seconds...so when someone hits =
clicks "Next" to go to the next page, it has to requery.
Is there a way to optimize this? Perhaps some sort of read ahead =
caching? Any suggestions would be helpful. I am including the query code =
below.
<cfquery name=3D"productsearch" datasource=3D"#dsn#" maxrows=3D500 =
dbtype=3D"ODBC">
SELECT cLinesID, cProductsNo, cProductsStyleNumber, cProductsName, =
cDescription, cProductsCategorys,cProductimage,cProductsPriceRange
FROM dbo.tProducts=20
WHERE 0=3D0=20
and (cProductimage > '')=20
<CFIF #user_category# IS NOT "">
and (cProductsCategorys like '%#user_category#%')
</CFIF>
<CFIF #user_style# IS NOT "">
and (cProductsStyleNumber LIKE '%#user_style#%')
</CFIF>
<CFIF #user_keyword# IS NOT "">
and (cProductsKeyWords LIKE '%#user_keyword#%')
</CFIF>
<CFIF #user_min# IS NOT "">
and (cProductspricerange >=3D #user_min#)
</CFIF>
<CFIF #user_max# IS NOT "">
and (cProductspricerange <=3D #user_max#)
</CFIF>
</cfquery>
<cfoutput query=3D"productsearch" startrow=3D#startr# =
maxrows=3D#maxdisplay#>
------=_NextPart_000_03AA_01C02A44.87489EC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.3018.900" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Ok, maybe someone could point me in the =
right=20
direction here. I am working with a database with 750,000 diferent =
products (yes=20
750,000). The database has been tweaked so that even the most =
complicated search=20
only takes 10 seconds to return data on the development server.This wil =
lspeed=20
up when it is live. That is fairly acceptable...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>The problem is that each page takes 10 =
seconds...so=20
when someone hits clicks "Next" to go to the next page, it has to=20
requery.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Is there a way to optimize this? =
Perhaps some sort=20
of read ahead caching? Any suggestions would be helpful. I am including =
the=20
query code below.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><cfquery name=3D"productsearch" =
datasource=3D"#dsn#"=20
maxrows=3D500 =
dbtype=3D"ODBC"><BR>SELECT cLinesID,=20
cProductsNo, cProductsStyleNumber, cProductsName, cDescription,=20
cProductsCategorys,cProductimage,cProductsPriceRange<BR>FROM &=
nbsp; =20
dbo.tProducts <BR>WHERE 0=3D0 =
<BR>and=20
(cProductimage > '') </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><CFIF #user_category# IS NOT=20
""><BR> and (cProductsCategorys like=20
'%#user_category#%')<BR></CFIF></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><CFIF #user_style# IS NOT=20
""><BR> and (cProductsStyleNumber LIKE=20
'%#user_style#%')<BR></CFIF></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><CFIF #user_keyword# IS NOT=20
""><BR> and (cProductsKeyWords LIKE=20
'%#user_keyword#%')<BR></CFIF></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><CFIF #user_min# IS NOT=20
""><BR> and (cProductspricerange >=3D=20
#user_min#)<BR></CFIF></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><CFIF #user_max# IS NOT=20
""><BR> and (cProductspricerange <=3D=20
#user_max#)<BR></CFIF></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><BR></cfquery></FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><cfoutput query=3D"productsearch"=20
startrow=3D#startr# =
maxrows=3D#maxdisplay#></FONT></DIV></BODY></HTML>
------=_NextPart_000_03AA_01C02A44.87489EC0--
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
