Help to improve query to find missing values

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)

Help to improve query to find missing values

Author

Topic

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2012-05-05 : 13:18:31

Hi,
I have two tables Quaters and QControls. I want to get missing rows into my select.

declare @Quaters table
(
Quater varchar(10),
ControlId int, -- Foregin key of @QControls table
test varchar(10)
)
Insert into @Quaters
select 'Q1',1,'1111'
Insert into @Quaters
select 'Q1',2 ,'2222'
Insert into @Quaters
select 'Q2',1 ,'33333'
Insert into @Quaters
select 'Q2',3,'4444';

--Select * from @Quaters

declare @QControls table
(
ControlId int,
ControlName Varchar(10)
)
Insert into @QControls
select 1,'C1'
UNION ALL
Select 2,'C2'
UNION ALL
select 3,'C3'
UNION ALL
Select 4,'C4'

--This query gives all Quarters with matching controls

Select q.Quater , q.ControlId from @Quaters q inner join @QControls c on q.ControlId = c.ControlId

--below query gives all quarters with missing controls
Select tbl1.Quater, c.ControlId from
(
Select q.Quater from @Quaters q group by q.Quater
)tbl1
cross join @QControls c
except
Select q.Quater , q.ControlId from @Quaters q inner join @QControls c on q.ControlId = c.ControlId

I am able to get the missing controls for all quarters but does not think the query is efficient. Please suggest improvements.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 13:37:50

ideally you should have a master table for quarters (may be a calendar table). using Current quarter table will not be always correct as there can be a chance that a particular quarter having no controls at all. So always best to use master table for Quarters rather than relying upon actual data table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RL
Starting Member

15 Posts

Posted - 2012-05-05 : 20:07:38

Totally agree about using a master calendar table for time-related entities.
That said, the following code should be more efficient:


SELECT DISTINCT Q.Quater, C.ControlID
  FROM Quaters Q CROSS JOIN QControls C
 WHERE NOT EXISTS
       (SELECT 1 FROM Quaters
         WHERE Quater = Q.Quater
           AND ControlID = C.ControlID)

Most of the query plan cost savings comes from using DISTINCT instead of the derived table with GROUP BY. Replacing the EXCEPT with the WHERE NOT EXISTS filter yields a little more reduction. The end result is about 40% lower cost.

To do the comparisons, I used physical tables (with no indexes) instead of table variables and checked the total cost of the estimated execution plan for each query. On my 2CPU 4G SQL2008 system, the results were:


Original Cost:     .0369462
No Derived Table:  .0261035
WHERE NOT EXISTS:  .0229184

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-06 : 14:55:42

Here are two other approaches

SELECT		f.Quater,
		c.ControlId
FROM		@QControls AS c
CROSS APPLY	(
			SELECT	q.Quater
			FROM	@Quaters AS q
			WHERE	q.ControlId <> c.ControlId
		) AS f
GROUP BY	f.Quater,
		c.ControlId
HAVING		COUNT(*) > 1

SELECT		q.Quater,
		c.ControlId
FROM		@QControls AS c
INNER JOIN	@Quaters AS q ON q.ControlId <> c.ControlId
GROUP BY	q.Quater,
		c.ControlId
HAVING		COUNT(*) > 1

N 56°04'39.26"
E 12°55'05.63"

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2012-05-07 : 00:55:31

Thanks a lot for all your suggestions. It really helped me in understanding new approaches put my case forward for the db design flaw(as pointed out by Visakh).

regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:14:19

ok..that should get you in right way

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources