INNER JOIN with a Nested SELECT

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)

INNER JOIN with a Nested SELECT

Author

Topic

HeTz
Starting Member

2 Posts

Posted - 2009-12-08 : 12:14:36

I'm fairly new to the more complex SQL statements in very large databases, so I need a little help (for those that are willing to give it!)

I'm designed an ASP .NET website that will be interacting with a SQL database. The SQL database has two tables: deployment_codes and deployment_plan

The deployment_plan will eventually house more than 500,000 records, so I'm trying to plan ahead with efficient SELECT statements.

Anyways, on the Dashboard, I'm trying to show some statistics based on the deployment_code and here's the SELECT statement I'm using:

-----------------------------------------------
SELECT
c.[ID], c.[Code], c.[Install],
c.[Start], c.[End], c.[Created],
p.[ActualTotalCount], p.[AgreedTotalCount],
p.[CodeTotalCount]
FROM
deployment_codes c
INNER JOIN
(SELECT
CodeID,
COUNT(Actual) AS ActualTotalCount,
COUNT(Agreed) AS AgreedTotalCount,
COUNT(CodeID) AS CodeTotalCount
FROM
deployment_plan
GROUP BY
CodeID) p
ON
c.[ID] = p.[CodeID]
WHERE (c.Code LIKE 'N%')
-----------------------------------------------

I've set up some indexes to make this SELECT performance better, but I still don't think I have it set-up correctly. The execution plan is showing that it does an index scan on the deployment_plan table - Is this because of the COUNTS? It also does a clustered index seek on the deployment_codes table, which is what I want. Is there any way to make this SELECT statement more efficient, such as doing an index seek on the deployment_plan table instead of an index scan?

I apologize if my understanding of some of the terms, etc. are off-base - I'm still learning.

Thanks for your help.

HeTz
Starting Member

2 Posts

Posted - 2009-12-08 : 13:46:50

Here's another variation of the SELECT statement that produces the same results:

SELECT
c.[ID], c.[Code], c.[Install],
c.[Start], c.[End], c.[Created],
(SELECT COUNT(Agreed) As AgreedTotalCount
FROM deployment_plan p
WHERE p.CodeID = c.[ID]) As AgreedTotalCount,
(SELECT COUNT(Actual) As ActualTotalCount
FROM deployment_plan p
WHERE p.CodeID = c.[ID]) As ActualTotalCount,
(SELECT COUNT(CodeID) As CodeTotalCount
FROM deployment_plan p
WHERE p.CodeID = c.[ID]) As CodeTotalCount
FROM
deployment_codes c
WHERE (c.Code LIKE 'N%')

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-16 : 15:24:54

I'd use a common table expression (or a temp table if you're going into the millions of records scenario) for deployment plan but for the most part your first query is good. Try putting a covering index on deployment plan with your summed columns as the included columns and ID as the indexed column.

It seems reasonable that you'd do an index scan on the deployment plan table.

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources