CODEWITHSUNDEEP
vb.netodbcdatareader
Stephen Archbold
Stephen Archbold

Reputation: 93

.HasRows() function on data reader taking forever

I have some simple code, an if statement checking the HasRows of a data reader and for some reason when I run my code in Visual Studio 2017 it takes forever to evaluate and return the answer (while writing this, my code has been running for 4 minutes). Any suggestions?

 Dim cmd As OdbcCommand
 Dim rdr As OdbcDataReader

cmd = New OdbcCommand("select GLPN,GLFY,GLDCT,GLDOC,GLCO,GLDGJ,GLANI,GLSBL,GLLT,GLCRCD,GLAA,GLU,GLGLC,GLEXA,GLICUT,GLR2,GLR1,GLSFX,GLOKCO" _
                      & ",GLEXR,GLODOC,GLPKCO,GLPDCT,GLCN,GLDKJ,GLVINV,GLIVD,GLPO,GLDCTO,GLLNID,GLTORG,GLAN8,GLICU,GLOPSQ,GLJBCD" _
                      & ",GLACR,GLABR2,GLABR1,GLDGJ,GLLT,GLCRCD,GLEXA,GLICUT,GLEXR,GLDKJ,GLIVD,GLAN8,GLICU,GLACR,GLKCO,GLSBLT,GLOBJ,GLSUB,GLJELN,GLEXTL,GLCRR,GLBCRC" _
                      & " from " _
                      & "PRODDTA.F0911 where GLPOST = 'P' and GLDGJ >= ? and GLDGJ <= ? and (GLLT = 'AA' or GLLT = 'CA') and GLDOC = 206940", cnn)
cmd.Parameters.Add("?GLUPMJs", OdbcType.Int).Value = todaysdate - 14
cmd.Parameters.Add("?GLUPMJe", OdbcType.Int).Value = todaysdate
cnn.Open()
cmd.CommandTimeout = 300
rdr = cmd.ExecuteReader

If rdr.HasRows() Then

    'Do a bunch of stuff

End if

Edit1: Still getting the funny issue but it I have noticed it's only in one spot, I have the "HasRows()" Check in multiple spots and it is working fast 3ms and such. it's only on the one query.

Edit2: The query I referenced above runs on SQL developer very fast total of 1.202 seconds last time I tried, it also returns no messages.

Edit3: I am wondering if it has something to do with the amount of fields I am returning, the other queries that run fast on this line are returning much smaller field counts.

Upvotes: 1

Views: 1472

Answers (4)

Stephen Archbold
Stephen Archbold

Reputation: 93

The reason for the delay on HasRows() is because the ExecuteReader didn't actually execute the query, it Defers the execution until it's needed. HasRows is the one executing it. thanks to Dwillis for pointing it out.

I tested this by putting a line of code ahead of it and this is where the delay sat and when hasRows ran it was fast.

Interestingly the same sql run in Sql Management Studio runes in 1.5 seconds returning all rows, yet in the Code it takes for ever which is a different issue but also may be due to ODBC settings.

Upvotes: 0

Scath
Scath

Reputation: 3824

This may not speed it up any but have you tried??

While rdr.Read()
'Do a bunch of stuff
End While

EDIT:

Dim dt As DataTable = New DataTable
dt.Load(rdr)
If dt.Rows.Count >= 0 Then
   'Do a bunch of stuff
End If

Upvotes: 1

Alicia
Alicia

Reputation: 864

It seems that you are not the only one having trouble with the HasRows method. In this post, the OP stated a problem with that method, although it is SQL-SERVER. But I think the solution might be useful, since both DataReaders inherit from the same class and they are closely related.

If you read the post that they reference in the question and the most voted answer, you'll see that they concluded that HasRows showed a buggy behavior when the SQL query execution returned not only some data results, but also a message. They ended up using an alternative way to check if there were any data on the data reader based on the Read() method, which proved to be more reliable.

In their case, the problem was that HasRows property was set to false when the reader actually contained data, but maybe it is related to your performance problem. You should definitely give a try to the Read method just to be sure that your query is not the problem.

P.S: another interesting link with a person reporting the same problem.

Upvotes: 0

ufo
ufo

Reputation: 685

Maybe the problem is that the query is taking a lot to be executed. If you are only interested in checking the existence of records you could perform a query with "SELECT TOP 1 1" and use ExecuteScalar instead of ExecuteReader.

Upvotes: 0

Related Questions