CODEWITHSUNDEEP
c#multithreadingodbcdatabase-connection
user3810913
user3810913

Reputation:

Programmatically set DB connection priority

We have a C# app that opens multiple ODBC connections to various databases (mostly Oracle and SQL Server).

Is there a way to programmatically tell the DB which connection to give the highest priority to?

For instance, if I have Con1, Con2, and Con3 all connecting to an ODBC database, I want to be able to tell the database to treat Con2 as the highest priority over any other connections so it'll prioritize that workload first.

While I could set a C# thread-priority, this won't work because while the C# thread may run at the highest, it'll still run only as fast as the underlying database connection (unless I'm wrong and the C# thread priority is passed through?).

Upvotes: 4

Views: 1511

Answers (1)

Don Kirkby
Don Kirkby

Reputation: 56660

I haven't used it, but it sounds like you can use Oracle's Database Resource Manager to assign different priorities to different sessions.

In an environment with multiple concurrent users sessions that run jobs with differing priorities, all sessions should not be treated equally. The Resource Manager enables you to classify sessions into groups based on session attributes, and to then allocate resources to those groups in a way that optimizes hardware utilization for your application environment.

This related question mentions SQL Server's Resource Governor.

If you want to set priorities between two different database systems, like Oracle and SQL server, I think it will be less flexible. The best I can think of would be to set the process priority for one of the database systems lower than the other. The resource manager and governor sound like they will let you limit how many resources any one query can use, but that may lead to situations where there are no other tasks, and the low-priority task ends up leaving some of the processor speed unused.

Upvotes: 1

Related Questions