xsharp.eu

This might be partly O.T. although this topic as about use of dbf or not as well.

We use dbf (almost) exclusively combined with ADS. Now we are slowly starting to use SQL queries to retrieve data, show bBrowsers, etc. ADS does support this, although they have their own interpretation of SQL, this is common I am afraid (MySQL is also different partly, from e.g. Microsoft SQL).

We found that e.g. a search action using a SQL query through a large dbf/cdx can be literally many times faster (I 've seen an estimated 5x faster) than using an optimized VO filter or likewise. I really wonder how this is possible. After all, we use the very same DBF, CDX and ADS.

Dick

Hi Dick,
I'm using SQL on ADS for a long time now, and it is ways faster.
That may be because the ADS Server can keep the indexes in memory and work from there.
From X# I'm using ADS not with the RDD, but with the ADO.NET driver.
Wolfgang

Dick,

ic2 wrote: We use dbf (almost) exclusively combined with ADS. Now we are slowly starting to use SQL queries to retrieve data, show bBrowsers, etc. ADS does support this, although they have their own interpretation of SQL, this is common I am afraid (MySQL is also different partly, from e.g. Microsoft SQL).

The ADS SQL is quite close to MS-SQL (which is even closer to vfp SQL - there newer idioms like UPSERT were not introduced).
DB2 used to be close to Oracle, but there you can set dialects by now. MySQL/MariaDB are IMO the ones doing their very own thing...

We found that e.g. a search action using a SQL query through a large dbf/cdx can be literally many times faster (I 've seen an estimated 5x faster) than using an optimized VO filter or likewise. I really wonder how this is possible. After all, we use the very same DBF, CDX and ADS.

Easy to explain: ADS has an optimizer working veeery similar to vfp Rushmore optimizer, which automatically kicks in if some indices can be used to minimize # of records loaded for SQL Joins and all filtering (SQL where and xBase FOR. There should be quite a few descriptions of Rushmore working in old conference notes on vfp nowadays to be found on the net - might explain better what is happening. They also support binary indices - which reduce index size often by 80%, which is esp. good as index file has to be read across LAN in advance. They have a setting similar to vfp sys(3054) showing optimization level - nothing like EXPLAIN of full blown server, but you understand what is happening and sometimes realize an easy way to optimize the operation.

HTH
thomas

Hello Thomas.

Very interesting what you write.

However, I keep thinking that when the ADS SQL technology is so fast and efficient that they probably should have used that as underlying technology in the "non SQL" data commands as well, to achieve the same speed.

Dick

Hi Dick,
why do you think the filter in ADS is as fast as it is? For sure it does not a serial read of the table like the "normal" RDD does.
For some customers that need filters often I have implemented a filter cache in memory (for those without ADS of course).
Wolfgang