Click here to Skip to main content
15,884,099 members
Home / Discussions / Database
   

Database

 
GeneralRe: Is it a bad idea to keep a Database connection open Pin
Richard.Berry10022-Apr-13 8:23
Richard.Berry10022-Apr-13 8:23 
AnswerRe: Is it a bad idea to keep a Database connection open Pin
PIEBALDconsult20-Apr-13 5:26
mvePIEBALDconsult20-Apr-13 5:26 
AnswerRe: Is it a bad idea to keep a Database connection open Pin
GuyThiebaut20-Apr-13 20:38
professionalGuyThiebaut20-Apr-13 20:38 
AnswerRe: Is it a bad idea to keep a Database connection open Pin
jschell21-Apr-13 6:37
jschell21-Apr-13 6:37 
GeneralRe: Is it a bad idea to keep a Database connection open Pin
PIEBALDconsult22-Apr-13 6:38
mvePIEBALDconsult22-Apr-13 6:38 
GeneralRe: Is it a bad idea to keep a Database connection open Pin
jschell24-Apr-13 8:59
jschell24-Apr-13 8:59 
GeneralRe: Is it a bad idea to keep a Database connection open Pin
Richard.Berry10024-Apr-13 10:51
Richard.Berry10024-Apr-13 10:51 
AnswerRe: Is it a bad idea to keep a Database connection open Pin
Richard.Berry10022-Apr-13 8:17
Richard.Berry10022-Apr-13 8:17 
Hi Guys - thanks to all for the responses.

I think the link Eddy posted puts a lot into perspective, in that closing the connection does not really kill it, it leaves it in the pool, and unless there is something in the connection that changes, like accessing a different database, or using a different User ID the same connection from that pool is used again.

I tried inserting 10000 records (on server on local machine) in three different ways.

The difference was only about 100ms

C#
private void cmdInsert_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
            string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
            sw.Start();
            using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr)) 
            {

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
                
		for (int i = 0; i < 10000; i++)
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }

            sw.Stop();
            this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                      
        }


//7958 miliseconds



private void cmdInsert_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
            string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
            sw.Start();
            using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr)) 
            {

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
                cn.Open();
		for (int i = 0; i < 10000; i++)
                {
                    
                    cmd.ExecuteNonQuery();
                    
                }
                cn.Close();
            }

            sw.Stop();
            this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                      
        }

//6894 miliseconds


private void cmdInsert_Click(object sender, EventArgs e)
        {
            Stopwatch sw = new Stopwatch();
            string cnStr = "Data Source=" + Server + ";Initial Catalog=" + Database + ";User Id=" + DBUser + "; Password=" + DBPass + ";";
            string strSql = "Insert INTO dbo.SpeedTest (Mydata) VALUES ('Hello')";
            sw.Start();
            for (int i = 0; i < 10000; i++)
            {
                using (System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cnStr)) 
                {

                    System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSql, cn);
                    cn.Open();
		            cmd.ExecuteNonQuery();
                    cn.Close();
                }
            }
            sw.Stop();
            this.txtResults.Text = sw.ElapsedMilliseconds.ToString();
                      
        }

//7963 miliseconds

QuestionRecursively build a string Pin
Lash2019-Apr-13 12:26
Lash2019-Apr-13 12:26 
AnswerRe: Recursively build a string Pin
jschell21-Apr-13 6:38
jschell21-Apr-13 6:38 
AnswerRe: Recursively build a string Pin
Niral Soni22-Apr-13 2:35
Niral Soni22-Apr-13 2:35 
GeneralRe: Recursively build a string Pin
Lash2022-Apr-13 7:56
Lash2022-Apr-13 7:56 
Questionsqlserver not found Pin
Member 999911219-Apr-13 2:18
Member 999911219-Apr-13 2:18 
AnswerRe: sqlserver not found Pin
jschell19-Apr-13 10:22
jschell19-Apr-13 10:22 
QuestionConversion failed in sql Pin
venkatesann18-Apr-13 4:23
venkatesann18-Apr-13 4:23 
AnswerRe: Conversion failed in sql Pin
Richard MacCutchan18-Apr-13 4:34
mveRichard MacCutchan18-Apr-13 4:34 
AnswerRe: Conversion failed in sql PinPopular
GuyThiebaut18-Apr-13 5:41
professionalGuyThiebaut18-Apr-13 5:41 
AnswerRe: Conversion failed in sql PinPopular
Mycroft Holmes18-Apr-13 12:39
professionalMycroft Holmes18-Apr-13 12:39 
AnswerRe: Conversion failed in sql Pin
Bernhard Hiller18-Apr-13 22:13
Bernhard Hiller18-Apr-13 22:13 
GeneralRe: Conversion failed in sql Pin
Mycroft Holmes19-Apr-13 0:49
professionalMycroft Holmes19-Apr-13 0:49 
GeneralRe: Conversion failed in sql Pin
GuyThiebaut19-Apr-13 1:36
professionalGuyThiebaut19-Apr-13 1:36 
GeneralRe: Conversion failed in sql Pin
Richard.Berry10024-Apr-13 11:02
Richard.Berry10024-Apr-13 11:02 
GeneralRe: Conversion failed in sql Pin
Mycroft Holmes24-Apr-13 12:29
professionalMycroft Holmes24-Apr-13 12:29 
QuestionTunig required for the below SQL. Pin
nirkar jena16-Apr-13 23:42
nirkar jena16-Apr-13 23:42 
AnswerRe: Tunig required for the below SQL. Pin
Mycroft Holmes17-Apr-13 0:45
professionalMycroft Holmes17-Apr-13 0:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.