Optimizing Queries

Question

Optimizing Queries

Frank Cazabon

Hall of Fame

Points: 3467
More actions
March 10, 2016 at 6:18 am

#308929

Hi, I have some queries which are taking about 6 seconds to run against a table with 12,000,000 odd records.
The queries are:
SELECT DISTINCT [PNRStatus] FROM [Transactions] ORDER BY [PNRStatus]
SELECT DISTINCT [AgentID] FROM [Transactions] ORDER BY [AgentID]
SELECT DISTINCT [ScriptResult] FROM [Transactions] ORDER BY [ScriptResult]
And the execution plan result is:
The indexes are:
CREATE NONCLUSTERED INDEX [IX_Transactions_ScriptResult] ON [dbo].[Transactions]
([ScriptResult])
create nonclustered index ix_transactions_agentid on dbo.transactions
(agentid)
create nonclustered index ix_transactions_pnrstatus on dbo.transactions
(pnrstatus)
Can anyone suggest ways to improve the speed of these?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Six seconds doesn't sound unreasonable for that lot on a table that size. What maintenance do you do on your indexes? Have you tried running the queries without parallelism?

John

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 2

John Mitchell-245523 (3/10/2016)
Six seconds doesn't sound unreasonable for that lot on a table that size. What maintenance do you do on your indexes? Have you tried running the queries without parallelism?
John

I agree, the time is not too bad, but I am stress testing somebody's web site and the page load has gone from less than a second to between 10 and 15 seconds and I am trying to see what I can do to speed it up.

I only just created the indexes and no data has been added since so I haven't done any maintenance on them.

I don't know how to turn on/off parallelism, Google here I come.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Those plans suggest that the number of rows returned is only a small fraction of the total, in which case Paul White's super-quick rCTE-based DISTINCT equivalent might be worth a shot.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Frank Cazabon (3/10/2016)

John Mitchell-245523 (3/10/2016)
I don't know how to turn on/off parallelism, Google here I come.

Be careful - you don't (necessarily) want to turn off parallelism on the whole server. Rather, you just want to run the queries without parallelism. This possibly isn't 100% syntactically correct, but you need to add a query hint, something like this:

WITH (MAXDOP = 1)

John

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 5

Thanks Chris, that CTE is incredibly faster!

Is it possible to use CTE in a view definition? All this code relies on this view so it would be easiest if I could just redefine the view.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Frank Cazabon (3/10/2016)
Thanks Chris, that CTE is incredibly faster!
Is it possible to use CTE in a view definition? All this code relies on this view so it would be easiest if I could just redefine the view.

Yes:

CREATE VIEW vw_CounterTest WITH SCHEMABINDING AS

WITH rCTE AS (

SELECT n = 1

UNION ALL

SELECT n+1

FROM rCTE

WHERE n < 10

)

SELECT n FROM rCTE

GO

SELECT n FROM vw_CounterTest

Note that this is not a good way to generate rows - just a simple and fast way to test if a rCTE can be objectified as a view.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 7

Thanks, it's giving me an error when I include the

OPTION (MAXRECURSION 0)

I have left it out, I hope it's not important 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Frank Cazabon (3/10/2016)
Thanks, it's giving me an error when I include the
OPTION (MAXRECURSION 0)
I have left it out, I hope it's not important 🙂

Correct syntax is to use the hint in the query referencing the view. It's very important.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 9

Frank Cazabon

Hall of Fame

Points: 3467

March 10, 2016 at 11:04 am

#1864324

Thanks Chris

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 10

John Mitchell-245523 (3/10/2016)

Frank Cazabon (3/10/2016)
John Mitchell-245523 (3/10/2016)
I don't know how to turn on/off parallelism, Google here I come.
Be careful - you don't (necessarily) want to turn off parallelism on the whole server. Rather, you just want to run the queries without parallelism. This possibly isn't 100% syntactically correct, but you need to add a query hint, something like this:
WITH (MAXDOP = 1)
John

Thanks I'll investigate that.

MadAdmin SSChampion Points: 11261 More actions · Answer 11

Frank Cazabon (3/10/2016)
SELECT DISTINCT [PNRStatus] FROM [Transactions] ORDER BY [PNRStatus]
SELECT DISTINCT [AgentID] FROM [Transactions] ORDER BY [AgentID]
SELECT DISTINCT [ScriptResult] FROM [Transactions] ORDER BY [ScriptResult]

Are these queries perhaps to populate a drop down list with data that should really be in a lookup table?

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Frank Cazabon Hall of Fame Points: 3467 More actions · Answer 12

MadAdmin (3/17/2016)
Frank Cazabon (3/10/2016)
SELECT DISTINCT [PNRStatus] FROM [Transactions] ORDER BY [PNRStatus]
SELECT DISTINCT [AgentID] FROM [Transactions] ORDER BY [AgentID]
SELECT DISTINCT [ScriptResult] FROM [Transactions] ORDER BY [ScriptResult]
Are these queries perhaps to populate a drop down list with data that should really be in a lookup table?

Sort of 🙂

They only want to see ones they actually have values for in the subset of the data.

So we might have a lookup table with AgentID values of 1,2,3,4 & 5, but the subset of data only has 1,2 & 5 in, then they only want to see those options displayed in the dropdown/combo box.

Sergiy SSC Guru Points: 110208 More actions · Answer 13

This should be incredibly faster:

SELECT StatusName PNRStatus from dbo.Status S

WHERE EXISTS (SELECT * FROM [Transactions] T WHERE T.[PNRStatus]=S.StatusName)

ORDER BY PNRStatus

SELECT [AgentID] from dbo.Agent A

WHERE EXISTS (SELECT * FROM [Transactions] T WHERE T.[AgentID]=A.[AgentID])

ORDER BY [AgentID]

SELECT StatusName ScriptResult from dbo.Status S

WHERE EXISTS (SELECT * FROM [Transactions] T WHERE T.[ScriptResult]=S.StatusName)

ORDER BY ScriptResult

_____________
Code for TallyGenerator