CODEWITHSUNDEEP
sqlsql-server-2008ef-code-firstentity-framework-5
hjavaher
hjavaher

Reputation: 2617

Which method should I use to save multiple rows of data?

First off, I'm using ASP.NET MVC with Entity Framework Code First 5.0 using SQL Server. I have an application that requires me to save the status (Working/Off) for every half hour of the day every day of the week. If you do the math that's 336 rows of nothing but (id, day, time, status, user_id) for every user (potentially thousands of users).

I was wondering which one these scenarios would be more optimum in terms of storage size and the performance of the application (in your opinion).

  1. Store these as 1 row per status (id, day, time, status, user_id) so 336 rows for every user.
  2. Save it all in a comma delimited field in the database (one very large row) and parse them out using C#.
  3. Is there a better way that I haven't looked into or haven't considered?

I'm currently leaning towards option 1.

Upvotes: 0

Views: 609

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

These are separate pieces of data so you should absolutely not munge them together as comma-separated values. This makes searching, reporting and other things a major pain.

Table-valued parameters are the most efficient way to do pass multiple rows to SQL Server. However, I do not know if EF is going to paint you into a corner - I hear that there are many normal things you can do in SQL Server that EF/CF still can't do.

How it works, you create a table type (I have to guess on data types here):

CREATE TYPE dbo.StatusData AS TABLE
(
  [day] DATE,
  [time] TIME,
  [status] VARCHAR(32),
  [user_id] INT
);

This should match the structure of your table where you plan to store this information (and again, no, you shouldn't store separate facts in the same column).

Now you can add this as a parameter to your stored procedure:

CREATE PROCEDURE dbo.SaveStatus
  @s AS dbo.StatusData READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.BaseTable([Day], [Time], [Status], [user_id])
    SELECT [Day], [Time], [Status], [user_id]
    FROM @s;
END
GO

Now you can just pass a DataTable or other collection of values from C# into a single stored procedure parameter (use SqlDbType.Structured).

Upvotes: 2

HMR
HMR

Reputation: 39320

I think it depends on what you need to report on. Like all users currently having a certain status. Status of a user during a certain period. Time a certain user had a certain status for a given period.

Status should be a foreign key, day and time could be milliseconds from epoch, rounded to half an hour and calculated to (if the user was in UTC), this requires more programming before inserting but as data grows it will benefit your performance on retrieving it.

Put indexes on the milliseconds, userid and statusid.

I used to write the class that does database stuff as a com+ component as that would increase performance (pooling, threading) if I remember correctly.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you are saving the data in the database, store the results in a table. I think you have described this as option (1).

This saves you from doing additional processing. The amount of data is small by database standard.

Upvotes: 0

Related Questions