I've got a website backed with a relational database composed from the usual e-commerce related tables (Order, OrderItem, ShoppingCart, CreditCard, Payment, Customer, Address, etc...).
The saved proc. which returns order history is shateringly slow because of the quantity of data + the various joins which must occur, and with respect to the search parameters often it occasions out (regardless of the indexing that's in position).
The DB schema is fairly well stabilized but I'm able to achieve better performance by on your journey to something similar to an information warehouse. DW projects aren't trivial after which there is the problem of keeping the information synchronized so I'm wondering if anybody knows of the shortcut. Possibly an out-from the box solution and build the DW schema and the information synchronized (via triggers possibly). I have heard about Lucene however it appears targeted more toward text searches and document management. Does anybody produce other suggestions?
How large is the database?
There isn't really any cutting corners, but dimensional modelling is actually Not too hard. You initially determine a grain after which have to identify your details and also the dimensions connected using the details. Then you definitely divide the size into tables which give you the size only grow gradually with time. The option of dimensions is totally practical and in line with the data behavior.
I suggest you take a look at Kimball's books.
For any database of the couple of GB, it's certainly easy to update a confirming database on your own several occasions each day (no history, just repopulating from the 3NF for any different model of the identical data). You will find certain realtime data warehousing techniques which just apply changes continuously during the day.
So while DW projects is probably not trivial, the denormalization techniques are extremely friendly and functional without always creating a complete time-invariant data warehouse.
Materialized Sights are what you are able use within Oracle. They provide you with the "keeping the information synchronizedInch feature you're searching for coupled with immediate access of aggregate data. Because you did not mention any specifics (platform, server specs, quantity of rows, quantity of hits/second, etc) of the platform, I can not help much much in addition to that.
Obviously, we're presuming you've already checked that your SQL is written correctly and brilliantly, that the indexing is correct, that you're correctly using caching in most amounts of your application, that the DB server has enough RAM, fast hard disk drives, etc.
Also, have you thought about denormalizing your schema, sufficient for everyone your most typical queries faster? that's much better than applying a whole data warehouse, that might not really be what you would like anyway. Often a data warehouse is perfect for confirming reasons, not for serving interactive applications.