New answers tagged sorting
Have you looked at the execution plan? It seems to me that this kind of query will have to pull back the entire table from Oracle over the linked server before doing the merge locally. How big is that table? This would probably be the "slow" part. Modify the source part of the merge to only bring back new or modified records; then do another statement to ...
Here is another approach, which, however, is likely to perform worse than the other suggestion: UPDATE Beleg SET SortID = ( SELECT COUNT(*) FROM Beleg AS Beleg2 WHERE Year ( Beleg2.Valuta ) = Year ( Beleg.Valuta ) AND Beleg2.Ursprungskonto = Beleg.Ursprungskonto AND Beleg2.SortID <= Beleg.SortID ) WHERE ...
If you're able to create a table, and update allows you to use the result of a subselect (all valid SQL92 code), you should be able do perform the following multi-step process: CREATE TABLE sorting (sid numeric(10,10), rn int); INSERT INTO sorting (sid, rn) SELECT SortID, RecordNumber FROM Beleg WHERE Year ( Valuta ) = 2016 AND Ursprungskonto = 1210 ORDER ...
If you are dealing with numbers you can also use ORDER BY -test DESC NULL are the lowest possible values, therefore DESC puts them at the end. Meanwhile the not null values have the sign inverted so DESC actually is an ASC on the real values. This should be faster than CASE and I suppose that query optimizer can also use indexes on test column.
Top 50 recent answers are included