Optimize the query in sql server
CREATE VIEW [dbo].[vwResults]
AS
SELECT r.ResultsPK, b.PersonID, dbo.ltcPerson.FirstName, dbo.ltcPerson.LastName, dbo.ltcPerson.PersonFriendlyID, b.DateCreated, b.FormName,
r.FieldName, r.ValueString, r.ValueNumber, r.ValueDate, r.ValueTextInt, r.ValueBit, b.UserID, b.BatchID, b.ParentID, r.Active,
CASE WHEN b.ParentID = 0 THEN 0 ELSE 1 END AS IsChildRecord, CAST(b.PersonID AS varchar(4)) AS PersonIDStr
FROM dbo.ltcBatch b INNER JOIN
dbo.ltcResults r ON b.BatchID = r.BatchID INNER JOIN
dbo.ltcPerson ON b.PersonID = dbo.ltcPerson.PersonID
GO
the ltcResults table have more than 30 million records.
CREATE VIEW [dbo].[vwRptHeaderToResultsFormRpts]
AS
SELECT TOP 100 PERCENT dbo.vwResults.ParentID, dbo.vwResults.BatchID, dbo.vwResults.PersonID, dbo.vwResults.FormName, dbo.vwResults.FieldName,
dbo.vwResults.ValueString, dbo.vwResults.ValueNumber, dbo.vwResults.ValueDate, dbo.vwResults.ValueBit, dbo.vwResults.Active,
dbo.vwRptFields.ControlGroupType, dbo.vwRptFields.ControlCaption, dbo.vwRptFields.ReportCaption AS ReportCaptionField,
dbo.vwRptFields.ControlName, dbo.vwRptFields.ControlSeqNo, dbo.vwRptFields.itemtype, dbo.vwResults.FirstName, dbo.vwResults.LastName,
dbo.vwResults.PersonFriendlyID, dbo.vwResults.DateCreated, dbo.vwResults.IsChildRecord, dbo.vwRptFields.FormCaption,
dbo.vwRptFields.FormSeqNo, dbo.vwRptFields.TabCaption, dbo.vwRptFields.TabSeqNo, dbo.vwRptFields.PanelCaption, dbo.vwRptFields.PanelSeqNo,
dbo.vwResults.ResultsPK, dbo.vwResults.UserID, dbo.vwRptHeader.CrystalRptID, dbo.vwRptHeader.ReportCaption
FROM dbo.vwRptFields INNER JOIN
dbo.vwRptHeader ON dbo.vwRptFields.FormName = dbo.vwRptHeader.RptFormName INNER JOIN
dbo.vwResults ON dbo.vwRptFields.fieldname = dbo.vwResults.FieldName
ORDER BY dbo.vwRptFields.FormSeqNo, dbo.vwRptFields.TabSeqNo, dbo.vwRptFields.PanelSeqNo, dbo.vwRptFields.ControlSeqNo
GO
other views are not taking more time but vwResults
Software/Hardware used:
Software/Hardware used:
ASKED:
June 1, 2009 12:35 PM
UPDATED:
June 3, 2009 12:53 AM
Answer Wiki:
Have you had a look at the execution plan for the query ?
A full table scan on a 30 million rows table would be expensive.
--------------------
To see all answers submitted to the Answer Wiki: View Answer History.
The first thing I would look at is the vwResults. If you do a “select count(*) from vwResults”, do you get back the same count as for itcResults, or is it possible there are some joins that are expanding the result set.
I am assuming (always bad, but…) that each record in vwResults can be in only one Batch. However, if there can be multiple Persons in each batch, you are probably getting a massive expansion of the results. For example, if there are 100 itcResults records linked to Batch 123, and Batch 123 has 20 Persons, that will result in 2000 result rows in the query. Multiply that by your 30 million base table rows and your intermediate view result set may be hundreds of millions of rows. (If BatchID is a single-column primary key, then this shouldn’t occur.)
Second, your larger view references “FieldName” but no other qualifiers (like “FormName”). If the same field name occurs on multiple forms, a similar unexpected expansion can be occurring. This looks odd to me and perhaps you are just missing a join on “FormName” in this expression.
Assuming that you verify the syntax and results, and the only issue is performance, then I would look at using an Indexed View. Since your outer view only references “FieldName” in vwResults, I would certainly put an index on that column (and make it a clustered index!). There are any number of sites with tutorials on creating indexed views – google “sql indexed view clustered” to get a pretty good list.