User-Defined Function in Where Clause 1

[andrea96]

I am attempting to use a user-defined function in the where clause of a query, but I am having a problem. With just one table, the query runs quickly. However, if I join two tables, the query runs until I cancel it or it times out. If I use the same function in the Select clause instead of the where clause, the query runs quickly with the two tables joined.

Runs until cancelled or times out:

SELECT i.part_no, qty, description, dbo.PartType(i.part_no)
FROM inv_master i, 
     what_part w
WHERE i.part_no = w.part_no and
      asm_no = 'K104081606' and
      dbo.PartType(i.part_no) = 'Special Part'

Runs quickly:

SELECT i.part_no, qty, description, dbo.PartType(i.part_no)
FROM inv_master i, 
     what_part w
WHERE i.part_no = w.part_no and
      asm_no = 'K104081606'

My other option is to change my function to a stored procedure and use a cursor, but I had hoped to avoid that.

Does anyone have any ideas?

Thank you,
Andrea

 

[hmckillop]

I would guess that in the second case, in order for SQL to determine which rows to filter data by, it needs to apply the function to all rows in the inv_master table and then try and filter on the match.

Can you post what the function does and what it accesses and maybe we could have a better look at ways to sort it out.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

No a cursor will probably be as slow or slower.

Functions in the where clause do not allow SQL server to use the indexes properly and should be avoided. Clearly we can't always do so.

Some things to try

Make a derived table out of the query which runs fast and then join it to the other table.

Take the query that runs fast and insert it into a temp variable or temp table. Then join to the temp table.

Please learn to use the preferred join syntax, right now I suspect it thinks this is part of the join which is making it run on every joined record instead of just the ones from table one. Running a function against 10000 records in table one, vice 200,000 records in the joined tables can be a big difference in processing time. I don't know that for a fact, but since the query without the join runs quickly, I would certainly suspect that to be what it is doing. The join should be clearly defined separetly fromthe where clause. Incidently, this syntax is unreliable if you do a left or right join, so you might as well learn the preferred join syntax now before you need to do those types of joins.



Questions about posting. See faq183-874

 

[ESquared]

Should I use JOIN or WHERE to join tables?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[andrea96]

Thank you, SQLSister. I ended up using a temp table, and so far, it is running quickly.