Order by Problem - 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

General SQL Server Forums

New to SQL Server Programming

Order by Problem

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-17 : 08:18:48

Hi

I am tring to build a query and there is a requirement to have a sort order of columns 1-4 and when I add these the sort-order cost just hits 99%.

What should I be looking to tune in my query to get round this?

This is the original query that to be honest is a bit slow:



SELECT     TOP 100 PERCENT  
            dbo.tab1.col1, 
            dbo.tab1.col2, 
            dbo.tab2.[col3], 
            dbo.tab3.col4, 
            dbo.tab4.col5, 
            dbo.tab5.col6, 
            dbo.tab5.col7, 
            dbo.procedure1(dbo.tab5.col8) AS Alias
FROM        dbo.tab5 INNER JOIN
            dbo.tab4 ON dbo.tab5.fk1 = dbo.tab4.pk1 INNER JOIN
            dbo.tab3 ON dbo.tab5.fk2 = dbo.tab3.pk2 INNER JOIN
            dbo.tab2 ON dbo.tab4.fk3 = dbo.tab2.pk3 INNER JOIN
            dbo.tab1 ON dbo.tab4.fk4 = dbo.tab1.pk1
WHERE       (dbo.tab4.col9 LIKE '%XYZ%')

Adding this makes it time out:

ORDER BY dbo.tab3.col4, dbo.tab4.col5, dbo.tab5.col7, Alias

I would be very grateful if anyone could be of any help here.

Thanks

G

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-17 : 08:21:28

How many rows returned?
How long does it take to return the resultset with and without the order by?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 08:33:02

Using a function in the ORDER BY statement almost always is a bad idea.
Try this rewrite.

SELECT		col1,
		col2,
		col3,
		col4,
		col5,
		col6,
		col7,
		Alias
FROM		(
			SELECT		dbo.tab1.col1, 
					dbo.tab1.col2, 
					dbo.tab2.col3, 
					dbo.tab3.col4, 
					dbo.tab4.col5, 
					dbo.tab5.col6, 
					dbo.tab5.col7, 
					dbo.procedure1(dbo.tab5.col8) AS Alias
			FROM		dbo.tab5
			INNER JOIN	dbo.tab4 ON dbo.tab5.fk1 = dbo.tab4.pk1
			INNER JOIN	dbo.tab3 ON dbo.tab5.fk2 = dbo.tab3.pk2
			INNER JOIN	dbo.tab2 ON dbo.tab4.fk3 = dbo.tab2.pk3
			INNER JOIN	dbo.tab1 ON dbo.tab4.fk4 = dbo.tab1.pk1
			WHERE		dbo.tab4.col9 LIKE '%XYZ%'
		) AS d
ORDER BY	col4,
		col5
		col7,
		Alias

E 12°55'05.63"
N 56°04'39.26"

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-17 : 08:49:32

Rows returned for query = 3211817

With the query without order by it seems to lock up and when I press cancel query the rows show up. With order by it times out so there's probably more than above figure of rows.

Peso I will try your recommendation and let you know how it goes.

Thanks

G

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-17 : 08:57:45

Try it with inserting into a temp table (select * into #a from (query) a.
Try it without the function first as that is a likely suspect.

I suspect that your query doesn't complete - with the order by it has to get the full resultset before returning anything.
Where do you want to use this? The top 100% looks like it's meant to be in a view?

I think you will probably need to address the way you are doing this - maybe materialise the function values. If you do this in an SP you could do it as part of the call.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-17 : 09:02:56

Peso

I tried using your recommendation and it still does the same thing. I checked the sort cost and it is still at 99%.

Thanks

G

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-17 : 09:07:30

Unfortunately slq server is pretty smart at spotting derived tables now so that technique often doesn't have any affect.

Try my suggestion about the temp tables.
(cost doesn't necessarily mean much)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-17 : 09:10:07

quote:

Try it with inserting into a temp table (select * into #a from (query) a.
Try it without the function first as that is a likely suspect.

I suspect that your query doesn't complete - with the order by it has to get the full resultset before returning anything.
Where do you want to use this? The top 100% looks like it's meant to be in a view?

I think you will probably need to address the way you are doing this - maybe materialise the function values. If you do this in an SP you could do it as part of the call.

I tried it without the function and it reduces sort cost to about 75%.

This is supposed to be a view for a crystal report. It is also used in a procedure for the report that goes live as there is parameters that the user can pick so these are incorporated into the query for the procedure.

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-17 : 09:16:33

Tried inserting into temp table and again it takes ages. I just cancelled it.

Would it be better to put this into a procedure? What I don't get is if the underlying query is so slow how can putting into a procedure help the speed of the query?

Thanks

G

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 09:22:06

Your biggest performance killer is the WHERE clause

WHERE (dbo.tab4.col9 LIKE '%XYZ%')

Since it effectively has to scan all records.

SELECT		dbo.tab1.col1, 
		dbo.tab1.col2, 
		dbo.tab2.col3, 
		dbo.tab3.col4, 
		w.col5, 
		q.col6, 
		q.col7, 
		q.Alias
FROM		(
			SELECT	col6,
				col7,
				fk2,
				dbo.procedure1(col8) AS Alias
			FROM	dbo.tab5
		) AS q
INNER JOIN	(
			SELECT	col5,
				fk3,
				fk4
			FROM	dbo.tab4
			WHERE	col9 LIKE '%XYZ%'
		) AS w ON q.fk1 = w.pk1
INNER JOIN	dbo.tab3 ON q.fk2 = dbo.tab3.pk2
INNER JOIN	dbo.tab2 ON w.fk3 = dbo.tab2.pk3
INNER JOIN	dbo.tab1 ON w.fk4 = dbo.tab1.pk1
ORDER BY	dbo.tab3.col4,
		w.col5
		q.col7,
		q.Alias

E 12°55'05.63"
N 56°04'39.26"

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-17 : 09:40:44

quote:
Originally posted by Grifter

Tried inserting into temp table and again it takes ages. I just cancelled it.

Would it be better to put this into a procedure? What I don't get is if the underlying query is so slow how can putting into a procedure help the speed of the query?

Thanks

G

Was that without the function?
It is better in a procedure as you can split the query up to optimise it. You can also create other structures outside the precedure (maybe overnight) and use them without changing the report. You could for instance save the report data and then only update that which has changed on the next call or return the same data if it is repeatedly called.

Given that you have more than 3211817 rows I doubt whether anyone would want to look at all those rows. You should probably be returning aggregates and maybe allowing the user to drill down on demand.

Does this have to be an on-line report or could you create a table overnight and just select from it when the report is called for instance.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-17 : 09:42:49

quote:

Originally posted by Peso

Your biggest performance killer is the WHERE clause

WHERE (dbo.tab4.col9 LIKE '%XYZ%')

Since it effectively has to scan all records.

SELECT		dbo.tab1.col1, 
		dbo.tab1.col2, 
		dbo.tab2.col3, 
		dbo.tab3.col4, 
		w.col5, 
		q.col6, 
		q.col7, 
		q.Alias
FROM		(
			SELECT	col6,
				col7,
				fk2,
				dbo.procedure1(col8) AS Alias
			FROM	dbo.tab5
		) AS q
INNER JOIN	(
			SELECT	col5,
				fk3,
				fk4
			FROM	dbo.tab4
			WHERE	col9 LIKE '%XYZ%'
		) AS w ON q.fk1 = w.pk1
INNER JOIN	dbo.tab3 ON q.fk2 = dbo.tab3.pk2
INNER JOIN	dbo.tab2 ON w.fk3 = dbo.tab2.pk3
INNER JOIN	dbo.tab1 ON w.fk4 = dbo.tab1.pk1
ORDER BY	dbo.tab3.col4,
		w.col5
		q.col7,
		q.Alias

E 12°55'05.63"
N 56°04'39.26"

Not necessarily - if tab4 is small this shouldn't cause a performance issue.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 09:50:34

It depends on SQL engine filters tab4 table before building a hash table and join other tables, or joining is done first and later applies filtering.
An execution plan would reveal this.

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-17 : 12:12:17

If the LIKE was killing performance, one option might be to put the results of:

			SELECT	col5,
				fk3,
				fk4
			FROM	dbo.tab4
			WHERE	col9 LIKE '%XYZ%'

Into a temp table (or table variable) and use that instead of a derived table. Just a thought.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources