|
Comments
|
|
Deja Vu! I have gone through this exercise as a consultant, where finding missing indexes and applied really helped.
The flip side of adding indexes is on the write side when the indexes are updated based on the new row.
My question is - how is that cost of updating the index actually reflected in the profiler, if at all?
The idea would be easily figure out point of diminishing returns on adding indexes.
|
|
|
Ok, how I solved this:
I've created a non-clustered index on firstname/lastname + using page level compression.
This gave me sum of 5 reads in case of insert and 2 in case of duplicate found with the original SP.
Then I rewrote the SP. As far as the original SP does not care about the result I've decided to rely on exception. I tried to create UNIQUE nonclustered index on FirstName_LastName but there were some duplicates in the table from AdwentureWorks so I replaced the original index with UNIQUE NONCLUSTERED INDEX on FirstName, LastName, EmailAddress. Then I rewrote the SP like this:
ALTER proc [dbo].[AddContact]
@FirstName varchar(50),
@LastName varchar(50)
as
BEGIN TRY
insert into dbo.Contacts (
FirstName,
LastName)
values (
@Firstname,
@LastName)
END TRY
BEGIN CATCH
END CATCH
This gives me 3 reads per SP in case of insert and 4 in case of duplicate present.
|
|
|
You opened up a can of worms by re-introducing us to EXISTS. Does SQL Server use the other operators like ANY and IN any more efficiently than their more manual counterparts? Perhaps some fodder for Part 3?
|
|
|
too primitive in respect to significance of Reads and impact of the presence of an index on INSERT.
|
|
Jamshid Nouri on
5/20/2010
excellent demo!!!
|
|
Douglas Kemp on
5/20/2010
I like seeing your overall optimization process.
|
|
|
It was very interesting. Please consider to continue with more videos on performance tuning. Thanks!
|
|
|
I have really enjoyed the performance videos, hope you can do more. Thanks again.
|
|
Dennis Allen on
5/20/2010
-- Tuning is a great subject, I would like to see more
-- of these.
--
-- another optional syntax, which ever is clearer to the
-- folks who will be maintaining the procedure would
-- take precedents...
insert into dbo.Contacts ( FirstName, LastName )
select @FirstName, @LastName
where not exists (
select 1
from dbo.Contacts
where FirstName = @FirstName and LastName = @LastName
)
|
|
|
Let's see part 3 (and 4 and 5 and...)
|
|
|
Found these to be very informative, would love to see a part 3 as performance tuning is always an area I am looking to improve. Thanks.
|
|
|
Great instruction! Please include a part three. Performance tuning is an art, and I need all the help I can get. Thanks!
|
|
|
Great tip. I'm going to use this.
|
|
|
Thanks for the information. I really enjoyed the pointers on what to look for when performance tuning.
|
|
|
Pros know the best. Excellent video Andy. Thanks once again for sharing and keep up the good work.
|
|
|
Slavius' approach with the try/catch and unique index on firt and last would solve the problem if the data supported it. As described though with the email added to establish uniqueness, you can add duplicate first and last name, provided you give different emails, which is not what was requested. But I think the example is a little wierd, in that existing data already fails the logic trying to be implemented.
|
|
|
It's very informative. Look forward for part3
|
|
|
I think it was great. Would appreciate a part 3. Thanks andy
|
|
|
What about statistics? How can those influence query optimization?:
|
|
|
Excellent
|
|
|
can you show what are the settings of the profiler that are being used in this case.
|