Locate within a numerice range

jonb2 · Post by **jonb2** » Thu 29 Mar 2012 18:56

Hi Devart

I am using a connection between a geo-location data table - MyQuery and a VirtualTable. I need speed as there are 100,000 records and it's a loop. Each row has a start and end numeric range for a country.

I would like to do the equivalent of this SQL in VirtualTable using locate if it's the fastest way:

SELECT
iwin.geo.CountryCode,
iwin.geo.CountryName
FROM
iwin.geo
WHERE
404232216 >= iwin.geo.StartIPNum AND
404232216 <= iwin.geo.EndIPNum LIMIT 1

Please could you illustrate this, as I am having some difficulty. I am not even sure it's possible with VT to search for a row by range.

Secondly, shoudl I index these integer columns for faster performance -if so, how ?

Brain hurting

Kind regards

Jon

AlexP · Post by **AlexP** » Fri 30 Mar 2012 11:07

Hello,

The query specified by you will return the DataSet with one record as you use LIMIT. When using the Locate method, the cursor in the DataSet will be set in the record corresponding to the indicated condition, but the DataSet will contain all records; when using the Filter property, the dataset will contain only those records that fit the conditions of the filter, but if there are several of them, they will be present in the DataSet too. Currently the VirtualTable has no possibility to limit the number of records (LIMIT), but we are planning to implement this functionality in one of the following versions.

jonb2 · Post by **jonb2** » Fri 30 Mar 2012 11:57

Hi Alex

Thanks for this.

I did not necessarily want to emulate the SQL statement. Just copy it's function using Locate (or another). I can extract the fields I want from the row at the cursor (presumably) once it's been located.

I have sorted out how to assign the indexes to the range fields using IndexFieldNames for quick searching.

On the basis of the criteria - find a row based on a variable number between a lower and upper limit - is Locate the most efficient way of doing this ??

Kind regards

Jon

jonb2 · Post by **jonb2** » Fri 30 Mar 2012 15:14

OK people.

It seems that I can't use VirtualTable for this as there is no way to check for a number in a numeric range using filters or locate.

Unless someone tells me otherwise.

Jon

jonb2 · Post by **jonb2** » Mon 02 Apr 2012 10:19

Thinking about it ...

I think what is missing here is the killer feature =

Virtualtable needs an SQL interface, this indeed would make it very complete and 110% useful ?

Any plans ?

Kind regards

Jon

AlexP · Post by **AlexP** » Mon 02 Apr 2012 16:10

Hello,

Please explain in more details what you mean by "find a row based on a variable number'.

If you just need to find the rows in the range of values of a certain field, you can use the Filter property by setting its value like you set the Where condition in a SQL query.

VirtualTable1.Filter := 'EndIPNum>=404232216 AND StartIPNum <= 404232216';

For this, you can use the Locate method, since this method finds and sets the cursor at the record that promptly agrees with the values set in the Locate method.

jonb2 · Post by **jonb2** » Mon 02 Apr 2012 18:20

Hi Alex

Thank you.

You are right. I should have seen this, stupid of me, I was obsessing about Locate. But I have now tried it, but it is 10 times slower than using MyQuery against the DB directly. Not sure if there are any tricks to speed up filtering, I doubt it somehow. Probably always less efficient than an optimised SQL query cache.

I am looking at a way of creating local caches of static data that will be on a remote server. It strikes me that the way MyDAC is so speedy, I would be better off just having a local copy of MySQL and doing the whole thing in MyDAC and MySQL. MyDAC is so fast and dependable. The only thing is having to install and admin MySQL on each client is a pain.

Thanks again for pointing out the filtering option.

Kind regards

Jon

AlexP · Post by **AlexP** » Tue 03 Apr 2012 10:51

Hello,

To accelerate filtering in VirtualTable, you can try setting field names, by which data is filtered, in the IndexFieldNames property. In this case, firstly, the data will be sorted by specified fields, and than the filter will be laid on the already filtered data.
The difference in performance between a real query executed at a server and filter usage in VirtualTable can depend on various reasons: the capability of a local PC and a server, the type and capacity of data, tuned indexes, etc. Therefore it is rather difficult to compare such things

jonb2 · Post by **jonb2** » Wed 04 Apr 2012 08:03

Thanks Alex.

I think I might try another route, as I have tried your suggestions, all too slow. But until Virtualtable is a bit more versatile, it's not appropriate. My local caches have to be able to replicate most the manipulation abilities of a main DB.

Really appreciate your help though

Kind regards

Jon

AlexP · Post by **AlexP** » Wed 04 Apr 2012 09:13

Hello,

We are considering the possibility to implement "in memory SQL Dataset" with the support of the option to use the query language with the local data, however, we cannot specify the exact date of the implementation. You can vote for the support of this feature on our UserVoice page http://devart.uservoice.com/forums/1046 ... ql-dataset

Devart Forums