I have a stored procedure which is very long in length having 500 Lines when i executes it using sql server it takes maximum of ten seconds to executes , but when i am calling it from my code Firstly i get timout error and then i set the timeout time in my connection string as well as in code i did
sda.SelectCommand.CommandTimeout = 0 now it is getting record but it is taking 5 minutes approximatly how to solve that . in sql it is running as fast as it can.
Private Function GetData() As DsetPartner
Dim constring As String = ConfigurationManager.ConnectionStrings("ConnStringDb1").ConnectionString
Dim cmd As SqlCommand = New SqlCommand("USP_DynamicIndicatorsTargetsForPu")
Dim selectedValueQuarter As String = String.Empty
Dim SelectedValueYear As String = String.Empty
Dim SelectedValueDistrict As String = String.Empty
Dim SelectedValueCDF As String = String.Empty
Dim SelectedValueVillages As String = String.Empty
Dim SelecctedValuePo As String = String.Empty
Dim SelectedValueStatus As String = String.Empty
Dim SelectedValueSegrifgation As String = String.Empty
Dim SelectedValueIndicators As String = String.Empty
Dim con As SqlConnection = New SqlConnection(constring)
Dim sda As SqlDataAdapter = New SqlDataAdapter
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
sda.SelectCommand = cmd
sda.SelectCommand.CommandTimeout = 0
If lstQuarter.SelectedIndex = -1 Then
cmd.Parameters.Add("@QuarterStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In lstQuarter.Items
If li.Selected Then
selectedValueQuarter = (selectedValueQuarter + (li.Value + ","))
End If
Next
selectedValueQuarter = selectedValueQuarter.Substring(0, selectedValueQuarter.Length - 1)
cmd.Parameters.Add("@QuarterStr", SqlDbType.NVarChar).Value = (selectedValueQuarter)
End If
If lstYear.SelectedIndex = -1 Then
cmd.Parameters.Add("@YearStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In lstYear.Items
If li.Selected Then
SelectedValueYear = (SelectedValueYear + (li.Value + ","))
End If
Next
SelectedValueYear = SelectedValueYear.Substring(0, SelectedValueYear.Length - 1)
cmd.Parameters.Add("@YearStr", SqlDbType.NVarChar).Value = (SelectedValueYear)
End If
If lstPO.SelectedIndex = -1 Then
cmd.Parameters.Add("@PoStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In lstPO.Items
If li.Selected Then
SelecctedValuePo = (SelecctedValuePo + (li.Value + ","))
End If
Next
SelecctedValuePo = SelecctedValuePo.Substring(0, SelecctedValuePo.Length - 1)
cmd.Parameters.Add("@PoStr", SqlDbType.NVarChar).Value = (SelecctedValuePo)
End If
If ddlDistrict.SelectedIndex = -1 Then
cmd.Parameters.Add("@DistrictStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In ddlDistrict.Items
If li.Selected Then
SelectedValueDistrict = (SelectedValueDistrict + (li.Value + ","))
End If
Next
SelectedValueDistrict = SelectedValueDistrict.Substring(0, SelectedValueDistrict.Length - 1)
cmd.Parameters.Add("@DistrictStr", SqlDbType.NVarChar).Value = (SelectedValueDistrict)
End If
If ddlCDF.SelectedIndex = -1 Then
cmd.Parameters.Add("@UserStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In ddlCDF.Items
If li.Selected Then
SelectedValueCDF = (SelectedValueCDF + (li.Value + ","))
End If
Next
SelectedValueCDF = SelectedValueCDF.Substring(0, SelectedValueCDF.Length - 1)
cmd.Parameters.Add("@UserStr", SqlDbType.NVarChar).Value = (SelectedValueCDF)
End If
If lstStatus.SelectedIndex = -1 Then
cmd.Parameters.Add("@StatusStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In lstStatus.Items
If li.Selected Then
SelectedValueStatus = (SelectedValueStatus + (li.Value + ","))
End If
Next
SelectedValueStatus = SelectedValueStatus.Substring(0, SelectedValueStatus.Length - 1)
cmd.Parameters.Add("@StatusStr", SqlDbType.NVarChar).Value = (SelectedValueStatus)
End If
If ddlVillages.SelectedIndex = -1 Then
cmd.Parameters.Add("@VillageStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In ddlVillages.Items
If li.Selected Then
SelectedValueVillages = (SelectedValueVillages + (li.Value + ","))
End If
Next
SelectedValueVillages = SelectedValueVillages.Substring(0, SelectedValueVillages.Length - 1)
cmd.Parameters.Add("@VillageStr", SqlDbType.NVarChar).Value = (SelectedValueVillages)
End If
If lstIndicator.SelectedIndex = -1 Then
cmd.Parameters.Add("@QuestionStr", SqlDbType.NVarChar).Value = DBNull.Value
Else
For Each li As ListItem In lstIndicator.Items
If li.Selected Then
SelectedValueIndicators = (SelectedValueIndicators + (li.Value + ","))
End If
Next
SelectedValueIndicators = SelectedValueIndicators.Substring(0, SelectedValueIndicators.Length - 1)
cmd.Parameters.Add("@QuestionStr", SqlDbType.NVarChar).Value = (SelectedValueIndicators)
End If
cmd.Parameters.Add("@Individual", SqlDbType.Bit).Value = chekSegrigation.Checked
Using dsCustomers As New DsetPartner()
sda.Fill(dsCustomers, "DataTable1")
Return dsCustomers
End Using
End Function