July 13, 2011 at 4:34 pm
I have a spotlight section on my homepage. the spotlights are stored in this table:
CREATE TABLE [dbo].[spotlights](
[id] [int] IDENTITY(1,1) NOT NULL,
[objectid] [int] NOT NULL,
[startdate] [datetime] NOT NULL,
[enddate] [datetime] NOT NULL,
[objecttype] [int] NOT NULL,
[createdate] [datetime] NOT NULL,
CONSTRAINT [PK_spotlights] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Now the objectid value determines which table to get the rest of the data.
case objectid=1: table [locations]
case objectid=2: table [artists]
case objectid=3: table [cameras]
case objectid>3: table [genobjects]
Now currently I retrieve data like so:
SELECT s.id, s.objectid, o.title, 1 AS objecttype, s.startdate, s.enddate, o.createdate, op.locpath
FROM locations AS o INNER JOIN
spotlights AS s ON s.objectid = o.id LEFT OUTER JOIN
location_photos AS op ON op.id = o.thumbid
WHERE (s.startdate <= GETDATE()) AND (s.enddate >= GETDATE())
UNION
SELECT s.id, s.objectid, o.title, 2 AS objecttype, s.startdate, s.enddate, o.createdate, op.locpath
FROM artists AS o INNER JOIN
spotlights AS s ON s.objectid = o.id LEFT OUTER JOIN
artist_photos AS op ON op.id = o.thumbid
WHERE (s.startdate <= GETDATE()) AND (s.enddate >= GETDATE())
UNION
SELECT s.id, s.objectid, o.title, 3 AS objecttype, s.startdate, s.enddate, o.createdate, op.locpath
FROM cameras AS o INNER JOIN
spotlights AS s ON s.objectid = o.id LEFT OUTER JOIN
camera_photos AS op ON op.id = o.thumbid
WHERE (s.startdate <= GETDATE()) AND (s.enddate >= GETDATE())
UNION
SELECT s.id, s.objectid, o.title, o.objecttype, s.startdate, s.enddate, o.createdate, op.locpath
FROM genobjects AS o INNER JOIN
spotlights AS s ON s.objectid = o.id LEFT OUTER JOIN
genobjects_photos AS op ON op.id = o.thumbid
WHERE (s.startdate <= GETDATE()) AND (s.enddate >= GETDATE())
The problem is, that this statement doesnt use the objecttype column correctly to join with the correct table.
If the value of [spotlights].objecttype=1, I want the statement to look only in the [locations] table for the inner join.
How can I improve my SQL statement?
July 14, 2011 at 5:42 am
This was removed by the editor as SPAM
July 14, 2011 at 5:45 am
This was removed by the editor as SPAM
July 14, 2011 at 5:59 am
No. The objecttype value is already in the spotlights table [spotlights].objecttype and should determine which table should be joined.
The entire query must be able to execute without passing any parameters.
Thanks! 🙂
July 14, 2011 at 6:22 am
This was removed by the editor as SPAM
July 16, 2011 at 4:33 am
Thanks! 🙂
July 16, 2011 at 4:50 am
You could also replace UNION with UNION ALL since due to the different values for s.objecttype in each UNION statement there isn't any need to check for duplicates.
July 19, 2011 at 2:31 pm
LutzM (7/16/2011)
You could also replace UNION with UNION ALL since due to the different values for s.objecttype in each UNION statement there isn't any need to check for duplicates.
+1000
Switching to UNION ALL will change your execution plan for the better, by A LOT!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply