|
I'd agree that it's better to put this in SQL than the application. In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.
However, I've seen suggestions that something like this might be slightly more efficient:
UPDATE YourTable SET ... WHERE ID = @ID
If @@ROWCOUNT = 0
INSERT YourTable (...) VALUES (...);
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: In SQL, there's less chance of the row being deleted between the If Exists test and the Update statement.
That's why we have transactions.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Just don't count on that to work on any database.
And for that purpose it's better to use MERGE[^]
|
|
|
|
|
IMHO anything that data handling (update, insert, delete, move, aggregate and on) is good to do in SQL - it's very good at it.
However IF isn't exactly in that category - so do update then insert where the where clause contains an addition of @@ROWCOUNT = 0 at the end...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Thanks for the feedback; I will use the ROWCOUNT test.
|
|
|
|
|
I don't like the rowcount method as is uses 2 writes to the database. Have not used the Merge function so I have no opinion.
I use convention instead, EVERY tale has an identity field as the primary key, so when I pass in the object (via variables) the ID field has either 0 or the record value. I then use IF to test the ID and either insert or update the record.
I put it into a tip [^].
While it is not guaranteed to be perfect it has been working for me for decades! 99% of database our work goes through stored procedure. String queries are heavily discouraged.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
SQL is very smart - an update of non existing record will end in a single index page read. And that read may be from cached index page if it's a highly used table. So no writing at the first part. And if ROWCOUNT isn't 0 no write in the second part...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
To be honest, I don't think it makes any difference which way it is done. Unless you are working with really big data the time taken is irrelevant these days. I was just offering an alternative, my code generator (written in the 90s) does all this work for me 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You may be right - and your approach is very good one. I read your tip and bookmarked it, as I plan to run some benchmarks on large data with you against rowcount...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: as I plan to run some benchmarks on large data with you against rowcount
I would be interested in the results from an intellectual POV but I suspect the differences will be so minute as to be irrelevant. Look forward to the results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Here it comes...
I got a personal info table - containing id, name, user, password, address, email and phone - with 19,245,718 records. The table has PK and indexes...
Run an insert/update of 108,382 records of which 32,190 where duplicated (same id).
With your method it took an average of 26.77 ms/record.
With the @@ROWCOUNT approach it took an average of 13.07 ms/record.
Both test run on the same machine with local SQL after restart.
The computer is a bit old one (one I can spare) with 4x2.93 CPU and 8GB memory, but I believe that the differences are real...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
The most important advantage of the MERGE statement is that the table is read and processed in one pass only.
So if you insert/update only one record there's no real win. It's when you insert/update from a query you'll get the real performance gain.
|
|
|
|
|
Jörgen Andersson wrote: The most important advantage of the MERGE statement is that the table is read and processed in one pass only.
Apparently not in Microsoft land:
MERGE looks like it will take care of concurrency for you, because implicitly it seems to a single, atomic statement. However, under the covers, SQL Server really does perform the different operations independently.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
No it doesn't handle concurrency for you. 
It was about performance only.
<edit>Some interesting info[^] on the subject</edit>
|
|
|
|
|
It is always better to put all the database code on the database side. An equivalent application code for the above query will make atleast three database calls as opposed to one call when the IF is on the database side.
There could be a race condition with you query and the possibility of a row being inserted by another user between the IF and the INSERT, so you should actually handle the exception and take appropriate action (either let the user know that someone else has already inserted it or do an update). The possibility is even higher in case of application code because of network latency between the server and the client.
And your UPDATE statement is missing the WHERE clause and it would end up updating all the rows in the table 
|
|
|
|
|
Gregory.Gadow wrote: I would think this is more efficient than putting the if...else in the application,
And this matters because your application currently has an efficiency problem?
Gregory.Gadow wrote: move a lot of code to SQL Server as stored procedures
I use stored procedures for the same reason I have a database layer in an application - to make the layers more distinct and hopefully insuring that access happens in a controlled and reasonable way.
Some business logic which might have performance concerns, can be done must more efficiently in the database server than a client application. There are of course still ways to badly mess this up.
Other than that one problem with using conditional structures in stored procedures is that programmers who do not understand how to really use SQL can end up creating structured programs (ifs, whiles, etc). Of course if one uses stored procedures then if all else fails one can hire a real DBA to redo the stored procs.
|
|
|
|
|
Hello There!!
I want to know database design tools to create a large database. so can any one help me??
|
|
|
|
|
|
|
ya i will try this one .
|
|
|
|
|
|
|
Hello,
I am a new in EF.
I have a Customewr class:
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string Email { get; set; }
}
And here is my DbContext class:
public class DiveShopContext:DbContext
{
public DbSet<Customer> Customers { get; set; }
}
I want to be able to save Customers to my DB, i do it as follow:
<pre lang="cs">class Program
{
static void Main(string[] args)
{
using (var db = new DiveShopContext())
{
var customer = new Customer();
customer.CustomerId = 3045;
customer.Name = "Alex";
db.Customers.Add(customer);
db.SaveChanges();
}
}
}</pre>
Every thing is saved fine but the CustomerId is changed from 3045 to 1 and saved.
|
|
|
|
|
Presumably what you actually mean is that when you save it and then look at the saved customer that the customer id is then 1 instead as you expected being 2945.
Presuming that you are in fact looking at the correct database/table/column then I would suppose that the specific column is an auto index column. And you shouldn't be attempting to set the id in the first place.
|
|
|
|
|
columbos14927 wrote: but the CustomerId is changed from 3045 to 1 and save
I am going to guess that CustomerID at the database level is an Identity Column? If that's the case then the value you are trying to set is not going to be accepted by the database engine.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
