CODEWITHSUNDEEP
c#sqlsql-server
zsebacher
zsebacher

Reputation: 11

Is there a more efficient/safer way of checking if a record exists and then inserting

I have the below code, and I am wondering if there is a more efficient and faster way of doing something like this for scaling purposes. This is a monitoring program, that checks for files and if it doesn't find the record already in the database, then it inserts it. But as we add on more and more directories to monitor I'm wondering about performance issues. Is there a faster way to check then using the an if not exists followed by an insert statement all in one sql command? Thank you for any suggestions. 

SqlCommand cmd = new SqlCommand("if not exists (select File_Name from [table].[dbo].[tablename] " +
    "where Workflow_Step_Name=@iWorkflow_Step_Name and File_Name=@latestfile) INSERT INTO " +
    "[table].[dbo].[tablename] (Workflow_Name,Workflow_Step_Name,Creation_Date,File_Name,File_Path,File_Size,Insert_TimeStamp,Workflow_Id,Batch_Id) " +
    "Values(@iWorkflow_Name,@iWorkflow_Step_Name,@lastUpdated,@latestfile,@iFolder_Path,@fileSize,@timestamp,@iworkflow_Id,@batch_id)");

cmd.Connection = myConnection;
cmd.Parameters.AddWithValue("@iWorkflow_Name", i.Workflow_Name);
cmd.Parameters.AddWithValue("@iWorkflow_Step_Name", i.Workflow_Step_Name);
cmd.Parameters.AddWithValue("@lastUpdated", lastupdated);
cmd.Parameters.AddWithValue("@latestfile", latestfile);
cmd.Parameters.AddWithValue("@iFolder_Path", i.Folder_Path);
cmd.Parameters.AddWithValue("@fileSize", fileSize);
cmd.Parameters.AddWithValue("@timestamp", DateTime.UtcNow);
cmd.Parameters.AddWithValue("@iworkflow_Id", i.Workflow_Id);
cmd.Parameters.AddWithValue("@batch_id", batchId);

myConnection.Open();
cmd.ExecuteNonQuery();

myConnection.Close();

Upvotes: 0

Views: 79

Answers (1)

The Impaler
The Impaler

Reputation: 48780

This is a clear example of anti-pattern.

You shouldn't enforce uniqueness by searching for similar rows and then inserting, since it's always subject to multi-user/multi-thread issues. You cannot make it error proof and will always be unsafe. Another user may insert a row in the millisecond it takes between the check and the insert.

Enforce uniqueness by adding a database constraint, as in:

alter table [table].[dbo].[tablename] add constraint unique_row
  unique (col1, col2, col3, ... colN);

This way the database takes care of the check.

Your uniqueness query seems to be based on two columns: Workflow_Step_Name and File_Name. If this is the case, then the following constraint will do what you need:

alter table [table].[dbo].[tablename] add constraint unique_file_name
  unique (Workflow_Step_Name, File_Name);

Then you just insert without prechecking: the database will perform the check for you. If there's already another row with those exact same values, the INSERT will fail.

Now, if your application is a single user application and always will be, then I guess you could execute a SELECT to check there's no row with the exact same file, and then perform the INSERT. In any case, it's a lot safer to add the constraint at the database level, to protect the data in case your program has a bug.

Upvotes: 1

Related Questions