SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 User-Defined function in WHERE clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wjerla
Starting Member

3 Posts

Posted - 11/23/2007 :  03:32:33  Show Profile  Reply with Quote
Hello,

I have a stored proc similar to this:

CREATE PROCEDUE SearchDB
@param int
AS
BEGIN
SELECT col1, col2, col3
FROM tb1 INNER JOIN tb2 ON tb1.col1 = tb2.otherColumn
WHERE dbo.IsMatch(@param, tb1.col1)
END

This runs fine in SQL Management Studio, and gives the expected results. But when I call the stored proc from an ASP.NET application, the query never completes - it times out.

I know I'm connected to SQL Server, I can access other stored procs in the same database just fine. It is the dbo.IsMatch() user-defined function that is causing SQL Server to timeout -- if I replace the function with, say, 'WHERE tb2.col1 = 5' it works fine from .NET.

Anyone have any ideas? I'm a little lost on this one. The really weird thing is that another stored proc that uses the dbo.IsMatch() function runs fine from .NET.

wjerla
Starting Member

3 Posts

Posted - 11/23/2007 :  03:34:19  Show Profile  Reply with Quote
Ooops, the actual WHERE clause I have is

WHERE dbo.IsMatch(@param, tb1.col1) = 1
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 11/23/2007 :  04:10:40  Show Profile  Reply with Quote
It sounds like SQL is unable to resolve the where clause. What is IsMatch() doing exactly? It look to me like it is just trying doing an = or Like for the @param againt the column you pass in.

In the procedure that is working, is the function called in the where clause as well?
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/23/2007 :  12:00:05  Show Profile  Reply with Quote
I'm guessing you have much more data in the live ASP.NET environment and using a function like that will perform very badly with lots of data - badly enough that it times out in your case.

In cases like that where the function is probably doing something pretty simple anyway, and you're query lots of data, just put the expression right in the query and it will probably run considerably faster.

Also check your indexing, do you have an index on tb1.col1 (I don't know what the function does so I can't say for sure that you need one, but you probably do for the join anyway)?
Go to Top of Page

wjerla
Starting Member

3 Posts

Posted - 11/23/2007 :  14:36:17  Show Profile  Reply with Quote
Thank you, snSQL, you were correct. The amount of data was much larger in this case, causing it to timeout. I rewrote the query without the function, which resulted in a larger 'WHERE' clause but the query runs many orders of magnitude faster now.

Thanks!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000