July 10, 2009 at 2:02 pm
We have a single table in a sql2000 db for tracking usage for our website.
The table has a couple of million rows.
The table has 6 columns.
UserName,url, date, ip, queriedFor, sessionID
Each time the user hits a page on the site it insert a record into table.
What is the best way to optimize this table to enable querying usage information.
Currently when querying this table it creates a performance issues on the entire website.
Thanks
July 10, 2009 at 2:23 pm
It would help greatly if you could post a sample of the queries that cause the performance problem as well as the table DDL including index information.
July 10, 2009 at 9:38 pm
Hello,
Would it be possible to move this information in to a Data Warehouse, which would be better suited for analysis and reporting purposes. If the DW was on a separate box, that would go a long way to curing your performance problems.
If the data really must be queried from this single table, then as per John Rowan’s post you need to look at the type of queries being performed. You could start with identifying the fields being used in the search conditions, which will help you determine the correct index strategy for the table. May be take a look at the following article for a quick overview of Indexing in SQL Server:-
http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply