Even very large tables can perform well with proper indexing. In your
case, the tracking table will contain about a million rows per year of
data. This is not really very big nowadays.
However, it appears you have separate needs here. One is to record
events in real time. The other is to report on historical data,
presumably for data mining and trend analysis, without slowing response
time. These requirements may necessitate different physical
implementations in order to meet those objectives.
It is common practice to use separate tables (perhaps in a different
database or server) for operational data and ad-hoc reporting. This
allows you to implement an operational table optimized for insert
performance (e.g. single clustered index on TRACK_DATE). You can
schedule a process to move data into your reporting structures and purge
from the operational table (assuming you don't need to keep data both
places).
My segregating operational and reporting data, you have considerably
more flexibility in addressing reporting requirements while maximizing
concurrency. You can denormalize data and/or index heavily to support
timely ad-hoc queries or use tools like Analysis Services. This will
provide a more flexible and robust solution.
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
Quote:> Hello,
> I have an online commerce site with an SQL 7.0 DB backend.
> I recently introduced a web based script that writes to the DB when
> users access certain pages, the homepage, as well as a few others.
> The data will be used for tracking.
> In one 24 hour period it has inserted 3000 records.
> Eventually a module will be written to retrieve this data in a
readable
> friendly format.
> My question is simple - how big is to big for this table.
> Below are the fields from the table.
> [tracking_id] [int] IDENTITY
> [HTTP_USER_AGENT] [varchar] (200)
> [LOCAL_ADDR] [varchar] (20)
> [REMOTE_HOST] [varchar] (20)
> [REMOTE_ADDR] [varchar] (50)
> [QUERY_STRING] [varchar] (200)
> [SCRIPT_NAME] [varchar] (200)
> [HTTP_COOKIE] [varchar] (255)
> [CUSTOMER_ID] [int]
> [TRACK_DATE] [datetime]
> [DEALER_ID] [int]
> [ORDER_ID] [int]
> [KWSOURCE] [varchar] (100)
> [KEYWORD] [varchar] (100)
> [trackingType_ID] [int]
> [SESSIONID] [varchar] (50)
> [HTTP_REFERER] [varchar] (200)
> Thank you in advance.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
by