Subject | Performance Question |
---|---|
Author | Robert DiFalco |
Post date | 2004-08-07T20:47:52Z |
For some reason I expected this first query to be faster than the
second. However, they appear to be about the same. I have ten of
thousands of records. The first query actual performs more fetches.
Also, if interested, there are no table scans in the plan for the query.
Using Exists
============
SELECT 1
FROM RDB$DATABASE
WHERE EXISTS(
SELECT *
FROM Groupable
INNER JOIN NodeGroup ON Groupable.oid = NodeGroup.oid
INNER JOIN Link ON NodeGroup.id = Link.childId
INNER JOIN Path ON Link.parentId = Path.descendentId
WHERE Path.ancestorId = 1 AND Link.childId = 3)
Using Count
===========
SELECT COUNT(*)
FROM Groupable
INNER JOIN NodeGroup ON Groupable.oid = NodeGroup.oid
INNER JOIN Link ON NodeGroup.id = Link.childId
INNER JOIN Path ON Link.parentId = Path.descendentId
WHERE Path.ancestorId = 1 AND Link.childId = 3
The Count returns 100. Any thoughts?
second. However, they appear to be about the same. I have ten of
thousands of records. The first query actual performs more fetches.
Also, if interested, there are no table scans in the plan for the query.
Using Exists
============
SELECT 1
FROM RDB$DATABASE
WHERE EXISTS(
SELECT *
FROM Groupable
INNER JOIN NodeGroup ON Groupable.oid = NodeGroup.oid
INNER JOIN Link ON NodeGroup.id = Link.childId
INNER JOIN Path ON Link.parentId = Path.descendentId
WHERE Path.ancestorId = 1 AND Link.childId = 3)
Using Count
===========
SELECT COUNT(*)
FROM Groupable
INNER JOIN NodeGroup ON Groupable.oid = NodeGroup.oid
INNER JOIN Link ON NodeGroup.id = Link.childId
INNER JOIN Path ON Link.parentId = Path.descendentId
WHERE Path.ancestorId = 1 AND Link.childId = 3
The Count returns 100. Any thoughts?