I'm working on a app that executes the same MySQL query repeatedly, with
different parameters (4 params right now) and returns a single row and
column, using the C API.

Has anybody done any performance testing to see whether it's faster to
dynamically build the query text and send it to MySQL, or if it would be
faster to pass the parameters to a MySQL stored procedure?

The stored procedure seems to have quite a bit of overhead in creating the
parameters, sending them to the server, retrieving the result-set and
actually accessing the results, however I'm not sure if this translates
into an actual performance hit, since it's possible the client libraries
are doing a bit of work even when passed a text SQL Query.

Has anybody done any actual tests?

To further muddy the waters, do MySQL Stored Procedures have the same
security benefits as SQL Server and Oracle, in that special characters
are not escaped, making SQL Injection impossible (if text passed to a
MySQL Stored Procedure contains ;'s and multiple commands are they
executed, or just handled as plain text?)

Thanks!

Terry

Terry Carmen wrote:
> I'm working on a app that executes the same MySQL query repeatedly, with
> different parameters (4 params right now) and returns a single row and
> column, using the C API.
>
> Has anybody done any performance testing to see whether it's faster to
> dynamically build the query text and send it to MySQL, or if it would be
> faster to pass the parameters to a MySQL stored procedure?
>
> The stored procedure seems to have quite a bit of overhead in creating the
> parameters, sending them to the server, retrieving the result-set and
> actually accessing the results, however I'm not sure if this translates
> into an actual performance hit, since it's possible the client libraries
> are doing a bit of work even when passed a text SQL Query.
>
> Has anybody done any actual tests?
>
> To further muddy the waters, do MySQL Stored Procedures have the same
> security benefits as SQL Server and Oracle, in that special characters
> are not escaped, making SQL Injection impossible (if text passed to a
> MySQL Stored Procedure contains ;'s and multiple commands are they
> executed, or just handled as plain text?)
>
> Thanks!
>
> Terry
>

The fastest way would be to use prepared statements.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
>>
>> Has anybody done any actual tests?
>>
>> To further muddy the waters, do MySQL Stored Procedures have the same
>> security benefits as SQL Server and Oracle, in that special characters
>> are not escaped, making SQL Injection impossible (if text passed to a
>> MySQL Stored Procedure contains ;'s and multiple commands are they
>> executed, or just handled as plain text?)
>>
>> Thanks!
>>
>> Terry
>>
>
> The fastest way would be to use prepared statements.
>

I have a number of parameters (right now, about 4, but could be 8 or
10). The prepared statements look like they require a server call for
the SET statement for each parameter.

Do you have any idea if this would be more efficient than generating the
entire SQL statement each time?

Thanks!

Terry

Terry Carmen wrote:
> Jerry Stuckle wrote:
>>>
>>> Has anybody done any actual tests?
>>>
>>> To further muddy the waters, do MySQL Stored Procedures have the same
>>> security benefits as SQL Server and Oracle, in that special characters
>>> are not escaped, making SQL Injection impossible (if text passed to a
>>> MySQL Stored Procedure contains ;'s and multiple commands are they
>>> executed, or just handled as plain text?)
>>>
>>> Thanks!
>>>
>>> Terry
>>>
>>
>> The fastest way would be to use prepared statements.
>>
>
> I have a number of parameters (right now, about 4, but could be 8 or
> 10). The prepared statements look like they require a server call for
> the SET statement for each parameter.
>
> Do you have any idea if this would be more efficient than generating the
> entire SQL statement each time?
>
> Thanks!
>
> Terry
>

Yes, because the server only has to parse the SQL statement once. Then
you just reuse the same prepared statement each time.

But why don't you just run some benchmarks and see which way is fastest?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================