June 22, 2007 at 1:43 pm
I have this SP:
SELECT Instances.Name AS InstanceName,
Guilds.GuildId,
Guilds.Name AS GuildName,
Guilds.Faction AS Faction,
convert(char(10), Kills.Date, 101) AS Date,
Realms.RealmId,
Realms.Name AS RealmName,
Realms.Zone,
dbo.fn_IsRealmPublic(Realms.RealmId) AS PublicRealm
FROM Instances
INNER JOIN Kills ON Instances.EndBossId = Kills.BossId
INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
INNER JOIN Realms ON Guilds.RealmId = Realms.RealmId
WHERE Kills.KillId = ( SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Horde'
ORDER BY Kills.Date, Guilds.Name
)
ORDER BY Instances.[Order]
It takes 1ms to execute and returns 16 rows.
If I change it by adding the UNION it takes 1:30 mins to execute and returns 32 rows (as intended).
Why It takes so long ? Please notice that I also tried to remove the UNION and I added a simple OR, but the result was the same.
SELECT Instances.Name AS InstanceName,
Guilds.GuildId,
Guilds.Name AS GuildName,
Guilds.Faction AS Faction,
convert(char(10), Kills.Date, 101) AS Date,
Realms.RealmId,
Realms.Name AS RealmName,
Realms.Zone,
dbo.fn_IsRealmPublic(Realms.RealmId) AS PublicRealm
FROM Instances
INNER JOIN Kills ON Instances.EndBossId = Kills.BossId
INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
INNER JOIN Realms ON Guilds.RealmId = Realms.RealmId
WHERE Kills.KillId IN (( SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Horde'
ORDER BY Kills.Date, Guilds.Name
) UNION
( SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Alliance'
ORDER BY Kills.Date, Guilds.Name
))
ORDER BY Instances.[Order]
June 22, 2007 at 2:46 pm
Well, there could be a lot of things, frankly. You might start by viewing the execution plan to see what action is taking so much time to execute.
What I'd guess (though it could be several different things)...
Usually using IN with a subquery is oftentimes pretty bad for performance (NOT IN/NOT EXISTS tends to be worse). SQL Server will try to convert this to a simple join statement if at all possible. However when you get too complicated, as your UNION probably is, the query processor can't convert the IN to a JOIN.
Try running just the innermost part of the IN query with the UNION and see how long that takes?
SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Horde'
ORDER BY Kills.Date, Guilds.Name
UNION
SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Alliance'
ORDER BY Kills.Date, Guilds.Name
You might try converting the IN to an inner join... i.e.
INNER JOIN ( SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Horde'
ORDER BY Kills.Date, Guilds.Name
UNION
SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Alliance'
ORDER BY Kills.Date, Guilds.Name
) derived
ON Kills.KillID = Derived.KillId
and seeing what you get... there's a some other potential problems too, but that's where I'd start. In general avoid UNION, IN/NOT IN whenever possible in my experience...
June 22, 2007 at 2:47 pm
Also if you're seeing long-term table scans on the Kills table, you might consider creating an index on Date and then Name... might help.
June 22, 2007 at 3:47 pm
Hello Aaron, thanks for your fast response.
I cannot convert it to an INNER JOIN, because of the Instances.EndBossId I use inside the sub-select that would not get recognized otherwise.
I tried running the the union alone, less than 1ms.
What I have noticed is that the code below executes in less than 1ms having = before the sub-select, but takes 30 seconds having IN. But I have to use IN because of the UNION.
...WHERE Kills.KillId = (( SELECT TOP 1 KillId
FROM Kills INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
WHERE Kills.BossId = Instances.EndBossId
AND Kills.Date IS NOT NULL
AND Guilds.Faction = 'Horde'
ORDER BY Kills.Date, Guilds.Name
)
June 22, 2007 at 4:06 pm
sal, Mihai; try changing to UNION ALL before looking for any other fixes. UNION is equivalent with SELECT DISTINCT - it looks for duplicates;
Hope this helps;
June 22, 2007 at 4:23 pm
No difference, the Union returns only 2 rows only after all.
But I think I found the problem ... "WHERE Kills.BossId = Instances.EndBossId" from the sub-selects seems to cause this. Replacing Instances.EndBossId with a value made the query to execute in less than 1ms. But Instances is just a small table that inner joins with the rest, it has only 16 rows.
June 22, 2007 at 4:37 pm
Ah, I'd missed that...
From what it looks like you're wanting to find the details of the most recent kill of a boss monster, correct? Why not do this...
SELECT Instances.Name AS InstanceName,
Guilds.GuildId,
Guilds.Name AS GuildName,
Guilds.Faction AS Faction,
convert(char(10), Kills.Date, 101) AS Date,
Realms.RealmId,
Realms.Name AS RealmName,
Realms.Zone,
dbo.fn_IsRealmPublic(Realms.RealmId) AS PublicRealm
FROM Instances
INNER JOIN Kills ON Instances.EndBossId = Kills.BossId
INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
INNER JOIN Realms ON Guilds.RealmId = Realms.RealmId
JOIN
(
SELECTk.KillId, x.BossID
FROMKills k
JOINGuilds g
ONk.GuildId = g.GuildID
JOIN
(
SELECTk.BossID, g.Faction, MAX(k.Date) As MAX_KillDate
FROM Kills k
JOIN Guilds g
ON k.GuildID = g.GuildID
WHERE g.Faction IN ('Horde', 'Alliance')
AND k.Date IS NOT NULL
) x
ONk.Date = x.MAX_Killdate
ANDk.BossID = x.BossID
ANDg.Faction = x.Faction
) sub
ONsub.KillID = k.KillID
ANDsub.BossID = k.BossID
That's kind of off the cuff and there may be a better way... it's the end of the day on a Friday and my brain ain't what it was on Monday.
June 22, 2007 at 4:52 pm
Something like that, but not the most recent, I want the first kill and this would be the oldest. Also I want the first kill when g.Faction = 'Alliance' and the first kill when g.Faction = 'Horde' for every instance. That would be 2 rows resulted for every instance.
The script above returns the lowest dates no matter if Alliance or Horde, but I need the lowest Alliance AND the lowest Horde.
Also I don't know if MIN or MAX can be used because there can be several Kills with the same Date, and the max or min one is determined by "ORDER BY Kills.Date, Guilds.Name"
June 22, 2007 at 4:55 pm
Change MAX() to MIN() in the script I gave you. Should do it.
June 22, 2007 at 4:58 pm
Please read again my last post, I edited it.
It also gives this error:
Column 'Kills.BossId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
June 25, 2007 at 11:43 am
Ah, whoops, my bad... try this:
SELECT Instances.Name AS InstanceName,
Guilds.GuildId,
Guilds.Name AS GuildName,
Guilds.Faction AS Faction,
convert(char(10), Kills.Date, 101) AS Date,
Realms.RealmId,
Realms.Name AS RealmName,
Realms.Zone,
dbo.fn_IsRealmPublic(Realms.RealmId) AS PublicRealm
FROM Instances
INNER JOIN Kills ON Instances.EndBossId = Kills.BossId
INNER JOIN Guilds ON Kills.GuildId = Guilds.GuildId
INNER JOIN Realms ON Guilds.RealmId = Realms.RealmId
JOIN
(
SELECT k.KillId, x.BossID
FROM Kills k
JOIN Guilds g
ON k.GuildId = g.GuildID
JOIN
(
SELECT k.BossID, g.Faction, MIN(k.Date) As MAX_KillDate
FROM Kills k
JOIN Guilds g
ON k.GuildID = g.GuildID
WHERE g.Faction IN ('Horde', 'Alliance')
AND k.Date IS NOT NULL
GROUP BY k.BossID, g.Faction
) x
ON k.Date = x.MAX_Killdate
AND k.BossID = x.BossID
AND g.Faction = x.Faction
) sub
ON sub.KillID = k.KillID
AND sub.BossID = k.BossID
June 30, 2007 at 11:14 am
Worked, thank you Aaron.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply