ADO.NET Parameterized Queries - Better Performance ?

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
Thu Dec 18 09:45:59 CST 2003

Hi Vikram,

"Vikram" <vikram404@hotmail.com> wrote in message
news:7acafb49.0312180723.3cbd4993@posting.google.com...
> 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.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

William
Thu Dec 18 10:23:14 CST 2003

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" <vikram404@hotmail.com> wrote in message
news:7acafb49.0312180723.3cbd4993@posting.google.com...
> 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

vikram404
Thu Dec 18 22:52:58 CST 2003

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


"William Ryan" <dotnetguru@comcast.nospam.net> wrote in message news:<unR4GLYxDHA.2408@tk2msftngp13.phx.gbl>...
> 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" <vikram404@hotmail.com> wrote in message
> news:7acafb49.0312180723.3cbd4993@posting.google.com...
> > 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

Chris
Fri Dec 19 08:44:32 CST 2003

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

"Vikram" <vikram404@hotmail.com> wrote in message
news:7acafb49.0312182052.5fda707a@posting.google.com...
> 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
>
>
> "William Ryan" <dotnetguru@comcast.nospam.net> wrote in message
news:<unR4GLYxDHA.2408@tk2msftngp13.phx.gbl>...
> > 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" <vikram404@hotmail.com> wrote in message
> > news:7acafb49.0312180723.3cbd4993@posting.google.com...
> > > 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

Eric
Mon Dec 22 17:12:41 CST 2003

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.