Re: why not SQL Authentication?

From: gabe garza (gbgarza_at_yahoo.com)
Date: 03/31/05 

Date: Thu, 31 Mar 2005 01:27:14 GMT

>The main drawback of SqlAuthentication (authing from browser thru website
>thru database) is that connections can't be pooled.
This is what you posted in an earlier post.
>From the MSDN connections are pooled based on the connection string, not
whether your an SQL Login or the ASPNET User.

> The trick to avoid latency is to not make the trip to the database, so in
> essence you need to cache data (if possible).
It's not possible. parameters change based on users requesting information.
If all parameters are the same then static pages would do. So regardless,
you're going to have to go to the database to get your data. I'm not taking
about retrieving commonly used data either, as in state codes, etc... those
can be cached.

So what's the thing that is semi-static if you will. Your query. the only
difference would be the parameters.

If I had a stored procedure call GetEmployeeName(?) that has one parameter
for an employee id, then I'd create a command object and pass that id to
GetEmployeeName() to get my resultset, which should be the employee
information. First Name, Last Name, etc...

Now what doesn't make sense is for me is to close the command object,
dispose it, then null it. Why wouldn't I just cache that command object so
that I could just reuse it again without having to set a connection object
to it, add all the parameter descriptions again (only one in this example),
then open the command object again.
If I put in in cache then all I have to do is get it from cache, set my one
parameter employee id and execute it.
Once I'm done with it I don't have to put it back into cache, it's there for
anyone else to use it.

Of course its just not that simple, in a web based application you're
dealing with multiple users at any given time. So locking your command
object would be one thing you'd have to look at as well as building multiple
command objects of the same query command, based on system usage. System
design, very important here.

That's why caching the command objects is better. The only thing that's
changing with the command object is the paramters being passed, it's the
same connection, same stored procedure, same number of parameters. I just
don't see why programmers close and dispose of it when they're just going to
use it again.

"Brock Allen" <ballen@NOSPAMdevelop.com> wrote in message
news:310438632478083570347712@msnews.microsoft.com...
>> Brock,
>> If the connection string was
> <snip>
>> So on a web based application, connections can and are pooled based on
>> the Connection String. So SQLAuthentication logins can be pooled.
>
> I don't think anything you said there is inconsistent with what I said.
>
>> I think reading about OLE-DB Providers would be helpful as well.
>
> OLEDB connection pooling is dog slow compared to the managed provider
> connection pooling.
>
>> Of course with all of this, in my opinion pooling a connection is just
>> the first step in having a performance enhancement system, the next
>> step is caching your command objects. The command objects are the
>> objects doing the actual work, over and over again.
>
> This is specious and in fact won't buy you much (if anything). The types
> of performance problems we're indirectly speaking about are caused by
> latency. The trick to avoid latency is to not make the trip to the
> database, so in essence you need to cache data (if possible). Connection
> pooling is a form of caching, as you're avoiding the latency in
> establishing the connection. Another way to avoid latency is caching data
> returned from the database (typically in the HttpContext.Cache) so that
> the next time the data's needed you don't need to hit the network. I don't
> see the benefit of caching command objects, as they simply represent what
> command you'd like to issue to the database. If you cache the command, to
> get the actual data you still have to call ExecuteReader() or
> ExecuteNonQuery() on the command, which induces a trip to the database. So
> sorry that I disagree with you, but I don't buy it.
>
> -Brock
> DevelopMentor
> http://staff.develop.com/ballen
>
>
>

Relevant Pages

  • Re: SQL Server 2005 Express connection error?
    ... //Obtain the database connection string ... //Obtain a database specific connection object ... //Create a database specific command object ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: why not SQL Authentication?
    ... >> website thru database) is that connections can't be pooled. ... you can't share/pool that connection. ... >> so in essence you need to cache data. ... then open the command object again. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Sleepy database
    ... The PHP has to connect to the database ... using the TCP connection on first connect. ... Alternatively it could be the cache on the database server. ...
    (comp.lang.php)
  • Re: [PHP] PHP+MySQL website cache ? Yes/No
    ... This benchmark is not very accurate, but you are right the database connection took most of the time. ... Shopping cart is stored in session - not in database. ... One way or another I will always need to connect do database (I cannot cache 300 products in 300 files, ...
    (php.general)
  • Re: Connecting to an Oracle Database
    ... When I create the command object, ... connection to it, create the OleDbDataAdapter, bind the command object to ... close the connection when you fill the data adapter, ... setup the database connection info ...
    (microsoft.public.sharepoint.windowsservices)