What is the real cost of an SQL transaction?

Question

This requires a bit of background. I am creating a web application (ASP.NET/VB.NET with a SQL Server backend) that will accept the application for funding, and this form has contact fields for three people. Since the information is the same for all three people (name, email address, phone), I decided to keep the contact information in a table separately from the applications. The application table has an identifier field for each of these three contacts, which indicates a contact in the contact table.

However, this presents an interesting question / issue when doing my CRUD stuff. The only ways I can think of to create, update, and retrieve information for an application and all three contacts in a single SQL transaction require very complex stored procedures. In other words, it would be much easier (for me) to get this information through several transactions. However, since this information is never needed independently, I ALWAYS perform several transactions to obtain information for one application.

So my questions are:

• Is this design redundant? There will not be more than three people per application, and there MUST be exactly three. Am I getting too complicated by deleting this information in a separate table?
• What is the real cost of running multiple SQL transactions versus writing complex stored procedures and only needs one transaction?
• Generally speaking, what is the cost of an SQL transaction for a web application using ADO.NET?

Thanks for the bearing with this long explanation.

* EDIT *

After reading some of your answers, it seems I am using the term “transaction” incorrectly. What I'm really interested in is the cost of running multiple requests in the same connection, not one request. Sorry for misunderstanding.

Answer 1

. "" . : . , , "" (, , ). . , , , .

, . , : , 3 , 3 3 , . , .

, . , . , . . , .

, ? ( ), . , , . : . - , , , . . .

, : . , . "X , Y", "X - ".

: , , . (.. ) . , . , . . , . , , , :

SqlCommand cmd = new SqlCommand(@"
exec usp_firstProc @arg1, @arg2;
exec usp_secondProc @arg2, @arg3;
exec usp_thirdProc @arg1, @arg3;", connection);
cmd.Parameters.Add("@arg1", someValue);
...
cmd.ExecuteNonQuery();

, , . , 1000 /, , .

, . , . : ( ADO.Net) , .

Answer 2

• . Don't-Repeat-Yourself. , 2 4 3.
• . . .
• .

"", , "". , :

SELECT *
  FROM application

SELECT *
  FROM contact

, . , .

Answer 3

"" . , , ACID transaction aide.

, . , , / .

, , , :

SELECT app.field1, app,field2, contact1.name, contact2.name, contact3.name
FROM Applications AS app
INNER JOIN Contacts AS contact1 ON app.contact1ID = contact1.id
INNER JOIN Contacts AS contact2 ON app.contact2ID = contact2.id
INNER JOIN Contacts AS contact3 ON app.contact3ID = contact3.id

- , ( , .. , , , .

/ , , , .

, , , . - .

Answer 4

...

- , ()

ID, contact_id1, contact_id2, contact_id3, otherinfo

contact_id, info

- 1 , ... Conatact 'Bob' contact_1 8 , , bob , . - 3 ,

.. application_ID, Info application_ID, id_sequence, info

- , .

, ... 3 , . 1000 , , , ( ). 1... , . " " 2 .

№2. ... , . , ( , , ).

Answer 5

. , , .

Answer 6

1 , , .

- ( , 0 )

In a procedure, if the record keys are 0, the procedure adds records to the database and quickly retrieves the keys using scope_identity (). If you send non-zero keys to a procedure, it updates the record. Then the procedure returns all the data back, all the variables of the stored procedure declared as output.

Then you can wrap the procedure inside the begin / end sql transaction so that it is complete or nothing is done.