Viewing 15 posts - 1,111 through 1,125 (of 2,646 total)
Scott's answer looks correct, the UPDATE is potentially updating the FromDate with the FromDate of a row it has previously just updated (which also could have been updated with...
February 21, 2020 at 11:53 pm
Normally data feeds have a specification that will specify the maximum length of a column and whether or not it's mandatory etc...
Make a column not null if it's mandatory.
February 21, 2020 at 4:07 pm
My guess is that there is another process reading the table with a lock on it and running all the SQL commands contained in the SQL_DETAIL column. Your UPDATE statement...
February 21, 2020 at 11:56 am
I ran your original view on the backup you've just provided and it returned all rows (626387) in 1.4 seconds.
select * into #a from dbo.RECIPE
February 20, 2020 at 1:13 am
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...
February 19, 2020 at 1:30 pm
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.
Well, there's...
February 19, 2020 at 1:05 pm
I also think they are logically different queries and produce different results. I ran the original query and it returned over 400k rows (in over 20 minutes). The rCTE version...
February 18, 2020 at 11:38 am
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
...
February 17, 2020 at 10: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...
February 17, 2020 at 5:45 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...
February 17, 2020 at 2:52 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...
February 17, 2020 at 2:44 pm
Another option would be to create a view or user-defined function or table-valued function. I'm not sure if this would be any more efficient without you testing.
User defined function:
February 17, 2020 at 2:04 pm
No, this won't work. As I said before, you can't have aggregate functions in the recursive part of a recursive CTE.
Ok, I see and agree.
So did your initial query...
February 17, 2020 at 1:41 pm
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 ...
February 17, 2020 at 12:16 pm
I don't understand why you are getting an overflow error when selecting from the table but not from the CTE?
Surely they add up to the same value wherever you do...
February 17, 2020 at 11:41 am
Viewing 15 posts - 1,111 through 1,125 (of 2,646 total)