I have been working on my single page application for the past few months and while trying to improve some things on the back end I started digging more into my process of Insert/Update and database in general. I use SQL Server 2008 and database is designed using Primary Keys and Foreign Keys as well as Indexes in order to make strong and reliable connection between the tables. I'm talking about these elements since our current system has a very poor designed database. We have a lot of problems with deadlocks, and slow performance. Our system is state wide product that has a large transaction of data especially in time periods like begging or end of the school year. However my new application will should expect the same or even bigger translations in the future so my database design and Insert/Update processes have to be improved and prevent the deadlocks. Question that I have is about SQL Server standards that should be used in this kind of systems. For example I recently started looking more into stored procedures and seems that a lot of DBA experts are recommending going that way in situations where same query is used over and over. That should prevent some security risks as well as improve the efficiency. Here is example on how I currently handle Insert and Update transaction in my system:
<cftransaction action="begin">
<cftry>
<cfquery name="qrySaveDictionary" datasource="#dsn#">
DECLARE @Status BIT = <cfqueryparam cfsqltype="cf_sql_bit" value="#trim(arguments.frm_status)#">;
DECLARE @Name VARCHAR(50) = <cfqueryparam cfsqltype="cf_sql_varchar" maxlength="50" value="#trim(arguments.frm_name)#">;
DECLARE @Code CHAR(2) = UPPER(<cfqueryparam cfsqltype="cf_sql_char" maxlength="2" value="#trim(arguments.frm_code)#">);
DECLARE @ActionDate DATETIME = CURRENT_TIMESTAMP;
DECLARE @ActionID UNIQUEIDENTIFIER = <cfqueryparam cfsqltype="cf_sql_idstamp" value="#SESSION.AccountID#">;
<cfif len(trim(arguments.frm_recordid))>
DECLARE @RecordID INT = <cfqueryparam cfsqltype="cf_sql_integer" value="#trim(arguments.frm_recordid)#">;
IF EXISTS(SELECT RecID FROM Dictionary WHERE RecID = @RecordID)
BEGIN
UPDATE Dictionary
SET
Status = @Status,
Name = @Name,
Code = @Code,
ActionDt = @ActionDate,
ActionID = @ActionID
WHERE RecID = @RecordID
SELECT @RecordID AS RecID
END
<cfelse>
BEGIN
IF NOT EXISTS(SELECT 1 FROM Dictionary WHERE Code = @Code)
INSERT INTO Dictionary (
Status,Name,Code,ActionDt,ActionID
) VALUES (
@Status,@Name,@Code,@ActionDate,@ActionID
)
SELECT SCOPE_IDENTITY() AS RecID
END
</cfif>
</cfquery>
<cfset local.fnResults = {status : "200", message : "Record successully saved!", RecID : qrySaveDictionary.RecID}>
<cfcatch type="any">
<cftransaction action="rollback" />
<cfset local.fnResults = {status : "400", class : "alert-danger", message : "Error! Please contact your administrator."}>
</cfcatch>
</cftry>
</cftransaction>
In example above I use ColdFusion to check for existing record that is passed in the function argument. Also I use transaction in ColdFusion to prevent bad data in case of some error situation. Also I'm declaring all arguments inside of the query and then running Update or Insert statement. I read a lot of blog about IF EXIST and IF NOT EXIST and some people say that they are bad for performance and can cause deadlocks. My question is what is the best approach for this kind of statements:
1) Something like this:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
update table set ...
where key = @key
end
else
begin
insert into table (key, ...)
values (@key, ...)
end
commit tran
Or
2)
begin tran
update table with (serializable) set ...
where key = @key
if @@rowcount = 0
begin
insert into table (key, ...) values (@key,..)
end
commit tran
Or
3) Maybe use MERGE that I did not find a good example of. Also I'm wondering should this be a stored procedure function? Again I'm working by myself on this project, we do not have DBA in the company and my resources are limited. I'm trying to research on the internet and get the information about SQL Server and good practices. This project is heavy on data transactions as I mentioned and it's very important to me that I choose right approach. If anyone can provide some thoughts, information or examples that would be greatly appreciated.
