Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bjornh
Yak Posting Veteran
87 Posts |
 Posted - 2003-11-12 : 17:39:03
|
Hi there,I would like to hear some feedback on this new design that i came up with... Some detailed background info:It's an online visitors statistics system.  There are 2 tables for logging, and about 10 for presentation (not really sure if it will be exactly 10, but that isn’t problem right now).I want to create presentation tables that have computed numbers from the log tables, and that will be filled with a job, every night.Let me explain:The Log2 table contains pageview info:Id (int)siteID (related to our clients table)Date (smalldatetime)visitorID (related to log1, which contains info about the visitor, os, browser, etc.).pageID (related to the pages table)So this is my log, now I want to created presentation tables from it.My thought:pageviews table:date (smalldatetime)hour (tinyint)siteID (int)pageID (int)total (smallint)So, why do i want to split date and hour? Because i need to do group by date. And sometimes group by hour. And using ' group by convert(varchar(20), date, 105)' gives very bad preformance (i think because the index isn't 'working').Question 1: is this a good idea? Or is there a better way that I can group by date?Question 2: I also have a table that contains the click path with totals. To insert these totals, I use the function on this page: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647It works fine, but with 7000 visitors a day (and rising) this takes about 12 min to complete, is this a bad thing, that a query runs for about 30 min (in the near future maybe)? I is happening at night, so there isn’t much activity at that moment.Question 3: are there any others thing that I can do to get better performance?That’s it I think, it’s getting pretty late, so maybe tomorrow I’ll have some more questions I forgot, but these are the main things I think.Thanks!Bjorn |
|
|
|
|
|
|
|
[db design] is this the way?