Index Scan - Any way to improve query or index? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Index Scan - Any way to improve query or index?

Author

Topic

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 11:50:29

I'm particularly concerned about the last part of the query below that deals with the students table as that part is 31% of the query cost. It is doing an index scan on an index that has clientid and departmentid as its keys. Is there a different index I can construct so that it does a seek instead of a scan or, alternatively, could the query be done more efficiently?


set statistics io on

SELECT [t0].[DepartmentID] AS [ID], [t0] 
   .[DepartmentName] AS [Name],  
   (  
   SELECT COUNT(*)  
   FROM  
      (  
      SELECT ( 
         CASE  
            WHEN ([t1].[ClientID] = 1)  
           AND ([t1].[DepartmentID] = ([t0].[DepartmentID])) THEN 1  
            WHEN NOT (([t1].[ClientID] = 1)  
           AND ([t1].[DepartmentID] = ([t0].[DepartmentID]))) THEN 0  
            ELSE NULL  
         END) AS [value]  
      FROM [dbo].[AdminLogins] AS [t1]  
      ) AS [t2]  
   WHERE [t2].[value] = 1  
   ) AS [Admins],  
   (  
   SELECT COUNT(*)  
   FROM  
      (  
      SELECT ( 
         CASE  
            WHEN ([t3].[ClientID] = 1)  
           AND ([t3].[ParentDeptID] = ([t0].[DepartmentID])) THEN 1  
            WHEN NOT (([t3].[ClientID] = 1)  
           AND ([t3].[ParentDeptID] = ([t0].[DepartmentID]))) THEN 0  
            ELSE NULL  
         END) AS [value]  
      FROM [dbo].[Departments] AS [t3]  
      ) AS [t4]  
   WHERE [t4].[value] = 1  
   ) AS [Departments],  
   (  
   SELECT COUNT(*)  
   FROM  
      (  
      SELECT ( 
         CASE  
            WHEN ([t5].[ClientID] = 1)  
           AND ([t5].[DepartmentID] = ([t0].[DepartmentID])) THEN 1  
            WHEN NOT (([t5].[ClientID] = 1)  
           AND ([t5].[DepartmentID] = ([t0].[DepartmentID]))) THEN 0  
            ELSE NULL  
         END) AS [value]  
      FROM [dbo].[Students] AS [t5]  
      ) AS [t6]  
   WHERE [t6].[value] = 1  
   ) AS [Students]  
FROM [dbo].[Departments] AS [t0]  
WHERE [t0].[ClientID] = 1

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-31 : 12:03:09

Table definitions, index definitions, execution plan please.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-31 : 12:27:48

Those case statements don't make any sense to me.

Isn't this exactly equivalent to your code


SELECT
	d.[DepartmentID] AS [ID]
	, d.[DepartmentName] AS [Name]
	, ( SELECT COUNT(*) FROM dbo.[AdminLogins] AS al WHERE al.[ClientID] = 1 AND al.[DepartmentID] = d.[DepartmentID] ) AS [Admins]
	, ( SELECT COUNT(*) FROM dbo.[Departments] AS d2 WHERE d2.[ClientID] = 1 AND d2.[ParentDeptID] = d.[DepartmentID] ) AS [Departments]
	, ( SELECT COUNT(*) FROM dbo.[Students] AS s WHERE s.[ClientId] = 1 AND s.[DepartmentID] = d.[DepartmentID] ) AS [Students]	
FROM
	[dbo].[Departments] AS d
WHERE
	d.[ClientID] = 1

It's probably not any more performant but its a damn sight easier to read.

I suspect a cut and paste massacre.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-31 : 12:33:00

I don't like subqueries so I'd do this as a first stab



SELECT
	d.[DepartmentID] AS [ID]
	, d.[DepartmentName] AS [Name]
	, a.[Admins]
	, d2.[Departments]
	, s.[Students]
FROM
	[dbo].[Departments] AS d

	CROSS APPLY (
		SELECT COUNT(*) AS [Admins]
		FROM dbo.[AdminLogins] AS al
		WHERE al.[ClientID] = d.[ClientID] AND al.[DepartmentID] = d.[DepartmentID]
		)
		AS a

	CROSS APPLY (
		SELECT COUNT(*) AS [Departments]
		FROM dbo.[Departments] AS d2
		WHERE d2.[ClientID] = d.[ClientID] AND d2.[ParentDeptID] = d.[DepartmentID]
		)
		AS d2

	CROSS APPLY (
		SELECT COUNT(*) AS [Students]
		FROM dbo.[Students] AS s
		WHERE s.[ClientID] = d.[ClientID] AND s.[DepartmentID] = d.[DepartmentID]
		)
		AS s
WHERE
	d.[ClientID] = 1

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-31 : 12:35:53

Index on students should be


CREATE NONCLUSTERED INDEX IX_Students_ClientID_DepartmentID ON dbo.Students ([ClientID], [DepartmentID])

If it's doing a scan then thats probably because the engine thinks it's more performant that a seek. If you are actually going to count most of the students then a scan is probably better.

But tables / indexes please.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 12:40:21

That's brilliant Charlie!!! I ran both and your query had 0% cost while my query had 100% cost. It is a massive improvement. Thank you soooo much.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-31 : 12:47:13

quote:
Originally posted by Transact Charlie

its a damn sight easier to read.

That's for sure!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2011-08-31 : 12:48:03

quote:

Originally posted by sodeep

SELECT [t0].[DepartmentID] AS [ID], [t0].[DepartmentName] AS [Name],  
    SUM ( 
         CASE  
            WHEN ([t1].[ClientID] = 1)  
           AND ([t1].[DepartmentID] = ([t0].[DepartmentID])) THEN 1  
           ELSE 0   
         END) AS [Admins],
    SUM( 
         CASE  
            WHEN ([t0] .[ClientID] = 1)  
           AND ([t0].[ParentDeptID] = ([t0].[DepartmentID])) THEN 1  
           ELSE 0  
         END) AS [Departments],

    SUM ( 
         CASE  
            WHEN ([t5].[ClientID] = 1)  
           AND ([t5].[DepartmentID] = ([t0].[DepartmentID])) THEN 1  
           ELSE 0  
         END) AS [Students]  
FROM [dbo].[Departments] [t0]  
left join [dbo].[AdminLogins] [t1] on [t0].[DepartmentID] = [t1].[DepartmentID]
left join [dbo].[Students] [t5] on [t5].[DepartmentID] = [t1].[DepartmentID]
WHERE [t0].[ClientID] = 1
Group by [t0].[DepartmentID], [t0].[DepartmentName]

Not sure though :)

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 13:45:06

I spoke with our developers and the inefficient version that I originally posted was generated by LINQ. Urg - not impressed with LINQ.

ferrethouse
Constraint Violating Yak Guru

352 Posts

Posted - 2011-08-31 : 13:47:05

quote:

Originally posted by sodeep

quote:

Originally posted by sodeep

SELECT [t0].[DepartmentID] AS [ID], [t0].[DepartmentName] AS [Name],  
    SUM ( 
         CASE  
            WHEN ([t1].[ClientID] = 1)  
           AND ([t1].[DepartmentID] = ([t0].[DepartmentID])) THEN 1  
           ELSE 0   
         END) AS [Admins],
    SUM( 
         CASE  
            WHEN ([t0] .[ClientID] = 1)  
           AND ([t0].[ParentDeptID] = ([t0].[DepartmentID])) THEN 1  
           ELSE 0  
         END) AS [Departments],

    SUM ( 
         CASE  
            WHEN ([t5].[ClientID] = 1)  
           AND ([t5].[DepartmentID] = ([t0].[DepartmentID])) THEN 1  
           ELSE 0  
         END) AS [Students]  
FROM [dbo].[Departments] [t0]  
left join [dbo].[AdminLogins] [t1] on [t0].[DepartmentID] = [t1].[DepartmentID]
left join [dbo].[Students] [t5] on [t5].[DepartmentID] = [t1].[DepartmentID]
WHERE [t0].[ClientID] = 1
Group by [t0].[DepartmentID], [t0].[DepartmentName]

Not sure though :)

Yours is considerably more efficient than my original one but considerably less efficient than the one posted by Charlie.

Thanks for everyone's efforts!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-31 : 14:42:17

quote:
Originally posted by ferrethouse

I spoke with our developers and the inefficient version that I originally posted was generated by LINQ. Urg - not impressed with LINQ.

LINQ is awesome. Just don't use to write your SQL for you!

. Just like hibernate.

But if you want to de-serialize a database response into a business object. It Rocks. It really, really rocks.

I take it you are using linq to sql? I love it. It's been canceled by MS and replaced by a much more cumbersome framework. With linq to sql it's so easy to match up results of a stored proc (just store a sample output of the sp into a temp table -- set up the class based on that in your datacontext then delete the temp table. Link the sp (and get intellisense for it!) hey presto! Love it.

but yeah.... don't let it write any SQL for you. Use stored procs.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-31 : 14:44:48

quote:
Originally posted by ferrethouse

That's brilliant Charlie!!! I ran both and your query had 0% cost while my query had 100% cost. It is a massive improvement. Thank you soooo much.

No problem. Those case statements just looked totally weird to me. Something had to be done!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources