rom-sql | SQL support for rom-rb | SQL Database library

As you mentioned in the comments, you are using an SQL Server login as a proxy account. While in the Microsoft documentation they mentioned that:

SQL Server Agent proxies use credentials to store information about Windows user accounts. The user-specified in the credential must have "Access this computer from the network" permission (SeNetworkLogonRight) on the computer on which SQL Server is running.

I suggest following one of the following articles to set up an SQL Server agent proxy:

• Create a SQL Server Agent Proxy
• Setting Up Your SQL Server Agent Correctly

I found out the answer. The problem isn't the parquet file itself, but the fact that these statements:

Ok, there is a simple explain for why this runs so slow. And in fact, you can make this run SEVERAL MILLION times faster!

First up: Database and indexing? Not a problem with 20,000 rows. In fact, if you use some old computer you found in a dumpster? It will easy pull 100,000 rows per second - and do so against a database WITHOUT ANY indexing!! - even if you apply sorting and criteria.

So, no, this is not a database issue - you have HUGE speed, and you would in fact require some efforts to make the database run slow.

so, then what is the problem?

Why of course it is the string concatenation !!!!

Think of it this way:

Say you have to walk 150 miles

But, say when you reach 100 miles? To go to the 101st mile?

Well, you go back to mile 1, and then walk 100 miles + 1 mile.

Then for mile 102?

You go back to mile 1, and then walk 102 miles

Then for mile 103?

You go back to mile 1, and then walk 103 miles

Note how for just 100 to 103, you now walked OVER 300 miles!!!

The SAME is occurring for your string concatenation. In fact, after your string reaches about 2000 or so characters, you going to see HUGE slowdowns.

So, assume your string has now 500,000 characters?

And you go

The code is 10 years old and was written with the assumption that a [PAGE ID] would only ever be expressed as a pair of integers, e.g. 0001:00000138 - however, as you have learned, sometimes that is expressed differently, like 0x41-->01 ; 0001:00000138.

You can fix that problem by adding this inside the cursor:

Well, I finally found that the only way to successfully move from Visual Studio Entity Framework to PostgreSQL, you have to use the Code First Approach.

There is no scaffolding solution I found to do the conversion. You will have to install the Npgsql Provider, change the connection string to point to PostgreSQL, and also install the Npgsql.EntityFramwework and make sure your models have been changed accordingly.

Use BEFORE UPDATE trigger and either NEW TABLE (in any case) or FINAL TABLE (if you don't have AFTER UPDATE triggers).

If you can't use BEFORE trigger to implement your update logic, then you can't use a data-change-statement to achieve your goal.

When there's a copy activity performance issue in ADF and the root cause is not obvious (e.g. if source is fast, but sink is throttled, and we know why) -- here's how I would go about it :

1. Start with the Integration Runtime (IR) (doc.). This might be a jobs' concurrency issue, a network throughput issue, or just an undersized VM (in case of self-hosted). Like, >80% of all issues in my prod ETL are caused by IR-s, in one way or another.
2. Replicate copy activity behavior both on source & sink. Query the views from your local machine (ideally, from a VM in the same environment as your IR), write the flat files to blob, etc. I'm assuming you've done that already, but having another observation rarely hurts.
3. Test various configurations of copy activity. Changing isolationLevel, partitionOption, parallelCopies and enableStaging would be my first steps here. This won't fix the root cause of your issue, obviously, but can point a direction for you to dig in further.
4. Try searching the documentation (this doc., provided by @Leon is a good start). This should have been a step #1, however, I find ADF documentation somewhat lacking.

N.B. this is based on my personal experience with Data Factory.
Providing a specific solution in this case is, indeed, quite hard.

You can't return result sets if not from a procedure

See docs

WITH RETURN Specifies that the result table of the cursor is intended to be used as a result set that will be returned from a procedure. WITH RETURN is relevant only if the DECLARE CURSOR statement is contained with the source code for a procedure. In other cases, the precompiler might accept the clause, but it has no effect.

I understand that it may just be an example, but procedural statements are not needed to return integers from 1 to 10, the following query also does :

That is the correct way which I was using everything was correct, turns out the problem was with adding single quotes in dates that's why it was not working. Now everything is working.