(local) SQL Server Running very Slow vb.net programming/debugging why so slow? Executenonquery hangs regular office computer

Question

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...

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.

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. 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?

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...

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. when i am programming in visual basic sometimes i stop the program half way through and leave a connection open 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!

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: " & 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

FIXED BY DOING BELOW THANK YOU FOR THE COMMENTS

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: " & 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

Answer 1

5 years later... I had the same problem and solved the problem by changing the table engine from MyISAN to InnoDB

Answer 2

One way to speed up deletes of an entire table is by using the TRUNCATE TABLE command.

There are limitations to using this (related to foreign keys), but if you're able to use it it's very fast.