Re: Storing data from multiple clients by Thomas
Thomas
Sun Oct 01 14:38:55 CDT 2006
Massimo
I have to clear some of my proposals:
> That's the problem: when the framework uses its own worker threads to
> process async socket callbacks, you don't have any control on how many
> threads there are and which one of them is used. To do as you suggest, I'd
> have to manually spawn worker threads and have each one of them manage one
> or more connected sockets.
> I could also have each callback create a connection object, open it, use it
> and then close it, but I think this would be *really* a bottleneck.
> Another option would be to have only one connection object and use a mutex
> to have only one thread at a time access it, but I think this would create a
> big bottleneck too.
We have written a customized DataAdapter, that handles the connections internally on a per thread basis. That means the connection will be created for a specific thread. We've got a big application (>1 million loc) written in C# and there are running about 700 queries (average) every second against a SQL Server 2000. For every query we will create a connection object, open it, run the query and close it. The only bottleneck is the database server itself!
I understand your distrust; try it out with a little sample project and measure the time spent to create, open and close the connection object.
> > Opening a connection will reuse the connection on the SQL Server side if
> > possible, so the SQL Server will create it only once (Connection Pooling).
> > Opening and closing the connection will be done fast.
>
> Of course, but only when using the same connection object(s).
Thats not true. Connection pooling will be done with the same "configuration". That means with the same data in the connection string and Windows identity when integrated security is used.
You should read the chapter "Using Connection Pooling" in the VS.NET 2005 help.
> > Try to reduce roundtrips to the database server. You could collect
> > lets say 10 messages from the clients and send them as a bunch
> > to a stored procedure on the database server.
> > Try to decouple the insert from the TCP sockets, because you never know,
> > how much time it will take. You could use Microsoft Message Queue for
> > that.
> That's a really good point; I think maybe MSMQ is too much for this, but an
> internal (synchronized) FIFO buffer which would be filled by the socket
> callbacks and periodically emptied by a worker thread which moves the data
> to the DB could be a good design choice. This could also let me see if a
> long queue of unstored data builds up (which would mean the DB can't handle
> all those queries), or temporarily store data if the DB is down/unreachable
> for a while. I should think more about this, thanks :-)
It's very easy to use MSMQ. With your own FIFO buffer, you have to handle the synchronisation by yourself. Of course you even need a worker thread with MSMQ.
I post some sample code here (I left exception handling away). To use this code, you have to install MSMQ from your Windows Components (even unter Windows 2000/XP):
using System;
using System.Messaging;
public class AutoCreateMessageQueue
{
private string m_queueName;
private MessageQueue m_msgQ;
public AutoCreateMessageQueue(string queueName)
{
m_queueName = queueName;
}
public void Initialize()
{
// Create the queue if it does not already exist
if (!MessageQueue.Exists(m_queueName))
{
m_msgQ = MessageQueue.Create(m_queueName);
}
else
{
m_msgQ = new MessageQueue(m_queueName);
}
}
/// <summary>
/// Deletes all the messages contained in the queue.
/// </summary>
public void Purge()
{
m_msgQ.Purge();
}
/// <summary>
/// Adds an object to the nontransactional queue and specifies a label for the message.
/// </summary>
public void Add(string message, string label)
{
m_msgQ.Send(message, label);
}
/// <summary>
/// Receives the first message available in the queue. This call is synchronous, and
/// blocks the current thread of execution until a message is available.
/// </summary>
/// <returns>Returns the content of the message. The caller is responsible to interpret the contents.</returns>
public object GetBlocking()
{
Message myMessage = m_msgQ.Receive();
return myMessage.Body;
}
}
...
// socket side initializing
AutoCreateMessageQueue queue = new AutoCreateMessageQueue(@".\private$\DbInsertData");
queue.Initialize();
queue.Purge(); // cleanup old messages in queue
...
// socket side adding a message
string data = GetData(your parameter to assemble a string);
queue.Add(data, data.Id); // you could serialize your data class directly
...
// Worker thread initializing
AutoCreateMessageQueue queue = new AutoCreateMessageQueue(@".\private$\DbInsertData");
queue.Initialize();
...
// Worker thread reading the message and inserting in db
string data = (string)queue.GetBlocking()
InsertDataIntoDb(data);
...
That way, you could dynamically setup more worker threads to insert data into the database. The synchronization is done completely by MSMQ.
Good luck
Thomas