|
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.
|
|
|
|
|
|
|
|
|
I have my inner query is giving me only parent
but I want to make use of the result of the
inner query again as input till I did not find
any child ,so I am using procedure as I have to
collect all the parent I get from the inner query
and then update the outer query using the inner query
result.it is the requirement what I want to achive,
e.g
update table A
set A.a as "xyz"
//here I will get t
where A.id in
(
select A.child_id from A,B,C
--I get the child by passing parent as input and again consider the child A.child_id as parent
and find the child
where A.id= b.id
b.name = x and A.prent_id = ?
//few join condtion here
)
e.g
input 1 result 1.1
input 1.1 result 1.1.1
input 1.1.1 result 1.1.1.1
update A
where A.id in
('1.1','1.1.1','1.1.1.1')--here I want to use a container and make use of it
Thanks for your response
|
|
|
|
|
ramina sen wrote: I am using procedure as I have to
collect all the parent I get from the inner query,
How about putting that into a temporary table and work with that?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
I've used the following expression, to define the number of rows per page.
=Int((RowNumber(Nothing)-1)/50)
However the last row is displaying on a new page although the previous page does not exceed 50 rows.
How can I prevent the last row from displaying on a new page?
|
|
|
|
|
I suspect the whole concept of defining a page length based on the number of rows is wrong. What happens if you reduce the height of the rows slightly. Page size has a defined length and each element within the page has it's own dimensions (rows, header, footer and groups) all of which affect the placement of the data relative to the page length.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have two tables in MS-Access "tmpChanges" (685 records) and "tmpChangesWithDates" (785 records). I want to design a query that will only show those 100 records that are the difference between the two tables. Each has 4 fields and are common to both tables: State, City, Sale, SaleDate. The table "tmpChanges" has no value listed in the SaleDate field. I tried this and it did not work, any clues?:
SELECT tmpChangesWithDates.City, tmpChangesWithDates.State, tmpChangesWithDates.Sale, tmpChangesWithDates.SaleDate
FROM tmpChangesWithDates LEFT JOIN tmpChanges ON tmpChangesWithDates.[Sale] = tmpChanges.[Sale]
WHERE (((tmpChanges.SaleDate) Is Null));
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
