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