sql - Quick SELECT sometimes time out -
i have stored procedure execute simple select. time run manually, runs under second. in production (sql azure s2 database) runs inside scheduled task every 12 ours - think reasonable expect run every time "cold" - no cached data. , performance unpredictable - takes 5 second, 30 , 100.
the select optimized maximum (of knowledge, anyway) - created filtered index including columns returned select, operation in execution plan index scan. there huge difference between estimated , actual rows:
but overall query seems pretty lightweight. not blame environment (sql azure) because there lot of queries executing time, , 1 1 performance problem.
here xml execution plan sql ninjas willing : http://pastebin.com/u5gcz0vw
edit:
table structure:
create table [myproject].[purchase]( [id] [int] identity(1,1) not null, [productid] [nvarchar](50) not null, [deviceid] [nvarchar](255) not null, [userid] [nvarchar](255) not null, [receipt] [nvarchar](max) null, [appversion] [nvarchar](50) not null, [ostype] [tinyint] not null, [ip] [nchar](15) not null, [createdon] [datetime] not null, [validationstate] [smallint] not null, [validationinfo] [nvarchar](max) null, [validationerror] [nvarchar](max) null, [validatedon] [datetime] null, [purchaseid] [nvarchar](255) null, [purchasedate] [datetime] null, [expirationdate] [datetime] null, constraint [pk_purchase] primary key clustered ( [id] asc )
index definition:
create nonclustered index [ix_androidrevalidationtargets3] on [myproject].[purchase] ( [expirationdate] asc, [validatedon] asc ) include ( [productid], [deviceid], [userid], [receipt], [appversion], [ostype], [ip], [createdon], [validationstate], [validationinfo], [validationerror], [purchaseid], [purchasedate]) ([ostype]=(1) , [productid] not null , [productid]<>'trial' , ([validationstate] in ((1), (0), (-2))))
data can considered sensitive, cant provide sample.
since query returns 1 match, think should trim down index bare minimum. can remaining columns via key lookup clustered index:
create nonclustered index [ix_androidrevalidationtargets3] on [myproject].[purchase] ( [expirationdate] asc, [validatedon] asc ) ([ostype]=(1) , [productid] not null , [productid]<>'trial' , ([validationstate] in ((1), (0), (-2))))
this doesn't eliminate scan, makes index leaner fast read.
edit: op stated slimmed-down index ignored sql server. can force sql server use filter index:
select * [myproject].[purchase] (index(ix_androidrevalidationtargets3))
Comments
Post a Comment