CODEWITHSUNDEEP
c#sql-serveroledb
Yaron
Yaron

Reputation: 233

A lot of insertQuerys - efficiency

I have a static method that opens a connection to SQL Server, writes a log message and closes the connection. I call this method plenty of times from the whole the code (every 2 sec on average).

The question is - is it efficient? I thought maybe it would be better to accumulate some logs and insert them with one connection?

But then I'll have to save them somewhere (xml?), and this costs too.

So.. What is the efficient way to do this? Write to an xml\file\whatever and then update in one connection with bulk or open connection for any little update?

public static bool writeLog(string TaskName="MainApp", string LogMessage=null)      
{
    string logCmd = ...
    OleDbConnection conn = ...
    OleDbCommand sqlCmd = new OleDbCommand(logCmd,conn);
    try
    {
        conn.Open();
        sqlCmd.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        Console.WriteLine(ex.ToString());
        Console.ReadLine();
    }
    finally
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }                
    }
    return isSuccses;
}

Upvotes: 2

Views: 69

Answers (2)

Brijesh Mishra
Brijesh Mishra

Reputation: 2748

no sql like mongo DB would be better alternative here.

Otherwise 2 sec is no big deal, if you are looking to avoid any kind of load on database, store all content in file and transfer them in batch during non peak hour.

Upvotes: 0

Szymon
Szymon

Reputation: 43023

It's hard to say what is your expectation of efficiency but here's a general comment.

Inserting a record every 2 seconds is certainly something SQL Server can handle without problems even if you open a connection every time. You insert one record at the time, there won't be even noticable locking problems, etc.

If you would change your architecture, it would make it more complex and harder to maintain data integrity (you would have to store the values in some sort of cache and make sure that this cache doesn't get lost, etc). This doesn't seem necessary in this case.

As an aside, you may want to switch to the native SQL Server provider and using SQLConnection for performance.

Upvotes: 3

Related Questions