The Dixie Flatline (7/1/2010)

---

An odd fact about inline table valued functions is that you can run inserts, updates, and deletes against them. They really are treated like parameterized...

CirquedeSQLeil (7/1/2010)

---

Jack Corbett (7/1/2010)

---

GilaMonster (7/1/2010)

---

I should bloody hope that a test with 1000 rows returns in under a second. It requires a really bad query to process 1000 rows in...

Cant guarantee itll always work 🙂

I took the liberty of forwarding on a simpler example of this to Connor Cunningham.

Not one but two blogs resulted...

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/07/01/conor-vs-join-algorithms.aspx

http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/07/01/conor-vs-udfs-in-joins.aspx

Check out simon sabins recent 24 hours of pass session

http://www.sqlpass.org/24hours/2010/Sessions/HighPerformanceFunctions.aspx

Just out of interest what a the rowcounts of the two table involved ?

Glad to of helped.

Though ,

A) Ensure that your statistics are upto date as Gail suggested.

B) Work on Getting rid of the function altogether (inline table functions are cool), i...

The biggest change is that the join is now a nested loop rather than a hash match.

You could try

Select Distinct t.MatchValue,M.MASTERname,M.MASTERCODE

from MASTER_TABLE M

INNER HASH JOIN

DB2..BufferTable t where

dbo.fnMyFunction(c.M.MASTERname)...

Is there a question here ?

Eeeek 🙂

Scrap that entirely.

Scalar UDFs perform very poorly. For Performance reason, here i would probably add a persisted calculated column for the processed (ie cleaned) values.

http://www.kodyaz.com/articles/sql-server-computed-column-calculated-column-sample.aspx

You could investigate service broker.

http://technet.microsoft.com/en-us/library/cc966419.aspx

Coincidence ? , homework ? or left hand not talking to right hand ?

Both similar problems , 2000 to 2008 upgrade , udf performance

http://www.sqlservercentral.com/Forums/Topic945233-392-1.aspx

http://www.sqlservercentral.com/Forums/Topic945210-392-1.aspx

Now cross posted

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/04351747-bbaf-4691-acae-9f75d63b56a4

IMO , table variables should be avoid unless you are using a small (100 ish) number of rows.

http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

That being said , Function calls are generally also wrong as they lead...