Very new to ADO question
I'm having trouble and just getting started with ADO. I've been using DAO.
In this code:
sqlStr = "select * from photos where bookmark='" & item_number & "'"
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
I'm just trying to see if a record exists in a table. It is very slow and the query ends up timing out. My approach must be wrong. Any ideas?
In this code:
sqlStr = "select * from photos where bookmark='" & item_number & "'"
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
I'm just trying to see if a record exists in a table. It is very slow and the query ends up timing out. My approach must be wrong. Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
1st: You are correct, I should have only selected bookmark.
2nd: I only store the location of the photo, not the photo itself.
3rd. 160,000 records
4th: Yes, it is the PrimaryKey.
HooKooDooKu, you're solution worked. It was still much slower than I expected.
2nd: I only store the location of the photo, not the photo itself.
3rd. 160,000 records
4th: Yes, it is the PrimaryKey.
HooKooDooKu, you're solution worked. It was still much slower than I expected.
If it the PK it should take milliseconds
ASKER
This takes about 25 seconds:
sqlStr = "select bookmark from photos where bookmark='" & item_number & "'"
myRecSet.CursorLocation = adUseClient
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly
MsgBox "Total Number of records = " & myRecSet.RecordCount
sqlStr = "select bookmark from photos where bookmark='" & item_number & "'"
myRecSet.CursorLocation = adUseClient
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly
MsgBox "Total Number of records = " & myRecSet.RecordCount
Your problem might be the "myRecSet.RecordCount"
I remember that when we went from DAO to SQL, if we ever needed to get a record count, it was faster to run a separate query to specifically count the number of records that matched a particular query than it was to ask the record set how many records there were. I seem to also recall, to get an accurate count, you had to do a MoveLast (depending upon if the cursor was client or server side) before the did a .RecordCount.
Replace the MsgBox with a simple MsgBox "Records Found" and check your timing again.
I remember that when we went from DAO to SQL, if we ever needed to get a record count, it was faster to run a separate query to specifically count the number of records that matched a particular query than it was to ask the record set how many records there were. I seem to also recall, to get an accurate count, you had to do a MoveLast (depending upon if the cursor was client or server side) before the did a .RecordCount.
Replace the MsgBox with a simple MsgBox "Records Found" and check your timing again.
ASKER
Same thing, if I comment out:
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly
It's super quick.
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset, adLockReadOnly
It's super quick.
Try this:
sqlStr = "SELECT COUNT( bookmark ) AS COUNTER FROM photos WHERE bookmark = ""& item_number & "'"
myRecSet.CursorLocation = adUseClient
Call myRecSet.Open( sqlStr, MyConnObj, adOpenForwardOnly, adLockReadOnly)
MsgBox "Total Number of records = " & CLng( myRecSet.FieldValue("COUNT
sqlStr = "SELECT COUNT( bookmark ) AS COUNTER FROM photos WHERE bookmark = ""& item_number & "'"
myRecSet.CursorLocation = adUseClient
Call myRecSet.Open( sqlStr, MyConnObj, adOpenForwardOnly, adLockReadOnly)
MsgBox "Total Number of records = " & CLng( myRecSet.FieldValue("COUNT
ASKER
Compile Error: Method or data member not found on "FieldValue"
What you havent said or I missed it is what the backend is? Whats the database? Where is it? On the PC? On a local Server? On the internet?
MsgBox "Total Number of records = " & CLng( myRecSet!COUNTER)
Sorry, I'm looking at 10 year old code... FieldValue() was one of my functions (it encapsulated the logic to get the value from a DAO field, ADO field, or Oracel DB field depending upon which DB I had openned).
Does the MsgBox come up before 25 seconds now?
Does the MsgBox come up before 25 seconds now?
Secondly do you store images in the DB?
Thirdly how many records do you have?
Fourthly is the field Bookmark indexed?