Does creating a Static SQL interface class make sense

  • Thread starter Thread starter Buddy Ackerman
  • Start date Start date
B

Buddy Ackerman

Wouldn't creating a static class for a SQL database interface cause all data
access to be serialized causing a bottleneck in the application?


--Buddy
 
Buddy,
Wouldn't creating a static class for a SQL database interface cause all
data
access to be serialized causing a bottleneck in the application?
Click to expand...
I have the idea that you mean with serialized something else than most of us
mean with this word.

Can you explain it a little bit more?

Cor
 
What I mean is that if 100 database requests are made from 100 different
threads will they will get satisfied sequentially since only one instance
of a static class exists per application? Is that right?


--Buddy
 
I think if you made the class into a Singleton, then the initial access to the classes instance would be a bottleneck, but not too much. After that every thread would have a reference to the instance and can invoke non-static methods.
 
Buddy Ackerman said:
What I mean is that if 100 database requests are made from 100 different
threads will they will get satisfied sequentially since only one instance
of a static class exists per application? Is that right?
Click to expand...

They should but it is your responsability to implement thread safety.
Otherwise you should create an instance of connection right before data
operation and close it asap - this way you'll use the minimum number of
concurrent physical connections
 
As far as I understand, to use a Static class means to directly call
the methods, thus you do not create a new instance. So it makes sense
to me, taking this with threads, it will execute in the order it was
called?
 
I have a feeling we're talking about different .NET versions. All you lucky people who have the money for VS2005, good for you. I will NOT get the stupid express versions, and the academic doesn't allow you to sell anything.

In 1.x there are no static classes. You could impliment the singleton pattern by making the constructor private, and providing a static property to return the one and only instance of the class. Clients access the static property first and then are able to invoke instance methods.

In 2.0 I hear the static keyword is just an easier way to create singletons.
 
Buddy,

I can me not imaging that in a program there will be such an independent way
to fetch the data, that there can be used more threads to do that. Even if
you are using more datatables directly than there are busy with that.

The network (which helps nothing to do that with extra threads, there are
normally not two networks)
The databaseserver( where it can help if you have more database servers, but
100 seems for me a little bit unmanagable in such a situation)
The clientcomputer (where one user is busy doing things with the data)

Can you tell me what I miss?

Cor
 
Buddy:

If you're running them on separate threads, you don't know what's going to
execute when so in the strict sense, they aren't going to execute
sequentially. Let's say that the second call fails b/c of a command timeout
(just assume for the sake of ease that it's a long running query), the
others are going to execute irrespsective of this and the others will still
execute. Now assume it doesn't fail but it's a really long running query,
one you know will take 100 times the time to execute as the rest. You can
almost be sure that the others will complete first (provided you aren't
intentionally blocking).

In the 2.0 Framework, you have MARS which allows for multiple commands to
fire on the same connection. However in the 1.x versions, you can't. So if
you have a static method, you are going to need new instances of connection
objects and commands. Those are what need to be new and even if you had
instance methods, if the connection for instance (pardon the pun) was
static, it wouldn't work.

Based on your other comments I think this is what you're asking, if not let
me konw.
 
OK, .NET, being multithreaded, can issue multiple statements asynchronously
(because each web request is handled by it's own thread). So, if I have 100
users hitting my web site simultaneously then I could possibly have 100
threads making a call to the database. If the class was not static then
there could be 100 independent calls to the database happening instead of 99
calls waiting for the first one to finish. The DB server (i.e SQL server in
this case) is a multi-cpu box that can process many commands at once and
return them in an asynchronous order. So maybe the first call was a long
running transaction (long running meaning maybe 10 seconds) and the second
call was less than a second. If the class is static does the second call to
the same method have to wait for the first one to complete before it could
be sent to the database for processing? It's the difference in parallel
execution vs serial execution. Seems kinda silly to create bottlemeck in
your application like that, if in fact that is the way the static class is
working.


--Buddy
 
Hendrik said:
As far as I understand, to use a Static class means to directly call
the methods, thus you do not create a new instance. So it makes sense
to me, taking this with threads, it will execute in the order it was
called?
Click to expand...


Which sound like a very bad idea in the case of database access since the
database my be able to process one command much faster than others. So, if
the first thread makes a DB request that takes 20 seconds to complete then
the other threads must wait 20 seconds before their requests can be issued.
Doesn't sound very efficient to be.



--Buddy
 
Maybe someone should create a test application and see if access to methods of a static class is synchronous or asynchronous. Regardless of that, it's a given that each method should create a new connection object. What about command objects and adapters though? Is there any internal state variables that are used during a query? If not, then those could be used over and over again.
 
Here's simple yet specific example:

static public class MyDB
{
static public string Exec(string sql)
{
string status = String.Emtpy;
SqlConnection cn = new SqlConnection("connectstring");
cn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
try {
cmd.ExecuteNonQuery();
status ="success";
}
catch
{
status = "fail";
}
cn.Close();
cmd.Dispose();
cn.Dispose();
}
}



Say every sql statement is executed using the Exec method of the MyDB class.
So if 100 threads try to execute this method and the first one that call
this method takes 30 seconds to run will the other threads have to wait for
this first call to complete or do the threads execute independent version of
this method? I was thinking that since this is a static class there's only
one instance of the class and the methods so all executes would execute that
one instance in the order they were received.

I've also seen DB classes created as Singletons which that is even more
restrictive so does that also have the problem that serial execution that
I'm want to avoid?


Thanks.


--Buddy
 
Buddy,

Where you wrote that it was an ASPNet application. Is this a kind of puzzle
for us?

You show very fine why it is not so clever to use a static/shared class for
the IO in a webapplication.

Cor
 
There is no problem with creating a static class for IO. I did my own test and static methods/properties are not serialized unless you do your own synchronization.

Simply put, you can use Singleton or all static methods and treat every method as if multiple threads could access it at the same time.

See this site for how to create a singleton.
http://www.yoda.arachsys.com/csharp/singleton.html

Buddy,
Why do you say Singleton classes are even more restrictive?


Cor Ligthert [MVP] said:
Buddy,

Where you wrote that it was an ASPNet application. Is this a kind of puzzle
for us?

You show very fine why it is not so clever to use a static/shared class for
the IO in a webapplication.

Cor

"Buddy Ackerman" schreef in bericht
news:[email protected]...
> OK, .NET, being multithreaded, can issue multiple statements
> asynchronously
> (because each web request is handled by it's own thread). So, if I have
> 100
> users hitting my web site simultaneously then I could possibly have 100
> threads making a call to the database. If the class was not static then
> there could be 100 independent calls to the database happening instead of
> 99
> calls waiting for the first one to finish. The DB server (i.e SQL server
> in
> this case) is a multi-cpu box that can process many commands at once and
> return them in an asynchronous order. So maybe the first call was a long
> running transaction (long running meaning maybe 10 seconds) and the second
> call was less than a second. If the class is static does the second call
> to
> the same method have to wait for the first one to complete before it could
> be sent to the database for processing? It's the difference in parallel
> execution vs serial execution. Seems kinda silly to create bottlemeck in
> your application like that, if in fact that is the way the static class is
> working.
>
>
> --Buddy
>
>
>
> "Cor Ligthert [MVP]" wrote in message
> news:[email protected]...
>> Buddy,
>>
>> I can me not imaging that in a program there will be such an independent
> way
>> to fetch the data, that there can be used more threads to do that. Even
>> if
>> you are using more datatables directly than there are busy with that.
>>
>> The network (which helps nothing to do that with extra threads, there are
>> normally not two networks)
>> The databaseserver( where it can help if you have more database servers,
> but
>> 100 seems for me a little bit unmanagable in such a situation)
>> The clientcomputer (where one user is busy doing things with the data)
>>
>> Can you tell me what I miss?
>>
>> Cor
>>
>> "Buddy Ackerman" schreef in bericht
>> news:%[email protected]...
>> > What I mean is that if 100 database requests are made from 100
>> > different
>> > threads will they will get satisfied sequentially since only one
> instance
>> > of a static class exists per application? Is that right?
>> >
>> >
>> > --Buddy
>> >
>> >
>> >
>> > "Cor Ligthert [MVP]" wrote in message
>> > news:[email protected]...
>> >> Buddy,
>> >>
>> >> > Wouldn't creating a static class for a SQL database interface cause
> all
>> >> > data
>> >> > access to be serialized causing a bottleneck in the application?
>> >> >
>> >> I have the idea that you mean with serialized something else than most
> of
>> > us
>> >> mean with this word.
>> >>
>> >> Can you explain it a little bit more?
>> >>
>> >> Cor
>> >>
>> >>
>> >
>> >
>>
>>
>
>
Click to expand...
 
Buddy Ackerman said:
OK, .NET, being multithreaded, can issue multiple statements
asynchronously
(because each web request is handled by it's own thread). So, if I have
100
users hitting my web site simultaneously then I could possibly have 100
threads making a call to the database.
Click to expand...
--It depends totally on the MaxWorker threads.
http://msdn2.microsoft.com/en-us/library/5dws599a.aspx

If the class was not static then
there could be 100 independent calls to the database happening instead of
99
calls waiting for the first one to finish.
Click to expand...
--It's more an issue of static methods but that's splitting hairs. Each of
these assumptions though isn't necessarily or always true and depends on
quite a bit of things. You could have threaded application that blocks.
The big difference here is between Processes and threads and it sounds like
they're being confused somewhat. Static are used all over the place in
ASP.NET, for instance, FormsAuthentication. You can certainly cause
problems if you use statics incorrectly but it doesn't sound like your
spinning any of your own threads. If you're not and you're dealing with
stateless members, then you really don't have much to worry about here.
The DB server (i.e SQL server in
this case) is a multi-cpu box that can process many commands at once and
return them in an asynchronous order. So maybe the first call was a long
running transaction (long running meaning maybe 10 seconds) and the second
call was less than a second. If the class is static does the second call
to
the same method have to wait for the first one to complete before it could
be sent to the database for processing? the short answer is no. You could
construct a scenario where it would, but this isn't default behavior and
again, you're not spinning your own threads so much of that is mitigated.
Click to expand...
It's the difference in parallel
 
Buddy:

First of all, remove static from the class MyDB as a class cannot be
static (not in .Net 1.1 anyway).

In your example, all 100 queries will be executed as soon as they are
called. Let's say all 100 queries take 5 seconds to execute, and the
calls to 100 MyDb.Exec("UPDATE table ...") can be made in under 1 second.
You will have 100 queries running at the same time.

The fact that a method is declared static does not introduce any kind of
synchronization. To introduce some kind of synchronization between
database calls, you will have to create your own model. For example, you
could use a singleton class, which would queue up commands. Then in
another thread, a "watcher" pops SQL statements from the queue for
execution.

HTH
Jeff Clausius
SourceGear
 
Back
Top