Search timeout

[Mike_25]

Hi there, I have have a challenge with my application. I am developing it using VB.Net and SQL server 2014. My database contains 2k plus records. I used to search and return the results very fast when the database was still small. The problem now is that whenever i try to search, it times out before the search is complete. see my code below. What should my code be to avoid time out and also search very fast?

 'connecting to the database 
            Try
                Dim con As New SqlClient.SqlConnection("Server=.\SQLEXPRESS;Initial Catalog=RCS;Integrated Security=True")
                Dim cmd As New SqlCommand()
                cmd.Connection = con
                Dim da As New SqlDataAdapter("select r.Surname, r.First_name, r.Sex, c.type from dbo.cand_results AS r, dbo.centres AS c WHERE r.Centre_code = c.code AND r.Exam_number= '" & TxtLatest.Text & "'", con)
                Dim ds As New DataSet("cand_results")
                da.FillSchema(ds, SchemaType.Source, "cand_results")
                da.Fill(ds, "cand_results")
                Dim tblcand_results As DataTable
                tblcand_results = ds.Tables("cand_results")
                If tblcand_results.Rows.Count = 0 Then
                    MsgBox("Candidate not found!", vbExclamation, "Candidate not found")
                    TxtLatestSurname.Text = ""
                    TxtLatestOther.Text = ""
                    TxtLatestSex.Text = ""

                    Exit Sub
                End If
                Dim drcurrent As DataRow
                For Each drcurrent In tblcand_results.Rows
                    TxtLatestSurname.Text = ds.Tables("cand_results").Rows(inc).Item(0)
                    TxtLatestOther.Text = ds.Tables("cand_results").Rows(inc).Item(1)
                    TxtLatestSex.Text = ds.Tables("cand_results").Rows(inc).Item(2)
                    TxtCentre_type1.Text = ds.Tables("cand_results").Rows(inc).Item(3)
                Next
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End If
    End Sub

[Hardz]

Hi,

You have to properly dipose the connection string and the Sql command to avoid memory leak, please see this link: Disposing Sql Connection. And be sure to use a parameterized query to avoid Sql Injection

        Dim con As New SqlClient.SqlConnection("Server=.\SQLEXPRESS;Initial Catalog=RCS;Integrated Security=True")
        Dim cmd As New SqlCommand()
        Try
            con.Open()
            Dim da As New SqlDataAdapter
            cmd.CommandText = "select r.Surname, r.First_name, r.Sex, c.type from dbo.cand_results AS r, dbo.centres AS c WHERE r.Centre_code = c.code AND r.Exam_number = @examno "
            cmd.CommandType = CommandType.Text
            cmd.Connection = con
            cmd.Parameters.AddWithValue("@examno", TxtLatest.Text)
            Dim ds As New DataSet("cand_results")
            da.SelectCommand = cmd
            da.FillSchema(ds, SchemaType.Source, "cand_results")
            da.Fill(ds, "cand_results")
            Dim tblcand_results As DataTable
            tblcand_results = ds.Tables("cand_results")
            If tblcand_results.Rows.Count = 0 Then
                MsgBox("Candidate not found!", vbExclamation, "Candidate not found")
                TxtLatestSurname.Text = ""
                TxtLatestOther.Text = ""
                TxtLatestSex.Text = ""
                Exit Sub
            End If
            Dim drcurrent As DataRow
            For Each drcurrent In tblcand_results.Rows
                TxtLatestSurname.Text = ds.Tables("cand_results").Rows(inc).Item(0)
                TxtLatestOther.Text = ds.Tables("cand_results").Rows(inc).Item(1)
                TxtLatestSex.Text = ds.Tables("cand_results").Rows(inc).Item(2)
                TxtCentre_type1.Text = ds.Tables("cand_results").Rows(inc).Item(3)
            Next
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
            cmd.Dispose()
        End Try

[pritaeas]

What is the column type of Exam_number and is it indexed?

You can also try:

cmd.CommandText = "select r.Surname, r.First_name, r.Sex, c.type from dbo.cand_results AS r LEFT JOIN dbo.centres AS c ON r.Centre_code = c.code WHERE r.Exam_number = @examno "

Although the optimizer should do this for you.

[Mike_25]

I have tried both suggestions but still times out only that it takes slightly longer now to time out. The Exam_number is a char data type.

[jwenting]

2K records IS very small. You're forcing a full table scan while it's iterating over every record, for every record. That's never going to work.

Ensure you have proper indices and foreign keys.

[pritaeas]

I have tried both suggestions but still times out only that it takes slightly longer now to time out

I wonder if it's something else. The query should be very very fast, even when not optimized, on such a small set.

When you say time out, do you get an exception? If so, show that message.

[Hardz]

yes, as jwenting said 2K records are very small, compare to approximately 20M records that our(company) table holds (the audit trail/history table).I have no problem querying this table because it is properly indexed and properly referenced to another table using only an express version of Sql server 2012.

[Mike_25]

Ok I ran a count query and the table has 10,626,529 records (likely to grow further). I have attached the screen shot of the exception message.

[pritaeas]

Can you also show the two table structures.

[jwenting]

make sure there's an index on the field combination you're searching on and that the index isn't corrupt. If you don't have such an index you're in trouble as you're going to be doing a full table scan for every possible combination of the fields you're searching on, which may indeed take a long time.

[Mike_25]

Thanks Jwenting I did the indexing and the problem is sorted out. The search is very fast now. Thank you all for your suggestions they really opened my head.