I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person several years ago, it has around 100 GB of data and it is increasing every 10 minutes (it stores 10 min data from several units -> 144 records per day per device). Several tables have around 10 milion rows. The point is that I think the main tables have been designed in a way that is not the most efficient or appropiate for the kind of queries that will be usually executed. Now I need to proof if what I say is better than what it is already implemented. The DB is extensive in number of tables, but the structure can be simplified by the following diagram:
The Date_Id field is generated automatically by a function using the DateTime field. There are two indexes in both tables. The cluster index for each table contains the PK fields in the same order. The second index for the Unit table contains only the Unit_Id field, while the second index in UnitData contains the Unit_Id and DateTime fields in this order.
However, I think the design should be this one:
In this case only a clustered index for the PK fields will be needed. For this DB design the usual query would be something like:
FROM Unit u, UnitData ud
WHERE u.Unit_Id = ud.Unit_Id and ud.DateTime >= 'dd-MM-yyyy'
ORDER BY ud.Unit_Id, ud.DateTime
Now comes the thing that I really don't understand: I've been told that the only reason for having a Date_Id column is to use it as partitioning column for this table. I've asked about the real necessity of having this table partitioned and the asnwer was "to run queries more efficiently when wanting daily or monthly data". I didn't know to much about partitioning before this, so I checked these links:
Considering that the ideal query would be filtering by device and datetime, the questions are:
- What do you think would be the most efficient and ideal query for the first DB design (with partitioning)?
- Do you really think that the most efficent query against the first DB design is better than the second one (the one I wrote above)?
- If the previous one was affirmative, do you really think the improvement is worth enough having two extras fields (Id and Date-Id) and an extra index?
Thank you very much!!