Choosing SQL database ...

[reinaldocrespo]

dedicated servers, the imposibility to hire a host with it, so little base knowledge in comparison with MYSQL, SQLITE, MS SQL, ORACLE, POSTGRES etc etc.and a performance not superior to any SQL engine, cause I don't think you can compare it to MYSQL SPEED or SQL SERVER.

What Adolfo is referring to here is for hosted web apps with hosted data. But, you can have your web app being hosted and connecting to your ADS server over the Internet (i.e. non hosted data). It works just as well. The reference Adolfo is making is the typical Web-App and very popular using php-MySql. So most hosting companies already have MySQL with PHP available for your webapp and hosted data. I think that if I was to write an app that will never be used on a local lan but that will always be used on a browser connected via Internet to a hosted server... I'd probably use php-MySql. Not fwh. I'm not saying that you can't write it with fwh. It's a matter of what's best for the job.

As it turns out, all my customers work their db in a local lan with their own server (as I'm sure most of you). They only want to share small portions of the data (such as pathology reports and other lab results) over the web. So for that, I write a php app that queries the very same ADS server being accessed by the fwh apps on the lan.

MS-SQL and Oracle are more expensive than ADS. AFIK -MYSQL is not really free when used commercially. But pricing is not the point. If you really want to talk pricing, then you will have to ask for a local quote. What's expensive for some, might not be for other apps. My point is that ISAM is better than SQL for some tasks. Adolfo's sample proves it. And that ADS does both; ISAM and SQL, and triggers, and referencial integrity, and "on-line" backups, and views, and stored procedures, and table -or- field encryption, and field constraints, and transactions, and replication, ... let me catch my breath for a minute...., and allows Internet connections (AIS), and SQL scripting, and -did I mention ISAM?, and has clients for .net, Delphi Tdecendand class, (x)harbour RDD, VO RDD, PHP, ODBC, ADO, DBI, Perl, MDAC, OLE DB. I ran out of breath again.

I understand how some people might be turned off by ISAM -DBFs or ADTs. I avoid mentioning "DBFs" to my customers. But that is only due to perception and not real facts. Like I said, there are advantages with client-server technology and SQL. But if you can have that and also keep ISAM. Trust me, it is you now, who has the advantage.

About pricing for the client-server version, we can talk as a separate subject. But, yes Adolfo does the right thing by bringing it up, it is a consideration you'd have to keep in mind.

Now I'm talking too much, gota go!




Reinaldo.

[James Bott]

Adolfo,

No James.. not the whole table.. just the recordset ( select id, name, address from clientes) not ( select * from clientes ) because I have tables with more than 50 fields, most of them codes or ids to other tables.

OK, by "whole table" I meant all the records of the table. Granted in your case you are only reading certain fields, which means the network traffic will be less, but still way more network traffic than browsing a DBF.

Example: Assuming we have a 10 record browse (using a DBF) and did an incremental search that took 3 keystrokes, then the most records we would have to read is 3 x 10 = 30.

Use a SQL recordset we have to read all the records in the table regardless of how many keystrokes it takes to find the seach item. For small table this may not be much of an issue, but with large tables you might have to read a million records.

Even if we look at the issue considering fields it is going to be more network traffic using a recordset. Lets say there are 100 fields per record and you are only using 3 for the recordset, you still have to read 3 fields x 1 million records = 3 million fields.

With the DBF browse finding the record in 3 keystrokes, you have to read (at most) 30 records x 100 fields or 3000 fields. This is way less network traffic than SQL.

Here is another point that came to mind. I don't think any records actally exist in the application's memory when using DBFs. The data remains in the disk cache. When you read a field, like customer->name, it is read from the disk cache. There is no built-in recordset or array of data in the application.

Regards,
James

[Enrico Maria Giordano]

With the DBF browse finding the record in 3 keystrokes, you have to read (at most) 30 records x 100 fields or 3000 fields.

Why 30? It seems to me that the number of records to read depends on the actual data.

EMG

[James Bott]

Enrico,

>Why 30? It seems to me that the number of records to read depends on the actual data.

Upon rethinking this it would actually be 40 counting the first display of records before the first keystroke.

So the first display is 10 records. After the first keystroke you jump to the first record starting with that key and read the next 10 records. Then the second key and the next 10 records, and the third key and the next 10 records. That is a total of 40 records.

There would always be a maximum of 40 records read regardless of the size of the database.

We are assuming that it requires 3 keystrokes for this discussion. Granted it would take less records if you found the item in less than 3 keystrokes or more records if it required more keystrokes. We are also assuming you are using an index and doing a seek, not skipping through the records sequentially looking for a match.

Regards,
James

[Enrico Maria Giordano]

I don't understand. If I'm not missing something, 3-keys indexed incremental searching should requires the read of a maximum of 3 records, plus the read of the index tree, of course. Or am I wrong?

EMG

[James Bott]

Enrico,

>I don't understand. If I'm not missing something, 3-keys indexed incremental searching should requires the read of a maximum of 3 records

I am also considering the browse showing 10 records. So the first keystroke uses the index to find the first match, then that record is displayed as the first record in the browse. Then the browse will skip through 9 more records and display each one (until the browse is full). The second keystroke causes the next 10 records to be read and displayed. Is that more clear?

James

[Enrico Maria Giordano]

Yes, thank you. You are right.

EMG

[Adolfo]

James..
Enrico

I'm preparing a test with a Network Traffic tool we have here, in order to analyze the traffic in both cases, a browse of a dbf with 300.00 records and a Mysql Table of the same size.
My problem is trying to isolate only the traffic for both cases, port 3306 is completely checked, but I have problems with the Network access to the File server. Once I got this working I'll post my results here.
I think this weekend, with noone here in the office ( we are almost 30 Pc's online ) I'll do the test. Besides I'll have to stop all the services which uses the net card, and my cache tool.

I'll do the same keystrokes in the Xbrowse to show 4 differents clinets, and record what's going on in the net.

I Hope it helps to clarify me what really happens in both cases.

From Chile
Adolfo

[Enrico Maria Giordano]

Sounds interesting. Thank you, Adolfo.

EMG

[Adolfo]

Enrico...

There will be 4 tests..

DBF, SQL, DBF plus index, SQL plus index.. would be nice to know how the use of an index will affect the traffic... don't you think so ?

From Chile
Adolfo

[reinaldocrespo]

Adolfo;

Please, also compare speed. Go to the bottom of the list by pressing Cntrl-End, then press and keep pressed the uparrow key for a while. Watch the records scroll. After a while, Go to the top, do the same in the opposite direction. Then try incremental search.

Also, please test both using a server on the LAN or a single computer with local data.


Thank you,



Reinaldo.

Ps. Please send me the code. I'd really like to see how to use your class. Thank you very much: reinaldo punto crespo en gmail.

[Enrico Maria Giordano]

There will be 4 tests..

DBF, SQL, DBF plus index, SQL plus index.. would be nice to know how the use of an index will affect the traffic... don't you think so ?

Yes.

EMG

[James Bott]

Adolfo,

DBF, SQL, DBF plus index, SQL plus index.. would be nice to know how the use of an index will affect the traffic... don't you think so ?

I'm not sure what you are testing for. You cannot do an incremental search without an index.

I do not know exactly how a recordset is searched--I am guessing by scanning for a match. This does mean that it has to look at a lot of records before it finds a match.

With a indexed DBF, a binary search is done on the index then only the found record is read from disk. This should be much faster.

However, in the SQL recordset case, the entire table (all the records) has to be read from disk into the recordset before any search can be made. This will add significantly to the total time from selecting and displaying the browse to finding the match. With the DBF only enough records to fill the browse have to be read before the browse is displayed so the overall search time will probably be less. My point here is that you have to measure the entire time including the load time to get a realistic comparison of the time it will take the user to find the match.

Regards,
James

[Adolfo]

James...

I'll will measure data traffic (from opening the DBF til the browse is completely full ), then each keystroke till the record is found, using incremental search, then the same for ADO.

I'll will record times, and bytes received and transmited to the ports related to each case, 3306 when MYSQL is used and (at least 4 ports ) when Accesing a Windosw 2003 FILE server, and Linux Fedora 10 and Samba

Any suggestions will be appreciated.

Going to use a Switch ( intelligent with data management ) and a Port Sniffer or Net Traffic Service.

From Chile
Adolfo

[Marcelo Via Giglio]

Adolfo,

I don't sure about the test, because SAMBA works like a file server then, it's different to a Database server, I think when you open a DBF with index, the index will load in memory and you will see in the network traffic, this is defferent when use DATABASE SERVER (MySQL, Postgres, ADS, etc), because all the work is in the server and only the result is transmited to the client, but please public your results.

Regards

Marcelo