Slow Query

Question

Slow Query

mandirkumar 18293

SSC Enthusiast

Points: 127
More actions
May 14, 2015 at 5:55 am

#303862

Hi,
I need to select a aggregate of column from a large table having 75 lakh records. There is no index on selected column. Optimiser is going for table scan which is not acceptable. If we create index also, since there is a aggregate(max()) function, query may not use index. Urgent help needed

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post query, table definition, index definitions and actual execution plan (saved as a .sqlplan file and attached)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mandirkumar 18293 SSC Enthusiast Points: 127 More actions · Answer 2

CREATE TABLE tbl_Login_Details(ID INT IDENTITY,Session_SRNO INT)

CREATE NONCLUSTERED INDEX NCX_Login ON tbl_Login_Details(ID)

DECLARE @SESSION_SRNO int

SELECT @SESSION_SRNO = ISNULL(MAX(SESSION_SRNO), 0) + 1 FROM dbo.tbl_login_details

SELECT @session_srno

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

Why no PK and/or clustered index?

Add a primary key to ID and you get a clustered index scan:

CREATE TABLE dbo.tbl_Login_Details(ID INT IDENTITY, Session_SRNO INT, CONSTRAINT PK_Login PRIMARY KEY(ID));

INSERT dbo.tbl_Login_Details

SELECT TOP 75000 ABS(CHECKSUM(newid()))%100000

FROM sys.all_columns a, sys.all_columns b;

DECLARE @SESSION_SRNO int;

SELECT @SESSION_SRNO = ISNULL(MAX(SESSION_SRNO), 0) + 1 FROM dbo.tbl_login_details

SELECT @session_srno ;

Add a nonclustered index on Session_SRNO and you get a nonclustered index scan.

CREATE NONCLUSTERED INDEX NCX_Login ON dbo.tbl_Login_Details(Session_SRNO);

Add a the following WHERE clause to your Query after that and you get a non-clustered index seek (the plan has a slightly lower subtree cost on the SELECT statement.

WHERE SESSION_SRNO >= 0

Edit That's WHERE SESSION >= 0 (I don't know why SSC sometimes does that to my query code when posted)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Alan.B (5/14/2015)
Add a the following WHERE clause to your Query after that and you get a non-clustered index seek (the plan has a slightly lower subtree cost on the SELECT statement.
WHERE SESSION_SRNO >= 0
Edit That's WHERE SESSION >= 0 (I don't know why SSC sometimes does that to my query code when posted)

But will have the same performance. Don't look at costs, they're estimates, not actual measures of performance. Adding a where clause that will have no effect just to turn a scan into a seek doesn't improve performance.

The index scan of the index on Session_SRNO will, in fact, read a single row because that's all it needs to do to get the highest value.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

GilaMonster (5/14/2015)
Alan.B (5/14/2015)
Add a the following WHERE clause to your Query after that and you get a non-clustered index seek (the plan has a slightly lower subtree cost on the SELECT statement.
WHERE SESSION_SRNO >= 0
Edit That's WHERE SESSION >= 0 (I don't know why SSC sometimes does that to my query code when posted)
But will have the same performance. Don't look at costs, they're estimates, not actual measures of performance. Adding a where clause that will have no effect just to turn a scan into a seek doesn't improve performance.
The index scan of the index on Session_SRNO will, in fact, read a single row because that's all it needs to do to get the highest value.

Noted. Thanks Gail.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001