April 14, 2009 at 4:34 pm
OK. That's very complete.
Get the execution plans. Especially the one where the 2005 ran well, but the others too. That way we can tell what's happening between the two servers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2009 at 4:39 pm
See if you can use this code to strip leading zeros and if it helps your query.
declare @varstr nvarchar(50);
set @varstr = '000045691823';
select substring(@varstr,patindex('%[A-Za-z1-9]%', @varstr),len(@varstr) - patindex('%[A-Za-z1-9]%', @varstr) + 1)
set @varstr = '000Z045691823';
select substring(@varstr,patindex('%[A-Za-Z1-9]%', @varstr),len(@varstr) - patindex('%[A-Za-z1-9]%', @varstr) + 1)
April 14, 2009 at 4:40 pm
Thanks Grant
I will look at the excecution plans but I might need your help with analyzing them
April 14, 2009 at 7:06 pm
No worries. Try the stuff Lynn is suggesting. He's looking over the queries. I just glanced at them. I'm interested in how the execution plans vary.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2009 at 9:39 pm
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) + 1)
April 15, 2009 at 1:08 am
thanks Lynn, i will try that
April 15, 2009 at 3:59 am
Hi Grant
How can I send you the screen prints of the execution plans?
April 15, 2009 at 5:16 am
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) + 1)
Hi Lynn
I tried your method and it works nicely, but when I run it on my sql 2005 instance it runs for about 9 minutes and when I run it on my sql 2000 instance over the sa data it runs for 2 seconds.
I looked at the diffenrent execution plans for 2000 and 2005 and they are completely different.
Why why why?
Thanks
April 15, 2009 at 5:52 am
could you save the execution plans as .zip files and attach them.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 6:06 am
Here's a video on exactly how to capture the execution plans & post them to SSC.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 15, 2009 at 6:25 am
While you are capturing the Execution Plan, could you post the complete query code you are running and the DDL (Including indexes) for the two tables.
From what I can tell, you are looking for all records in Table1 that does not have a corresponding record in Table2 based on Table1.BIBNAC = Table2.ACCOUNTNUM, correct?
April 15, 2009 at 6:33 am
Had an idea for SQL Server 2005. This WON"T work on SQL Server 2000. Please give it a try, and let's see what happens.
select
*
from
Table1 T1
where
substring(T1.BIBNAC,patindex('%[^0]%', T1.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1.BIBNAC) + 1) in
(select
substring(T1A.BIBNAC,patindex('%[^0]%', T1A.BIBNAC),len(T1.BIBNAC) - patindex('%[^0]%', T1A.BIBNAC) + 1)
from
Table1 T1A
except
select
substring(T2.ACCOUNTNUM,patindex('%[^0]%', T2.ACCOUNTNUM),len(T2.ACCOUNTNUM) - patindex('%[^0]%', T2.ACCOUNTNUM) + 1)
from
Table2 T2)
April 15, 2009 at 6:34 am
mmm
Please confirm if this is a double post or not?
http://www.sqlservercentral.com/Forums/Topic694547-146-2.aspx?Update=1
If it is then could the reason not be that your code is different.
The one removes 0's and the other doesn't
as Lynn and I asked before please pot your code...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
April 15, 2009 at 6:35 am
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) - patindex('%[^0]%', accountnum) + 1)
FROM
KRI_ASP_AccFACSNtEqAccConsole_DATA_A
)
over the same amount of data on sql 2000 and 2005
I could not save the execution plan for the 2000 instance but attached a screen shot
April 15, 2009 at 6:43 am
hwo long did you say the 2005 query takes vs the sme query on 2000?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 16 through 30 (of 68 total)
You must be logged in to reply to this topic. Login to reply