Grant Fritchey (4/15/2009)

---

I am confused as to why the performance is so different between the two, but the fact that performance is sub-optimal is because of the functions on the...

Here is the actual execution paln for the query run on 2005

I'm not sure how to get the 2000 text plan you are talking about? Please excuse the ignorance

Here is the execution plans for the same query

I used the method that Lynn posted

select * from KRI_ASP_AccFACSNtEqAccConsole_DATA_C where

substring(BIBNAC,patindex('%[^0]%', BIBNAC),len(BIBNAC) - patindex('%[^0]%', BIBNAC) + 1)

NOT IN

(

SELECT

substring(accountnum,patindex('%[^0]%', accountnum),len(accountnum) -...

Lynn Pettis (4/14/2009)

---

I came up with another option, and I think it is easier. It uses patindex to find the first non-zero character.

select substring(@varstr,patindex('%[^0]%', @varstr),len(@varstr) - patindex('%[^0]%', @varstr) +...

Hi Grant

How can I send you the screen prints of the execution plans?

thanks Lynn, i will try that

Thanks Grant

I will look at the excecution plans but I might need your help with analyzing them

Ok wait I think we are takling past each other here

Here is a step for step list of things I did from scratch

-- I restored a sql 2000 db into...

Ok so what you are saying is that execution plans for the same query may be different in sql 2000 & 2005, and thus the reason for the different execution...

Yeah i've updated statistics and reindexed.

The udf is different code.

Ok I think I see what is happening.

So sql 2000 and sql 2005 might have different excecution plans for the same query?

Using a udf to remove the zero's also results...

Thanks Lowell

I tried your first query because it is similar to my current query and it ran very fast.

SELECT * FROM TABLE1 WHERE

CONVERT(INT,BIBNAC)

NOT IN

(

SELECT

CONVERT(INT,ACCOUNTNUM) WHERE ISNUMERIC(ACCOUNTNUM) = 1

FROM

Table2

)

WHERE ISNUMERIC(BIBNAC)...

Thanks.

Its part of normal business processes.

I'm just a bit concerned because the same type of query over the same amount of data on a sql 2000 server takes a matter...

Thanks for the reply.

It still does not explain the following...

select * from Table1 where

BIBNAC not in

(

SELECT

ACCOUNTNUM

FROM

Table2

)

completes in a like 3 seconds

where as

select * from Table1 where

REPLACE(LTRIM(REPLACE(BIBNAC, '0',...

Yeah I will get rid of the 'isnull'

I looked at the execution plan in both sql 2000 and sql 2005 and they are the same, and this does not help...