Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Would authentication settings on SQL server 2008 R2 make any performance difference?

Alright this is the first method

public static string srConnectionString = "server=localhost;database=myDB; "+
" uid=sa;pwd=myPW;";

And this is the second method

public static string srConnectionString = "server=localhost;database=myDB; "+
" integrated security=SSPI;persist security info=False; Trusted_Connection=Yes;";

Are there any performance difference or any other difference between these 2 connection strings?

Here my sql connection class any suggestion ?

using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

public static class DbConnection

{
    public static string srConnectionString = "server=localhost; database=myDB; uid=sa; pwd=myPW;";

    public static DataSet db_Select_Query(string strQuery)
    {
        DataSet dSet = new DataSet();
        if (strQuery.Length < 5)
            return dSet;
        try
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
                {
                    DA.Fill(dSet);
                }
            }
            return dSet;
        }
        catch
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                if (srConnectionString.IndexOf("select Id from tblAspErrors") != -1)
                {
                    connection.Open();
                    strQuery = strQuery.Replace("'", "''");

                    using (SqlCommand command = new SqlCommand("insert into tblSqlErrors values ('" + strQuery + "')", connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
            }
            return dSet;
        }
    }

    public static void db_Update_Delete_Query(string strQuery)
    {
        if (strQuery.Length < 5)
            return;
        try
        {
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(strQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }
        catch
        {
            strQuery = strQuery.Replace("'", "''");
            using (SqlConnection connection = new SqlConnection(srConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("insert into tblSqlErrors values ('" + strQuery + "')", connection))
                {
                    command.ExecuteNonQuery();
                }
            }
    }
}

}

like image 890
MonsterMMORPG Avatar asked Dec 19 '12 19:12

MonsterMMORPG


1 Answers

The performance difference is insignificantly small that it can be ignored. The authentication checks are

  1. ...performed only on login. The connection is not rechecked for each query. However with connection pooling, the connection is authenticated and reset many times, quite possibly for nearly every query
  2. ...the same as every file access and other activity involving the domain getting checked against Active Directory

FYI these two settings are the same (use one or the other):

integrated security=SSPI
Trusted_Connection=Yes

like image 68
RichardTheKiwi Avatar answered Sep 28 '22 07:09

RichardTheKiwi