February 17, 2020 at 2:08 pm
I will try them and let you know. I like more the TVF than the function in this case. Right now, view is working fine but I'm facing a new problem. This view is used inside another stored procedure and now performance is worst than before for that stp. I need to investigate why.
February 17, 2020 at 2:44 pm
I will try them and let you know. I like more the TVF than the function in this case. Right now, view is working fine but I'm facing a new problem. This view is used inside another stored procedure and now performance is worst than before for that stp. I need to investigate why.
I think you will get better performance if you create an indexed view with a clustered index to pre-aggregate the value:
CREATE VIEW [dbo].[vwDBDOPSKSumant]
WITH SCHEMABINDING AS
SELECT DOP.NR,
SUM(DOP.ANT) SUMANT,
COUNT_BIG(*) as z
FROM dbo.DBDOPSK DOP
GROUP BY DOP.NR
GO
CREATE UNIQUE CLUSTERED INDEX IX_vwDBDOPSKSumant ON [vwDBDOPSKSumant](NR)
GO
February 17, 2020 at 2:47 pm
That's an interesting alternative. Right now, as I said before, CTE view performs better if you filter for one of the first 2 columns. However, if you just SELECT all the records, performance is worst.
February 17, 2020 at 2:52 pm
That's an interesting alternative. Right now, as I said before, CTE view performs better if you filter for one of the first 2 columns. However, if you just SELECT all the records, performance is worst.
The next step is probably to look if any indexes will help with specific user-cases for the query.
February 17, 2020 at 4:50 pm
That's an interesting alternative. Right now, as I said before, CTE view performs better if you filter for one of the first 2 columns. However, if you just SELECT all the records, performance is worst.
can you post explain plans for the case where you select all records - that may lead us to suggest something else or different indexes.
February 17, 2020 at 5:09 pm
Sure. Here are the plans for a given record:
https://www.brentozar.com/pastetheplan/?id=BJIydB_XL
And here for all records:
https://www.brentozar.com/pastetheplan/?id=ryJY_SOmU
Problem is in the amount of logical reads for DBDOPSK and DBRVARE.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(626387 rows affected)
Table 'DBDOPSK'. Scan count 1138331, logical reads 2290520, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBRVARE'. Scan count 512506, logical reads 1040317, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 4, logical reads 5305402, physical reads 333, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBOPSK'. Scan count 1, logical reads 447, physical reads 1, read-ahead reads 450, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBVARE'. Scan count 1, logical reads 43, physical reads 1, read-ahead reads 48, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 21172 ms, elapsed time = 24576 ms.
(626387 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 104, logical reads 3508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBDOPSK'. Scan count 72, logical reads 16472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBRVARE'. Scan count 42, logical reads 3498, physical reads 1, read-ahead reads 64, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBOPSK'. Scan count 6, logical reads 2682, physical reads 1, read-ahead reads 450, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 3610 ms, elapsed time = 14075 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
February 17, 2020 at 5:32 pm
May I be honest? Comparing the two plans, the original view is better that the your rewrite. Your rewrite, looking at the plan in SentryOne Plan Explorer timed out trying to generate a execution plan leaving it with what you see. Just a visual comparison shows that your rewrite is doing a lot more work than the original.
I have a few things that I need to do today, but I will try to look at your original code that posted earlier.
February 17, 2020 at 5:38 pm
Hi Lynn, I appreciate your comment, however I don't agree with it :). I really believe that the one with many UNION ALL is more complicated but probably I'm wrong. I insist, when you query a given value, recursive CTE is faster than the other. Not the same when you query the whole view. If you can find something wrong, I will appreciate it.
Thanks.
February 17, 2020 at 5:41 pm
These are the statistics when query just one value.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(12 rows affected)
Table 'DBDOPSK'. Scan count 20, logical reads 40, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBRVARE'. Scan count 12, logical reads 24, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 69, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBOPSK'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBVARE'. Scan count 0, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 425 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(12 rows affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBRVARE'. Scan count 15, logical reads 772, physical reads 3, read-ahead reads 183, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBDOPSK'. Scan count 66, logical reads 831, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBOPSK'. Scan count 6, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 1888 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
New view is the first one. As you can see there, it has less logical reads and CPU time is smaller.
February 17, 2020 at 5:45 pm
I think the indexed view I suggested on dbo.DBDOPSK will reduce the reads on that that table.
For dbo.DBRVARE, I would try adding an index on column (NR) and INCLUDE (all other columns on that table used in the query).
February 17, 2020 at 8:03 pm
For the OP. I spent some time looking at the execution plans and the code you posted. I have come to the conclusion that there just isn't enough here for me to help. I am visual problem solver and we are missing two critical parts that are needed here. The inputs to the code and the outputs.
What that means is I would need the DDL for the tables involved in the query. This does not mean the entire schema for all the tables, just the columns that make up the primary key and the other indexes that are used by the original query and the columns whose data is being used. The indexes on the tables as well.
Also need sample data to populate those tables. They key word there is SAMPLE. This is not production data but data that you make up that represents the problem domain. We would also need enough data to test all the paths that the data may follow in the original code.
With the original code and the information above we can generate the expected results based on the sample data. With this information we would more than likely be able to assist you better in improving the code.
Our problem is that we don't know your data or what you are trying to achieve. Anything we give you at this point are just shots in the dark by someone wearing a blindfold. Sure, we may hit the mark but how many shots will it take?
February 17, 2020 at 10:08 pm
Hi,
I've created a small database with data (enough data for test). The link for downloading is this one: https://www.dropbox.com/s/lf20z838x1j9g4o/TEST_DB.zip?dl=0
It's a zip file, about 3,8 MB. Database is SQL 2017.
February 17, 2020 at 10:45 pm
I added these indexes:
CREATE NONCLUSTERED INDEX [IX_DBRVARE_NR_INC_NAVN_TYPE]
ON [dbo].[DBRVARE] ([NR])
INCLUDE ([NAVN],[TYPE])
CREATE VIEW [dbo].[vwDBDOPSKSumant]
WITH SCHEMABINDING AS
SELECT DOP.NR,
SUM(ISNULL(DOP.ANT,0)) SUMANT
,COUNT_BIG(*) as z
FROM dbo.DBDOPSK DOP
WHERE DOP.NR IS NOT NULL
GROUP BY DOP.NR
GO
CREATE UNIQUE CLUSTERED INDEX IX_vwDBDOPSKSumant ON [vwDBDOPSKSumant](NR)
Then changed the view to this:
CREATE VIEW [dbo].[vRecipe] AS
WITH RECIPE_DBVARE (OPNR, DOPNR, ANTL, ANT, ANTS, SVIND, RVNR, RVNAVN, TYPE, NIVEAU, PARENT, LINE, PLINE)
AS
(
SELECT VR.NR [OPNR],
0 [DOPNR],
CAST(OPS.ANTL AS DECIMAL(28,6)) [ANTL],
CAST(OPS.ANTN AS DECIMAL(28,6)) [ANT],
CAST(OPS.ANTS AS DECIMAL(28,6)) [ANTS],
OPS.SVIND [SVIND],
RV.NR [RVNR],
RV.NAVN [RVNAVN],
RV.TYPE [TYPE],
CAST(1 AS INT) [NIVEAU],
0 [PARENT],
OPS.LINE [LINE],
CAST(0 AS SMALLINT) [PLINE]
FROM dbo.DBVARE VR
INNER JOIN dbo.DBOPSK OPS ON OPS.VARENR = VR.NR
INNER JOIN dbo.DBRVARE RV ON RV.NR = OPS.RVARENR
WHERE 1 = 1
UNION ALL
SELECT RECIPE.OPNR,
0,
CAST(RECIPE.ANTL * DOP.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANTL],
CAST(RECIPE.ANT * DOP.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
CAST(RECIPE.ANTS * DOP.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANTS],
DOP.SVIND,
RV.NR [RVNR],
RV.NAVN [RVNAVN],
RV.TYPE [TYPE],
RECIPE.NIVEAU +1 [NIVEAU],
RECIPE.RVNR [PARENT],
DOP.LINE [LINE],
DOP.LINE [PLINE]
FROM RECIPE_DBVARE RECIPE
INNER JOIN dbo.DBDOPSK DOP ON DOP.NR = RECIPE.RVNR
INNER JOIN dbo.DBRVARE RV ON RV.NR = DOP.RVARENR
--OUTER APPLY (SELECT SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.RVNR ) OA
OUTER APPLY (SELECT SUMANT FROM [dbo].[vwDBDOPSKSumant] OA WHERE OA.NR = RECIPE.RVNR) OA
WHERE RECIPE.NIVEAU <= 5
--AND (OA.RN IS NULL OR OA.RN = 1)
),
RECIPE_DBRVARE (OPNR, DOPNR, ANTL, ANT, ANTS, SVIND, RVNR, RVNAVN, TYPE, NIVEAU, PARENT, LINE, PLINE) AS
(
SELECT 0 [OPNR],
OPS.NR [DOPNR],
0 [ANTL],
CAST((OPS.ANT) / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
0 [ANTS],
OPS.SVIND [SVIND],
RV.NR [RVNR],
RV.NAVN [RVNAVN],
RV.TYPE [TYPE],
CAST(1 AS SMALLINT) [NIVEAU],
CAST(0 AS INT) [PARENT],
OPS.LINE [LINE],
CAST(0 AS SMALLINT) [PLINE]
FROM dbo.DBDOPSK OPS
INNER JOIN dbo.DBRVARE RV ON RV.NR = OPS.RVARENR
--OUTER APPLY (select SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = OPS.NR ) OA
OUTER APPLY (SELECT SUMANT FROM [dbo].[vwDBDOPSKSumant] OA WHERE OA.NR = OPS.NR) OA
WHERE 1 = 1
--AND (OA.RN IS NULL OR OA.RN = 1)
UNION ALL
SELECT 0,
RECIPE.DOPNR,
0 [ANTL],
CAST(RECIPE.ANT / ISNULL(OA.SUMANT,1) AS DECIMAL(28,6)) [ANT],
0 [ANTS],
DOP.SVIND,
RV.NR [RVNR],
RV.NAVN [RVNAVN],
RV.TYPE [TYPE],
CAST(RECIPE.NIVEAU +1 AS SMALLINT) [NIVEAU],
RECIPE.RVNR [PARENT],
DOP.LINE [LINE],
RECIPE.LINE [PLINE]
FROM RECIPE_DBRVARE RECIPE
INNER JOIN dbo.DBDOPSK DOP ON DOP.NR = RECIPE.RVNR
INNER JOIN dbo.DBRVARE RV ON RV.NR = DOP.RVARENR
--OUTER APPLY (select SUM(ANT) OVER(PARTITION BY NR) SUMANT, ROW_NUMBER() OVER(ORDER BY NR) RN FROM dbo.DBDOPSK DOP WHERE DOP.NR = RECIPE.DOPNR ) OA
OUTER APPLY (SELECT SUMANT FROM [dbo].[vwDBDOPSKSumant] OA WHERE OA.NR = RECIPE.DOPNR) OA
WHERE RECIPE.NIVEAU <= 5
--AND (OA.RN IS NULL OR OA.RN = 1)
)
SELECT RECIPE_DBVARE.OPNR ,
RECIPE_DBVARE.DOPNR ,
CAST(RECIPE_DBVARE.ANTL AS DECIMAL(15,6)) ANTL,
CAST(RECIPE_DBVARE.ANT AS DECIMAL(15,6)) ANT,
CAST(RECIPE_DBVARE.ANTS AS DECIMAL(15,6)) ANTS,
RECIPE_DBVARE.SVIND ,
RECIPE_DBVARE.RVNR ,
RECIPE_DBVARE.RVNAVN ,
RECIPE_DBVARE.TYPE ,
RECIPE_DBVARE.NIVEAU ,
RECIPE_DBVARE.PARENT ,
RECIPE_DBVARE.LINE ,
RECIPE_DBVARE.PLINE
FROM RECIPE_DBVARE
UNION ALL
SELECT RECIPE_DBRVARE.OPNR ,
RECIPE_DBRVARE.DOPNR ,
RECIPE_DBRVARE.ANTL ,
RECIPE_DBRVARE.ANT ,
RECIPE_DBRVARE.ANTS ,
RECIPE_DBRVARE.SVIND ,
RECIPE_DBRVARE.RVNR ,
RECIPE_DBRVARE.RVNAVN ,
RECIPE_DBRVARE.TYPE ,
RECIPE_DBRVARE.NIVEAU ,
RECIPE_DBRVARE.PARENT ,
RECIPE_DBRVARE.LINE ,
RECIPE_DBRVARE.PLINE
FROM RECIPE_DBRVARE
And then this select takes just over 1 second to complete
select *
into #temp
from [dbo].[vRecipe]
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 78845, logical reads 832937, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBOPSK'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBVARE'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'vwDBDOPSKSumant'. Scan count 1, logical reads 64634, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBRVARE'. Scan count 36331, logical reads 72895, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DBDOPSK'. Scan count 2, logical reads 798, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(78877 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 1251 ms, elapsed time = 1195 ms.
February 18, 2020 at 12:01 am
looking at the view i wonder if we could do the join to get the sumamt at the very end on the last 2 unions - would need to ensure that 0 [PARENT], is null or that there isn't a NR - 0 on the aggregate view (which may not be needed at all on this case.
Viewing 15 posts - 31 through 45 (of 60 total)
You must be logged in to reply to this topic. Login to reply