0

I have a requirement to call a stored procedure which can take up to 15 minutes for each call on SQL Server 2008 database.

Will it cause performance impact on other systems accessing the same database / database server? If so, how can I minimise the performance impact?

Update: The store procedure compares audit history records and it needs to call another stored procedure depending on the type of the record so it has to be done row by row. I can change the cursor to while loop however there could be more than 3 million rows to process (depending on the filter criteria).

developer
  • 1,401
  • 4
  • 28
  • 73
  • cursor or while loop are both slow, and they are probably the reason why you sp is so slow. try to replace it with CTEs and joins – Ethan Li Apr 12 '13 at 05:22

4 Answers4

2

I can usually get away with not using a cursor by using a join with the list of IDs that need to be processed. Your cursor query can usually serve as a WITH clause or an internal join to select the rows to be processed within the statement that does the processing.

But do check the indexes first. That's the most detrimental cause of slow queries. At least every column in the where clauses and all ON columns in joins should be indexed.

Metaphor
  • 6,157
  • 10
  • 54
  • 77
0

Take a look at the CPU activity on the server when the proc is active. If you know how to use PerfMon, you can look at some SQL Server counters too.

I'd say that a 15 minute query is probably a candidate for optimization. Make sure you have all necessary indexes and avoid using cursors.

Metaphor
  • 6,157
  • 10
  • 54
  • 77
  • Thanks for the quick reply! The stored procedure has to perform certain operation for each row so I am using cursor inside the stored procedure - Do you recon I should open a data reader connection from .net and process each row in .net code instead? – developer Apr 05 '13 at 15:28
  • 1
    @Mait Yes! Anything! Cursors are one of the worst thing you can do for your performance as they take a lot of memory and 15 min procedure using cursors will most definitely take an impact on overall server performance during it's execution. Fortunately there are a lot of good ways to replace cursors. Best option would be to convert your queries to set based operation. Here is good link explaining various ways of doing it - http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them – Nenad Zivkovic Apr 05 '13 at 15:39
  • @Mait And, at the end, if you don't find a way to move to set based operation and really need to go row-by-row, there still are a better options then cursor - SQL while loop or .NET. – Nenad Zivkovic Apr 05 '13 at 15:40
  • @Nenad I have updated the question with the functionality required by the stored procedure. – developer Apr 05 '13 at 15:59
  • I was wondering if there is any way we can restrict SQL server to use limited resources for this stored procedure so it can run for longer period but without impacting other database queries? – developer Apr 05 '13 at 16:00
0

Option 1. Run as is but this will most likely cause performance issues.

Option 2. Rework query to improve performance if possible. Surely it cant be taking 15 minutes unless your SQL is badly written or you are just working with massive amounts of data. Maybe post the SQL??

Option 3. As you mentioned you could do the work in .NET.

CathalMF
  • 9,705
  • 6
  • 70
  • 106
0

We have similar issue with our server. My experience is SQL server is quite resource intensive application, if you have such a sp, it will have a huge impact on the server's performance.

Here are few things you can check to improve performance of the sp, or make use less resource:

1, run query analyzer to check if you are missing indexes, add them if any.

2, if you use temp table, try not to use it. Tempdb is use by SQL server for every database, if you use it a lot in your sp, when it runs, it is going to block other queries which use temp table. Alternatively, you can use cte.

3, redo you sp to make smaller, perhaps you can break it into few smaller one which can be run in a parallel.

4, this is a wired one. If you pass parameters to the sp, in the sp, assign these parameters in to local variables, and use these variables in the sp, rather than the parameters. SQL server generates better execution plan with local variables. You will see huge speed improvement and. You won't believe it.

If you can post the sp, I am happy to help you to make faster

Ethan Li
  • 1,001
  • 1
  • 13
  • 18