Performance issue

[sheila11]

Hi all,

I have a Stored Procedure that runs in less than a second when there is no load on the database. But when just a few queries are fired, this Procedure takes as much as 30 seconds. The procedure queries three tables. It's a CLR Procedure and uses Regex.

I use (NOLOCK) in all my queries, and I don't see any locks on the tables that when I run SP_Lock.

What else should I check/do to resolve this issue?

TIA,
Sheila

 

[gmmastros]

Is it possible to remove the CLR procedure? This may improve your performance. What, specifically, are you doing with Regex that requires you to use a CLR?



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sheila11]

The CLR procedure runs a Regex function on one column.
The column contains patterns that are matched by the function against the URL parameter received when users hit the site.

 

[sheila11]

George,

You asked very valid question. I added some code to the Procedure, to create a temp table, with a smaller set of rows to run the CLR function on. So, now the Procedure is able to scale far better (6 to 7 seconds). But I need to see if I can do better.

Sheila