0

I have a question about performance for each of those two scenarios on oracle database :-

background : I develop asp.net business application for enterprise use oracle database

one:

I make one database user and use this user in the connection string of asp.net application to connect to db when doing some business logic, so if twenty end-users use this asp.net application simultaneously, then all of them are connect to db as one user

two:

I make multiple database users ( 2 - 5 users), and use them in pool of connection strings so if twenty end-users use the asp.net application simultaneously, then not all connect to db as one user

what is better

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
mustafak26
  • 3
  • 3
  • It makes no difference if you have twenty sessions from same user or twenty different users with one session each. That sounds too low a number to be an issue by itself, but perhaps all twenty are doing intensive work simultaneously. Are you already using connection pools? – Alex Poole Feb 07 '13 at 12:55

2 Answers2

0

I'm not sure about Oracle specifics ... but generally, a good model is as follows:

  1. Create multiple database users, each with different levels of database access, depending on their role. These db users do not correspond to actual users, instead they correspond to roles, probably about 4: full access to create/drop/update tables etc., write access to update tables, read access to select from tables, execute stored procedures and functions.

  2. For any type of modification to the data that your ASP.NET app requires, encapsulate this operation in a stored procedure. Essentially you write a data-access interface at the stored procedure level.

  3. Create a "stored_procedure_executor" db user, whose access only permits executing certain stored procedures (and functions). This user does NOT have access to CREATE/DROP/UPDATE/SELECT directly from tables.

  4. In your ASP.NET application, you only need to store/provide the connection string (db user login information) for the "stored_procedure_executor" db account.

  5. During development, maintenance, support you will need the ability to CREATE/DROP/UPDATE/SELECT directly, and for this you can create additional db users with the appropriate level of access. But you will use these db accounts from db management tools, not via ASP.nET. Thus, the login info will never need to be exposed at the ASP.NET level.

This is primarily guided by security concerns, but also can improve performance as it forces you to think about the way/patterns in which the data will be accessed, and thus allows you to optimize both the structure of the database, as well as the implementation of the most-often used queries (embedded in stored procedures).

user2049524
  • 16
  • it 's good but my situation is: I observe the degradation of performance when 20 end-user run application at same time, so this because i do all my db work as single user of db or can enhance by use multiple users – mustafak26 Feb 07 '13 at 08:32
0

it 's good but my situation is: I observe the degradation of performance when 20 end-user run application at same time, so this because i do all my db work as single user of db or can enhance by use multiple users ???????????????????????

mustafak26
  • 3
  • 3
  • This should be a comment or an edit to the original question. – Branko Dimitrijevic Feb 11 '13 at 12:41
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient [reputation](http://stackoverflow.com/faq#reputation) you will be able to [comment on any post](http://stackoverflow.com/privileges/comment). – Jon Egerton Feb 11 '13 at 12:48