Does this make sense?
tblProduct_.Locate('vendorproductid', sData, []);   takes 0.011 seconds

tblProduct_.Locate('vendorproductid;vendorid;storeid', VarArrayOf([sData, 39, 2]), []); takes 0.491 seconds

MySession.Execute('SELECT * FROM product WHERE storeid=2 and vendorid=39 and vendorproductid=' + QuotedStr(sData )');  takes 0.012 seconds

Why is the locate with multiple fields not optimized?

Greg,

<< tblProduct_.Locate('vendorproductid', sData, []);   takes 0.011 seconds

tblProduct_.Locate('vendorproductid;vendorid;storeid', VarArrayOf([sData, 39, 2]), []); takes 0.491 seconds

MySession.Execute('SELECT * FROM product WHERE storeid=2 and vendorid=39 and vendorproductid=' + QuotedStr(sData )');  takes 0.012 seconds

Why is the locate with multiple fields not optimized? >>

Locate requires that there be an index available that satisfies all fields being searched on, from left to right.  If you want something that is similar to the query version, you can use a filter or the FindFirst method (check the Delphi documentation because the Find* methods are all base TDataSet methods).

Tim Young
Elevate Software
www.elevatesoft.com

Well that is a little disappointing as I have become quite used to doing that in ADS.  I like the way the logic flows in the code.  It would also appear that when I do a simple locate on a query result it also doesn't use the index if it exists on the master table.  Most of this can be remedied with more use of queries so that is OK.

Is it difficult to implement or was ADS the exception where most others don't do this either . . .

Greg,

<< Well that is a little disappointing as I have become quite used to doing that in ADS.  I like the way the logic flows in the code.  It would also appear that when I do a simple locate on a query result it also doesn't use the index if it exists on the master table. >>

If a query is set up to request a sensitive result set, then any indexes in the table getting queried will be used for Locate calls:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Result_Set_Cursor_Sensitivity

For non-sensitive queries, simply having an ORDER BY clause on the columns that you want to search on will get you an index that can be used with Locate operations on the non-sensitive result set.

<< Is it difficult to implement or was ADS the exception where most others don't do this either . . .
>>

It's a matter of using the right tool for the job, and I try not to mix different concepts too much in EDB.  If you want to perform a direct search, then you should probably make sure that you have an index that will support such operations and use FindKey or Locate.  If you want to search using conditions and aren't sure of the underlying indexes, then your best bet is to use something more expressive like a filter (same as SQL for conditions) or a SQL SELECT statement and let the filter/query optimizer do its thing.

Having said that, I'll see what I can do.  Whatever I do, though, it won't be as smart as a filter or SQL because that involves a lot more optimization and decision making.

Tim Young
Elevate Software
www.elevatesoft.com

<<If a query is set up to request a sensitive result set, then any indexes in the table getting queried will be used for Locate calls: >>

That statement doesn't match my results.  I have a database that has many tables that are shared by many stores using my application.  There is a field called "storeID" that defines the owner of the information.  Therefore many times I may start with a query like:

 SELECT * FROM product WHERE storeID=2

Later I may have to do qryProduct.locate('vendorproductID', sData, []) and this operation is quite slow even though there is a simple index on VendorProductID.  I can tell as there is about 60,000 records in the store I am testing right now, so the locate takes just under a second.  If I add "ORDER BY vendorproductID" to the query I still get a sensitive cursor and my locate will be faster (.1s).  Although better it is still 10X slower than a query (.01s).  I have switched the problem section to using queries so it is all good for this particular example.  Unfortunately for me it is an issue I will come across many times as it was used extensively in existing ADS applications.

gbh100


Those times say to me its doing a brute force scan with the vendorproductID you want at the end of the unsorted query. Did you ask for a sensitive result set and did you check to see if you got one?

Roy Lambert

Yes it is a sensitive query.  My test actually writes to it as well.

Greg,

<< That statement doesn't match my results.  I have a database that has many tables that are shared by many stores using my application.  There is a field called "storeID" that defines the owner of the information.  Therefore many times I may start with a query like:

 SELECT * FROM product WHERE storeID=2

Later I may have to do qryProduct.locate('vendorproductID', sData, []) and this operation is quite slow even though there is a simple index on VendorProductID. >>

Please email me the database catalog and I'll tell you what's going on.  There can be some wrinkles with case-insensitive collations that can affect index usage with locates, depending upon how the column collation is defined vs the index column collation.

Also, whenever EDB has to perform a locate on the non-active index, it will need to perform *two* searches, one on the index and then one to re-position to the same row using the active index, and this can take time, especially if the second search is a search on a filtered table (sensitive queries are effectively just filtered tables).

Tim Young
Elevate Software
www.elevatesoft.com