I have an internet db designed as follows (scripts follow and the end of the post)
table entities - different articles to be published to the internet
table medias - media files upload to our site (may be of different types - audio, video, pictures,...)
table entity_medias - N:N relationship between entities and entity_medias
many queries are like : choose the desired entities and then give me the top 1 media files of each type of media (i.e. 1 picture, 1 video, 1 audio, etc.) for each entity chosen. there are several pieces of info needed for each media: url, type, tool tip, descr,...) each entity can ahve between 0 and N medias of each type
I created a function using pivot to return all the media info to be select after the entity list has been created and it seems faster than any other method I tried, but still it is used a lot and is heavy. any suggestions for improvement?
typical query:
select bunch of fields from entities, all fields from func
from entities
outer apply fn_add_top1_media_info (entities.entity_Code) func
where conditions on entities
ALTER function [fn_add_top1_media_info]
(@entity int )
RETURNS @t TABLE ( entity_code int,
pic_media_code int,
video_media_code int,
audio_media_code int,
passport_media_code int,
file_media_code int,
hd_media_code int,
pic_file_ext char(4),
video_file_ext char(4),
audio_file_ext char(4),
passport_file_ext char(4),
file_file_ext char(4),
hd_file_ext char(4),
pic_tool_tip nvarchar(450),
video_tool_tip nvarchar(450),
audio_tool_tip nvarchar(450),
passport_tool_tip nvarchar(450),
file_tool_tip nvarchar(450),
hd_tool_tip nvarchar(450),
pic_bottom_desc nvarchar(275),
video_bottom_desc nvarchar(275),
audio_bottom_desc nvarchar(275),
passport_bottom_desc nvarchar(275),
file_bottom_desc nvarchar(275),
hd_bottom_desc nvarchar(275),
video_url varchar(300),
audio_url varchar(300),
hd_url varchar(300),
video_statDesc nvarchar(275),
audio_statDesc nvarchar(275),
hd_statDesc nvarchar(275),
can_enlarge bit)
AS
begin
insert into @t (entity_code ,
pic_media_code ,
video_media_code ,
audio_media_code ,
passport_media_code ,
file_media_code ,
hd_media_code ,
pic_file_ext ,
video_file_ext ,
audio_file_ext ,
passport_file_ext ,
file_file_ext ,
hd_file_ext ,
pic_tool_tip ,
video_tool_tip ,
audio_tool_tip ,
passport_tool_tip ,
file_tool_tip ,
hd_tool_tip ,
pic_bottom_desc ,
video_bottom_desc ,
audio_bottom_desc ,
passport_bottom_desc ,
file_bottom_desc ,
hd_bottom_desc ,
video_url ,
audio_url ,
hd_url,
video_statDesc,
audio_statDesc,
hd_statDesc,
can_enlarge)
select entity_code,
min([1]) as pic_media_code,
min([2]) as video_media_code,
min([3]) as audio_media_code,
min([4]) as passport_media_code,
min([5]) as file_media_code,
min([7]) as hd_media_code,
min([101]) as pic_file_ext,
min([102]) as video_file_ext,
min([103]) as audio_file_ext,
min([104]) as passport_file_ext,
min([105]) as file_file_ext,
min([107]) as hd_file_ext,
min([201]) as pic_tool_tip,
min([202]) as video_tool_tip,
min([203]) as audio_tool_tip,
min([204]) as passport_tool_tip,
min([205]) as file_tool_tip,
min([207]) as hd_tool_tip,
min([301]) as pic_bottom_desc,
min([302]) as video_bottom_desc,
min([303]) as audio_bottom_desc,
min([304]) as passport_bottom_desc,
min([305]) as file_bottom_desc,
min([307]) as hd_bottom_desc,
min([402]) as video_url,
min([403]) as audio_url,
min([407]) as hd_url,
min([502]) as video_statDesc,
min([503]) as audio_statDesc,
min([507]) as hd_statDesc,
min(can_enlarge) as can_enlarge
from
(
select em.entity_code, em.media_code, m.file_extension, url,
case when m.media_type_code=1 then convert(tinyint,m.can_enlarge) else null end as can_enlarge,
tool_tip = dbo.add_copyrights( em.tool_tip, m.media_company_code,0),
bottom_desc = dbo.add_copyrights(em.bottom_desc, m.media_company_code, 1 ),
statDesc = isNull(dbo.Trim(em.bottom_desc),m.media_desc),
--ROW_NUMBER() OVER(PARTITION BY em.entity_code,m.media_type_code ORDER BY em.entity_code DESC) as rownum,
media_type = m.media_type_code,
ext_type = m.media_type_code+100,
tool_type = m.media_type_code+200,
bottom_type = m.media_type_code+300,
url_type = m.media_type_code+400,
stat_type = m.media_type_code+500
from entity_medias em
inner join medias m
on em.media_code=m.media_code
where em.sort_order = 1 and entity_code = @entity) as tmpPic
pivot
(min(media_code)
for media_type in ([1],[2],[3],[4],[5],[7])
) as mvt
pivot
(min(file_extension)
for ext_type in ([101],[102],[103],[104],[105],[107])
) as fvt
pivot
(min(tool_tip)
for tool_type in ([201],[202],[203],[204],[205],[207])
) as tvt
pivot
(min(bottom_desc)
for bottom_type in ([301],[302],[303],[304],[305],[307])
) as bvt
pivot
(min(url)
for url_type in ([402],[403],[407])
) as uvt
pivot
(min(statDesc)
for stat_type in ([502],[503],[507])
) as svt
--where rownum=1
group by entity_code
return
end
table definitions
CREATE TABLE [entities](
[site_code] [smallint] NOT NULL,
[entity_type_code] [smallint] NOT NULL,
[entity_code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[extra] [bit] NOT NULL,
[start_date] [smalldatetime] NOT NULL,
[end_date] [smalldatetime] NOT NULL,
[create_date] [smalldatetime] NOT NULL,
[show_time] [tinyint] NOT NULL,
[update_date] [smalldatetime] NOT NULL,
[comment] [nvarchar](350) NOT NULL,
[worker_code] [smallint] NULL,
[user_code] [smallint] NOT NULL,
[worker_name] [nvarchar](280) SPARSE NULL,
[email] [varchar](40) SPARSE NULL,
[update_content_date] [smalldatetime] NULL,
[locked_user_code] [smallint] SPARSE NULL,
[locked_time] [smalldatetime] SPARSE NULL,
[locked_ip] [char](15) SPARSE NULL,
[locked_station] [char](15) SPARSE NULL,
[locked_user] [varchar](25) SPARSE NULL,
[update_computer] [char](15) SPARSE NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[is_exclusive] [bit] NOT NULL,
CONSTRAINT [PK_entities] PRIMARY KEY NONCLUSTERED
(
[entity_code] ASC)
CREATE TABLE [medias](
[media_code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[media_date] [smalldatetime] NOT NULL,
[media_desc] [nvarchar](50) NOT NULL,
[media_type_code] [smallint] NOT NULL,
[media_subtype_code] [tinyint] NOT NULL,
[url] [varchar](300) NOT NULL,
[file_extension] [char](4) NOT NULL,
[media_rights] [nvarchar](50) SPARSE NULL,
[can_enlarge] [bit] NOT NULL,
[can_delete] [bit] NOT NULL,
[is_deleted] [bit] NOT NULL,
[media_company_code] [tinyint] NOT NULL,
CONSTRAINT [PK_medias] PRIMARY KEY CLUSTERED
(
[media_code] ASC)
CREATE TABLE [entity_medias](
[entity_code] [int] NOT NULL,
[media_code] [int] NOT NULL,
[sort_order] [tinyint] NOT NULL,
[tool_tip] [nvarchar](350) NOT NULL,
[bottom_desc] [nvarchar](175) NOT NULL,
[updated_by] [smallint] NULL,
[update_time] [smalldatetime] NULL,
[update_computer] [char](15) SPARSE NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
CONSTRAINT [PK_entity_medias] PRIMARY KEY CLUSTERED
(
[entity_code] ASC,
[media_code] ASC)
CREATE NONCLUSTERED INDEX [IX_entity_medias] ON [entity_medias]
(
[entity_code] ASC,
[sort_order] ASC
)
INCLUDE ( [media_code]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [index]
GO
CREATE NONCLUSTERED INDEX [IX_entity_medias_1] ON [entity_medias]
(
[sort_order] ASC
)
INCLUDE ( [entity_code],
[media_code]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [index]
GO
Other recent topics
