At 3:08 PM -0400 9/30/00, Jon Hall wrote:
>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
The large # of items and the ability to update/insert/delete items at
will, does not *necessarily* mean than CF caching wont help...
follow me on this:
There is an old rule that often applies to parts, inventories, etc...
the 80-20 rule. After observing activity against large inventories
it was found that:
about 80% of the activity was against 20% of the items, and vice-versa
You often can apply this same "rule" to query activity.
If so, you will have a few high-activity, similar, queries; and many,
many low-activity dissimilar queries.
drill-down accentuates this, as you will be repeating the
high-activity queries.
So, if you can let CF cache the queries. The similar, high-activity
queries will tend to remain cached. So, you can significantly
improve performance of a significant part (80%) of the overall
system... even if the components of the queries change frequently
(requiring re-caching)
Now, the probability is that the "changes affecting caching" will be
a small (or zero) percentage of the queries during peak periods...
then you *do* have "static" queries... at least for a useful amount
of time.
I have done this successfully using cachedafter on the queries.
set an application variable for each main table (used in all the queries)
application.PartsLastUpdatedAt = now()
update this each time you add/delete/change anything which will affect the
queries
reference this variable as the cachedafter designation in all the queries.
The beauty of this is that it is sell-tuning. There is much
potential gain & very little overhead,
Here is the best part, you can easily try this, with a little timing
routine, and see if you have enough similar queries to benefit.
In a similar way, you can experiment with normalized child tables for
the keywords, etc, without affecting the existing db structure...
Just create a (bastard) child table (no foreign key constraint) for testing.
Populate the child table by extracting the keywords from the parent.
Then write a simple query template to time the same query with the
child and without.
If this gives improvement, then look into the indexing options of
your specific database... you may be able to improve it even more.
>(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.
That is not necessarily a problem... if you can efficiently send the
data to the browser. One bigger browser-server connection may be
faster than many smaller browser-server connections. This is
especially significant when the network is congested & it could take
2-3 seconds to establish each connection.
Also you would not need to repeat the queries for drill-down.
For example, your code indicated that you would return a max of 500 rows.
If each row required 100 characters to be transmitted to the browser
that's 10-20 seconds for the largest query. (and no additional
browser-server connections to do the drill down).
>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.
all the more reason to normalize it... this is a performance storage
tradeoff (as well as some others). If you have the disk storage to
afford a child table, the db engine can process this much more
efficiently.
Try it... I think you'll be amazed at the results.
>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?
The first 20 I can see... how are you going to get the next 20?
HTH
Dick
>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_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.
