Depending on the totality of what you anticipate is going to be
happening at any given time, you can:
1) let CF cache the queries... this is especially good if you
anticipate that there will be a lot of similar queries as in
drill-downs, or several users making identical queries (skewed
popularity of certain items), e.g. today's specials, hot items, etc.
2) cache the queries yourself... more work for you, less for the db server
3) spew the recordset to the client (easy with WDDX), then do the
drill-down at the client. OK, if the user has the bandwidth, and a
fast client machine (you can often assume/dictate this in a B2B
application)
4) spew the recordset to the client as JavaScript array:
more work for you at the CF server
much less bandwith than a WDDX packet
much less work at the client
Hmmm... 10 seconds to query sounds like some db redesign may be in order.
This will vary with the db engine, but there are things you can do.
I noticed that your db is denormalized (ProductsCategory,
ProductsKeyWords, and maybe ProductsStyleNumber). If you haven't
already tried it, you might experiment with normalizng these... & let
the db engine do its thing. (you might be surprised by the results)
HTH
Dick
At 6:38 PM -0400 9/29/00, Jon Hall wrote:
>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_tal
>k 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.
