CODEWITHSUNDEEP
sqlvb.netms-accessoledb
Prime
Prime

Reputation: 140

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

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

Upvotes: 0

Views: 174

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

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.

Upvotes: 1

Related Questions