Optimize the query in sql server

Microsoft SQL Server 2000
SQL Server Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.


Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kccrosser
    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.
    3,830 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: