July 12, 2021 at 11:21 am
I am using a large Manufacturing Database and would like to create a number of Indexed Views in another Database that reference my manufacturing Database.
I use a common table expression to produce a list of bill of materials but this slows down due to lack of an index - can this be done?
Roberto
July 12, 2021 at 2:46 pm
Indexed views can't reference other databases - Tables must be referenced by two-part names, schema.tablename in the view definition.
Indexed views cannot contain common table expressions.
https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/
A couple of possible alternatives:
What is the volume of data involved? How often is this data queried?
July 12, 2021 at 2:52 pm
The first view holds circa 1500 records and returns circa 2200 through a second cte view.
The data is consistently refreshed throughout the working day - eg. every 5 minutes and published through Crystal Reports.
July 12, 2021 at 4:48 pm
I use a common table expression to produce a list of bill of materials but this slows down due to lack of an index - can this be done?
You should post that code and the DDL for any related tables and the related indexes on those tables.
As for you indexed view problem... create it on the database where the data is and create a synonym to it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 12, 2021 at 7:06 pm
The first view holds circa 1500 records and returns circa 2200 through a second cte view.
The data is consistently refreshed throughout the working day - eg. every 5 minutes and published through Crystal Reports.
How often is up to date information actually needed for the reports? If this is something that really needs to be up to date every time it runs and it is run often throughout the day - then you would be better off adding the missing index(es) to the primary tables and optimizing the code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2021 at 7:45 pm
I have created a Scalar-valued Function and called this from within my view - basically the join that I perform on the non-index fields I have removed from my Sql View code and added into the function - the execute time has reduced from 60 seconds to 2.
I am not sure how or why I get the performance bump.
July 12, 2021 at 8:28 pm
I have created a Scalar-valued Function and called this from within my view - basically the join that I perform on the non-index fields I have removed from my Sql View code and added into the function - the execute time has reduced from 60 seconds to 2.
I am not sure how or why I get the performance bump.
If you are asking for help in determine how or why you got this performance bump - then you need to provide the code, both the before and after versions - including the scalar function. It is possible we can improve that code even further - possibly changing the scalar function to a inline-table valued function, but without the code we really can't say.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2021 at 8:37 pm
WITH SubQuery AS (SELECT TOP (100) PERCENT jh.Company, jh.JobNum AS ParentJob, jh.JobFirm, jh.JobReleased, ja.AssemblySeq, jh.PartNum AS ParentPart, jm.MtlSeq, jm.PartNum AS ComponentPart,
pp.SourceType, CAST(jm.QtyPer AS int) AS QtyPer, CAST(jm.RequiredQty AS int) AS RequiredQty, CAST(jm.IssuedQty AS int) AS IssuedQty, jm.FixedQty, CAST(pq.OnHandQty AS int)
AS OnHandQty, CAST((CASE WHEN jm.RequiredQty - jm.IssuedQty <= pq.OnHandQty THEN 0 ELSE (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty) END) AS int) AS Shortage,
jm.RelatedOperation, jo.OpCode, jhc.JobNum AS ComponentJob, jhc.DueDate, jm.ReqDate, CAST(jhc.ProdQty AS int) AS ProdQty, CAST(jhc.QtyCompleted AS int) AS QtyCompleted
FROM [ERP10-Live].dbo.JobHead AS jh WITH (NoLock) INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja WITH (NoLock) ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm WITH (NoLock) ON ja.Company = jm.Company AND ja.JobNum = jm.JobNum AND ja.AssemblySeq = jm.AssemblySeq LEFT OUTER JOIN
[ERP10-Live].Erp.JobOper AS jo WITH (NoLock) ON jm.Company = jo.Company AND jm.JobNum = jo.JobNum AND jm.AssemblySeq = jo.AssemblySeq AND
jm.RelatedOperation = jo.OprSeq LEFT OUTER JOIN
[ERP10-Live].Erp.PartPlant AS pp WITH (NoLock) ON jm.Company = pp.Company AND jm.PartNum = pp.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq WITH (NoLock) ON jm.Company = pq.Company AND jm.PartNum = pq.PartNum LEFT OUTER JOIN
[ERP10-Live].dbo.JobHead AS jhc WITH (NoLock) ON jm.Company = jhc.Company AND jm.PartNum = jhc.PartNum AND jhc.JobNum =
(SELECT TOP (1) JobNum
FROM [ERP10-Live].dbo.JobHead AS jhc1
WHERE (Company = jhc.Company) AND (PartNum = jm.PartNum) AND (JobComplete = 0) AND (ProdQty - QtyCompleted > 0)
ORDER BY DueDate, JobNum)
WHERE (jh.JobComplete = 0) AND (jh.DueDate <= GETDATE() + 35) AND (jh.ProdQty - jh.QtyCompleted > 0) AND (jm.IssuedComplete = 0)
ORDER BY jh.Company, ParentJob, ja.AssemblySeq, jm.MtlSeq, ComponentPart, jm.ReqDate)
SELECT Company, ParentJob, JobFirm, JobReleased, AssemblySeq, ParentPart, MtlSeq, ComponentPart, SourceType, QtyPer, RequiredQty, IssuedQty, FixedQty, OnHandQty, Shortage, RelatedOperation, OpCode,
ComponentJob, DueDate, ReqDate, ProdQty, QtyCompleted
FROM SubQuery AS SubQuery_1
WHERE (Shortage > 0) AND (SourceType = 'M')
WITH SubQuery AS (SELECT TOP (100) PERCENT jh.Company, jh.JobNum AS ParentJob, jh.JobFirm, jh.JobReleased, ja.AssemblySeq, jh.PartNum AS ParentPart, jm.MtlSeq, jm.PartNum AS ComponentPart,
pp.SourceType, CAST(jm.QtyPer AS int) AS QtyPer, CAST(jm.RequiredQty AS int) AS RequiredQty, CAST(jm.IssuedQty AS int) AS IssuedQty, jm.FixedQty, CAST(pq.OnHandQty AS int)
AS OnHandQty, CAST((CASE WHEN jm.RequiredQty - jm.IssuedQty <= pq.OnHandQty THEN 0 ELSE (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty) END) AS int) AS Shortage,
jm.RelatedOperation, jo.OpCode, jhc.JobNum AS ComponentJob, jhc.DueDate, jm.ReqDate, CAST(jhc.ProdQty AS int) AS ProdQty, CAST(jhc.QtyCompleted AS int) AS QtyCompleted
FROM [ERP10-Live].dbo.JobHead AS jh WITH (NoLock) INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja WITH (NoLock) ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm WITH (NoLock) ON ja.Company = jm.Company AND ja.JobNum = jm.JobNum AND ja.AssemblySeq = jm.AssemblySeq LEFT OUTER JOIN
[ERP10-Live].Erp.JobOper AS jo WITH (NoLock) ON jm.Company = jo.Company AND jm.JobNum = jo.JobNum AND jm.AssemblySeq = jo.AssemblySeq AND
jm.RelatedOperation = jo.OprSeq LEFT OUTER JOIN
[ERP10-Live].Erp.PartPlant AS pp WITH (NoLock) ON jm.Company = pp.Company AND jm.PartNum = pp.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq WITH (NoLock) ON jm.Company = pq.Company AND jm.PartNum = pq.PartNum LEFT OUTER JOIN
[ERP10-Live].dbo.JobHead AS jhc WITH (NoLock) ON jm.Company = jhc.Company AND jm.PartNum = jhc.PartNum AND jhc.JobNum =
(SELECT dbo.ChildJob(jm.Company, jm.PartNum) AS Expr1)
WHERE (jh.JobComplete = 0) AND (jh.DueDate <= GETDATE() + 35) AND (jh.ProdQty - jh.QtyCompleted > 0) AND (jm.IssuedComplete = 0)
ORDER BY jh.Company, ParentJob, ja.AssemblySeq, jm.MtlSeq, ComponentPart, jm.ReqDate)
SELECT Company, ParentJob, JobFirm, JobReleased, AssemblySeq, ParentPart, MtlSeq, ComponentPart, SourceType, QtyPer, RequiredQty, IssuedQty, FixedQty, OnHandQty, Shortage, RelatedOperation, OpCode,
ComponentJob, DueDate, ReqDate, ProdQty, QtyCompleted
FROM SubQuery AS SubQuery_1
WHERE (Shortage > 0) AND (SourceType = 'M')
ALTER FUNCTION [dbo].[ChildJob]
(
-- Add the parameters for the function here
@Company AS nvarchar(8),
@PartNum AS nvarchar(50)
)
RETURNS nvarchar(14)
AS
BEGIN
-- Declare the return variable here
DECLARE @JobNum nvarchar(14)
-- Add the T-SQL statements to compute the return value here
SELECT @JobNum = MIN(JobNum)
FROM [ERP10-Live].dbo.JobHead AS jh WITH (NoLock)
WHERE (JobClosed = 0) AND (JobComplete = 0) AND (ProdQty - QtyCompleted > 0) AND (Company = @Company) AND (PartNum = @PartNum)
GROUP BY Company, PartNum
-- Return the result of the function
RETURN @JobNum
END
July 12, 2021 at 8:40 pm
The first SQL snippet is the original sql.
The second SQL snippet is the new sql.
The third snippet is the Function.
The JobHead table has an Index of Company, JobNum but I join on PartNum.
July 12, 2021 at 9:13 pm
If I am reading this correctly - you should be able to do this without needing a function. You can replace this code:
Left Outer Join [ERP10-Live].dbo.JobHead As jhc With (NoLock) On jm.Company = jhc.Company
And jm.PartNum = jhc.PartNum
And jhc.JobNum =
(
Select dbo.ChildJob(jm.Company, jm.PartNum) As Expr1
)
With an OUTER APPLY like this:
Outer Apply (Select Top 1
*
From [ERP10-Live].dbo.JobHead jh2
Where jh2.Company = jm.Company
And jh2.PartNum = jm.PartNum
And jh2.JobClosed = 0
And jh2.JobComplete = 0
And jh2.ProdQty - jh2.QtyCompleted > 0
Order By
jh2.JobNum
) As jhc
You also need to remove the TOP 100 PERCENT - and the ORDER BY statements. They are useless - the order by is only included (and can only be included) because of the TOP statement and the results are still not guaranteed to be in that order.
I would highly recommend that you stop using the GUI to design and build your views. That tool has quite a few issues and leads to some invalid code (see TOP/ORDER BY) and also can prevent you from using perfectly valid T-SQL because it doesn't understand that syntax.
It also has a tendency to 'reformat' the code into something that is incredibly difficult to read.
Final note: are you aware of the issues with NOLOCK and how that can return invalid results?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2021 at 9:51 pm
I tend to write my code outwith the gui then when I save it as a view SQL reformats and adds the TOP(100) etc.
July 12, 2021 at 10:10 pm
I also, for readability, tend to create multiple smaller SQL Views then reuse them to build larger more complex views.
I always use NoLock as I was getting a number of crashes when I didn't use them.
July 12, 2021 at 10:28 pm
I tend to write my code outwith the gui then when I save it as a view SQL reformats and adds the TOP(100) etc.
It only reformats it if you open it using the GUI - you can right-click and script as alter to a new query window and the original formatting will be maintained.
I also, for readability, tend to create multiple smaller SQL Views then reuse them to build larger more complex views.
I always use NoLock as I was getting a number of crashes when I didn't use them.
This is a bad idea - views of views tend to cause a lot of issues. Especially if the views use the same base tables - and try to join back to other views.
Using NOLOCK does not fix crash issues...it can cause rows to be read twice, rows to be skipped - or even failure due to data movement.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 12, 2021 at 11:03 pm
Instead of using multiple nested views - what technique should I adopt to build my more complex views?
July 13, 2021 at 8:27 am
I have removed all the 'reformatting' from the Views and removed the NoLock and added the Outer Apply.
SELECT jh.Company, jh.JobNum AS ParentJob, jh.JobFirm, jh.JobReleased, ja.AssemblySeq, jh.PartNum AS ParentPart, jm.MtlSeq, jm.PartNum AS ComponentPart,
pp.SourceType, CAST(jm.QtyPer AS int) AS QtyPer, CAST(jm.RequiredQty AS int) AS RequiredQty, CAST(jm.IssuedQty AS int) AS IssuedQty, jm.FixedQty, CAST(pq.OnHandQty AS int)
AS OnHandQty, CAST((CASE WHEN jm.RequiredQty - jm.IssuedQty <= pq.OnHandQty THEN 0 ELSE (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty) END) AS int) AS Shortage,
jm.RelatedOperation, jo.OpCode, jhc.JobNum AS ComponentJob, jhc.DueDate, jm.ReqDate, CAST(jhc.ProdQty AS int) AS ProdQty, CAST(jhc.QtyCompleted AS int) AS QtyCompleted
FROM [ERP10-Live].dbo.JobHead AS jh INNER JOIN
[ERP10-Live].Erp.JobAsmbl AS ja ON jh.Company = ja.Company AND jh.JobNum = ja.JobNum INNER JOIN
[ERP10-Live].Erp.JobMtl AS jm ON ja.Company = jm.Company AND ja.JobNum = jm.JobNum AND ja.AssemblySeq = jm.AssemblySeq LEFT OUTER JOIN
[ERP10-Live].Erp.JobOper AS jo ON jm.Company = jo.Company AND jm.JobNum = jo.JobNum AND jm.AssemblySeq = jo.AssemblySeq AND
jm.RelatedOperation = jo.OprSeq LEFT OUTER JOIN
[ERP10-Live].Erp.PartPlant AS pp ON jm.Company = pp.Company AND jm.PartNum = pp.PartNum LEFT OUTER JOIN
[ERP10-Live].Erp.PartQty AS pq ON jm.Company = pq.Company AND jm.PartNum = pq.PartNum OUTER APPLY (Select Top 1
*
From [ERP10-Live].dbo.JobHead jh2
Where jh2.Company = jm.Company
And jh2.PartNum = jm.PartNum
And jh2.JobClosed = 0
And jh2.JobComplete = 0
And jh2.ProdQty - jh2.QtyCompleted > 0
Order By
jh2.DueDate
) As jhc
WHERE (jh.JobComplete = 0) AND (jh.DueDate <= GETDATE() + 35) AND (jh.ProdQty - jh.QtyCompleted > 0) AND (jm.IssuedComplete = 0)
AND (jm.RequiredQty - jm.IssuedQty - pq.OnHandQty > 0) AND (pp.SourceType = 'M')
The code now executes in 00:00:00 from 00:00:13.
I'm biggest performance gain appears to come from me removing the WITh SubQuery AS element.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply