T-SQL query runs slower for particular user accounts

Question

Post reply

T-SQL query runs slower for particular user accounts

narensantanam

SSC Journeyman

Points: 99
More actions
July 11, 2008 at 3:50 am

#189391

Hi,
On a SQL 2005 platform, we have a rather peculiar problem:
When 1 of our users executes a T-SQL query (as below), it runs much quicker than when any other user runs it. Also, we have noticed that the query execution plans generated for that user vs the others is completely different. All users are connected to the same SQL Server instance, database and running the same query.
Can anyone think of why this would happen ?
Thanks
Naren.

Viewing 9 posts - 1 through 8 (of 8 total)

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

DNA_DBA SSCrazy Eights Points: 9107 More actions · Answer 1

DNA_DBA

SSCrazy Eights

Points: 9107

July 11, 2008 at 6:55 am

#841435

Where's the query?

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 2

ANSI settings are different for that user than the others. Probably, but not definately, ANSI_NULLS.

"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

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 3

Ansi settings may indeed have an impact.

Also check the transaction isolation level.

(although IMO that shouldn't generate another exec plan)

Same query :crazy:

Are you sure the query is 100 % equal !

(included the used variable definitions !!)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

Please provide the query?

----------------------------------------------
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

narensantanam SSC Journeyman Points: 99 More actions · Answer 5

Thanks everybody, I did figure out that the ANSI settings were different. What I did notice however, was that because of the earlier ANSI settings (most probably the NULLS setting), the execution plan showed a "LAZY SPOOL (Table Spool)" in the execution plan. Can someone please help me understand what these ANSI settings are about and how they influence the query plan ?

1. This is the query that had the problem:

SELECT Tran_Date_Key, Account_Key, ABS(Amount), ABS(Amount), Tran_Key, 200807

FROM F_Transaction_200807

INNER JOIN D_Tran_Code ON F_Transaction_200807.Tran_Code_Key = D_Tran_Code.Tran_Code_Key

WHERE D_Tran_Code.Tran_Payment_Received = 1

AND Tran_Key NOT IN (SELECT Tran_Key FROM [F_Payment_Received] (nolock) WHERE Tran_Table = 200807)

2. Without changing the ANSI settings, when I rewrote the query as below, it ran equally quick for all users that executed it.

SELECT a.Tran_Date_Key AS Received_Date_Key, a.Account_Key, ABS(a.Amount) AS Amount_Received, ABS(a.Amount) AS Amount_Unmatched, a.Tran_Key, 200807 AS Table_Name

FROM F_Transaction_200807 a WITH (NOLOCK)

INNER JOIN D_Tran_Code WITH (NOLOCK) ON a.Tran_Code_Key = D_Tran_Code.Tran_Code_Key

LEFT OUTER JOIN [F_Payment_Received] WITH (NOLOCK) ON a.Tran_Key = [F_Payment_Received].Tran_Key AND [F_Payment_Received].Tran_Table = 200807

WHERE D_Tran_Code.Tran_Payment_Received = 1 AND [F_Payment_Received].Tran_Key IS NULL

GSquared SSC Guru Points: 260824 More actions · Answer 6

Well, for one thing, left outer join is usually faster than Where Not In.

Second, you're speeding it up with all the NoLock hints. Risking dirty reads (potential for junk/wrong data), but faster.

The ANSI settings are explained pretty well in Books Online. The first one to look up is ANSI_NULLS. Look up "Query Execution Options (ANSI Page)" in Books Online for a good summary.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 135265 More actions · Answer 7

Tran_Key NOT IN ( SELECT Tran_Key

FROM [F_Payment_Received] (nolock)

WHERE Tran_Table = 200807 )

There are some nice articles at SSC regarding NULL and ISNULL, ... and the ANSI effects ...:Whistling:

www.sqlservercentral.com/articles/Advanced+Querying/2829/

www.sqlservercentral.com/articles/Basic+Querying/ansijoins/189/

www.sqlservercentral.com/articles/Administering/understandingtheimplicationsofansisql92setoptions/482/

www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/

www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/

...