Query tuning - 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 2000 Forums

SQL Server Development (2000)

Query tuning

Author

Topic

vinod236
Starting Member

1 Post

Posted - 2007-04-20 : 05:21:02

I have fallowing block of code

declare @P1 nvarchar(2),@P2 nvarchar(3),@P3 int,@P4 nvarchar(2),@P5 nvarchar(3),@P6 nvarchar(12),@P7 nvarchar(12),@P8 nvarchar(12),@P9 nvarchar(12),@P10 nvarchar(12),@P11 nvarchar(12),@P12 uniqueidentifier,@P13 uniqueidentifier,@P14 int

set @P1 = 'No'

set @P2 = 'Yes'

set @P3 = 0

set @P4 = 'No'

set @P5 = 'Yes'

set @P6 = 'jones, mark%'

set @P7 = 'jones, mark%' middle name

set @P8 = 'jones, mark%' last name

set @P9 = 'jones, mark%' full name

set @P10 = 'jones, mark%' first name

set @P11 = 'jones, mark%' emailaddress

set @P12 = 'AB436D2A-0AEE-43AA-BE3B-8AD0AA23CCEF'

set @P13 = 'AB436D2A-0AEE-43AA-BE3B-8AD0AA23CCEF'

set @P14 = 2

select top 251

contact.FullName as 'fullname',

contact.ParentCustomerId as 'parentcustomerid',

contact.ParentCustomerIdDsc as 'parentcustomeriddsc',

contact.ParentCustomerIdName as 'parentcustomeridname',

contact.ParentCustomerIdType as 'parentcustomeridtype',

contact.ParentContactId as 'parentcontactid',

contact.AccountId as 'accountid',

contact.Address1_City as 'address1_city',

contact.Address1_Telephone1 as 'address1_telephone1',

contact.Telephone1 as 'telephone1',

contact.EMailAddress1 as 'emailaddress1',

contact.New_Tech_contact as 'new_tech_contact',

'new_tech_contactname' = case contact.New_Tech_contact when 0 then @P1 when 1 then @P2 else null end ,

contact.ContactId as 'contactid'

from Contact

as contact

where contact.DeletionStateCode in (0)

and (

(contact.StateCode = @P3)

and (

case contact.New_Tech_contact when 0 then @P4 when 1 then @P5 else null end like @P6

or contact.MiddleName like @P7

or contact.LastName like @P8

or contact.FullName like @P9

or contact.FirstName like @P10

or contact.EMailAddress1 like @P11

)

)

AND ((

(contact.OwningUser is null)

OR (contact.OwningUser = @P12)

OR (contact.OwningBusinessUnit is not null)

OR (contact.ContactId in ( select POA.ObjectId

from PrincipalObjectAccess POA

join SystemUserPrincipals sup

on POA.PrincipalId = sup.PrincipalId

where sup.SystemUserId = @P13

and POA.ObjectTypeCode = @P14

and ((POA.AccessRightsMask|POA.InheritedAccessRightsMask) & 1)=1

)

)

))

order by contact.FullName asc , contact.ContactId asc
####################################################################

This block of code is taking 33 seconds to execute , I want optimise this or some body please waht are the thing we should look into this to find the problem.

Note : " Contact " is a View which is formed through 7-8 Basetables

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-20 : 12:10:41

(1) Your TOP/ORDER BY is something you should look at to see if you can just use SET ROWCOUNT instead.
(2) Do you have indexes on these columns - FirstName, MiddleName , LastName , EMailAddress1 , OwningUser
(3) Also try running the query without the subquery condition and see which part of the query is killing you.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources