Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query/View optimization

Status
Not open for further replies.
wbodger

wbodger

Programmer
Joined
Apr 23, 2007
Messages
769
Location
US
If
Code: 
SELECT * 
FROM view_NEWAEROQCCellInterfacesFASTER

returns data (1700 records) in 6 seconds (longer than I would like, but another inherited system that is doing way too much). and this code

Code: 
SELECT * 
FROM view_NEWAEROQCCellInterfacesFASTER
Where upper([STOCK_CODE]) = '787 LANTAL MAIN CARPET'

returns data (15 records) in 45 seconds, is there anything that I can do short of recreating all affected views with schema binding so that I can create indexes on the appropriate columns?

Thanks,
Willie
 
Sort by date Sort by votes
How about:
Code: 
SELECT * 
FROM view_NEWAEROQCCellInterfacesFASTER
Where [STOCK_CODE] = '787 LANTAL MAIN CARPET'
If your collation is NOT case sensitive?

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
Collation is case sensitive (and I am not happy about it), but even if I run it without the UPPER it still takes just as long.

Thanks,
Willie
 
Upvote 0 Downvote
Does this view return data from other views, which returns data from other views, etc....

When you nest views too deeply, performance can degrade. Can you show us the definition of the view?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Yes, there are nested views. This sview is

Code: 
SELECT     *
FROM         dbo.view_NEWAEROQCCellInterfacesFASTER_Pre
WHERE     (CUSTOMER = 'Goodrich Aircraft Seating Prod') AND (NOT (DateReleased IS NULL)) OR
                      (NOT (CUSTOMER LIKE 'Good%')) OR
                      (CUSTOMER = 'Goodrich Aircraft Seating Prod') AND (LEN(RTRIM(FOM)) <> 0)

and dbo.view_NEWAEROQCCellInterfacesFASTER_Pre is

Code: 
SELECT DISTINCT 
                      TOP 100 PERCENT EncoreCompanyK.dbo.WipMaster.Job, dbo.tbl_ReleaseStatus.MaterialComplete, dbo.tbl_ReleaseStatus.WaterSpiderComplete, 
                      EncoreCompanyK.dbo.WipMaster.JobDeliveryDate AS JOB_DELIVERY_DATE, EncoreCompanyK.dbo.WipMaster.CustomerName AS CUSTOMER, 
                      EncoreCompanyK.dbo.WipMaster.JobDescription AS JOB_DESCRIPTION, EncoreCompanyK.dbo.WipMaster.StockCode AS STOCK_CODE, 
                      dbo.tbl_ReleaseStatus.PercentComplete, ISNULL(dbo.tbl_SplitJobs.Qty, EncoreCompanyK.dbo.WipMaster.QtyToMake) AS QTY_TO_MAKE, 
                      ISNULL(dbo.tbl_SplitJobs.PullDueDate, CONVERT(DATETIME, '1900-01-01 00:00:00', 102)) AS PullDueDate, 
                      ISNULL(CAST(dbo.tbl_SplitJobs.ReleaseNumber AS nvarchar(8)), CAST(EncoreCompanyK.dbo.WipMaster.Job AS nvarchar(8))) AS ReleaseNumber, 
                      dbo.view_MaxReleasePerJob.EXPR1 AS LastRelease, EncoreCompanyK.dbo.WipMaster.QtyManufactured AS QTY_MANUFACTURED, 
                      dbo.tbl_ReleaseStatus.CellAssignedTo, dbo.tbl_ReleaseStatus.FoamStatus, dbo.tbl_SplitJobs.Cell AS CellNumber, 
                      dbo.view_ECOSForPM.StockCode AS ECODStockCode, dbo.tbl_WOSA.EXPR1 AS WOSA, ISNULL(CONVERT(nvarchar(10), 
                      dbo.view_MaxEngineeringPass.PassedDate, 101), N'&nbsp;') AS PassedDate, dbo.view_MaxEngineeringPass.PassedDate AS EngineeringReleased, 
                      ISNULL(dbo.view_JobsWithFAI.EXPR1, '') AS FOM, ISNULL(dbo.tbl_ReleaseStatus.WC7PercentComplete, 0) AS WC7PercentComplete, 
                      ISNULL(dbo.tbl_ReleaseStatus.WC11PercentComplete, 0) AS WC11PercentComplete, ISNULL(dbo.tbl_ReleaseStatus.FoamLocation, 
                      N'<font color=red>N/S</font>') AS FoamLocation, dbo.tbl_AEROJobs.Salesperson, CONVERT(nvarchar(10), ISNULL(dbo.tbl_SplitJobs.Qty, 
                      EncoreCompanyK.dbo.WipMaster.QtyToMake)) + N' of  ' + CONVERT(nvarchar(10), CAST(EncoreCompanyK.dbo.WipMaster.QtyToMake AS integer)) 
                      AS NumberOfReleases, dbo.tbl_SplitJobs.Cell, EncoreCompanyK.dbo.WipMaster.Customer AS CustomerName, 
                      ISNULL(ISNULL(REPLACE(dbo.tbl_QC_Narrations.ASRequired, EncoreCompanyK.dbo.WipMaster.StockCode, 'QC'), dbo.tbl_QC_Narrations.EXPR1), '') 
                      AS QC, dbo.tbl_SplitJobs.QCed, dbo.tbl_SplitJobs.QCedBy, ISNULL
                          ((SELECT     DisplayQCed
                              FROM         view_JobIsSplit
                              WHERE     Job = tbl_SplitJobs.Job), 'none') AS DisplayQC, 'PL Printed, Click Here To Remove' + REPLACE(EncoreCompanyK.dbo.WipMaster.Job, 
                      PrintCenter.dbo.tblPackSlipHistory.Job, '') AS PL, dbo.tbl_ReleaseStatus.QCScheduleTagPrinted, dbo.tbl_ReleaseStatus.QCScheduleTagPrintedBy, 
                      ISNULL(CAST(RTRIM(CAST(MONTH(AIP.dbo.tbl_ToQC.ToQC) AS nvarchar(2))) + '/' + RTRIM(CAST(DAY(AIP.dbo.tbl_ToQC.ToQC) AS nvarchar(2))) 
                      + '/' + RTRIM(CAST(YEAR(AIP.dbo.tbl_ToQC.ToQC) AS nvarchar(4))) AS datetime), ISNULL(CAST(RTRIM(CAST(MONTH(dbo.tbl_ReleaseStatus.ToQC) 
                      AS nvarchar(2))) + '/' + RTRIM(CAST(DAY(dbo.tbl_ReleaseStatus.ToQC) AS nvarchar(2))) + '/' + RTRIM(CAST(YEAR(dbo.tbl_ReleaseStatus.ToQC) 
                      AS nvarchar(4))) AS datetime), EncoreCompanyK.dbo.WipMaster.JobDeliveryDate)) AS JobDeliveryDate, dbo.tbl_ReleaseStatus.WhoSentToQC, 
                      'This Job Is On Hold Because:<br><br>''''' + dbo.tbl_JobsOnHold.Reason + '''''<br>  By ' + dbo.tbl_JobsOnHold.PlacedBy + ' On ' + CONVERT(nvarchar(10),
                       dbo.tbl_JobsOnHold.PlacedOnHold, 101) AS Reason, CAST(ISNULL(dbo.view_NEWAEROQCExpRunTimesForSubJobsForSchedule.ExpRunTime, 
                      dbo.view_ExpRunTimeForSchedule.ExpRunTime) AS float) / EncoreCompanyK.dbo.WipMaster.QtyToMake * ISNULL(dbo.tbl_SplitJobs.Qty, 
                      EncoreCompanyK.dbo.WipMaster.QtyToMake) AS ExpRunTime, '' AS MaxReleaseNumber, AIP.dbo.tbl_ReleasedJobs.Job AS EXPR1, 
                      AIP.dbo.tbl_ReleasedJobs.DateReleased, dbo.tbl_SplitJobs.ReleaseNumber AS EXPR2, AIP.dbo.tbl_ToQC.ToQC, 
                      dbo.view_ExpRunTimeForSchedule.ExpRunTime AS EXPR3
FROM         dbo.view_JobsWithFAI RIGHT OUTER JOIN
                      PrintCenter.dbo.tbl_JobStatus RIGHT OUTER JOIN
                      EncoreCompanyK.dbo.WipMaster INNER JOIN
                      dbo.tbl_AEROJobs ON EncoreCompanyK.dbo.WipMaster.Job = dbo.tbl_AEROJobs.Job LEFT OUTER JOIN
                      AIP.dbo.tbl_ReleasedJobs ON EncoreCompanyK.dbo.WipMaster.Job = AIP.dbo.tbl_ReleasedJobs.Job LEFT OUTER JOIN
                      dbo.view_NEWAEROQCExpRunTimesForSubJobsForSchedule ON 
                      EncoreCompanyK.dbo.WipMaster.Job = dbo.view_NEWAEROQCExpRunTimesForSubJobsForSchedule.Job LEFT OUTER JOIN
                      dbo.view_ExpRunTimeForSchedule ON EncoreCompanyK.dbo.WipMaster.Job = dbo.view_ExpRunTimeForSchedule.Job LEFT OUTER JOIN
                      dbo.tbl_SplitJobs INNER JOIN
                      dbo.tbl_ReleaseStatus ON dbo.tbl_SplitJobs.ReleaseNumber = dbo.tbl_ReleaseStatus.ReleaseNumber ON 
                      EncoreCompanyK.dbo.WipMaster.Job = dbo.tbl_SplitJobs.Job LEFT OUTER JOIN
                      AIP.dbo.tbl_ToQC ON EncoreCompanyK.dbo.WipMaster.Job = AIP.dbo.tbl_ToQC.Job LEFT OUTER JOIN
                      PackslipData.dbo.tbl_PackSlipInfo ON EncoreCompanyK.dbo.WipMaster.Job = PackslipData.dbo.tbl_PackSlipInfo.Job LEFT OUTER JOIN
                      dbo.tbl_JobsOnHold ON EncoreCompanyK.dbo.WipMaster.Job = dbo.tbl_JobsOnHold.Job LEFT OUTER JOIN
                      PrintCenter.dbo.tblPackSlipHistory ON EncoreCompanyK.dbo.WipMaster.Job = PrintCenter.dbo.tblPackSlipHistory.Job LEFT OUTER JOIN
                      dbo.view_MaxReleasePerJob ON EncoreCompanyK.dbo.WipMaster.Job = dbo.view_MaxReleasePerJob.Job LEFT OUTER JOIN
                      dbo.tbl_QC_Narrations ON EncoreCompanyK.dbo.WipMaster.Job = dbo.tbl_QC_Narrations.Job ON 
                      PrintCenter.dbo.tbl_JobStatus.Job = EncoreCompanyK.dbo.WipMaster.Job LEFT OUTER JOIN
                      dbo.view_MaxEngineeringPass ON EncoreCompanyK.dbo.WipMaster.StockCode = dbo.view_MaxEngineeringPass.STOCK_CODE LEFT OUTER JOIN
                      dbo.view_ECOSForPM ON EncoreCompanyK.dbo.WipMaster.StockCode = dbo.view_ECOSForPM.StockCode ON 
                      dbo.view_JobsWithFAI.Job = EncoreCompanyK.dbo.WipMaster.Job LEFT OUTER JOIN
                      dbo.tbl_WOSA ON EncoreCompanyK.dbo.WipMaster.Job = dbo.tbl_WOSA.Job
WHERE     (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell = 13 OR
                      dbo.tbl_SplitJobs.Cell = 20 OR
                      dbo.tbl_SplitJobs.Cell = 1 OR
                      dbo.tbl_SplitJobs.Cell = 27 OR
                      dbo.tbl_SplitJobs.Cell IS NULL) AND (NOT (AIP.dbo.tbl_ReleasedJobs.DateReleased IS NULL)) OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell = 13 OR
                      dbo.tbl_SplitJobs.Cell = 20 OR
                      dbo.tbl_SplitJobs.Cell = 1 OR
                      dbo.tbl_SplitJobs.Cell = 27 OR
                      dbo.tbl_SplitJobs.Cell IS NULL) AND (NOT (AIP.dbo.tbl_ReleasedJobs.DateReleased IS NULL)) AND 
                      (ISNULL(dbo.tbl_ReleaseStatus.WC7PercentComplete, 0) = 1) OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell = 13 OR
                      dbo.tbl_SplitJobs.Cell = 20 OR
                      dbo.tbl_SplitJobs.Cell = 1 OR
                      dbo.tbl_SplitJobs.Cell = 27 OR
                      dbo.tbl_SplitJobs.Cell IS NULL) AND (NOT (AIP.dbo.tbl_ReleasedJobs.DateReleased IS NULL)) AND 
                      (ISNULL(dbo.tbl_ReleaseStatus.WC11PercentComplete, 0) = 1) OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (NOT (AIP.dbo.tbl_ReleasedJobs.DateReleased IS NULL)) AND 
                      (ISNULL(dbo.tbl_ReleaseStatus.WC7PercentComplete, 0) = 1) OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell = 1) OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell = 13 OR
                      dbo.tbl_SplitJobs.Cell = 20 OR
                      dbo.tbl_SplitJobs.Cell = 27 OR
                      dbo.tbl_SplitJobs.Cell IS NULL) AND (NOT (AIP.dbo.tbl_ReleasedJobs.DateReleased IS NULL)) OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell = 13 OR
                      dbo.tbl_SplitJobs.Cell = 20 OR
                      dbo.tbl_SplitJobs.Cell = 27 OR
                      dbo.tbl_SplitJobs.Cell IS NULL) AND (ISNULL(dbo.tbl_SplitJobs.PullDueDate, '1/1/1900') <> '1/1/1900') OR
                      (PackslipData.dbo.tbl_PackSlipInfo.Shipped IS NULL OR
                      PackslipData.dbo.tbl_PackSlipInfo.Shipped <> 1) AND (EncoreCompanyK.dbo.WipMaster.Complete = 'N') AND 
                      (PrintCenter.dbo.tbl_JobStatus.PassedQC IS NULL) AND (dbo.tbl_SplitJobs.Cell IS NULL) AND 
                      (NOT (dbo.view_ExpRunTimeForSchedule.ExpRunTime IS NULL))

What I am trying to figure out is how to speed up the filtering on that last view.

Thanks,
Willie
 
Upvote 0 Downvote
This is really ugly and doomed to have poor performance. Looks like this last view was some bastardized thing created in Access many years ago and then ported to SQL Server.

The problem is that there are too many outer joins, which is bad for performance. There's too many OR conditions in your where clause which is bad for performance. There are NOT conditions, and function calls (ISNULL). All of this leads to poor performance.

The problem is... there's probably logic in there that you need and probably don't even know you need it.

Looking at your original post... no where clause = 6 seconds and 1700 rows. With a where clause, 45 seconds and 15 rows. Normally, where clauses speed up a query because you end up dealing with less data. In your case, it's slowing down the query. This probably occurs because of the many layers of nested views. Your best bet would be to ignore the views and go straight to the base tables. Unfortunately, this may be more difficult than you think because of all the logic involved in the various views.

When you run a query that involves nested views, SQL Server will attempt to optimize things to speed up the query. If the views are too deep, the optimizations may actually slow things down. One method you could try is to circumvent the query optimizer's attempt at improving performance. To do this, you can insert the data in to a temp table and then filter on that. Something like this:

Code: 
SELECT * 
INTO   #TempData
FROM   view_NEWAEROQCCellInterfacesFASTER

Select *
From   #TempData
Where upper([STOCK_CODE]) = '787 LANTAL MAIN CARPET'

In this case, all 1700 rows are added to the temp table and then you select only the rows you want. This may not work in your case (or it might). You see, you cannot create a table that is too wide. If this happens to you, then you could try create a temp table that only contains the columns you want and then do an insert into from the view.

For what it's worth, I wish you luck.

PS: Instead of using the UPPER function to make the filter case insensitive, it's usually better to use a case insensitive collation within the query. I'll post an example in a couple minutes.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks, George. This (and many, many, many other) views are most likely ported over from Access, built by folks who really don't know anything other than dragging and dropping tables and columns in Access. I know my end goal is to re-write this view and all of the supporting views, but to do that I have to find somebody that actually understands what they need... Thanks for your help, I may try the temp table approach if I can't get the view(s) re-written sometime soon...
 
Upvote 0 Downvote
Fixing the views may help a little, but there may still be problems. Grant Fitchey explains it better than me. Take a look at this:
Scroll down to: Indulging in Nested Views



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Thanks, I will take a look!

wb
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Locked
  • Question Question
Parameterised sql server query
Replies
8
Views
959
Chriss Miller
C
elly00
  • Locked
  • Question Question
create sql view using batch file
Replies
5
Views
1K
Mike Lewis
Mike Lewis
leifoet
  • Locked
  • Question Question
Error in Select query
Replies
14
Views
1K
Andrzejek
Andrzejek
WomanPro
  • Locked
  • Question Question
Insert into query adds records to unreadable format, how do I change that?
Replies
5
Views
823
Chris Miller
Chris Miller
ahmedsa2018
  • Locked
  • Question Question
How to enhance query to run faster ?
Replies
0
Views
305
ahmedsa2018
ahmedsa2018

Part and Inventory Search

Sponsor

Back
Top