[RESOLVED] SQL Server '05: Query performance

[aconybeare]

Hi,

I've got a query that seems to take an age to execute can anyone see how I can speed it up

SELECT p.User_Id
	,p.User_Name
	,p.Prefix
	,p.First_Name
	,p.Last_Name
	,IsNull(p.Prefix+' ','')+IsNull(p.First_Name+' ','')+IsNull(p.Middle_Name+' ','')+IsNull(p.Last_Name,'') As Full_Name
	,IsNull(p.Prefix+' ',IsNull(p.First_Name+' ',''))+IsNull(p.Last_Name,'') As Display_Name
	,p.Email
	,(SELECT Min(l.Security_Level) As Sec_Level 
		FROM LU_Roles l 
		JOIN User_Roles u ON l.Role_ID=u.Role_ID 
		WHERE u.User_ID=p.User_ID 
		AND u.Status IN('A')
	) As Security_Level
FROM People p
WHERE p.User_ID=@User_ID

The execution plan indicates that the hilighted subquery is taking all the time. Does anyone know how to improve this? The problem I've got is that I need to get the record back regardless of whether the person has a role or not.

Cheers Al

[si_the_geek]

Does each person only have one entry in the People table? If so, you can Join all the tables and use a Group By, eg:

SELECT p.User_Id
...
	,p.Email
	, Min(l.Security_Level) As Security_Level
FROM People p
LEFT JOIN User_Roles u ON (u.User_ID=p.User_ID AND u.Status = 'A') 
LEFT JOIN LU_Roles l ON (l.Role_ID=u.Role_ID)		

WHERE p.User_ID=@User_ID

GROUP BY p.User_Id
...
	,p.Email

Note that I also changed IN('A') to ='A' as I think that is a bit faster.

[aconybeare]

Si,

Thanks a lot for your reply, this works on a shortened version of my query, but when i expand it to the full table I get the error -

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I've commented out the offending fields and put in their datatypes any ideas for getting round this snag?

SELECT p.User_Id
	,p.User_Name
	,p.Prefix
	,p.First_Name
	,p.First_Name_Accented
	,p.Middle_Name
	,p.Middle_Name_Accented
	,p.Last_Name
	,p.Last_Name_Accented
	,IsNull(p.Prefix+' ','')+IsNull(p.First_Name+' ','')+IsNull(p.Middle_Name+' ','')+IsNull(p.Last_Name,'') As Full_Name
	,IsNull(p.Prefix+' ',IsNull(p.First_Name+' ',''))+IsNull(p.Last_Name,'') As Display_Name
	,p.Department
	,p.Institution
	,p.Affiliation
	,p.Address1
	,p.Address2
	,p.Address3
	,p.Address4
	,p.Town
	,p.County
	,p.Postcode
	,p.Country
	,p.Country_Code
	,p.Telephone
	,p.Fax
	,p.Email
	,p.Email2
	,p.CCEmail
	,p.CCName
	,p.CC_Check
	,p.Sec_Question
	,p.Sec_Answer
	--,p.Blurb (ntext)
	,p.Timss_ID
	,p.Mad_ID
	,p.Status
	,p.Status_Reason
	,p.Date_Created
	,p.Last_Updated
	,p.Reviewer
	,p.Sec_Question
	,p.Sec_Answer
	--,p.Admin_Comment (nvarchar(500))
	,p.Author_Embargo_Check
	--,p.Author_Embargo_Comment (nvarchar(500))
	,p.Salutation
	--,p.Key_Phrases (ntext)
	--,p.Notes (ntext)
	,p.Originally_Recruited_For
	,p.Marketing_Material		
	,Min(Security_Level) As Sec_Level
FROM People p
LEFT JOIN User_Roles u ON (u.User_ID=p.User_ID AND u.Status = 'A') 
LEFT JOIN LU_Roles l ON (l.Role_ID=u.Role_ID)
WHERE p.User_ID=1--@User_ID
GROUP BY p.User_Id
		,p.User_Name
		,p.Prefix
		,p.First_Name
		,p.First_Name_Accented
		,p.Middle_Name
		,p.Middle_Name_Accented
		,p.Last_Name
		,p.Last_Name_Accented
		,p.Department
		,p.Institution
		,p.Affiliation
		,p.Address1
		,p.Address2
		,p.Address3
		,p.Address4
		,p.Town
		,p.County
		,p.Postcode
		,p.Country
		,p.Country_Code
		,p.Telephone
		,p.Fax
		,p.Email
		,p.Email2
		,p.CCEmail
		,p.CCName
		,p.CC_Check
		,p.Sec_Question
		,p.Sec_Answer
		--,p.Blurb (ntext)
		,p.Timss_ID
		,p.Mad_ID
		,p.Status
		,p.Status_Reason
		,p.Date_Created
		,p.Last_Updated
		,p.Reviewer
		,p.Sec_Question
		,p.Sec_Answer
		--,p.Admin_Comment (nvarchar(500))
		,p.Author_Embargo_Check
		--,p.Author_Embargo_Comment (nvarchar(500))
		,p.Salutation
		--,p.Key_Phrases (ntext)
		--,p.Notes (ntext)
		,p.Originally_Recruited_For
		,p.Marketing_Material

Cheers Al

[si_the_geek]

I think the NVarChar fields should be fine, are you sure they are causing issues?

As for the NText fields, a Cast/Convert to NVarChar should work if the data is short enough (if they are too long, you will lose data), tho if NVarChar is causing problems you may need to use VarChar instead - which may also lose some of the data.


There is another solution whereby you effectively create a temporary table, eg:

SELECT p.User_Id
...
	,p.Marketing_Material		
	, S.Sec_Level
FROM People p
LEFT JOIN 
  (SELECT u.User_ID, Min(Security_Level) As Sec_Level
   FROM User_Roles u 
   LEFT JOIN LU_Roles l ON (l.Role_ID=u.Role_ID)
   WHERE u.Status = 'A'
  ) S ON (P.User_ID = S.User_ID)
WHERE p.User_ID=1--@User_ID

[aconybeare]

Si,

Sorry you're right the nvarchar fields weren't causing a problem.

I've elected to go for option two, because I can't risk outputting truncated data.

Thanks a lot for your help much appreciated

Cheers Al