Select query runs forever in sql 2005

Question

Select query runs forever in sql 2005

Viewing 15 posts - 16 through 30 (of 68 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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)

Rowan-283474 SSCommitted Points: 1560 More actions · Answer 3

Thanks Grant

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

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)

Rowan-283474 SSCommitted Points: 1560 More actions · Answer 6

Rowan-283474

SSCommitted

Points: 1560

April 15, 2009 at 1:08 am

#977870

thanks Lynn, i will try that

Rowan-283474 SSCommitted Points: 1560 More actions · Answer 7

Hi Grant

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

Rowan-283474 SSCommitted Points: 1560 More actions · Answer 8

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 9

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]

SQL-4-Life

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

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)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 13

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]

SQL-4-Life

Rowan-283474 SSCommitted Points: 1560 More actions · Answer 14

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

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 15

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]

SQL-4-Life