SELECT performance with ORDER BY

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)

SELECT performance with ORDER BY

Author

Topic

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-09-24 : 16:03:18

I'm working on an address management system with a normalized db structure. I have a view that aggregates the various lookup tables into a "nice" address. The only unique aspect of the view is that it places a leading zero in front of the street name field for street names that are single-digit numeric (i.e. 5th St. becomes 05th St) for ordering purposes. When I run a query against the view, with or without a WHERE clause, performance is acceptable; however, once I introduce an ORDER BY clause, the query becomes very slow. I want to run the ORDER BY the field with the leading zeros (OBSTNAME), but I have even altered which fields the ORDER BY runs against, without any significant performance improvement.

Any ideas? Thanks in advance!
Chris

VIEW:

ALTER VIEW [dbo].[v_ai_StreetZips]
AS
SELECT 
	sz.StreetNameZipID,
	sn.StreetNameID,
	sp.Prefix,
	sb.BaseName,
	st.[Type],
	ss.Suffix,
	sz.ZipCode,
	RTRIM(LTRIM(ISNULL(sp.Prefix,'') + ' ' + sb.BaseName + ' ' + ISNULL(st.[Type],'') + ' ' + ISNULL(ss.Suffix,''))) 
	AS FullName,
	CASE 
		WHEN LEFT(sb.BaseName,1) IN ('1','2','3','4','5','6','7','8','9','0') 
			AND SUBSTRING(sb.BaseName, 2, 1) NOT IN ('1','2','3','4','5','6','7','8','9','0') 
			THEN '0' + sb.BaseName 
		ELSE sb.BaseName 
	END As OBSTNAME,
	sp.PrefixID,
	sb.BaseNameID,
	st.TypeID,
	ss.SuffixID
FROM
	ai_StreetNameZips sz
LEFT OUTER JOIN
	ai_StreetNames sn
	ON sn.StreetNameID = sz.StreetNameID
LEFT OUTER JOIN
	ai_StreetPrefix sp
	ON sn.PrefixID = sp.PrefixID
LEFT OUTER JOIN
	ai_StreetBaseNames sb
	ON sn.BaseNameID = sb.BaseNameID
LEFT OUTER JOIN
	ai_StreetTypes st
	ON sn.TypeID = st.TypeID
LEFT OUTER JOIN
	ai_StreetSuffix ss
	ON sn.SuffixID = ss.SuffixID
GO

QUERY:

DECLARE	@streetnamezipid INT ,
	@streetnameid INT ,
	@prefix VARCHAR(2) ,
	@basename VARCHAR(50) ,
	@type VARCHAR(4),
	@suffix VARCHAR(2) ,
	@prefixid INT ,
	@basenameid INT ,
	@typeid INT ,
	@suffixid INT ,
	@zipcode VARCHAR(5)

Set @zipcode = '99999'

	SELECT 
		StreetNameZipID,
		StreetNameID, 
		FullName, 
		Prefix,
		BaseName,
		[Type],
		Suffix,
		PrefixID,
		BaseNameID,
		TypeID,
		SuffixID,
		ZipCode
	FROM v_ai_StreetZips
	WHERE 
		StreetNameZipID = COALESCE(@streetnamezipid, StreetNameZipID)
		AND
		StreetNameID = COALESCE(@streetnameid, StreetNameID)
		AND
		Prefix = COALESCE(@prefix, Prefix)
		AND
		BaseName = COALESCE(@basename, BaseName)
		AND
		[Type] = COALESCE(@type, [Type])
		AND
		Suffix = COALESCE(@suffix, Suffix)
		AND
		PrefixID = COALESCE(@prefixid, PrefixID)
		AND
		BaseNameID = COALESCE(@basenameid, BaseNameID)
		AND
		TypeID = COALESCE(@typeid, TypeID)
		AND
		SuffixID = COALESCE(@suffixid, SuffixID)
		AND
		ZipCode = COALESCE(@zipcode, ZipCode)
-- ORDER BY OBSTNAME, [Type], Prefix

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-24 : 16:29:55

Post all indexes and constraints for the table involved in the view.

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-24 : 16:36:23

Are there any indexes on OBSTNAME, [Type], Prefix columns? Also if the record size is small you can do the ordering at front end.

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

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-09-24 : 17:14:08

Here are the indexes... Interestingly, even if I perform an ORDER BY BaseName (which has a non-clustered index already), performance is still slow. I added non-clustered indexes to the Type and Prefix fields, to no avail.

Here are the indexes and constraints:

ALTER TABLE [dbo].[ai_StreetNameZips] ADD  CONSTRAINT [StreetNameZipID_pk] PRIMARY KEY CLUSTERED 
([StreetNameZipID] ASC)
ALTER TABLE [dbo].[ai_StreetNameZips]  WITH CHECK ADD  CONSTRAINT [StreetNameIDZips_fk] FOREIGN KEY([StreetNameID])
REFERENCES [dbo].[ai_StreetNames] ([StreetNameID])

ALTER TABLE [dbo].[ai_StreetNameZips] CHECK CONSTRAINT [StreetNameIDZips_fk]

ALTER TABLE [dbo].[ai_StreetNameZips]  WITH CHECK ADD  CONSTRAINT [ZoneSet_fk] FOREIGN KEY([ZipCode])
REFERENCES [dbo].[ai_ZoneSet] ([ZipCode])

ALTER TABLE [dbo].[ai_StreetNameZips] CHECK CONSTRAINT [ZoneSet_fk]



ALTER TABLE [dbo].[ai_StreetNames] ADD  CONSTRAINT [StreetName_pk] PRIMARY KEY CLUSTERED 
([StreetNameID] ASC)
ALTER TABLE [dbo].[ai_StreetNames]  WITH CHECK ADD  CONSTRAINT [ai_BaseNames_fk] FOREIGN KEY([BaseNameID])
REFERENCES [dbo].[ai_StreetBaseNames] ([BaseNameID])

ALTER TABLE [dbo].[ai_StreetNames] CHECK CONSTRAINT [ai_BaseNames_fk]


ALTER TABLE [dbo].[ai_StreetNames]  WITH CHECK ADD  CONSTRAINT [ai_Suffix_fk] FOREIGN KEY([SuffixID])
REFERENCES [dbo].[ai_StreetSuffix] ([SuffixID])

ALTER TABLE [dbo].[ai_StreetNames] CHECK CONSTRAINT [ai_Suffix_fk]


ALTER TABLE [dbo].[ai_StreetNames]  WITH CHECK ADD  CONSTRAINT [Prefix_fk] FOREIGN KEY([PrefixID])
REFERENCES [dbo].[ai_StreetPrefix] ([PrefixID])

ALTER TABLE [dbo].[ai_StreetNames] CHECK CONSTRAINT [Prefix_fk]


ALTER TABLE [dbo].[ai_StreetNames]  WITH CHECK ADD  CONSTRAINT [Type_fk] FOREIGN KEY([TypeID])
REFERENCES [dbo].[ai_StreetTypes] ([TypeID])

ALTER TABLE [dbo].[ai_StreetNames] CHECK CONSTRAINT [Type_fk]



ALTER TABLE [dbo].[ai_StreetPrefix] ADD  CONSTRAINT [ai_StreetPrefix_pk] PRIMARY KEY CLUSTERED 
([PrefixID] ASC)

ALTER TABLE [dbo].[ai_StreetBaseNames] ADD  CONSTRAINT [ai_StreetBaseNames_pk] PRIMARY KEY CLUSTERED 
([BaseNameID] ASC)

CREATE NONCLUSTERED INDEX [ai_StreetBaseNames_idx] ON [dbo].[ai_StreetBaseNames] 
([BaseName] ASC)

ALTER TABLE [dbo].[ai_StreetTypes] ADD  CONSTRAINT [ai_StreetType_pk] PRIMARY KEY CLUSTERED 
([TypeID] ASC)

ALTER TABLE [dbo].[ai_StreetSuffix] ADD  CONSTRAINT [ai_StreetSuffix_pk] PRIMARY KEY CLUSTERED 
([SuffixID] ASC)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 17:18:51

[code]ALTER VIEW dbo.v_ai_StreetZips
AS

SELECT sz.StreetNameZipID,
sn.StreetNameID,
sp.Prefix,
sb.BaseName,
st.[Type],
ss.Suffix,
sz.ZipCode,
RTRIM(LTRIM(ISNULL(sp.Prefix, '') + ' ' + sb.BaseName + ' ' + ISNULL(st.[Type], '') + ' ' + ISNULL(ss.Suffix, ''))) AS FullName,
CASE
WHEN sb.BaseName LIKE '[0-9][^0-9]%' THEN '0' + sb.BaseName
ELSE sb.BaseName
END AS OBSTNAME,
sp.PrefixID,
sb.BaseNameID,
st.TypeID,
ss.SuffixID
FROM ai_StreetNameZips AS sz
LEFT JOIN ai_StreetNames as sn ON sn.StreetNameID = sz.StreetNameID
LEFT JOIN ai_StreetPrefix as sp ON sn.PrefixID = sp.PrefixID
LEFT JOIN ai_StreetBaseNames as sb ON sn.BaseNameID = sb.BaseNameID
LEFT JOIN ai_StreetTypes as st ON sn.TypeID = st.TypeID
LEFT JOIN ai_StreetSuffix as ss ON sn.SuffixID = ss.SuffixID[/code]

E 12°55'05.25"
N 56°04'39.16"

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-09-25 : 10:37:59

Thanks for the

WHEN sb.BaseName LIKE '[0-9][^0-9]%' THEN '0' + sb.BaseName

hint. Much more elegant.

After some careful teasing apart, I have been able to determine that the offending portion of the query is this portion of the WHERE clause:

AND TypeID = COALESCE(@typeid, TypeID)

The TypeID is the PK in the ai_StreetTypes table and a FK in the ai_StreetNames table. I rebuilt the indexes just to make sure that there were no issues. This did not help.

Just to clarify, if this portion of the WHERE clause is commented out, the query takes less than 1 second. When used, the query takes 40 seconds. Although I think I could probably find a work-around, I really ought to figure out why this is so.

Any ideas now that I've narrowed it down? This is quite frustrating.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-25 : 10:44:09

AND TypeID = COALESCE(@typeid, TypeID)

cannot use any indexes on that column ... Replace that with:

AND (@TypeID is null OR TypeID = @TypeID)

more here:

http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 10:45:38

Try this query

SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE StreetNameZipID = @streetnamezipid
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE StreetNameID = @streetnameid
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE Prefix = @prefix
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE BaseName = @basename
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE [Type] = @type
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE Suffix = @suffix
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE PrefixID = @prefixid
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE BaseNameID = @basenameid
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE TypeID = @typeid
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE SuffixID = @suffixid
UNION
SELECT StreetNameZipID, StreetNameID,  FullName,  Prefix, BaseName, [Type], Suffix, PrefixID, BaseNameID, TypeID, SuffixID, ZipCode FROM v_ai_StreetZips WHERE ZipCode = @zipcode

E 12°55'05.25"
N 56°04'39.16"

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-09-25 : 11:12:31

Jeff...

THANKS!!! That worked perfectly. I think you've made a convert of me... avoid CASE (and by proxy, COALESCE) in a WHERE clause! I read your blog posting and I can definitely see how using simple boolean logic like this would be more efficient, but I'm a little confused about why this would result in a query so horribly inefficient for only this table/field. The inefficient COALESCE performs acceptably for all the other fields. Any ideas?

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-09-26 : 00:15:30

Hi,
You can arrive OBSTNAME as RIGHT('00' + sb.BaseName,3) AS OBSTNAME

Try it .. so that can avoid the case as well as the sbustring function

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 06:03:28

"I'm a little confused about why this would result in a query so horribly inefficient for only this table/field"

If the most efficient index for a particular query in on, say, Col1 and you do something like:

WHERE SomeFunction(Col1)

then the Query Optimiser can no longer use the index on that column. Doesn't matter how efficient the function is, the fact that you are no longer using the most efficient index for that query is a killer.

Usually anyway ...

Kristen

Mountain_Nerd
Starting Member

28 Posts

Posted - 2007-09-26 : 16:46:29

aravindt77... Thanks... that would work if all the values were numeric, but I am trying to do an alphanumeric ordering of street names like 1ST, 23RD, 15TH, 2ND, MAIN, or BROADWAY so that I get, in order:
1ST
2ND
15TH
23RD
BROADWAY
MAIN
Nevertheless, I appreciate your input.

Kristen...
I think I get what you're saying... definitely something to keep in mind when writing queries. THANKS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources