query help for lot of reads - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query help for lot of reads

Author

Topic

buzzi
Starting Member

48 Posts

Posted - 2008-10-24 : 17:35:38

Hello all,
I have the below query can some one please tell me whats wrong with this query.

declare @ActiveStatusId int
SELECT @ActiveStatusId = StatusId FROM [Status] where statusname = 'Active'
select uid,uname,email
from #R1 S
inner join tblusers u on s.RecipientId=u.companyId and s.RecipientTypeId=102 and u.StatusId = @ActiveStatusId
inner join tblcompanies C on C.CompanyId = U.CompanyId and C.StatusId = @ActiveStatusId
inner join tbllocsusermap l on u.userid=l.userid
inner join tblLocs Loc on Loc.LocationId = l.LocationId
inner join tblNetworkLocsMap vl on vl.locationId=l.locationId AND vl.StatusId = @ActiveStatusId
inner join
tblnetworks v on v.networkId =vl.networkId
left join tblDepts D on D.DepartmentId = U.DepartmentId
where v.StatusId = @ActiveStatusId
and (
(v.OriginatorCompanyId=u.companyid and (v.OToR=1 or v.OToO=1 ))
or
(v.RToR=1 )
or (v.RToO=1 and v.OriginatorCompanyId = SenderCompanyId))
and v.networkId=s.sendernetworkId

If i run the above query there logical reads in profiler shows me in millions,But i replace the @ActiveStatusId as 1(which is the id for Active Status), the reads comes down to 1000's. I am breaking my head to figure this, anyhelp greatly appreciated, by the way all these tables have few 1000's of records none are huge tables

sample data in the temp table
RecipientId RecipientTypeId SenderCompanyId SenderNetworkId
4205 104 4365 3
4388 104 4365 3

Thanks you all

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-24 : 17:51:10

Your query doesn't make sense, which is probably the issue. You are using the variable in your join conditions, yet you haven't told it what value to use. And then you are putting StatusId into it at the end. Because you haven't put a value into the variable, it has NULL in it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

buzzi
Starting Member

48 Posts

Posted - 2008-10-25 : 10:04:29

Thank you Tara,
Sorry i didn't get this right. The varible value is set by the statement above the query
declare @ActiveStatusId int
SELECT @ActiveStatusId = StatusId FROM [Status] where statusname = 'Active',
so by the time the query is executed the value of the variable is already set to 1.why it will be NULL??, also You have mentioned " You are using the variable in your join conditions"?? shouldn't i use variables in join conditions,
Thanks again

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-10-25 : 15:28:49

What I think you have there is a case of parameter sniffing. The optimiser can't see the values of variable and hence has to guess how many rows are affected by the query. This can lead to bad execution plans.
[url]http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/[/url]

Can you try putting OPTION (RECOMPILE) at the end of the query and see if it resolves the issue. If the status value is unlikely to change, you could also try OPTION (OPTIMIZE FOR @ActiveStatusId = 1)

Where does this query typically get called from?

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-25 : 22:37:04

quote:
Originally posted by buzzi

Thank you Tara,
Sorry i didn't get this right. The varible value is set by the statement above the query
declare @ActiveStatusId int
SELECT @ActiveStatusId = StatusId FROM [Status] where statusname = 'Active',
so by the time the query is executed the value of the variable is already set to 1.why it will be NULL??, also You have mentioned " You are using the variable in your join conditions"?? shouldn't i use variables in join conditions,
Thanks again

Sorry yes I missed that. You didn't have hard returns in between the lines, so I didn't notice the two select statements. You should add code tags around any code posted to retain formatting in a forum so that your code is very clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources