Function takes long time for execution

Actually, I am trying to find out wheather the partno is existing in the database table or not and it takes long time in count = checkCommand.ExecuteReader sataement

Please find the method below

Public Function CheckProductNo(ByVal Partno As String) As Boolean
    Dim count As SqlDataReader
    Dim valid As Boolean = False
    Using connection As New SqlConnection
         connection.Open()
        Using checkCommand As New SqlCommand("PartNo_check", connection)

            checkCommand.CommandType = CommandType.StoredProcedure
            checkCommand.Parameters.Add(New SqlParameter("@PartNo", Partno))
            count = checkCommand.ExecuteReader
            If count.ToString > 0 Then
                valid = True
            End If
        End Using

    End Using
    Return valid

End Function


STORED PROCEDURE
CREATE PROCEDURE PartNo_Check
@PartNo VARCHAR(30)

AS

SELECT PartNo from T_Product where PartNo=@Partno

Thanks 

Answers


It's important to be specific about what your code is supposed to do - looks to me like you're trying to determine if something exists, rather than its count or some other attribute. Towards that, I would modify your sproc as follows:

STORED PROCEDURE
CREATE PROCEDURE PartNo_Check
@PartNo VARCHAR(30)

AS

SET NOCOUNT ON;

SELECT  CASE WHEN EXISTS ( SELECT PartNo 
                           FROM     T_Product
                           where PartNo=@Partno) THEN 1
             ELSE 0
        END

Theoretically that should perform the best for what you're trying to do, and you should ensure there's an index on the ParNo column, as called out by Steve. Your method could then be written as:

 Public Function CheckProductNo(ByVal Partno As String) As Boolean
   Using connection As New SqlConnection
     connection.Open()
    Using checkCommand As New SqlCommand("PartNo_check", connection)
        checkCommand.CommandType = CommandType.StoredProcedure
        checkCommand.Parameters.Add(New SqlParameter("@PartNo", Partno))
        return convert.toboolean(checkCommand.executescalar)
    End Using

End Using

End Function

Supposing that your PartNo_check returns only a single numeric value, then try to use

Dim result As Integer = Convert.ToInt32(checkCommand.ExecuteScalar())

With the stored procedure code given then use

Dim result As String = checkCommand.ExecuteScalar().ToString()

If your stored procedure returns only one row with one column the ExecuteScalar take that unique result as its return value. Additional columns or rows are ignored

See MSDN references


I would try and debug the problem from the bottom up; You can check if the stored procedure is taking too much time, by querying it in SQL management tool of some kind.

If that is the case, my first resolution would be to optimize the database, check your indexes etc.


If your goal is to acknowledge if there is data for Partno then I suggest you to modify your query to :

STORED PROCEDURE
CREATE PROCEDURE PartNo_Check
@PartNo VARCHAR(30)

AS

SELECT Top 1 PartNo from T_Product where PartNo=@Partno

then you will have to

count = checkCommand.ExecuteReader
If count.HasRow then valid=true

this is for your problem then, but if you want to manipulate any data related, this would not be a good solution.

Hope this works as fast as you wish!


Need Your Help

How to start a basic WebRTC data channel?

javascript webrtc

How to start a basic WebRTC data channel?