What kind of index on dates for a emp duty table?

views:

answers:

+1 Q:

What kind of index on dates for a emp duty table?

I have a query that gets all employee duties for a start date and end date against a database. Basically the table would be like

Emp
Duty
StartDate
EndDate

Should I give indexing for dates? what kind etc? to make things easier on db level (orm level for me it would be an attribute to set).There will be huge data depending on number of employees off course (expected) , better safe than sorry?

Please advice.

+1 A:

A combined index on emp, startdate, enddate seems like a good choice. But it really depends on how you plan to access/query your data. A proper answer cannot really be given without knowning how your application works. Because in the end, the best solution is the solution that works for you.

Bor 2010-01-12 11:16:17

would get all employee duties for a start date and end date mean index on startdate and end date alone or? mmmm

abmv 2010-01-12 11:17:50

Generally I'd imagine your WHERE clause would look like this:`WHERE emp = X AND startDate .. AND endDate ..`

Bor 2010-01-12 11:20:15

no emp get all emp duty so

abmv 2010-01-12 11:21:56

get all emp duty for startdate and end date

abmv 2010-01-12 11:22:27

+1 A:

Assuming you have a primary key on this table, I would use non clustered indices on the date fields if you need to search them oftenly - this is more important the more data you have in the table.

Adding only two more indexes on the table shouldn't hurt (though I wouldn't index all fields - that is asking for trouble!).

Oded 2010-01-12 11:16:55

you mean combine all emp + duty + std + end into one index right? hee??

abmv 2010-01-12 11:20:33

I mean a different index for each field - shudder. But your's is even more cringe worthy ;)

Oded 2010-01-12 11:25:32

Can you explain the repercussions on adding non clustered index on emp and duty separately ,since I use an orm and am fixing actually some orm code with attributes I see [Index()] on these I think leave them there maybe the orm on demand would throw some queries at them...? thanks again

abmv 2010-01-12 11:30:54

@abmv - it depends on how you (the ORM) are going to query the DB - if emp and duty are used for your where clauses, having the indexing overhead may be a benefit. In general, the more indexes you have, the slower inserting will be - if you have lots of inserts you want to avoid excessive indexing.

Oded 2010-01-12 11:33:51

ok thanks now I get it,my db knowledge was eroding away well.Not much inserts on employee duty table after it is set.I'm just analyzing the whole BL again.Thanks for your replies.

abmv 2010-01-12 11:40:02

ansaurus

tags:

views:

answers:

What kind of index on dates for a emp duty table?

related questions