ADO.NET Parameterized Queries - Better Performance ?

[Vikram]

Hi,

I dont have stored procedures since my app needs to be Database
independent in the sense that I should be able to use SQL Server or
Oracle. I have wrapped the entire data access functionality with a
custom data layer through which all database calls would be made.

Right now, the sqls are being built by string concatenation.
Will changing this to parameterized queries using OldDbParameter
objects increase performance ??

Regards,
Vikram

 

[Miha Markic]

Hi Vikram,

Hi,

I dont have stored procedures since my app needs to be Database
independent in the sense that I should be able to use SQL Server or
Oracle. I have wrapped the entire data access functionality with a
custom data layer through which all database calls would be made.

Right now, the sqls are being built by string concatenation.
Will changing this to parameterized queries using OldDbParameter
objects increase performance ??

Yes, and not only performance - even security will be more enforced and
you'll get rid also of regional settings problems.

 

[William Ryan]

LEt me second what Miha said, yes, much better performance, much better
security, either of which alone is reason enough to switch, and much better
maintainability. If at all possible, avoid dymamic sql like the plague

 

[Vikram]

Thanks guys for the response...
Can you point me to some link in MSDN or explain how this improved
performance is achieved because of parameterized queries....

I need to back this change in design by some docs...any link or
document which explains the internals as to how performance improves
will help.

Thanks,
Vikram

 

[Chris]

Check out a SQL performance article in VS Mag:
http://www.fawcette.com/vsm/2003_11/magazine/features/jennings/

Pretty convincing evidence and it appears that parameterized are even faster
than SPs in some situations.

Chris Snyder
MCSD

 

[Eric]

The speed improvement is based on the principle of parsing the SQL
statement only one time. After that, the parameter values are
plugged-in each time, but the whole SQL statement doesn't have to be
parsed again.