ORDER BY slowing SELECT - 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

Transact-SQL (2000)

ORDER BY slowing SELECT

Author

Topic

lfortnam
Starting Member

5 Posts

Posted - 2010-11-15 : 05:34:04

Morning All,

I have a quick question for anyone who can help. Firstly I know a few basics on SQL but a ilttle knowlesge as they say is a dangerous thing!

I have a system which allows people to looks for deals in the mobile market space. There are the following tables:

MasterDeals (this holds all the IDs of the relevant parts that make up a deal, handset price on that deal, monthly discount etc)

Handsets (this holds all the handsets including make, model, dimensions etc)

Tariffs (holds all the tariff info such as mins, text, data, operator, tariff name etc)

Retailer (The details of the retailer offering the deal)

FreeGift (holds info about the free gifts, i.e. category (laptop, ipod etc), description and general highstreet price).

In the masterdeals table (approx 900K records) we have DealTextID (varchar,255), HandsetID (int), TariffID (int), GiftID (int), RetailerID (int).

The corresponding fields all exist in the relevant tables. I have a nonclustered index on these ID fields (excluding the DealTextID) and then in each of the other tables a Primary Key based clustered index on the ID field.

In addition to this I also have another Index on the tariffs table which is nonclustered that looks at the Mins (int), texts (int), data (int) and monthlycost (int) fields.

If i search all I get quick responses, if I an an order by to this such as order by Mins desc then is can take 20 odd seconds to return a result. Am I missing something here?

Regards,

Lee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-15 : 05:39:42

What indexes exist on each table?

Are you definitely using SQL 2000? If it is 2005, you can use ROW_NUMBER() to sort instead.

lfortnam
Starting Member

5 Posts

Posted - 2010-11-15 : 06:00:30

Hi Rick, thanks for your quick response,

Currently using MS QSL 2005.

I have a nonclustered index on these ID fields (HandsetID (int), TariffID (int), GiftID (int), RetailerID (int)) in the masterdeals and then in each of the other tables a Primary Key based clustered index on the ID field.

In addition to this I also have another Index on the tariffs table which is nonclustered that looks at the Mins (int), texts (int), data (int) and monthlycost (int) fields.

How would Row_Number() help in the sort as the order by could change depending on whether the user says they want their results order by the number of mins in a deal, the number of texts in a deal, the amount of data in a deal or the monthly cost of the deal.

regards,

Lee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-15 : 06:27:43

Well, I am guessing you are doing the change to the order by via dynamic sql, so instead of adding the order by, add a ROW_NUMBER() OVER (ORDER BY <yourfield>) As Rownum. This will do the same job as an order by, but in most cases is faster.

If you are not constructing your sql in this way, let us know how you are constructing it and you'll get a most in-depth answer.

lfortnam
Starting Member

5 Posts

Posted - 2010-11-15 : 07:22:13

Here is a sample query which was timing out until I put the TOP 500 in, now it still takes about 9 seconds.
I know I should not be selecting * but have tried only selecting the fields needed and same problem exists.

The WHERE clause is dynamic as is the ORDER By.

SELECT TOP 500 *
FROM d8m8_2005.M4E_datafeeddealsalt INNER JOIN d8m8_2005.M4E_datafeedretailers ON d8m8_2005.M4E_datafeeddealsalt.RetailerID = d8m8_2005.M4E_datafeedretailers.RetailerID INNER JOIN d8m8_2005.M4E_datafeedtariffs ON d8m8_2005.M4E_datafeeddealsalt.TarifID = d8m8_2005.M4E_datafeedtariffs.TariffID LEFT OUTER JOIN d8m8_2005.M4E_datafeedgifts ON d8m8_2005.M4E_datafeeddealsalt.GiftID = d8m8_2005.M4E_datafeedgifts.GiftID LEFT OUTER JOIN d8m8_2005.M4E_datafeedhandsets ON d8m8_2005.M4E_datafeeddealsalt.HandsetID = d8m8_2005.M4E_datafeedhandsets.HandsetID
WHERE (IncTexts > 0) AND (IncTexts >= 500) AND (IncAnyTimeMins >= 500)
ORDER BY IncTexts Desc

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-15 : 11:29:56

As you build the Order by and the Where dynamically (still unsure how, SP or front end code?), you can instead build it as follows:


SELECT TOP 500 ROW_NUMBER() OVER (ORDER BY IncTexts DESC) RowNum, *
FROM d8m8_2005.M4E_datafeeddealsalt 
INNER JOIN d8m8_2005.M4E_datafeedretailers 
ON d8m8_2005.M4E_datafeeddealsalt.RetailerID = d8m8_2005.M4E_datafeedretailers.RetailerID 
INNER JOIN d8m8_2005.M4E_datafeedtariffs 
ON d8m8_2005.M4E_datafeeddealsalt.TarifID = d8m8_2005.M4E_datafeedtariffs.TariffID 
LEFT OUTER JOIN d8m8_2005.M4E_datafeedgifts 
ON d8m8_2005.M4E_datafeeddealsalt.GiftID = d8m8_2005.M4E_datafeedgifts.GiftID 
LEFT OUTER JOIN d8m8_2005.M4E_datafeedhandsets 
ON d8m8_2005.M4E_datafeeddealsalt.HandsetID = d8m8_2005.M4E_datafeedhandsets.HandsetID
WHERE (IncTexts > 0) AND (IncTexts >= 500) AND (IncAnyTimeMins >= 500)

Then you can change those parts rather than adding the order by.

P.S. Can someone move this to the SQL 2005 forum please?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-11-15 : 11:35:05

definitely try to stay away from the "select *" syndrome

1. can you post the expected execution plan?

2. tables in queries should be prefixed by "dbo."
I'm presuming d8m6_2005 is a server name?

3. there are methods available for including optional filters, which would allow use of SP's....which are better for performance (cache-reuse) and security. search here examples.

4. what tables do the optional fields come from? if they are on the RHS of the LEFT join's...those joins will become INNER joins (automatically).

lfortnam
Starting Member

5 Posts

Posted - 2010-11-15 : 11:56:45

The SQL is begin generated dynamically by the front end code (in this case a cold fusion page).

d8m8_2005 is the name of the database as it is on a shared SQL platform, don't have to have it in there just gets added by default in the query builder.

How do I get to the execution plan in 'SQL Server Management Studio Express'?

I think a lot has to do with the amount of data being shipped between the SQL server and the CF server. The master deals table (M4E_datafeeddealsalt) is an 80MB CSV file that gets imported to generate the 900k+ rows.

Cannot see an option to move this to anpther forum, sorry for posting in wrong place.

Thanks,

Lee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-15 : 12:16:38

Funnily enough, I am working with Cold Fusion developers at the moment, we have found the ROW_NUMBER way works best with the drivers CF uses, think it may well be specific to the CF driver..

No, I was hoping one of the admins would move it..

lfortnam
Starting Member

5 Posts

Posted - 2010-11-15 : 12:42:24

Cool, the task I have is as follows:

1. Return a set of results from the DB that match the criteria ranked in the order of the users request.

2. Strip out the handset elements so I am only given the distinct retailer, tariff and gift elements of the result set.

3. Then loop through these and find out how many handsets were linked with this particular retailer/tariff/gift combo.

I could do it all in an SP I suppose and return just the 10 result sets that would give me the 10 deals and for each deal the handsets available.

Question then is how to get the first result (not export it as a result set) set and then loop through internally to the SP that creates a new result set for each of the results in the first set.

Sure it is on here somewhere but feel free to impart more wisdom :-)

Would this be a good idea?

Think part of my original concern was whether I have the indexing and linking correct in the DB.

masterdeals table at the center and then each deal MUST come with a handset so a standard join, each deal MAY have a gift so left outer join with ALL rows from master deals table, each deal MUST have a retailer so standard join.

Regards,

Lee

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-11-16 : 04:30:18

3. Then loop through these and find out how many handsets were linked with this particular retailer/tariff/gift combo.

No need for that. If you need a total, either do a count() or something like this:


SELECT
	  RowNum
	, RowNum2
	, RowNum + RowNum2 - 1 As counter
	, *
FROM
	(
		SELECT TOP 500 ROW_NUMBER() OVER (PARTITION BY TariffID, GiftID, RetailerID ORDER BY HandsetID DESC) RowNum
					, ROW_NUMBER() OVER (PARTITION BY TariffID, GiftID, RetailerID ORDER BY HandsetID ASC) RowNum2, *
		FROM d8m8_2005.M4E_datafeeddealsalt 
		INNER JOIN d8m8_2005.M4E_datafeedretailers 
		ON d8m8_2005.M4E_datafeeddealsalt.RetailerID = d8m8_2005.M4E_datafeedretailers.RetailerID 
		INNER JOIN d8m8_2005.M4E_datafeedtariffs 
		ON d8m8_2005.M4E_datafeeddealsalt.TarifID = d8m8_2005.M4E_datafeedtariffs.TariffID 
		LEFT OUTER JOIN d8m8_2005.M4E_datafeedgifts 
		ON d8m8_2005.M4E_datafeeddealsalt.GiftID = d8m8_2005.M4E_datafeedgifts.GiftID 
		LEFT OUTER JOIN d8m8_2005.M4E_datafeedhandsets 
		ON d8m8_2005.M4E_datafeeddealsalt.HandsetID = d8m8_2005.M4E_datafeedhandsets.HandsetID
		WHERE (IncTexts > 0) AND (IncTexts >= 500) AND (IncAnyTimeMins >= 500)
	) a

(not 100% this will work, you will need to test, but I am guessing that HandsetID is unique. If its not, then find the unique key and change the order by in the result set to it).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources