CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
user1255154
user1255154

Reputation:

Slow MSSQL Stored Procedure

I am not sure what I am missing, but I have a stored procedure that brings back the newest content in my database (via php), but it is really slow.

I have a View that brings in a specific kind of data (about 8000 records).

My stored procedure looks like this and takes about 9-11 seconds to complete, any advice? Be kind, I am new to this :)

 WITH maxdate As (
    SELECT id_cr, MAX(date_activation) "LastReading"
    FROM [pwf].[dbo].[content_code_service_new_content]
    GROUP BY id_cr
 )
 SELECT DISTINCT TOP 7 s.id_cr, s.date_activation, s.title, s.id_element
 FROM [pwf].[dbo].[content_code_service] s
 INNER JOIN maxdate t on s.id_cr = t.id_cr and s.date_activation = t.LastReading
  WHERE (
       id_service = @id_service
       AND  content_languages_list LIKE '%' + @id_language + '%'
  ) ORDER by date_activation DESC

Upvotes: 0

Views: 99

Answers (2)

Padwan
Padwan

Reputation: 101

I don't know anything about your data, but I would guess that this bit is hurting your performance

AND  content_languages_list LIKE '%' + @id_language + '%'

Searching with wildcards like that is always slow. For more info see https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

Upvotes: 0

Kevin
Kevin

Reputation: 2243

Okay, you're admitting your kinda new to this, so after all of this, you'll probably want to do some googling on how to performance tune SQL queries.

But, here's a quick rundown that should help you get through this particular problem.

First up: "Display Actual Execution Plan". One of the most useful tools in MS SQL is the "Display Actual Execution Plan" - which can be found in the Query menu. When this is checked, running the query will create a third tab alongside Results and Messages after you run the query. It'll display each operation the SQL engine had to perform, along with the percentage each took. Usually, this will be enough to figure out what might be wrong (if 1 of your 12 steps took 95% of the time, it's probably indicating how it's using the DB slowly.)

One of the most important things in this is looking at how it's actually reading the data from SQL - they're the right-most nodes in the little tree it constructs. There are a few possibilities:

Table Scan. This is usually bad - it means its having to read the entire table to get what it wants

Clustered Index Scan. This is also usually bad. Clustered Indexes are the table, and if it's Scanning it, it means it's looking through all the records.

Non-Clustered Index Scan. Not optimal, but not necessarily a problem. It's able to use an index to help out, but not enough that it can perform a binary search for what it's looking for (it has to scan the whole index.)

Index Seek (Clustered or Non-Clustered). This is what you're after. It's performing a binary find to get quickly to the specific data its looking for.

So! How do you get Index Seeks? You make sure your table has indexes on the appropriate fields.

From a quick skimming of your query, here are the columns that SQL's having to look up:

  • content_code_service_new_content.id_cr
  • content_code_service_new_content.date_activation
  • content_code_service.id_cr
  • content_code_service.date_activation
  • content_code_service.id_service
  • content_code_service.content_languages_list

So right off the bad, I'd check those two tables, and make sure those columns have indexes for them.

Upvotes: 1

Related Questions