use tempdb
go
if object_id('article') > 0
drop table article
go
create table article (
id int
identity (1, 1) not null
primary key clustered
, clid int null
, affids varchar (1000) null
, grptype varchar (500) null
, [type] varchar (500) null
, chksmcurr varchar (50) null
, chksmver varchar (50) null
, crdt datetime null
, creator varchar (100) null
, dtend datetime null
, dtstart datetime null
, vseqno int null
, protcont int null
, date datetime null
, [time] datetime null
, headline varchar (1000) null
, shheadline varchar (1000) null
, pullquote varchar (8000) null
, prodprior int null
, grpprior int null
, author varchar (255) null
, source varchar (255) null
, crdt2 datetime null
, params varchar (5000) null
)
go
create index article2 on article(crdt2)
go
create index article3 on article(clid)
go
create index article4 on article(prodprior)
go
create index article5 on article(creator)
go
create index article6 on article(crdt, creator, vseqno)
go
--recommended by MS support engineers
--but worsened the problem (much higher CPU)
create index ndx_article2
on article(clid, grptype, chksmcurr,
chksmver, affids, crdt, creator,
dtend, dtstart, vseqno, [date] desc,
[time] desc , crdt2)
go
--also recommended by MS support engineers
--but worsened the problem (much higher CPU)
create index ndx_article3 on article(clid, grptype,
chksmcurr, chksmver, affids,
crdt, creator, dtend, dtstart,
vseqno, prodprior, grpprior, date desc,
[time] desc , crdt2)
goselect top 5
a.id
, a.params
, a.affids
, a.protcont
, a.headline
, a.shheadline
, a.date
, a.time
, a.author
, a.source
, a.pullquote
, '' as atparam
, gt.params as gtparam
from article a
join grptype gt on a.grptype = gt.grptype and a.clid = gt.clid
join articletype at on a.type = at.type
where a.clid = 2
and ('' + a.creator = ''
or a.crdt is null
or a.vseqno is null
or (a.vseqno = (select top 1 vseqno
from article v
where a.crdt = v.crdt
and a.creator = v.creator
order by vseqno desc)
)
)
and a.grptype = 'news'
and ((a.dtstart <= getdate() or (isdate(a.dtstart) = 0))
and (a.dtend > getdate() or (isdate(a.dtend) = 0)))
and ((',' + a.affids + ',' like '%,713,%') and ',' + a.affids + ',' not like '%,706,%')
and a.chksmcurr = a.chksmver
order by a.prodprior asc
, a.grpprior asc
, a.date desc
, a.time desc
go