|
I usually try and pick the index that is going to hit the most granular field and create that first, rerun the analysis and repeat until satisfied with the performance. This often reduces the number of indexes actually required to gain significance performance gains.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
As Mycroft suggests, create the most granular index first. Indexes tend to be more useful when the distribution of data in the indexed column(s) is high. And create one index at a time and rerun the query to see how much performance improvement you've gained, if any. This way, if you find that the query has actually slowed down (which may happen sometimes), you can always disable or drop the index and proceed with the next one.
|
|
|
|
|
I've been at this for more than 15 years, and I'm still learning stuff. Most recently, it has been conditional commands in SQL. By way of example:
IF EXISTS (SELECT * FROM Inventory WHERE ProductId=@ProductId)
UPDATE Inventory SET Price=@Price, Count=@Count
ELSE
INSERT Inventory (ProductId, Price, Count) VALUES (@ProductId, @Price, @Count)I would think this is more efficient than putting the if...else in the application, and it would let me move a lot of code to SQL Server as stored procedures. But before I go hog wild, I wanted to hear from other programmers on whether this is Good Design or something that should be avoided.
|
|
|
|
|
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 .
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
