Danish Habib

Danish Habib

  • NA
  • 694
  • 244.2k

Stored Procedure taking longer time in code then in sql

Nov 10 2015 2:16 AM
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.
below my code

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

 

Answers (2)