Viewing 15 posts - 1 through 15 (of 105 total)
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 ONSET STATISTICS IO ONSELECT *FROM dbo.vRecipeSELECT *FROM dbo.vRecipe3 -- with indexed viewSELECT * 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
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:
And here for all records:
Problem is in the amount of logical reads for DBDOPSK and DBRVARE.
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
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...
February 17, 2020 at 2:08 pm