Unfortunately with the large amount of items that the customer has control
of, they can add and delete items at will. Letting CF cache queries wont
work since there are no static queries (unless..just had a thought, see
below).
Maybe as we go on and collect some statistics as to what is popular.
This will also be a consumer based site, not b2b. So 56k must be taken into
account.
The data structure as it came from the client was horrible, we have done
quite a bit of tweaking, but I liked the idea of indivual keywords per
field. That would definately be more efficient, but each item can have many
keywords attached to it. These are like that company that does the url
keywords for msn...you type it in and you get the products that match that
keyword. We have a keyword search that searches all item descriptions
also...
I had a thought as I was typing this...What if I queried 20 records at a
time and displayed only 10. Then took the remaining ten and assigned them to
an array. When the user clicked next I could display the contents of the
array...seems very doable to me. What do you think?
Thanks for your help
jon
----- Original Message -----
From: "Dick Applebaum" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Saturday, September 30, 2000 8:08 AM
Subject: Re: query caching?
> 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.
------------------------------------------------------------------------------
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.
