Hi all. This may be a little long, but I really need some advice from
the gurus out there.
I'm updating an application I built for a client awhile back and am
looking to try and pick up some speed if possible. The way it works
now is, the items being sold can only be sold one to an area. Sales
reps access the application so I have to make items unavailable to
other reps in the area once the item has been purchased.
Currently it works like this:
I have a column in the products table, which currently has about
2,000 items in it, named "area".
I also have a table called "areas" with an "area_id" field.
When a sales rep makes a purchase in an area, I place that "area_id"
into the area column of the products table, period delimited, like so.
..100.
As other reps purchase the product in other areas, the area_id is
added to the column. Example:
..100.,.101.,.200.,.350.,.etc.
On the query to show the products, I'm using the LOCATE scalar function:
SELECT Product_ID, etc
FROM Products
WHERE ({ fn locate('.#area_ID#.', area) } = 0 or area is null)
This has been working fine, but he just landed a big contract and may
end up with
<gulp>
several hundred (or more) reps hitting the application at once.
</gulp>
There are about 1,500 areas so I have to use a large field for the
area column of the products table. Right now I have it set as ntext
as there could potentially be over 10,000 characters in the column.
OK. I have one other option. I also write the area_id to the lineitem
table. I could write my query like this and delete the area column
from the products table altogether.
SELECT Product_ID, etc
FROM Products
WHERE Product_ID NOT IN
(SELECT Product_ID FROM LineItem
WHERE area_id = #area_id#)
This also works, but is slower. About 35ms for the first to 160ms for
the second example. The thing is, right now the products haven't been
purchased in many areas and there aren't many line items. I'm
wondering what you think may happen down the road when the area
fields of the products table have tons of data in them as opposed to
querying what will be probably thousands of Line Items.
Whatcha think?
--
Bud Schneehagen - Tropical Web Creations
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
