SQL Server stored proc performance

I have an ASP.NET app that has the following SQL query in a stored procedure:

SELECT @result = COUNT(id)
FROM course_tracking
W开发者_运维知识库HERE employee_number = @employee_number
AND course_code = @course_code

IF @result = 0
BEGIN
    INSERT INTO COURSE TRACKING
END
ELSE
    UPDATE COURSE TRACKING

The Course Tracking table has almost a million rows in it and SQL Profiler shows between 40k and 50k reads each time this proc is executed which is once ever couple of seconds.

Since I'm not a SQL Server perf guy...what is the best way to correct this?

Any help is greatly appreciated.

Thanks

---

Try this

IF EXISTS(SELECT 1 FROM course_tracking
WHERE employee_number = @employee_number
AND course_code = @course_code)
BEGIN
     UPDATE COURSE TRACKING
END
ELSE
BEGIN
     INSERT INTO COURSE TRACKING
END

---

Alex,

There can be a number of reasons for the large amount of reads, and I believe that @Jonathan's answer will help, or mask the problem for now. The reason for getting lots of reads will be because you are missing some critical indexes, and/or your table statistics is not up to date, or it is turned off.

Now doing perf tuning on a large table like this can be complicated, if it is used by many other procedures, views and queries. Because changing any indexes can have a massive impact on other queries that are running fine now, but then again if your table is lacking any indexes, any change might just help. Things to consider when you attempt optimising something like this is:

1. How frequently is this query ran
2. When it run's does it impact other processes on the system
3. What is the most frequent operation on this table, inserts or updates?

If this query is ran once a day, or very infrequently, and it is not impacting other processes, I might be inclined to leave it. But if this is a process that runs very frequently and/or it is impacting other processes, then you need to fix it. Now by fixing it, it can be as simple as just adding a WITH(NOLOCK) table hint if you find it is blocking, but if it is ran very frequently, then look at indexes and statistics.

If you have a lot of inserts on this table, then any extra index will impact insert performance, but it will speed up updates. Then comes index fill factor etc etc. My point is that perf tuning on a Sql box is very complicated, and requires the entire picture most of the time.

SO what I would suggest is that you take a few hints here and, because nobody knows your system as well as you, you can make some decisions, and experiment, and learn from it too! Enable your Query Execution Plan and look at it, see if you see any table scans, they are bad, and indicate a lack of proper indexes, so does key lookups.

But looking at your query you have given here and ignoring any other factors, here is what I would suggest.

Make sure you have a NON CLUSTERED index that have employee_number and course_code in the index. If it is Sql 2008 you can add some included columns too if you have other queries filtering on these two columns, but output other columns. As far as if they should be ascending or descending in the index is up to you, but leave them both as ascending.

Then for the query, use what @Jonathan (+1 for you!) proposed, with a minor change, add a WITH(NOLOCK) hint on the table. This will tell the Sql optimiser not to lock the table when it reads it, but you will be able to read dirty data. Normally not a problem, unless it is a highly transactional table, lots of updates and inserts going on the whole time.

IF EXISTS(SELECT 1 FROM course_tracking WITH(NOLOCK)
WHERE employee_number = @employee_number
      AND course_code = @course_code)
BEGIN
     UPDATE COURSE TRACKING
END
ELSE
BEGIN
     INSERT INTO COURSE TRACKING
END

Also, check if your statistics are updated automatically on the database, if not, create a maintenance job that will update it once a day, when the server is least active. Create a SQL Agent job with this as the query EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'

---

If your query is actually updating the rows WHERE employee_number = @employee_number AND course_code = @course_code), then you could try this technique:

UPDATE course_tracking
SET ...
WHERE employee_number = @employee_number
  AND course_code = @course_code;
IF @@ROWCOUNT = 0
  INSERT INTO course_tracking
  ...

---

If you have SQL 2008, the MERGE syntax is good. Pinal Dave has a good walkthrough http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

MERGE course_tracking AS c
ON c.employee_number =  @employee_number
AND c.course_code  = @course_code 
WHEN MATCHED THEN 
UPDATE STUFF
WHEN NOT MATCHED THEN
INSERT STUFF
GO