SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Tabledesign (newbie)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

avansor
Starting Member

2 Posts

Posted - 12/05/2013 :  13:36:56  Show Profile  Reply with Quote
Hello,

I am creating a simple database to log the number of visits from clients. The table is as follows :

tbl_statistics

--------------------------------------------------
p_key | client_id | client_datetime
--------------------------------------------------
1______4________2013-12-05 21:00:00
2______3________2013-12-05 21:02:11
3______1________2013-12-05 21:07:31
4______3________2013-12-05 21:12:42
.....



It will probably be logging about 2-3 rows in this table per second maximum so there will be some rows ... My question to you experts now is how the design of this table is done in the best way ( performance wise) in terms of "primary keys" , " clustered index" , "non clustered index"...

The only type of query that will retrieve data from this table is a standard "SELECT FROM" where " client_id " is of a certain value and " DATE_TIME " is between a specific date range.

example:

SELECT Count ( client_id ) AS totantal FROM tbl_statistics
WHERE ( client_datetime ) Between '2013 - 01-01 00:00:00 ' And '2014 - 01-01 00:00:00 '

Or is this better?

SELECT Count ( p_key ) AS totantal FROM tbl_statistics
WHERE ( client_datetime ) Between '2013 - 01-01 00:00:00 ' And '2014 - 01-01 00:00:00 '


Are extremely grateful for all tips and comments.

Edited by - avansor on 12/05/2013 17:19:13

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 12/05/2013 :  15:22:26  Show Profile  Reply with Quote
Do the sequence client_datetime values pretty much coincide with identity sequence? In other words the value is NOW for each insert?
If so then I would make the clustered index on client_datetime. That way inserts are very efficient with rows added naturally to the "bottom" of the table. And your queries by client_datetime range will be very efficient.

However, I now see that your example date ranges are for a full year. That is a lot of data to return at 2-3 rows per second. If that is a typical query then I would probably change my answer and make the clustered index on p_key and a non clustered on (Client_id, client_datetime).

Be One with the Optimizer
TG
Go to Top of Page

avansor
Starting Member

2 Posts

Posted - 12/05/2013 :  17:09:49  Show Profile  Reply with Quote

TG:

You are correct. The "client_datetime" is always "NOW". In the example query i selected a range for a full year. This will not be the most common question. I guess the most common range would be 1-30 days.

So would that mean you recommend a clustered index on client_datetime and nothing on the other columns (p_key, client_id) ?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 12/05/2013 :  17:30:29  Show Profile  Reply with Quote
Best practice is to have some sort of unique constraint on the table so that you can avoid duplicates and have a reliable row identifier. If there is no logical key - in other words duplicates are OK - then a non clustered primary key on p_key an obvious choice. I would only consider an index on client_id to resolve a performance problem. I could see that if do end up many rows in your date range or no date range at all.

EDIT:
one more thing to consider. because you only have the three columns perhaps a clustered primary key on all of them could be best. That would essentially mean the PK IS the table. (Client_Datetime, Client_id, p_key). no other indexes.

The p_key would be last and just be a tie breaker if a couple rows got in there with the exact same date.

If all your queries include a date range then that would probably be best performing for both the inserts and the selects.

Be One with the Optimizer
TG

Edited by - TG on 12/05/2013 17:36:15
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000