How do i speed up this SQL query for an Access DB Using OLEDB

Question

I am attempting to retrieve one field of one row in an access database. The current setup i have takes around 100 ms. Is there a faster way for a single field?

    Public Function GetEntityConfig(ByVal pintID As Integer) As aadXML.XMLDoc
    Dim objRtn As New aadXML.XMLDoc, myreader As OleDbDataReader


    Dim Query As New OleDbCommand("Select e_config from entity where e_id = " & pintID, CurrentActiveConnectionToDB)
    If CurrentActiveConnectionToDB.State = False Then
        CurrentActiveConnectionToDB.Open()
    End If
    myreader = Query.ExecuteReader()

    myreader.Read()
    objRtn.LoadXML(myreader.Item("e_config"))
    myreader.Close()
    GetEntityConfig = objRtn

    CurrentActiveConnectionToDB.Close()
End Function

Gordon Linoff · Accepted Answer · 2019-12-05 17:38:30Z

1

First, you should learn to use parameters, rather than munging a query string with input values.

Your query is:

Select e_config
from entity
where e_id = ?

For this query, an index will speed it up:

create index idx_entity_e_d_e_config on entity(e_id, e_config);

You don't have to include e_config in the index; it is helpful but only marginal.

answered Dec 5, 2019 at 17:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@Prime . . . You didn't find that this helped?

Collectives™ on Stack Overflow

How do i speed up this SQL query for an Access DB Using OLEDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related