Why is this code so SLOW?!?!?!?!

[Marcus]

I have a a VB.Net app using an Oledb data connection to an Access 2000
database. There are three tables that I am using in a SELECT query:

- ReadProps (2025 records, 5 fields)
- Devices (511 records, 27 fields)
- ReadPropEnums (3060 records, 6 fields)

When I run the following SELECT statement (that returns 500 records)
directly in Access, it takes less than one second to execute:

SELECT ReadProps.RPID, ReadProps.DeviceID , ReadProps.RPname,
Devices.Label FROM ReadProps INNER JOIN Devices ON ReadProps.DeviceID
= Devices.ID WHERE ReadProps.RPID NOT IN (SELECT RPID FROM
ReadPropEnums)

However, when I run my VB.Net code, the filling of the datatable in
the line "da.Fill(dt)" takes about 15 seconds! What am I missing
here?

Here is my VB.Net code:

'--------------------------------------------
Dim conn As System.Data.OleDb.OleDbConnection
Dim connStr As String
Dim sqlSelectString as string
Dim da As OleDbDataAdapter
Dim cmd As OleDbCommand
Dim dt As DataTable

sqlSelectString = "SELECT ReadProps.RPID, ReadProps.DeviceID ,
ReadProps.RPname, Devices.Label FROM ReadProps INNER JOIN Devices ON
ReadProps.DeviceID = Devices.ID WHERE ReadProps.RPID NOT IN (SELECT
DISTINCT RPID FROM ReadPropEnums) "

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:
\test.mdb;Jet OLEDBatabase Password=123;"
conn = New System.Data.OleDb.OleDbConnection(connStr)
cmd = New OleDbCommand(sqlSelectString, conn)
da = New OleDbDataAdapter(cmd)
dt = New DataTable
conn.Open()

'filling this datatable below takes about 15 seconds! It is also the
same
da.Fill(dt)
'--------------------------------------------

By the way, I get the same poor performance using a OleDbDataReader:

Dim reader As OleDbDataReader
Dim myCmd As New OleDbCommand(sqlSelectString , conn)
reader = myCmd.ExecuteReader
While reader.Read
c1 = c1 + 1
End While

Thanks for any help.

Marcus

 

[William \(Bill\) Vaughn]

Is the JET database file on the same system or on a network share? Consider
that when you open a connection to a JET database you're doing physical IO
over the wire if the database is on a share. Have you defined indexes for
the tables or is the engine having to read the entire database several times
to get the JOINS completed? What else is running on the system? How many
other users are contending for the same database file?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Marcus]

Thanks for your feedabck William. The Access database is on the same
machine where my code it located. I think I have figured it out. When
I said that the query ran in under one second in Access directly, I
was wrong. The page of results was coming up in that time frame, but
by paging down through the result set it was taking some time to
return the results for each page. I thought that once I saw one page
of results in Access that meant the whole resultset was retrieved...
not so.

Apparently Access doesn't like the the inner SELECT that I have
embedded in the query. When I replace that with a hardcoded string of
all the RPIDs that the inner select would have returned, then it runs
very fast. I guess Access is not "smart" enough to do this inner query
first??? Anyway, I am satisfied with this.

Cheers,
Marcus

 

[Miha Markic [MVP C#]]

Yep, Access cheats when it shows result.
And as per your inner join it probably depends on keys and the structure of
sql statement.