Viewing 15 posts - 1 through 15 (of 106 total)
Position could be an option. However, it's not enough. I've renamed a column and drop another, in a higher position of the renamed and SQL Compare detected the drop and...
February 20, 2024 at 7:20 am
Ok, I will leave the original view then. I have to admit that even when I don't like it, performance is better 🙂
Thanks for all the advices and time, guys!
February 20, 2020 at 6:50 am
You can try this link. Both views (vRecipe and Recipe) have 626387 rows: https://www.dropbox.com/s/pgdjg9pel9z2fbc/TEST_DB.zip?dl=0
February 19, 2020 at 6:22 pm
I will answer Frederico and Lynn. First, I've checked that output is the same, at least in my test database (not the one that I've uploaded, which has only a...
February 19, 2020 at 4:58 pm
So, from your point of view, in this case it's better to use UNION ALL even when some levels are not used than to use a recursive CTE?. I know...
February 19, 2020 at 1:15 pm
Yes, but the amount of logical reads is too high and if you compare with the UNION ALL version, you can see the difference.
February 19, 2020 at 11:50 am
No, I think that's because I had some problems executing the script for exporting data (it was too huge). In my databases both views produce the same result.
February 19, 2020 at 11:49 am
Hi Jonathan, still a lot of logical reads.
I've tried this:
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT *
FROM dbo.vRecipe
SELECT *
FROM dbo.vRecipe3 -- with indexed view
SELECT *
FROM dbo.Recipe...
February 18, 2020 at 6:29 am
I tried that but, because of RECIPE.ANTL * DOP.ANT I was getting an arithmetic overflow.
February 18, 2020 at 6:23 am
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:08 pm
I had already tried your suggestion, no big differences in fact. For DBRVARE I think it's using a covered index, not sure if I need a new one.
February 17, 2020 at 5:53 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,...
February 17, 2020 at 5:41 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....
February 17, 2020 at 5:38 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...
February 17, 2020 at 5:09 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...
February 17, 2020 at 2:47 pm
Viewing 15 posts - 1 through 15 (of 106 total)