Announcement

**Edwin Knoppert** · 18 Jun 2009, 02:44 PM

I use ADO and a schema.ini
Superfast!

Schema.ini File (Text File Driver) - Open Database Connectivity (ODBC)

http://msdn.microsoft.com/en-us/library/ms709353.aspx

Schema.ini File (Text File Driver)

**Edwin Knoppert** · 18 Jun 2009, 02:54 PM

Here is the schema.ini (place in same folder)
rename the datafile to thedoc.txt or adapt the schema.ini and SQL

Code:

[thedoc.txt]
ColNameHeader=True
CharacterSet=1252
DecimalSymbol=.
Format=Delimited(,)
DateTimeFormat=mm/dd/yyyy hh:nn:ss
Col1=Date DATETIME
Col2=TIME LONG
Col3=OPEN DOUBLE
Col4=HIGH DOUBLE
Col5=LOW DOUBLE
Col6=VOLUME LONG
Col7="OPEN INTEREST" LONG

SQL:

Code:

SELECT * 
FROM [thedoc#txt]

Connectionstring for ADO:

Code:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folderwithdatafiles;Extended Properties=TEXT;Persist Security Info=False

**Michael Mattias** · 18 Jun 2009, 03:09 PM

.. and if using ADO, this demo puts all the column data into a two-dimensional (col, row) string array... (row 0 contains the column names)...

Generic 'ADO' Connection and Query Tester (CC 5+/Win 9+) 11-02-08

**John Gleason** · 19 Jun 2009, 08:31 AM

This reads it all in and parses it by $CRLF to an array, then you can further parse it using your parser1 GOSUB.

Code:

FUNCTION PBMAIN () AS LONG
   LOCAL allFile AS STRING, iRecs, ii AS LONG
   LOCAL high AS DOUBLE

'   OPEN dpath$+dfile$ FOR BINARY SHARED AS #1
   OPEN "C:\stockData.txt" FOR BINARY SHARED AS #1
   GET$ #1, LOF(#1), allFile
   iRecs = PARSECOUNT(allFile, $CRLF)
   DIM arrOfRecs(iRecs) AS STRING
   PARSE allFile, arrOfRecs(), $CRLF

   FOR ii = 0 TO iRecs - 1
      GOSUB parser1
   NEXT
   WAITKEY$

   EXIT FUNCTION

   parser1:
     high = VAL(PARSE$(arrOfRecs(ii), ",", 4))
     ? STR$(high)
   RETURN

END FUNCTION

**Michael Mattias** · 19 Jun 2009, 09:58 AM

You know, after all is said and done, LINE INPUT might be plenty good enough. (It ain't broke, don't fix it?)

But FWIW you can make that a lot more efficient simply by setting up a bigger buffer with the use of the LEN= clause on the OPEN.

MCM

**John Gleason** · 19 Jun 2009, 11:58 AM

I wondered about that too because I didn't check the speed of the two PB techniques: whole file vs. record read. So I did check it below and was surprised that the whole read was ~7x faster. I tried the LEN optimization but didn't see a speedup. I tried 1,4,16, and 32k. My tests used PBCC5, 30MB test file.

Code:

#COMPILE EXE
#DIM ALL

FUNCTION PBMAIN () AS LONG
   LOCAL allFile AS STRING, iRecs, ii AS LONG
   LOCAL high, t AS DOUBLE

'   OPEN dpath$+dfile$ FOR BINARY SHARED AS #1
   OPEN "C:\dataFil07.dat" FOR BINARY SHARED AS #1
   OPEN "C:\dataFil07.dat" FOR INPUT SHARED AS #2'len = &h00400 'I didn't see speed increases with larger rec sizes.
   t = TIMER
   DO
      LINE INPUT #2, allFile
      GOSUB parser2
   LOOP UNTIL EOF(#2)
    ? "done individual rec read: " & STR$(TIMER - t)
        
   t = TIMER
   GET$ #1, LOF(#1), allFile
   iRecs = PARSECOUNT(allFile, $CRLF)
   DIM arrOfRecs(iRecs) AS STRING
   PARSE allFile, arrOfRecs(), $CRLF
                   
   FOR ii = 0 TO iRecs - 1
      GOSUB parser1
   NEXT
    ? "done whole file read: " & STR$(TIMER - t)
   WAITKEY$
   EXIT FUNCTION

   parser1:
     high = VAL(PARSE$(arrOfRecs(ii), ",", 4))
   RETURN

   parser2:
     high = VAL(PARSE$(allFile, ",", 4))
   RETURN

END FUNCTION

**Michael Mattias** · 19 Jun 2009, 12:57 PM

Code:

 t = TIMER
   DO
      LINE INPUT #2, allFile
      GOSUB parser2
   LOOP UNTIL EOF(#2)
    ? "done individual rec read: " & STR$(TIMER - t)

This is really not a fair 'timing' of the disk access of any sort.... how much of the time was spent in 'Parser2?'

Not to mention, holding two handles open on a SHARED file introduces some system overhead.

See PROFILE and #TOOLS ON in the help file; then structure the program to isolate the disk access into its own procedures. Run again.

Then, look at your results, subtract out the parsing time and decide if perhaps you haven't already spent enough time trying to optimize a section of code which is but a small percentage of the total job.

MCM

**John Gleason** · 19 Jun 2009, 01:51 PM

I commented out the GOSUB code and closed and reopened the file rather than having it SHARED, and the difference increased to over 10x. The below results were typical on my machine:

Code:

done individual rec read:  42.7899999999965
done whole file read:  3.57000000000291

done individual rec read:  43.2799999999965
done whole file read:  3.94999999999767

done individual rec read:  40.8600000000012 'this timing was SHARED
done whole file read:  3.52000000000058     'this timing was SHARED

**Doug Ingram** · 19 Jun 2009, 04:14 PM

Now - we're cooking with gas... Thanks all!

I am downloading history from this site..
You can get 50 to 80 years at once (daily data).

Though this method drops in as a cvs file, another one just gives me ".txt"
(And it's free)

S&P 500 (^GSPC) Historical Data - Yahoo Finance

http://finance.yahoo.com/q/hp?s=%5EGSPC

Get historical data for the S&P 500 (^GSPC) on Yahoo Finance. View and download daily, weekly or monthly data to help your investment decisions.

Yahoo Finance - Stock Market Live, Quotes, Business & Finance News

http://finance.yahoo.com

At Yahoo Finance, you get free stock quotes, up-to-date news, portfolio management resources, international market data, social interaction and mortgage rates that help you manage your financial life.

Yabba Dabba Doooo!!!

My "parser1" routine simply takes individual lines and places Date, time, open, high, low, and close into an array.

**Guy Dombrowski** · 19 Jun 2009, 06:29 PM

Nice technique

John,

Your research on whole file reading speed was simply amazing !
I never thought that the speed difference would be that big !
I have been using binary read more and more and I noticed it was faster and also simpler to program but I never would have thought that that technique was that much faster than reading the "Old Way"
Congratulation on your work as you must have invested a few hours to test it.
I will be putting that knowledge to good use.

**Bob Zale** · 20 Jun 2009, 02:04 AM

Originally posted by Michael Mattias View Post

[code]
Not to mention, holding two handles open on a SHARED file introduces some system overhead.

That's a huge under-statement. When a sequential file is shared, there is absolutely no buffering possible. Every line must be re-read from disk, every time, because it may have been altered a few nanoseconds ago. Try it and see...

Bob Zale
PowerBASIC Inc.

**Francisco Castanedo** · 20 Jun 2009, 08:06 AM

Reading the file in a swoop

Doug, from the help file for the FILESCAN statement:

Example
OPEN "datafile.dat" FOR INPUT AS #1
FILESCAN #1, RECORDS TO count&
DIM TheData(1 TO count&) AS STRING
LINE INPUT #1, TheData() TO count&
CLOSE #1

I use it a lot and it works fine.

**Michael Mattias** · 20 Jun 2009, 10:46 AM

Your research on whole file reading speed was simply amazing !
I never thought that the speed difference would be that big !

You can also do memory-mapping; that offers the possibility of accessing the file data without the need to load it to your memory space (eg., into a PB array).

For delimited records (a standard 'sequential text' file uses CRLF as a record delimiter) there is no real good reason to do this, but for fixed-record length files, you can use something like:

Memory Mapped Files instead of RANDOM disk file access 5-8-04

Very, very fast; good thing to do with big files (whatever 'big' means).

Note that 'as written,' that demo will not handle files larger than the available system space for MMF objects, which has been in my experience somewhere between 400 and 600 Mb.

Or, for sequential files you will be processing in a truly sequential fashion, there's also this demo:

Memory-Mapped File Version of LINE INPUT

Just a few little somethings to keep in mind....

MCM

**John Gleason** · 20 Jun 2009, 11:10 AM

I'm sad to report I made a mistake in my timing code, but happy to report that it works fine using individual record reads! I didn't know a SHARED file disabled sequential file buffering

I said I was surprised at the difference in speed of the two, but I think stunned was my actual state. I didn't understand why changing the record length had no effect. I wondered if there was a problem somewhere, but couldn't find one. Well, problem solved!

Check out the new times. The record read is faster. I didn't time Francisco's technique, but it looks fast too.

Code:

done individual rec read:  2.84999999999767
done whole file read:  3.56999999999913

done individual rec read:  3.20099502459925E-12
done whole file read:  3.20099502459925E-12

Code:

#COMPILE EXE
#DIM ALL

FUNCTION PBMAIN () AS LONG
   LOCAL allFile AS STRING, iRecs, ii AS LONG
   LOCAL high, t AS DOUBLE

   OPEN "C:\BinFil08.dat" FOR INPUT AS #2 LEN = &h00800 'I DID see speed increases with larger rec sizes.
   t = TIMER
   DO
      LINE INPUT #2, allFile
   LOOP UNTIL EOF(#2)
   ? "done individual rec read: " & STR$(TIMER - t)
   CLOSE

   OPEN "C:\BinFil08.dat" FOR BINARY AS #1
   t = TIMER
   GET$ #1, LOF(#1), allFile
   iRecs = PARSECOUNT(allFile, $CRLF)
   DIM arrOfRecs(iRecs) AS STRING
   PARSE allFile, arrOfRecs(), $CRLF

    ? "done whole file read: " & STR$(TIMER - t)
   WAITKEY$ 
END FUNCTION

**Michael Mattias** · 20 Jun 2009, 11:21 AM

>LEN = &h00800 'I DID see speed increases

&h800 is only 2K, relatively not a heck of a lot more than than the 128 byte default.

Don't be such a wimp; you have 2 Gb user memory to play with!

At least go one full page size (usually 64K, use GetSystemInfo() function to get page size on target system).

Unless I'm mistaken, you will be allocating a full page anyway, so you may as well put all of it to work.

**John Petty** · 20 Jun 2009, 12:35 PM

Doug
I don't understand why you are useing LINE INPUT and parsing. Old school Basic has always seperated input fields with commas or CRLF combinations so why are you bringing these 8 variables per line into a string and then parsing them. Far more efficient to let basic do the parsing automatically as it reads the file. Here is a small code example assuming there are only 3 variables per line. Of course this could also be done with a single UDT array. As for the disk yes increase the buffer size.

Code:

FUNCTION PBMAIN () AS LONG
    LOCAL x AS LONG
    LOCAL a AS STRING
    LOCAL TheDate() AS STRING * 10
    LOCAL TheTime() AS LONG
    LOCAL TheHigh() AS SINGLE
    REDIM TheDate(1,000,000)    ' a number larger than the largest number
    'of records you will ever get
    REDIM TheTime(1,000,000)
    REDIM TheHigh(1,000,000)
    OPEN dpath$+dfile$ FOR INPUT AS #1
    'after opening the file if there is a heading line then
    LINE INPUT #1, a
    WHILE ISFALSE EOF(1)
        INPUT #1, TheDate(x), TheTime(x), TheHigh(x)    'etc too lazy to name all the fields
        INCR x
    WEND
    CLOSE 1
    REDIM PRESERVE TheDate(x - 1)
    REDIM PRESERVE TheTime(x - 1)
    REDIM PRESERVE TheHigh(x - 1)

END FUNCTION

**John Petty** · 20 Jun 2009, 12:50 PM

Originally posted by Michael Mattias View Post

You can also do memory-mapping; that offers the possibility of accessing the file data without the need to load it to your memory space (eg., into a PB array).

For delimited records (a standard 'sequential text' file uses CRLF as a record delimiter) there is no real good reason to do this, but for fixed-record length files, you can use something like:

Memory Mapped Files instead of RANDOM disk file access 5-8-04

Very, very fast; good thing to do with big files (whatever 'big' means).

Note that 'as written,' that demo will not handle files larger than the available system space for MMF objects, which has been in my experience somewhere between 400 and 600 Mb.

Or, for sequential files you will be processing in a truly sequential fashion, there's also this demo:

Memory-Mapped File Version of LINE INPUT
MCM

Michael
You must stop perpetuating this myth. This occasion is probably the worst occasion I can think of to use a MMF. The O/S uses 4K of the programs (PB) memory space and then only reads the file in 4K increments, no different to setting to a 4K buffer. It will of course save the entire file to the swap file ready for reuse by other applications but in this case there are no other applications nor does he wish to read it more than once. Just how slow are you trying to make his program run?

**Michael Mattias** · 20 Jun 2009, 01:20 PM

Which "myth?"

That using MMFs is not faster than using pure sequential or random access?

Well, I guess it must be application-specific, because I have had nothing but success using MMFs.

Minor technical correction required:

It will of course save the entire file to the swap file ready for reuse by other applications

That's not quite accurate, in that the page files will be used for additional access by the current process as well... which is exactly what happens if you load the file to user memory.

Hey, MMFs are another technique.. use it or not, your choice. Me, I've always enjoyed having options.

MCM

**John Petty** · 20 Jun 2009, 01:27 PM

Originally posted by Michael Mattias View Post

Which "myth?"

That using MMFs is not faster than using pure sequential MCM

Correct

Announcement

Reading a whole text file... market data

Reading a whole text file... market data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment