Optimize Database Query Runs Slow with growing number of records

Question

Is there a better way to optimize this query, it gets slower and slower as more records are added to the database.

Thanks

SELECT     
    VoterID, AnswerID, SectionNumber, AnswerText, SurveyID, 
    CaseID, versionID, voterAnswerStatus, dateupdated, 
    recordstatus, modifiedby
FROM         
    Sample.vts_tbVoterAnswers AS a
WHERE
    (dateupdated = (SELECT MAX(dateupdated) AS Expr1
                    FROM Sample.vts_tbVoterAnswers AS b
                    WHERE (a.VoterID = VoterID) 
                      AND (a.AnswerID = AnswerID) 
                      AND (a.SectionNumber = SectionNumber) 
                      AND (a.SurveyID = SurveyID) 
                      AND (a.CaseID = CaseID) 
                      AND (a.versionID = versionID)))
    AND (recordstatus <> 'D')

SQL Server 2012

Table Definition enter image description here

    [Sample].[vts_tbVoterAnswers](
[VoterAnswerID_PK] [int] IDENTITY(1,1) NOT NULL,
[VoterID] [int] NOT NULL,
[AnswerID] [int] NOT NULL,
[SectionNumber] [int] NOT NULL,
[AnswerText] [ntext] NULL,
[SurveyID] [int] NULL,
[CaseID] [int] NULL,
[versionID] [int] NULL,
[voterAnswerStatus] [varchar](20) NULL,
[dateupdated] [datetime] NOT NULL,
[recordstatus] [varchar](2) NULL,
[modifiedby] [varchar](40) NULL,
[changereason] [varchar](200) NULL,
 CONSTRAINT [PK_vts_tbVoterAnswers] PRIMARY KEY CLUSTERED 
(
    [VoterAnswerID_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Hope this helps

Here are the Indexes associated with query.

CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20150203-143420] ON [Sample].[vts_tbVoterAnswers]
(
    [AnswerID] ASC,
    [VoterID] ASC,
    [SectionNumber] ASC,
    [SurveyID] ASC,
    [CaseID] ASC,
    [versionID] ASC,
    [recordstatus] ASC,
    [dateupdated] ASC,
    [voterAnswerStatus] ASC
)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 [PRIMARY]
GO


ALTER TABLE [Sample].[vts_tbVoterAnswers] ADD  CONSTRAINT [PK_vts_tbVoterAnswers] PRIMARY KEY CLUSTERED 
(
    [VoterAnswerID_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

USE [AdjudicateV3]
GO

/****** Object:  Index [TBVOTER_DECISION_IDX]    Script Date: 7/16/2015 12:27:20 PM ******/
CREATE NONCLUSTERED INDEX [TBVOTER_DECISION_IDX] ON [Sample].[vts_tbVoterAnswers]
(
    [recordstatus] ASC
)
INCLUDE (   [VoterID],
    [AnswerID],
    [SectionNumber],
    [SurveyID],
    [CaseID],
    [versionID],
    [dateupdated]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


USE [AdjudicateV3]
GO

/****** Object:  Index [TBVOTERANSWERS_CASEID_INX]    Script Date: 7/16/2015 12:27:38 PM ******/
CREATE NONCLUSTERED INDEX [TBVOTERANSWERS_CASEID_INX] ON [Sample].[vts_tbVoterAnswers]
(
    [AnswerID] ASC,
    [recordstatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


USE [AdjudicateV3]
GO

/****** Object:  Index [TBVOTERSANSWERS_PK]    Script Date: 7/16/2015 12:27:51 PM ******/
CREATE NONCLUSTERED INDEX [TBVOTERSANSWERS_PK] ON [Sample].[vts_tbVoterAnswers]
(
    [CaseID] ASC,
    [recordstatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


USE [AdjudicateV3]
GO

/****** Object:  Index [vts_tbVoterAnswers_PK]    Script Date: 7/16/2015 12:28:10 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [vts_tbVoterAnswers_PK] ON [Sample].[vts_tbVoterAnswers]
(
    [SurveyID] ASC,
    [CaseID] ASC,
    [versionID] ASC,
    [VoterID] ASC,
    [AnswerID] ASC,
    [dateupdated] ASC
)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, FILLFACTOR = 90) ON [PRIMARY]
GO

enter image description here

What concrete RDBMS is this for? What is your table structure? What indexes do you have on this table? — marc_s, Jul 16 '15 at 16:07
**You need to show us the table and index definitions**, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We need row counts because that can affect query planning. If you know how to do an `EXPLAIN` or get an execution plan, put the results in the question as well. If you have no indexes, visit http://use-the-index-luke.com. — Andy Lester, Jul 16 '15 at 16:11
Show the execution plan http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — Juan Carlos Oropeza, Jul 16 '15 at 16:34

score 2 · Accepted Answer · answered Jul 16 '15 at 16:52

Try to rewrite to RANK and see if this helps:

SELECT     
    VoterID, AnswerID, SectionNumber, AnswerText, SurveyID, 
    CaseID, versionID, voterAnswerStatus, dateupdated, 
    recordstatus, modifiedby
FROM
 (
  SELECT     
    VoterID, AnswerID, SectionNumber, AnswerText, SurveyID, 
    CaseID, versionID, voterAnswerStatus, dateupdated, 
    recordstatus, modifiedby,
    RANK() OVER (PARTITION BY VoterID, AnswerID, SectionNumber, SurveyID, CaseID, versionID ORDER BY dateupdated DESC) AS rnk
  FROM         
     Sample.vts_tbVoterAnswers AS a
 ) AS dt
WHERE (recordstatus <> 'D')
  AND rn = 1

Dnoeth it went from 9 seconds to 2 seconds! Thanks so much!!! — larry, Jul 17 '15 at 15:47

score 0 · Answer 2 · answered Jul 16 '15 at 16:35

0

I don't see an index on DateUpdated. Without it, the database has to walk through the entire table looking for rows that match DateUpdated=(... subquery ...). Add an index on the single DateUpdated column.

answered Jul 16 '15 at 16:35

Andy Lester

91,102
13
100
152

What would be best Non Clustered Index or Non Clustered Column Store Index. Great site (http://use-the-index-luke.com) by the way! Thanks – larry Jul 16 '15 at 16:50
I don't know the difference between those two types of indexes. Sorry. – Andy Lester Jul 16 '15 at 16:58
1

I modified the indexes, thanks so much for you help! – larry Jul 17 '15 at 15:48

score 0 · Answer 3 · answered Jul 16 '15 at 16:55

Use FISRT_VALUE OVER PARTITION (see documentation) instead of the subquery.

It should look like the following code for every column that is not used as key for partitioning:

FIRST_VALUE(columnname)
  OVER (
    PARTITION BY VoterID, AnswerID, SectionNumber, SurveyID, CaseID, versionID
    ORDER BY dateupdated ASC
  ) AS columnname

Optimize Database Query Runs Slow with growing number of records

3 Answers3