Why query take 1+ sec.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Henning - 30 Jan 2007 02:37 GMT

Hi, me again

Can someone plz tell me why this query takes over 1 sec. The table has some
4000 records, and 72 columns.
Or is it concidered 'normal'?

In Module1.bas

Public conn As New ADODB.Connection

Sub DBConnect()
conn.Open "DSN=MyODBC3"

End Sub

Sub GetRentKvitt(nr As Integer, stru As kv_Rent)

Dim strSQL As String
Dim rsPtr As New ADODB.Recordset
Dim i%, j%
Dim tid As Single

strSQL = "SELECT * FROM kvrent WHERE kvnr= " & nr & ";"
Set rsPtr.ActiveConnection = conn
rsPtr.CursorLocation = adUseServer
rsPtr.CursorType = adOpenStatic
rsPtr.Source = strSQL
tid = Timer
rsPtr.Open '***this takes 1+ sec.
tid = Timer - tid
Debug.Print tid

.....
Fill a UDT with Field values
....

rsPtr.Close
Set rsPtr = Nothing

End Sub 'GetRentKvitt()

Signature

Time is present only to prevent everything from happening at once.
Still it seems that everything happens at once.
Then there must be a bug in time.
To find the bug in time, isn't that what we all hope for.

Reply to this Message

Jan Hyde - 30 Jan 2007 09:24 GMT

"Henning" <computer_hero@coldmail.com>'s wild thoughts were
released on Tue, 30 Jan 2007 03:37:46 +0100 bearing the
following fruit:

>Hi, me again
>
[quoted text clipped - 27 lines]
>tid = Timer - tid
>Debug.Print tid

First, your pulling back every single field for every
record, it's better to pull back only the fields you need.

Secondly, is 'kvnr' an indexed field? If it's not then every
record in the database must be processed.

J

>.....
>Fill a UDT with Field values
[quoted text clipped - 4 lines]
>
>End Sub 'GetRentKvitt()

Jan Hyde (VB MVP)

Signature

Foresight: Spectacles are (Jan Hyde)

Reply to this Message

VB Forum / Database Access / January 2007

Why query take 1+ sec.