Choosing SQL database ...

[Rimantas]

Ok. But this was exactly the point. We can't do fast incremental searching using SQL in an engine-independent way. SQL was been designed to be a "query-language" not for the modern interactive applications.

EMG

Enrico ,

Something I can't understand - ADODb have .Find( cExpr ) method , where cExpr = "myField = 'MyVar' " . What hinder to do something like MyIncremSearch( cField, cVar ) function or method ? You can pass literal cExpr = cField + "=" + cVar . And cVar you can add incrementally . And that wil be independent from any SQL server - that it's ADO DB ... It's easy to adopt to any browse with ADO recordsets .

Just my 2 cents ..

Regards !

[Enrico Maria Giordano]

You have to load a recordset before ADO can do its magic. The problem is exactly this loading time.

EMG

[Rimantas]

You have to load a recordset before ADO can do its magic. The problem is exactly this loading time.

EMG

In one project today I have 2 DBF's tables of making of workers . The header is ~ 100 K records and second - ~ 7 millions records . As you can see - heading line have about 60 - 80 lines of making . I'm using ordscope for second table and that is working fine . But ... then opening this MDI child window with two browses it tooks some time - from 5 to 15 sec ... At this time I didn't tried with SQL , because I'm rewriting this parts for SQL and after some it will be ready . But I'm believing that after some tuning I can achieve good performance with SQL ... Why ? The second browse is on the seconf folder tab . So with SQL I'll open recordset only for needful heading line , not all DBF as with ordscoping . The amount of network traffic will be smaller , the recordset depends only for needful data ...

[reinaldocrespo]

Rimantas;

That's really good.

Perhaps you'll be so kind as to show both code samples with performance description once you have it working with sql.

Thank you,



Reinaldo.

[Enrico Maria Giordano]

But ... then opening this MDI child window with two browses it tooks some time - from 5 to 15 sec ...

So you have something wrong in your code, nothing more.

EMG

[James Bott]

Rimantas,

Why ? The second browse is on the second folder tab . So with SQL I'll open recordset only for needful heading line , not all DBF as with ordscoping .

I'm not sure what you are saying above, but if you think ordscope is reading the entire database just to display those records within the scope, this is not true as Adolfo's test has shown. In fact, unless the browse is longer than all the records in the workorder, the browse will load less records than a recordset. The browse will only read as many records as needed to fill the browse.

I'm not trying to say you shouldn't use SQL but you should know the differences between SQL and DBFs so you can make an informed decision.

Regards,
James

[Rimantas]

Rimantas,

Why ? The second browse is on the second folder tab . So with SQL I'll open recordset only for needful heading line , not all DBF as with ordscoping .

I'm not sure what you are saying above, but if you think ordscope is reading the entire database just to display those records within the scope, this is not true as Adolfo's test has shown. In fact, unless the browse is longer than all the records in the workorder, the browse will load less records than a recordset. The browse will only read as many records as needed to fill the browse.

I'm not trying to say you shouldn't use SQL but you should know the differences between SQL and DBFs so you can make an informed decision.

Regards,
James

I'm talking about this situation that at first I must OPEN the 2 dbfs with indexs - and that tooks amount of time . After that all is working OK . In mdichild is folder with 2 tabs - in first browse with heading dbf , in the 2-nd browse making . Changing tabs I'm setting on/of ordscope for dbfs - that is working fine and with good speed . Image - with SQL that will be one big recordset as for 1-st dbf and nothing for 2-nd browse . Second browse will get data only on that time , then you will push 2-nd tab of folder and will transfer recordset of 60-80 records form SQL . You are avoiding a big amount data transfer at a FIRST TIME ( openning ) , because you aren't openning a big ~ 7 mln record dbf with a big CDX file with 5 index tags inside ...

I'm not comparing - better or not with DBFs or SQLs ... That is a waste of time . Both techiques have owns + and - . And you can get a good result with both - simply you will find needful solution in any situation ... That is my point of view . After some test I can say that increment searching is working fine as I wroted for Enrico - ado db techique is powerfull and easy maintaining thing . After reviewing ADORDD I can say that ussual ADO recordset techique is better - you can use that in more flexible way .

Regards !

[Rimantas]

Here you have the post.
Download it, try it, comment it

http://forums.fivetechsupport.com/viewtopic.php?f=6&t=17178


Adolfo

Adolfo ,

Perfect class ... Thank you !

With best regards !

[Enrico Maria Giordano]

I'm talking about this situation that at first I must OPEN the 2 dbfs with indexs - and that tooks amount of time .

Opening a DBF with indexes doesn't require any time. And the content of the DBF is irrelevant (ie. it doesn't make any differences to open an empty DBF or a million records DBF).

ado db techique is powerfull and easy maintaining thing .

ADO is without a doubt a good common interface to databases but here we are speaking about the query, that is not part of ADO. If Adolfo uses LIMIT clause in his query then he's losting compatibility with the databases which not support it. This is a fact.

EMG

[Rimantas]

I'm talking about this situation that at first I must OPEN the 2 dbfs with indexs - and that tooks amount of time .

Opening a DBF with indexes doesn't require any time. And the content of the DBF is irrelevant (ie. it doesn't make any differences to open an empty DBF or a million records DBF).

EMG

If open DBFs with indexes in network enviroment - then you will see the difference of time . I can't agree in this situation . In clipper times , when most servers was FILE Servers as Novell , then the diff wasn't visible . But MS Wind 2003 server is working in other mannier . Some users PC are in other building and exist 2 routes betwen PC and server . Opening application you can see diff of time ... Maybe I'm not true , maybe that is the problem of network , but from experience I can say that in MS wind servers networks the more reords in dbfs then you can feel the diff of openning time ...

Regards !

[Enrico Maria Giordano]

from experience I can say that in MS wind servers networks the more reords in dbfs then you can feel the diff of openning time ...

It doesn't make sense. Opening a file does not require to read its content. Please try a sample in your network that only does

USE YOURDBF

and then another one that opens the indexes too. The slowness in opening could be caused by something else in your code.

EMG

[Rimantas]

[
It doesn't make sense. Opening a file does not require to read its content. Please try a sample in your network that only does

USE YOURDBF

and then another one that opens the indexes too. The slowness in opening could be caused by something else in your code.

EMG

It seems that we talking about different things ... That it's a waste of time ...

Regards !

[Enrico Maria Giordano]

It seems that we talking about different things ...

That it's a waste of time ...

I don't think so. If you can't do the test I propose just say "I can't do it".

EMG

[James Bott]

Rimantas,

I just did a test here on a peer-to-peer network and here are the times to open DBFs of different sizes.

First test
100 records = 70 milliseconds
1 million records = 110 milliseconds

Repeat test
100 records = 60 ms
1 million records = 50 ms

So, you can see there is no significant difference in the time it takes to open a DBF no matter what the size.

I can provide my test code if you wish.

Regards,
James

[Otto]

Hello James,

please send me the code. I would be fine if we could establish a reference value table with test results from different networks. So if we install a program on the costumer side we could first ran the network-test-software to check if all is ok.

SQL

I had an internet application (booking system) where I used a sql server. The server was not my own server it was provided by the hosting company (discountasp).
But I changed the application from sql to mdb because mdb was much faster for this purpose and I use mdb the dbase way 1 table per mdb database.
Speed is very good.

The same I noticed when I used dotnetnuke for my webpages. Till version 2 the database was mdb then dotnetnuke changed and we had to use SQL server.
But speed since then was not as good anymore.
I think it depends always on the purpose for what you use the database.
For example our tax-law says that you have to provide your data in ASCI or dbase format. So if I would use SQL I always have to make an export tool. As I have dbase I can provide my original files.
Also security is relative. In SQL you have one file if this is corrupt then all your data is lost. You can delete with on click a whole SQL server.
I had much work to keep the server always going. But as I say it depends on the purpose.
Programming is always at the end 0 or 1 and SQL has a good marketing.
Best regards,
Otto