"Marcus" <holysmoke***@hotmail.com> wrote in message
news:1171929262.484822.83750@h3g2000cwc.googlegroups.com...
>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 OLEDB:Database 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
>

> 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 Speakerwww.betav.com/blog/billvawww.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.
> __________________________________
> Visitwww.hitchhikerguides.netto 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" <holysmoke***@hotmail.com> wrote in message
>
> news:1171929262.484822.83750@h3g2000cwc.googlegroups.com...
>
> >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 OLEDB:Database 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

"Marcus" <holysmoke***@hotmail.com> wrote in message
news:1171993580.018559.132600@q2g2000cwa.googlegroups.com...
> 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
>
> On Feb 19, 7:10 pm, "William \(Bill\) Vaughn"
> <billvaRemoveT***@nwlink.com> wrote:
>> 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 Speakerwww.betav.com/blog/billvawww.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.
>> __________________________________
>> Visitwww.hitchhikerguides.netto 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" <holysmoke***@hotmail.com> wrote in message
>>
>> news:1171929262.484822.83750@h3g2000cwc.googlegroups.com...
>>
>> >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 OLEDB:Database 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
>
>