Re: Performance problems with StoredProcedure in Web application
- From: "Dave Sexton" <dave@jwa[remove.this]online.com>
- Date: Wed, 11 Oct 2006 09:36:02 -0400
Hi Frank,
I appreciate the background info.
<snip>
BUT - THE PERFORMANCE:
Where the Text query took approx. 10s, the StoredProcedure took up to 2
minutes to return as the same data!
Does anybody have an idea what might be the reason for that behaviour?
It's probably the fault of the cursor. Any particular reason why you need the cursor? It's not being used by the textual query.
Really appreciate your help - Frank
PS: Finaly two snapshots of the code, first the fast hardcoded version,
second the stored procedure based one:
[OLD]
dwhConnection.ConnectionString=(String)getSessionObject("dwhConnectionString");
generalDataAdapter.SelectCommand.CommandText= "SELECT * FROM MYTABLE";
DataTable dataGridInputTable=new DataTable("general");
generalDataAdapter.Fill(dataGridInputTable);
generalAdapter.Fill(dataGridInputTable);
Stored procedures will commonly out-perform textual queries when selecting data, so I'd imagine that in stress situations the above
might perform worse then a stored procedure that does the same exact thing, but I doubt the opposite will ever be true.
[NEW]
OracleConnection oraConnection=new
OracleConnection((String)getSessionObject("oraConnectionString"));
OracleCommand generalQuery=new
OracleCommand("expt.expt_reports_pkg.getAllExperiments",
oraConnection);
generalQuery.CommandType=CommandType.StoredProcedure;
generalQuery.Parameters.Clear();
generalQuery.Parameters.Add(new OracleParameter("M_CURSOR",
OracleType.Cursor));
generalQuery.Parameters["M_CURSOR"].Direction=ParameterDirection.Output;
OracleDataAdapter currentAdapter=new OracleDataAdapter(generalQuery);
DataTable dataGridInputTable=new DataTable("general");
currentAdapter.Fill(dataGridInputTable);
Stored procedures are much safer to use because they're not susceptible to SQL injection attacks, where the user submits data in the
query string, for example, such as ""frank' OR 1=1) timedelay...". The exception is, of course, when executing dynamic SQL in your
stored procedures. I only mention this because of the background you supplied and how it sounded much more complex then your
example "SELECT * FROM MYTABLE", and the code above that executes the stored procedure. i.e., In your code examples you aren't
dealing with parameters but you seemed to imply that you use them.
I suggest keeping the procedure and removing the cursor to see if that helps.
--
Dave Sexton
.
- Follow-Ups:
- References:
- Prev by Date: Re: Math.Round Question
- Next by Date: Need design for calling a method at regular intervals
- Previous by thread: Performance problems with StoredProcedure in Web application
- Next by thread: Re: Performance problems with StoredProcedure in Web application
- Index(es):
Relevant Pages
|
