Better way??
-
I am just playing around with joins and max and mins. This is not for a project just a chance to improve. I have the following query that I would like to get Max and min information with. Is there a better performing way of doing the following:
select s.name, d.updated, d.name, d.free, d.total
from diskspace d join server s on (d.serverid=s.serverid)
where updated in
((select max(updated)
from diskspace where serverid=d.serverid
and Description='Local Fixed Disk'
group by serverid, name)
union
(select min(updated)
from diskspace
where serverid=d.serverid
and Description='Local Fixed Disk'
group by serverid, name))
order by d.serverid, d.updated
Thanks in advance.
Stacey W. A. Gregerson -
Getting rid of the UNION and the WHERE <column> IN should help performance.
I'd use a derived table to get the required aggregates (Min & Max) per serverid, then join to the derived table:
select s.name, d.updated, d.name, d.free, d.total
from diskspace d
Inner join server s on (d.serverid=s.serverid)
Inner Join
(
Select serverid, Max(updated) As MaxUpdated, Min(updated) As MinUpdated)
From diskspace
Where description = 'Local Fixed Disk'
Group By serverid
) dt
On (dt.serverid = d.serverid and
(updated = dt.MaxUpdated Or updated = dt.MinUpdated)
)
-
That worked much faster. It is now 4 seconds instead of 24. It seems I need to learn more about derived tables.
I thank you very much.
Stacey W. A. Gregerson -
The speed up probably has less to do with the DT, and more to do with getting rid of the IN() and the correlated sub-query. Use of IN() should be a last resort and you should aim for EXISTS, or JOINS or in this case, a join to a DT instead.
-
I see that you used an inner join where I used just a straight join in the first section.
I am a little confused with the derived statement though. It looks like you just used a DT. I am not a programmer but I always thought you had to define. I just see your dt after a select inside of some parenthesis. is that all it take to create it? with temp tables yopu have to do a create.
This is very good.
Stacey W. A. Gregerson -
(
Select serverid, Max(updated) As MaxUpdated, Min(updated) As MinUpdated)
From diskspace
Where description = 'Local Fixed Disk'
Group By serverid
) dt
dt is short for derived table.
I usually name those like this dtMeaningfullName. And yes, that's all it takes to create one.
-
Thanks again. I plan on reviewing some production queries that have had simular performance issues.
Stacey
Stacey W. A. Gregerson -
>>I always thought you had to define. I just see your dt after a select inside of some parenthesis. is that all it take to create it?
In SqlServer, yes, that's all it takes. Maybe you're thinking of Common Table Expressions (CTE) which are coming in the next version of SqlServer. A CTE has to be defined before use, but is more flexible than a simple derived table.
>>with temp tables yopu have to do a create.
Right, or a SELECT INTO
And Remi's advice is good, you should name the derived table something more meaningful than "dt". I usually do, but not on Friday mornings when under-caffeinated
-
I was just requoting PW's solution... I never name any derived table dt (in prod environement anyways). I must say I don't apply all best practices when I just need a short test for myself
that's not gonna make it to production.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply