title: (local) SQL Server Running very Slow vb.net programming/debugging why so slow? Executenonquery hangs regular office computer
body: <p>I am running a local SQL Sever instance on my machine as (local).  i am using a regular desktop office computer (i5-2400 @ 3.1 ghz 2.85 gb ram). sqlserver.exe uses 1.5GB of memory in my task manager.  I have started to use the sql server instance on my machine to upload tables from a dataset and then query the data in sql server to dump in excel.  doing so i have built up like 8 tables in my sql server table.  the table opcosourcingweekly has about 400k rows and like 26 columns...</p>
  
  <p>i think my issue has to do with settings maybe on indexing or killing processes in SQL server.  i dont see how my code would hang so bad to do simple delete statements on a local instance of SQL server.</p>
  
  <p>for some reason my code hangs when i use ExecuteNonQuery below...I have commented it out and tried running the program and doing the updates in my Local Sql Server instance.  <strong>the simple queries to delete are taking 2-3 minutes to run in SQL Management Studeo Express...Any idea why my sql server instance would be performing so bad?</strong>  </p>
  
  <p>Before when I had the stuff in the button_click argument it wasn't taking this long...i added the datamonthlydump option to my program and needed to create private subs...</p>
  
  <p>Any idea how to make SQL Server faster when it is running on a local machine?  i have already written some programs and this is the first time where my system is hanging.  <strong>when i am programming in visual basic sometimes i stop the program half way through and leave a connection open</strong>  i don't know if this has left connections open on my sql server even after a restart or how to clear my sql server/restart it any ideas how to increase performance for the code below?  thanks!</p>
  
  <pre><code>Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSQLImport.Click
  
      If btnMthly.Checked = True Then
          datadumpmthly()
      ElseIf btnWkly.Checked = True Then
          datadumpwkly()
      End If
  
  End Sub
  
      Private Sub datadumpwkly()
      Dim connectionstring1 As String = "Data Source=(local); Initial Catalog=master;Pooling=False;Integrated Security=True; "
      Dim connection As SqlConnection = New SqlConnection(connectionstring1)
      connection.Open()
      Dim queryString As String = "delete from dbo.OpCoSourcingweekly"
      Dim dbCommand As New SqlCommand(queryString, connection)
      'dbCommand.CommandText = queryString
      'dbCommand.Connection = connection
      'dbCommand.CommandTimeout = 100000
      'dbCommand.ExecuteNonQuery()
  
      Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(connectionstring1)
          bulkcopy.DestinationTableName = "OpCoSourcingweekly"
          bulkcopy.BulkCopyTimeout = 10000000
  
          Try
              bulkcopy.WriteToServer(dataset1.Tables(0))
          Catch ex As Exception
              Dim error1 As String
              error1 = ex.Message
              MsgBox("ERROR: " &amp; error1)
          End Try
          bulkcopy.Close()
  
      End Using
      'dbCommand.CommandTimeout = 100000
      'queryString = "delete from dbo.OpCoSourcingweekly where [Fiscal Year Week] = '201233'"
  
      'Dim dbCommand1 As New SqlCommand(queryString, connection)
      'dbCommand1.CommandText = queryString
      'dbCommand1.Connection = connection
      'dbCommand.CommandTimeout = 100000
      'dbCommand.ExecuteNonQuery()
  
      'queryString = "delete from dbo.OpCoSourcingweekly where [Fiscal Year Week] = '201234'"
      'Dim dbCommand2 As New SqlCommand(queryString, connection)
      'dbCommand2.CommandText = queryString
      'dbCommand2.Connection = connection
      'dbCommand.CommandTimeout = 100000
      'dbCommand.ExecuteNonQuery()
      MsgBox("Done with import to SQL")
      connection.Close()
  End Sub
  </code></pre>
  
  <p><strong>FIXED BY DOING BELOW THANK YOU FOR THE COMMENTS</strong></p>
  
  <pre><code>Private Sub datadumpwkly()
      Dim connectionstring1 As String = "Data Source=(local); Initial Catalog=master; Pooling=False;Integrated Security=True; "
      Using connection As SqlConnection = New SqlConnection(connectionstring1)
          connection.Open()
          Dim queryString As String = "truncate table dbo.OpCoSourcingweekly"
          Using dbCommand As New SqlCommand(queryString, connection)
              dbCommand.CommandText = queryString
              dbCommand.Connection = connection
              dbCommand.CommandTimeout = 100000
              dbCommand.ExecuteNonQuery()
  
              Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(connectionstring1)
                  bulkcopy.DestinationTableName = "OpCoSourcingweekly"
                  bulkcopy.BulkCopyTimeout = 10000000
  
                  Try
                      bulkcopy.WriteToServer(dataset1.Tables(0))
                  Catch ex As Exception
                      Dim error1 As String
                      error1 = ex.Message
                      MsgBox("ERROR: " &amp; error1)
                  End Try
                  bulkcopy.Close()
  
              End Using
              dbCommand.CommandTimeout = 100000
          End Using
          queryString = "delete from dbo.OpCoSourcingweekly where [Fiscal Year Week] = '201233'"
  
          Using dbCommand1 As New SqlCommand(queryString, connection)
              dbCommand1.CommandText = queryString
              dbCommand1.Connection = connection
              dbCommand1.CommandTimeout = 100000
              dbCommand1.ExecuteNonQuery()
          End Using
  
          queryString = "delete from dbo.OpCoSourcingweekly where [Fiscal Year Week] = '201234'"
          Using dbCommand2 As New SqlCommand(queryString, connection)
              dbCommand2.CommandText = queryString
              dbCommand2.Connection = connection
              dbCommand2.CommandTimeout = 100000
              dbCommand2.ExecuteNonQuery()
          End Using
          MsgBox("Done with import to SQL")
          connection.Close()
      End Using
  End Sub
  </code></pre>