ADS : DbSetFilter VS SQL Statement?

[dutch]

Dear All,

I use ADS but never use with SQL Statement.

How does it different speed between DbSetFilter and ADS with SQL Statement?

May I have an example to use SQL Statement to open file with ADS?

Do I need to use and Index file for SQL Statement? If so, how can I specific the index tag in SQL Statement?

Thank you in advance for any help and idea.

Regards,
Dutch

[reinaldocrespo]

Hi.

You must connect to an ADS data dictionary. The SQL engine is smart enough to know which indexes or combination of indexes based on the metadata on the DD should be used to produce an optimized result. No need to open tables or specify index tags.

For example, provided that you have an index based on field state and field lastname, then sql below is really fast:

SELECT * FROM customers WHERE lastname = 'Smith' AND state = 'CA'

That statement will return a cursor (like a table) with all the columns on table customers where the customer lastname field is Smith and state field contains CA. Execution happens ont he server side and the client side only receives a result set or cursor that points to the result set on the server. This makes execution really fast. Even without indexes the statement above can return a result set in less than 5 seconds when searching over 250k records, depending on the server resources. Again, the speed gains are the results of not having data travel back and forth between the client and the server in order to produce a result.

To learn how to create a data dictionary, see the examples I've posted:
viewtopic.php?f=3&t=23962&start=22

Notice that I posted a few different samples on how to create a data dictionary, tables, and indexes using regular clipper syntax as well as SQL. Just follow that same thread.

It takes some practice, but at the end you will see the benefits of using a DD other than just learning SQL.


Reinaldo.

[AlexSchaft]

We are doing queries on dbf/cdx files without a dictionary. The only thing to notice is that any condition like "Where Name = 'Smith'" needs an index on Name without a condition

The process is basically:

Code: Select all  Expand view

#include "ads.ch"

Adsconnect("c:\pathtodata")

select 0
if adscreatesqlstatement("MYALIAS", ADS_CDX)
  if !adsexecutesqldirect(pcSQL)
      Msginfo("Ads statement execution failed:" + DNL + AdsGetErrorString(AdsGetLastError()), "SQL")
      AdsShowError()
      lSqlOk := .f.
  else
      lSqlOk := .t.  
  endif
else
  Msginfo("Ads statement creation failed:" + DNL + AdsGetErrorString(AdsGetLastError()), "SQL")
  AdsShowError()
  lSqlOk := .f.
endif

if lSqlOk
  ("MYALIAS")->(DoStuff())
  ("MYALIAS")->(DBCloseArea())
endif
 

HTH,
Alex

[Otto]

Hello Reinaldo,
I read your post and then I made a test with pure DBF.
This is what I did.
Database with 124 fields 280.000 records 500 MB.
I simulate your Select statement with a temporary index.
In the screen shot you see the technical data of my RDP – Server.
I get the result within a 1 sec.
Best regards,
Otto

05/30/12 10:33:36: Start
05/30/12 10:33:37: nach Index
05/30/12 10:51:50: Start
05/30/12 10:51:50: nach Index
05/30/12 10:51:58: Start
05/30/12 10:51:58: nach Index
05/30/12 10:52:08: Start
05/30/12 10:52:08: nach Index

Code: Select all  Expand view

function test_tempIndexXbrowse()

    use kunden new
    logfile("speedtest.txt",{"Start"} )
    index on kunden->Name + kunden->ort  TAG TmpB5Nr TO TEMP5 TEMPORARY
    set order to tag TmpB5Nr
    logfile("speedtest.txt",{"nach Index"} )
    xbrowse( "kunden" )
   
    use
return nil
//----------------------------------------------------------------------------//
 

[reinaldocrespo]

We are doing queries on dbf/cdx files without a dictionary. The only thing to notice is that any condition like "Where Name = 'Smith'" needs an index on Name without a condition
Alex

Alex;

True, you may execute SQL without a DD connection and still take advantage of indexes, but that's only true with compound indexes (cdx, adi). NTXs will not be used by the SQL engine since there is no way for the sql parser to be aware of those indexes. However, provided that you do have a dd created, then cdx, adi, and ntxs are used.

Otto;

The performance on the RDP server should be pretty much the same than on a client-server situation such as ADS, since the operation executes on the server and data does not have to flow to the workstation. Still ADS allows you to set certain tables as memory tables so that consultations to those tables are even faster. However speed is not the only consideration or benefit when considering an sql engine. Let me show in more detail some other benefits you get:

1. Triggers -You write triggers to perform validation or to perform additional tasks in response to a data operation. For example, a trigger can be used to generate a unique customer ID each time a new record is being inserted into the customer table. Likewise, a trigger can be used to write to a log table each time a change is made to a customer record. This type of operation is usually called an audit trail. I use a trigger to keep an audit trail on critical tables. I also use a trigger to create a new entry on a queue of messages to be send via email every time a pathology result is signed. The trigger is executed on the server and by the server and it will execute when the appropriate action takes place even if the table is being accessed from some other application other than your own.

2. Constraints and referential integrity. When managing many tables from many different apps it can become a nightmare to avoid orphan records on a parent child table relationship. Constraints are data dictionary based rules that Advantage can use to validate your data as it is being posted to a table. Referential integrity is a special type of constraint that ensures the data residing in two or more related tables remains consistent. You can have a RI rule to either restrict changing or to perform a cascade change of a foreign key when a primary key is changed or the record is deleted. Field constraints as well as record level constraints will ensure your data is consistent. I could enumerate a long list of real life applications for RI and constraints, but I'm going to leave it a that.

Notice that #1 and #2 are just some of ways to remove some of the business logic from the client side and moving it to the server side, never to be a concern to the developer ever again.

3. Hot-Backups. With ADS (and I suppose with any other sql engine as well), you can perform a backup of the data even while the tables are open and being used. What it does is that it creates a snap shot of the db at the moment the backup got started it.

4.Using Php, .net, perl, ADO, MDAC, OLE DB and other apps with your same data. Many times you need provide access to certain information from a browser such as ie. With Advantage you can have your main apps written as win32 with fw and still also have a php web portal where users can access certain information or all of it. Advantage also provides web services via oData standard where apps written for the iphone, android, or windows iphone can access the same dbfs that your win32 apps does. I write php web pages for my customer's customers. In this way they can access pathology results for tests they sent to the lab from any web browser anywhere.

5. Replication - With Advantage you can have many servers connected via an internet connection replicating the whole db or just some tables such that an entry in one server is visible on another server anywhere in the world. Replication can work nway, as a spoke wheel and in any dierection.

6. Stored procedures. You can have some procedures stored on the server for faster execution.

7. SQL scripting language.

8.Data security. You don't need to create a share in order to provide access to tables. The tables may reside on a directory that ins not visible to any user. A user can not copy a dbf to a pendrive or to a local drive. A user can not delete (by accident) a .dbf. The user simply does not know what the data looks like or where is it stored. Plus you can add data encryption as well as communication encryption and a lot more.

I'm going to stop here. What I mean by this is that an SQL engine opens up a whole new world we were not aware of. RDP will work great with ADS. AMOF - I use RDP for some users, but always with ADS running on the server.


Reinaldo.

[Otto]

Reinaldo,
you are right in every point.
What I want to say is that RDP offers now possibilities we didn’t had before. And we should evaluate if we really need a client/server solution.
Till one understands and feels comfortable with “client server“ programming he has to learn much.
Then there are also some disadvantages like record looking which you have to care yourself I mean if you use SQL maybe ADS handles this automatically and the whole story of real – data browsing up and down.
If you browse a record set in practices you are browsing an array and without re reading you can’t say if data is actual.
BTW, we are setting up a VPN Server. I will document all the steps we take and I open a thread in the forum. I will invite all to help me to find the best setup (security!) for such a server and how we could maintain our software (updates, etc.).
So far we have a 17 points long installation guide (hiding directories, access rights, ... ). This should become a demo facility which we then suggest to our customers. VPN access is for our customers an option but the main work is done inside the LAN but always remote.

I have ordered a TB Link SafeStream™ Gigabit VPN Router. When I have it here I start and I will give you if you want access to my demo VPN Server.

Please keep in mind that I do not have to handle such sensible data like you (pathology results) . Some editions are download versions. The installation should be easy and the maintaining of the systems too.
For example a typical installation for a café: 1 ECR and up to 4 PDAs. Or a small hotel: front office, back office and a notebook.

If you use RDP you do not have to create a share. We set up a Remote Desktop user and we define which programs he is allowed to use. If he closes the program the remote session is closed automatically.
I think client/Server was developed because there was a limitation in data transfer in net. RDP solves this.
I developed my own client /server solution for my mobile application. But now the application runs on the server and the PDA is only working remote. So all this is vacant because I work local. If the connection breaks I reconnect that’s it.
You can’t imagine how many checks we inserted in this FWPPC solution: we were able to check the WIFI signal then we made the transfer. It is working but not as rock solid as the RDP solution.
The discussion and the test I made today helped me to improve my software. As I used the database with the 280.000 records I notices a speed problem which with my usually databases (10.000-20.000 records) was not noticeable.
The query took about 1 min. So I had a look into the code and I inserted a TEMPORARY index and now the speed is 1 sec.

Code: Select all  Expand view

//building the query string  

   oKustr:gotop()
   wahl := ""

   do while .not. oKustr:EOF()
   
   
      if len(trim(oKustr:von)) > 0
         if oKustr:typ="C"
            if len(trim(wahl))=0
               wahl="Upper("+trim(oKustr:feld)+") >= "+chr(34)+trim(Upper( left(oKustr:von,oKustr:LNG)   ))+chr(34)
            else
               wahl = wahl+" .and. Upper("+trim(oKustr:feld)+") >= "+chr(34) + trim(Upper( left(oKustr:von,oKustr:LNG) ))+chr(34)
            endif
         endif

         if oKustr:typ = "N"
            if len(trim(wahl))=0
               wahl=trim(oKustr:feld)+">="+trim(oKustr:von)
            else
               wahl=wahl+" .and. "+trim(oKustr:feld)+">="+trim(oKustr:von)
            endif
         endif

         if oKustr:typ="D"
            if len(trim(wahl))=0
               wahl=trim(oKustr:feld)+">=ctod("+chr(34)+trim(oKustr:von)+chr(34)+")"
            else
               *  wahl=wahl+" .and. "+trim(oKustr:feld)+">=ctod("+chr(34)+trim(oKustr:von)+chr(34)+")"
            endif
         endif
      endif

      if len(trim(oKustr:bis))>0
         if oKustr:typ="C"
            if len(trim(wahl))=0
               wahl = "Upper("+trim(oKustr:feld)+")<="+chr(34)+trim(Upper( left(oKustr:bis,oKustr:LNG) ))+chr(34)
            else
               wahl=wahl+" .and. Upper("+trim(oKustr:feld)+")<="+chr(34)+trim(Upper( left(oKustr:bis,oKustr:LNG) ))+chr(34)
            endif
         endif
         if oKustr:typ="N"
            if len(trim(wahl))=0
               wahl=trim(oKustr:feld)+"<="+trim(oKustr:bis)
            else
               wahl=wahl+" .and. "+trim(oKustr:feld)+"<="+trim(oKustr:bis)
            endif
         endif
         if oKustr:typ="D"
            if len(trim(wahl))=0
               wahl=trim(oKustr:feld)+"<=ctod("+chr(34)+trim(oKustr:bis)+chr(34)+")"
            else
               wahl=wahl+" .and. "+trim(oKustr:feld)+"<=ctod("+chr(34)+trim(oKustr:bis)+chr(34)+")"
            endif
         endif
      endif
     
     
     
      oKustr:skip(+1)
   ENDDO
   
   
   
   
    select TempKunden
 //new  
   index on TempKunden->name  TAG TmpB5Nr TO TEMP5 TEMPORARY;
   FOR &wahl
   nTotal := ordKeyCount()
   
   msginfo( nTotal )
   
   
   select TempKunden
   set order to tag TmpB5Nr
   go top

   do while .not. EOF()

/*  code I used befor
if &wahl

endif
*/
   skip
   enddo
 

Best regards,
Otto

[reinaldocrespo]

Otto;

On the subject of RDP, I'm using a product called WinFlector. It is written with xharbour. My experience as a whole has been positive but I never use it for more than 3 users.

What are you using? Can you compare it to Winflector?

Thank you,


Reinaldo.