Hi
for those who work with dbf files.
How do you do research? Seek and do while or with query?

I do for example
DbUseArea(.t.,,"CUSTOMER" )
OrdListADD("CUSTOMER")
DbSeek("C" )
do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
	(tempfile)->(dbAppend())
	...
dbskip()
enddo
dbcommit()

is slow?

Salvatore Megna <smegna@tiscali.it> wrote in news:40287282$d831126$b8633
@news.alaska-software.com:

> for those who work with dbf files.
> How do you do research? Seek and do while or with query?

Do you use Advantage?

If you do, then you can execute SQL quries via the Advantage ODBC driver 
+ SQLExpress. In many cases the SQL will execute much faster and the 
code will be simpler than using traditional ISAM logic to generate the 
query. Example:

 traditional ISAM syntax:

SET SOFTSEEK ON
USE ORDERS
SET INDEX TO ORDERS
...
...
SELECT ORDERS
SET ORDER TO TAG "PARTNO"  index exp is: PARTNO+DTOS(DATE_REQ)
SEEK "123456"
WHILE !EOF() .AND. ORDERS->PARTNO == "123456"
   IF ORDERS->QTY_SHIPPED <> ORDERS->QTY_ORDERED
	 do your thing here....
   ENDIF
   SKIP
END

 same query using SQLExpress:

cSQL := SELECT * FROM ORDERS WHERE PARTNO=? AND QTY_SHIPPED<>QTY_ORDERED 
ORDER BY DATE_REQ"

oCursor := oConnection:Cursor( cSQL )

oCursor:Execute( "123456" )
WHILE !oCursor:EOF()
    do your thing here....
   oCursor:Skip()
END

The difference in both speed of execution and code simplicity becomes 
more pronounced when the query involves joining data from multiple 
tables.

Best regards,
Boris Borzic

http://xb2.net
http://sqlexpress.net
industrial strength Xbase++ development tools

Boris,

Is that right? If you use ADS + SQLExpress, you can then run sql queries? So 
I guess I don't really understand what SQL Express does for a living. Could 
you explain?

And so I'm currently researching PostgreSQL as a solution to start moving to 
a more SQL based system. The selling point of PostgreSQL is that you can use 
much of the same ISAM style code, but you can also use SQL as needed when 
you need better performance. So with ADS and SQLExpress you can accomplish 
basically the same thing?

The only downside of going that route is that your tables are still file 
based so the security issues are still an issue?

How would the performance compare between ADS + SQLExpress  vs PostgreSQL 
when running SQL Queries?

Thanks,

Scott

"Boris Borzic" wrote in message 
news:XnsAB0D7B5AC58C7SQLExpress@87.106.143.233...

Salvatore Megna <smegna@tiscali.it> wrote in news:40287282$d831126$b8633
@news.alaska-software.com:

> for those who work with dbf files.
> How do you do research? Seek and do while or with query?

Do you use Advantage?

If you do, then you can execute SQL quries via the Advantage ODBC driver
+ SQLExpress. In many cases the SQL will execute much faster and the
code will be simpler than using traditional ISAM logic to generate the
query. Example:

 traditional ISAM syntax:

SET SOFTSEEK ON
USE ORDERS
SET INDEX TO ORDERS
...
...
SELECT ORDERS
SET ORDER TO TAG "PARTNO"  index exp is: PARTNO+DTOS(DATE_REQ)
SEEK "123456"
WHILE !EOF() .AND. ORDERS->PARTNO == "123456"
   IF ORDERS->QTY_SHIPPED <> ORDERS->QTY_ORDERED
 do your thing here....
   ENDIF
   SKIP
END

 same query using SQLExpress:

cSQL := SELECT * FROM ORDERS WHERE PARTNO=? AND QTY_SHIPPED<>QTY_ORDERED
ORDER BY DATE_REQ"

oCursor := oConnection:Cursor( cSQL )

oCursor:Execute( "123456" )
WHILE !oCursor:EOF()
    do your thing here....
   oCursor:Skip()
END

The difference in both speed of execution and code simplicity becomes
more pronounced when the query involves joining data from multiple
tables.

Best regards,
Boris Borzic

http://xb2.net
http://sqlexpress.net
industrial strength Xbase++ development tools

"Scott Krise" <scottkrise@verizon.net> wrote in
news:3410023d$16da2dc7$bcb52@news.alaska-software.com: 

> Is that right? If you use ADS + SQLExpress, you can then run sql
> queries? So I guess I don't really understand what SQL Express does
> for a living. Could you explain?

SQLExpress provides a simple interface to ODBC for Xbase++ programmers. 
ODBC is a widely supported open industry standard for SQL access to many 
different types of databases. 

BTW, ODBC is Microsoft's "de-facto standard for native access to SQL 
Server", see here:

https://blogs.msdn.microsoft.com/sqlnativeclient/2013/01/23/introducing-
the-new-microsoft-odbc-drivers-for-sql-server/

> And so I'm currently researching PostgreSQL as a solution to start
> moving to a more SQL based system. The selling point of PostgreSQL is
> that you can use much of the same ISAM style code, but you can also
> use SQL as needed when you need better performance. So with ADS and
> SQLExpress you can accomplish basically the same thing?

Yes!

I will also add that converting parts of your code to native SQL will 
also simplify the code (less lines of code, easier to read, easier to 
maintain). Some of the code you can even throw away. Example: any code 
dealing with table indexes, reindexing, record recycling, packing, etc..

Eventually, if you manage to convert all your code to native SQL, then 
with a bit of work you can fairly easily switch the database back end.

> The only downside of going that route is that your tables are still
> file based so the security issues are still an issue?

It does not matter what database back end you use; the actual data is 
still stored in "files". The difference is that the database files are 
hosted on a (remote) database server and managed by a DBMS. You can do 
the same thing using ADS (Advantage Database Server) where all data 
access must go through the ADS DBMS. 

> How would the performance compare between ADS + SQLExpress  vs
> PostgreSQL when running SQL Queries?

I suspect the difference between the two would be negligible when 
running straight SQL queries. However, ADS will have a big advantage for 
running your legacy ISAM code.

Best regards,
Boris Borzic

http://xb2.net
http://sqlexpress.net
industrial strength Xbase++ development tools

The commit command is slowing you down I'd bet.

I replaced:

select 250
append blank
.
.
.
commit
unlock

With:

select 250
append blank
.
.
.
skip 0
unlock

And the performance was much better.

"Salvatore Megna"  wrote in message 
news:40287282$d831126$b8633@news.alaska-software.com...

Hi
for those who work with dbf files.
How do you do research? Seek and do while or with query?

I do for example
DbUseArea(.t.,,"CUSTOMER" )
OrdListADD("CUSTOMER")
DbSeek("C" )
do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
(tempfile)->(dbAppend())
...
dbskip()
enddo
dbcommit()

is slow?

Is the dbcommit not for writing on the record?


Il 20/11/2019 18:10, Scott Krise ha scritto:
> The commit command is slowing you down I'd bet.
> 
> I replaced:
> 
> select 250
> append blank
> .
> .
> .
> commit
> unlock
> 
> With:
> 
> select 250
> append blank
> .
> .
> .
> skip 0
> unlock

Scott,

>The commit command is slowing you down I'd bet.

I don't think so. The posted code had the commit outside / after the loop, 
so it would have been executed only once, after the loop was completed:

>do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
>(tempfile)->(dbAppend())
>...
>dbskip()
>enddo
>dbcommit()

That doesn't mean that your code pattern change wouldn't be faster, but not 
having the Skip(0) or, if possible, opening the database exclusively -- 
which shouldn't be a problem for copying to a temp table -- will be even 
faster. And you can always do a DbCommit() at the end of the loop, unless 
time-critical concurrent data access to the appended records is important.

Your code will do an implicit write whenever the OS thinks its necessary, 
so in this kind of situation, adding a SbSkip(0) inside the loop isn't 
really necessary.

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC

Thank you all for your help


Il 20/11/2019 18:10, Scott Krise ha scritto:
> The commit command is slowing you down I'd bet.
> 
> I replaced:
> 
> select 250
> append blank
> .
> .
> .
> commit
> unlock
> 
> With:
> 
> select 250
> append blank
> .
> .
> .
> skip 0
> unlock
> 
> And the performance was much better.
> 
> "Salvatore Megna"  wrote in message 
> news:40287282$d831126$b8633@news.alaska-software.com...
> 
> Hi
> for those who work with dbf files.
> How do you do research? Seek and do while or with query?
> 
> I do for example
> DbUseArea(.t.,,"CUSTOMER" )
> OrdListADD("CUSTOMER")
> DbSeek("C" )
> do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
> (tempfile)->(dbAppend())
> ...
> dbskip()
> enddo
> dbcommit()
> 
> is slow?

Salvatore,

>How do you do research? Seek and do while or with query?
>[...]
> is slow?

You could use either a Filter or a Scope. In your example, a Scope would 
work fine, as you have an appropriate Index:

DbUseArea(.t.,,"CUSTOMER" )
OrdListADD("CUSTOMER")
DbSetScope(SCOPE_BOTH, "C")	 Set Scope To "C"
DbGoTop()
while .not. EoF()
   ...
   DbSkip()
enddo

Another option would be a Filter, which will work slower, but independently 
of any available Index keys:

DbUseArea(.t.,,"CUSTOMER" )
OrdListADD("CUSTOMER")
DbSetFilter({|| left(NameCompa, 1) == 'C'})	 Set Filter To ....
DbGoTop()
while .not. EoF()
   ...
   DbSkip()
enddo

You might be able to improve speed with enabling SmartFilter and/or Rushmore 
optimizations, but those might cause runtime errors, so be careful where you 
enable those:

Set Optimize On
Set SmartFilter On
Set Rushmore On

I have never enabled those, though.

Hope that helps,

Andreas

Andreas Gehrs-Pahl
Absolute Software, LLC

phone: (989) 723-9927
email: Andreas@AbsoluteSoftwareLLC.com
web:   http://www.AbsoluteSoftwareLLC.com
[F]:   https://www.facebook.com/AbsoluteSoftwareLLC

hi,

> do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
> (tempfile)->(dbAppend())

why do you use a tempfile and not a Array for Result ?
it is much faster than "write" and make not Network Problem.

you can use Array to Browse and all other action you want to do.

Have you an example with array? My problem is that i'havent use browse 
with array


Il 25/11/2019 09:54, Jim Lee ha scritto:
> hi,
> 
>> do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
>> (tempfile)->(dbAppend())
> 
> why do you use a tempfile and not a Array for Result ?
> it is much faster than "write" and make not Network Problem.
> 
> you can use Array to Browse and all other action you want to do.
> 
> 
>

> Have you an example with array? My problem is that i'havent use browse
> with array

it is the same Syntax as a normal Browse only "Skipper" is different.

have a look at Alaska Samle FBROWSE.PRG or use this "Skipper" for Array

   oBrowse:skipBlock     := {|nSkip| -np + (np := MAX(1, MIN(LEN(aArray), np
+ nSkip))) }
   oBrowse:goTopBlock    := {|| np := 1}
   oBrowse:goBottomBlock := {|| np := LEN(aArray) }
   oBrowse:posBlock      := {|| np }
   oBrowse:phyPosBlock   := {|| np }
   oBrowse:lastPosBlock  := {|| Len( aArray ) }
   oBrowse:firstPosBlock := {|| 1  }

if you want to "write" back to DBF than add a Element for RECNO() in Array 
so you can easy GOTO(nRec)



---
Diese E-Mail wurde von AVG auf Viren geprüft.
http://www.avg.com

Salvatore.

as I wrote in my answer: Have a look on the docs for XbpBrowse. The last 
sample here is browsing an array.

Jan

Am 26.11.2019 um 10:35 schrieb Salvatore Megna:
> 
> Have you an example with array? My problem is that i'havent use browse 
> with array
> 
> 
> Il 25/11/2019 09:54, Jim Lee ha scritto:
>> hi,
>>
>>> do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
>>> (tempfile)->(dbAppend())
>>
>> why do you use a tempfile and not a Array for Result ?
>> it is much faster than "write" and make not Network Problem.
>>
>> you can use Array to Browse and all other action you want to do.
>>
>>
>>
>

Additional to Jimmys answer I prefere an array with DataObjects. Your 
loop would look like this if I would write it (I also always use the 
alias for database actions):

LOCAL aFilter  := {}
LOCAL oDoRecord := NIL
DbUseArea(.T., , "CUSTOMER" )
OrdListADD("CUSTOMER")
customer->(DbSeek("C",  , "Customer")
DO WHILE ! customer->(EoF()) .AND. SubStr(customer->NameCompa, 1, 1) = "C"
    SCATTER NAME INTO oDoRecord
    AAdd(aFilter, oDoRecord)
    customer->(dbskip())
ENDDO

You also browse an array with DataObjects as elements. There is a sample 
in the docs for XbpBrowse how to browse an array.

Jan

Am 20.11.2019 um 17:24 schrieb Salvatore Megna:
> Hi
> for those who work with dbf files.
> How do you do research? Seek and do while or with query?
> 
> I do for example
> DbUseArea(.t.,,"CUSTOMER" )
> OrdListADD("CUSTOMER")
> DbSeek("C" )
> do while !eof() .and. SubStr(Field->NameCompa,1,1) = "C"
>      (tempfile)->(dbAppend())
>      ...
> dbskip()
> enddo
> dbcommit()
> 
> is slow?