DWH better performing alternative to subquery? - 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)

DWH better performing alternative to subquery?

Author

Topic

jochemamsterdam
Starting Member

4 Posts

Posted - 2011-11-18 : 06:16:34

Hi there,

I have a T-SQL-question.

Simplified DWH description:

SoftwareDimension (SoftwareKey int, SoftwareName varchar(128))
SoftwareVersionDimension (SoftwareKey int, SoftwareVersionName varchar(128))
ComputerDimension (ComputerKey int, MachineName (varchar(64))
SoftwareVersionInstallation (ComputerKey int FK, SoftwareKey int FK, SoftwareVersionKey int FK, IsUsed bit)

Query goal:

List all unused Software Version Installations and indicate for each row if there is a USED installation of the same software with other version on the same computer.

We are talking about 10,000 computers, with on average 150 software version installations on each computer, which makes performance important.

I made a query but its performance is sub-optimal.

Have you got any ideas to improve this query?

Thanks a lot in advance,

Jochem


Select ComputerDimension.*, SoftwareDimension.*, SoftwareVersionDimension.*, 
	(select count(*) > 0 
	from SoftwareVersionInstallation subquery
	where subquery.SoftwareKey = SoftwareDimension.SoftwareKey
	and subquery.ComputerKey = ComputerDimension.ComputerKey
	and subquery.IsUsed = 1) as OtherUsedVersionsExists

From SoftwareVersionInstallation, SoftwareDimension, SoftwareVersionDimension, ComputerDimension 

Where IsUsed = 0
And
--Joins
(
SoftwareVersionInstallation.ComputerKey = ComputerDimension.ComputerKey
And SoftwareVersionInstallation.SoftwareKey = SoftwareDimension.SoftwareKey
And SoftwareVersionInstallation.SoftwareVersionkey = SoftwareVersionDimension.SoftwareVersionKey

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 06:52:11

and subquery.ComputerKey = SoftwareDimension.ComputerKey

SoftwareDimension.ComputerKey
doesn't exist.

I have a query that doesn't have the ciorrelated subquery but I think there might be issues with what you are trying to do.
Also count(*) > 0 doesn't work - it needs to return a value - I suspect a yes or no.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 06:58:09

maybe


;with cte as
(
select	sd_SoftwareKey = sd.SoftwareKey,
		cd_ComputerKey = cd.ComputerKey
From SoftwareVersionInstallation svi
	join SoftwareVersionDimension svd
		on svi.SoftwareVersionkey = svd.SoftwareVersionKey
	join ComputerDimension cd
		on svd.ComputerKey = cd.ComputerKey
where svi.IsUsed = 1
group by svd.SoftwareKey, sd.SoftwareKey
)
Select	cd.*, sd.*, svd.*, 
		OtherUsedVersionsExists = case when cte.sd_SoftwareKey is not null then 'yes' else 'no' end
From SoftwareVersionInstallation svi 
	join SoftwareDimension sd
		on svi.SoftwareKey = sd.SoftwareKey
	join SoftwareVersionDimension svd
		on svi.SoftwareVersionkey = svd.SoftwareVersionKey
	join ComputerDimension cd
		on svi.ComputerKey = cd.ComputerKey
	left join cte
		on cte.svd_SoftwareKey = svd.SoftwareKey
		and cte.ComputerKey = cd.ComputerKey
Where svi.IsUsed = 0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jochemamsterdam
Starting Member

4 Posts

Posted - 2011-11-18 : 07:02:52

quote:
Originally posted by nigelrivett

and subquery.ComputerKey = SoftwareDimension.ComputerKey

SoftwareDimension.ComputerKey
doesn't exist.

Thanks for your response. I am sorry, there was a mistake in that subquery. I just edited my original message.

quote:

Also count(*) > 0 doesn't work - it needs to return a value - I suspect a yes or no.

To my opinion this is just a boolean expression that returns 1 or 0, or am I wrong?

jochemamsterdam
Starting Member

4 Posts

Posted - 2011-11-18 : 07:05:54

quote:
Originally posted by nigelrivett

maybe
[code]
;with cte as

Thanks for your response.

I have been thinking about using a CTE, but would this be the only option to compare each row to one or more other rows?
I mainly thought about using CTE's on recursive query's.

Besides this, not many people have much knowledge on CTE's, and I would prefer a query that is also readable by programmers in the future that don't know about it..... :-(

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 07:15:41

Without a cte - but most people are used to them now as they are very useful.
count(*) > 0 doesn't return anything it just checks a value.


Select	cd.*, sd.*, svd.*, 
		OtherUsedVersionsExists = case when cte.sd_SoftwareKey is not null then 'yes' else 'no' end
From SoftwareVersionInstallation svi 
	join SoftwareDimension sd
		on svi.SoftwareKey = sd.SoftwareKey
	join SoftwareVersionDimension svd
		on svi.SoftwareVersionkey = svd.SoftwareVersionKey
	join ComputerDimension cd
		on svi.ComputerKey = cd.ComputerKey
	left join
		(	select	sd_SoftwareKey = svi.SoftwareKey,
					cd_ComputerKey = svi.ComputerKey
			From SoftwareVersionInstallation svi
			where svi.IsUsed = 1
			group by svi.SoftwareKey, svi.ComputerKey
		) cte
		on cte.svd_SoftwareKey = svd.SoftwareKey
		and cte.ComputerKey = cd.ComputerKey
Where svi.IsUsed = 0

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-18 : 08:53:41

[code]SELECT cd.*,
sd.*,
svi.*
FROM (
SELECT ComputerKey,
SoftwareKey
FROM dbo.SoftwareVersionInstallation
WHERE IsUsed = 1
GROUP BY ComputerKey,
SoftwareKey
) AS w
INNER JOIN dbo.SoftwareVersionInstallation AS svi ON svi.ComputerKey = w.ComputerKey
AND svi.SoftwareKey = w.SoftwareKey
AND svi.IsUsed = 0
INNER JOIN dbo.ComputerDimension AS cd ON cd.ComputerKey = svi.ComputerKey
INNER JOIN dbo.SoftwareDimension AS sd ON sd.SoftwareKey = svi.SoftwareKey
INNER JOIN dbo.SoftwareVersionDimension AS svd ON svd.SoftwareVersionKey = svi.SoftwareVersionKey[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-18 : 09:00:16

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-18 : 09:02:25

quote:
Originally posted by SwePeso

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

You pointed out that I have softwherekey instead of computerkey in the subquery.
I also ncluded an unnecessary join in the subquery. Corrected now
Also you need to left join to the subquery or you'll lose things with no other versions.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-18 : 09:20:50

Yes, I read the statement later. I first thought he wanted onyl the unused installations where there were an used installtion for same computer and fotware.

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

jochemamsterdam
Starting Member

4 Posts

Posted - 2011-11-28 : 17:43:43

After creating a index on ComputerKey, SoftwareKey and another one on IsUsed, even with 800K records in SoftwareVersionInstallations performs in just a couple of seconds. Thanks everyone for your help, and SwePeso for the query I am using now.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-11-29 : 03:26:02

[code]CREATE NONCLUSTERED INDEX IX_ComputerKey_SoftwareKey ON dbo.SoftwareVersionInstallation (ComputerKey, SoftwareKey) WHERE IsUsed = 1
CREATE NONCLUSTERED INDEX IX_ComputerKey_SoftwareKey_SoftwareVersionKey ON dbo.SoftwareVersionInstallation (ComputerKey, SoftwareKey, SoftwareVersionKey) WHERE IsUsed = 0[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources