mysql - Multiple queries VS Stored Procedure -

i have application around 20000 data-operations per/hour data-operation has overall 30 parameters(for 10 queries). text, numeric. text params long 10000 chars.

every data-operation following:

• a single data-operation, inserts / updates multiple tables(around 10) in database.
• for every data-operation, take 1 connection,
• then use new prepared-statement each query in data-operation.
• prepared-statement closed every time query executed.
• connection reused 10 prepared-statements.
• connection closed when data-operation completed.

now perform data-operation,

• 10 queries, 10 prepared-statement(create, execute, close), 1o n/w calls.
• 1 connection (open,close).

i think that, if create stored procedure above 10 queries, better choice.

in case of sp, data-operation have:

• 1 connection, 1 callable statement, 1 n/w hit.

i suggested this, told

• this might more time consuming sql-queries.
• it put additional load on db server.

i still think sp better choice. please let me know inputs.

benchmarking option. have search tools can in this. can 1 suggest available benchmarks kind of problem.

any recommendation depends partially on script executing queries resides. if script executing queries on same server mysql instance won't see of difference, there still small overhead in executing 200k queries compared 1 stored procedure.

my advice either way though make stored procedure. need maybe couple of procedures.

1. a procedure combines 10 statements per-operation 1 call
2. a procedure can iterate on table of arguments using cursor feed procedure 1

your process be

1. populate table arguments fed procedure 1 procedure 2
2. execute procedure 2

this yield performance benefits there no need connect mysql server 20000*10 times. while overhead per-request may small, milliseconds add up. if saving 0.1ms per request, that's still 20 seconds saved.

another option modify requests perform 20k data operations @ once (if viable) adjusting 10 queries pull data database table mentioned above. key of arguments loaded in single batch insert, , using statements on mysql server within procedure process them without further round trips.