• Welcome, Guest. Please login.
June 22, 2021, 11:47:13 AM


Welcome to the SQLitening support forums!

Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - Peter Presland

Thanks Fred

I've already implemented a work around per your last post but useful to have the pragma command pointed out.

Peter Presland
Thanks once again Fred.

I'm making serious progress with both this and PB9 now. It's getting easier  :)
I need to test if table creation was successful or not. Documentation simply says that a 'No-op' occurs if the table already exists which is not an error, and I can find no further reference to it.

So, question: How do I test for the 'No-op' condition immediately following the Create command?
Thanks a bunch Joe - Much appreciated.  :) :) :)

That worked a treat. Although I was certain that when it first happened, the transaction was NOT ended and the process went through to completion, even displaying the end message box way after the main form had unloaded. I must have changed something in between though because it's fine now.

I feel I am making real progress at last.

Peter Presland
Well Joe, you asked for it  ;)

I built the form with EZGUI and have used a good few of the EZ commands/functions. I know you are familiar with them from posts on other boards etc. Also, please excuse the sloppy/dirty coding - my need is for something that works rather than elegance - and this does work - like greased lightening too compared to farting around with MS Access as has been my wont for a good few years. Nonetheless, if I'm doing anything obviously silly or 'makeWork' a steer would also be appreciated.

It's just the Abort button that I'm having a problem with.

As you can see the guts of the processing is done following a click event in a single Sub.

The abort button is in a separate Sub.

SUB TTTMAIN_GO_BUTTON_Events( MyID&, CMsg&, CVal&, Cancel&)
         CASE %EZ_Click
              IF EZ_GetText("TTTMAIN", 155) = "" THEN
                 EZ_MsgBox("TTTMAIN", "{!}    No Source File    ", "ATTENTION", "OK")
                 EZ_SetFocus  "TTTMAIN",  155
                 EXIT SELECT
              END IF
              IF EZ_GetText("TTTMAIN", 165) = "" THEN
                 EZ_MsgBox("TTTMAIN", "{!}    No SQL Database File    ", "ATTENTION", "OK")
                 EZ_SetFocus  "TTTMAIN",  165
                 EXIT SELECT
              END IF
              IF EZ_GetText("TTTMAIN", 175) = "" THEN
                 EZ_MsgBox("TTTMAIN", "{!}    No SQL Table    ", "ATTENTION", "OK")
                 EZ_SetFocus  "TTTMAIN",  175
                 EXIT SELECT
              END IF
              LOCAL IFile$, Fld$               ' For Input file name and field names variables
              LOCAL x&                         ' for size of input file
              LOCAL t&                         ' for timer
              DIM Fld(7) AS STRING
              IFile$ = EZ_GetText("TTTMAIN", 155)
              OPEN IFile$ FOR INPUT AS #1
              x& = LOF(#1)/1000000             ' Size of input file in Kb
              INPUT #1, Fld(0), Fld(1), Fld(2), Fld(3), Fld(4), Fld(5), Fld(6), Fld(7)
              IF Fld$(0) <> "Date" THEN     'Test input file
                 CLOSE #1
                 EZ_MsgBox("TTTMAIN", "{!}  Input file stucture not useable   ", "ATTENTION", "OK")
                 EXIT SELECT
              END IF
              IF EZ_MsgBox("TTTMAIN", "{?}  Parameters appear OK   "+ _
                                   "||   Do you wish to proceed?   "+ _
                                   "||   File Size is: "+ STR$(X&), "ATTENTION", "YN") = 0 THEN
                 CLOSE #1
                 EXIT SELECT
              END IF
              EZ_ShowC    "TTTMAIN", 115, 115  ' Show Abort button
              EZ_HideC    "TTTMAIN", 100, 110  ' Hide Go, Clear & Exit Buttons
              EZ_DisableC "TTTMAIN", 100       ' Clear Button
              EZ_DisableC "TTTMAIN", 105       ' Go button
              EZ_DisableC "TTTMAIN", 110       ' Exit button
              EZ_DisableC "TTTMAIN", 160       ' File input select button
              EZ_DisableC "TTTMAIN", 170       ' SQL select button
              EZ_ShowC    "TTTMAIN", 120,150   ' Show progress bar + messages etx
              LOCAL DbName$, TblName$, Date1$, Date2$, Time1$, Time2$, Price1$, Ssize$
              LOCAL Bid$, BidSize$,Ask$,AskSize$,BA$
              LOCAL B1$, Bs1$, A1$, As1$, Ci&, Co&
              DbName$ = EZ_GetText("TTTMAIN", 165)
              TblName$ = EZ_GetText("TTTMAIN", 175)
              slOpen (DbName$,"C")
              slExe "Create Table "+TblName$+" (Date INTEGER,Time TEXT,Price REAL,Size INTEGER, _
                                                Bid REAL,BidSize INTEGER,Ask REAL,AskSize INTEGER,BA TEXT)"
              LOCAL TStart&, TEnd&                                        ' Start & end time values
              TStart& = TIMER
              LOCAL NSeconds&, ESeconds&, EMins&                          ' To calculate elapsed time
              Ci& = 0
              Co& = 0
              t&  = 0
              WHILE ISFALSE EOF(1)
                  INPUT #1, Date1$, Time1$, Price1$, Ssize$, Bid$, BidSize$, Ask$, AskSize$
                  ci& += 1
                  t&  += 1
                  IF Price1$ = "" THEN B1$ = Bid$: Bs1$ = BidSize$ : A1$ = Ask$ : As1$ = AskSize$
                  IF VAL(Price1$) <= VAL(B1$) THEN
                     BA$ = "B"
                  ELSEIF VAL(Price1$) >= VAL(A1$) THEN
                     BA$ = "A"
                     BA$ = "I"
                  END IF
                  IF Price1$ <> "" THEN
                      Date2$ = MID$(Date1$,7,4) & MID$(Date1$,4,2) & MID$(Date1$,1,2)
                      Time2$ = MID$(Time1$,1,2) & MID$(Time1$,4,2) & MID$(Time1$,7,2)
                      slExe "Insert into "+TblName$+" values ("+"'"+ Date2$ +"','"+ Time2$ +"','"+ Price1$ _
                            +"','"+ Ssize$ +"','"+ B1$ +"','"+ Bs1$ +"','"+ A1$ +"','"+ As1$ +"','"+ BA$ +"')"
                      Co& += 1
                  END IF
                  EZ_DoEvents  1                                  ' clear windows messages
                  IF t& > 1000  THEN
                    TEnd& = TIMER - TStart&
                    EZ_SetPBar "TTTMAIN", 120, 0, x&, TEnd&       ' This assumes 1 Mb/Sec processed - more work needed
                    EZ_SetText "TTTMAIN", 130, STR$(Co& / 1000)   ' Records written text
                    TEnd& = TIMER
                    NSeconds& = TEnd& - TStart&                   ' elapsed time in seconds
                    EMins& = Nseconds& \ 60                       ' minutes portion of elapsed time
                    ESeconds& = NSeconds& - (EMins& * 60)         ' seconds portion of elapsed time
                    EZ_SetText "TTTMAIN", 135, STR$(Emins&)+" Min"+STR$(ESeconds&)+" Sec"
                    t& = 0
                  END IF
              slExe "END TRANSACTION"
              CLOSE #1
              TEnd& = TIMER
              NSeconds& = TEnd& - TStart&                   ' elapsed time in seconds
              EMins& = Nseconds& \ 60                       ' minutes portion of elapsed time
              ESeconds& = NSeconds& - (EMins& * 60)         ' seconds portion of elapsed time
              EZ_MsgBox("TTTMAIN", "{!}  File Conversion Complete||    Records processed: "+STR$(Ci&)+ _
                                   "    ||    Records written: "+ STR$(Co&)+ _
                                   "    ||    Elapsed time: "+STR$(Emins&)+" Min"+STR$(ESeconds&)+" Sec", "ATTENTION", "OK")
              EZ_HideC   "TTTMAIN", 115, 115  ' Hide Abort button
              EZ_ShowC   "TTTMAIN", 100, 110  ' Show Go, Clear & Exit Buttons
              EZ_EnableC "TTTMAIN", 100       ' Clear Button
              EZ_EnableC "TTTMAIN", 105       ' Go button
              EZ_EnableC "TTTMAIN", 110       ' Exit button
              EZ_EnableC "TTTMAIN", 160       ' File input select button
              EZ_EnableC "TTTMAIN", 170       ' SQL select button
              EZ_HideC   "TTTMAIN", 120,150   ' Hide progress bar + messages etc
          CASE ELSE
SUB TTTMAIN_EXIT_BUTTON_Events( MyID&, CMsg&, CVal&, Cancel&)
          CASE %EZ_Click
              EZ_UnloadForm   "TTTMAIN"
          CASE ELSE
SUB TTTMAIN_ABORT_BUTTON_Events( MyID&, CMsg&, CVal&, Cancel&)
          CASE %EZ_Click
                slExe "END TRANSACTION"
                CLOSE #1
          CASE ELSE

Peter Presland
This problem is probably a function of my beginner status with PB9. However, since it concerns my use of SQLitening as well , I'm posting it here first.

Briefly, when running an SQLlite transaction involving the import of several million records from a .csv file, PB seems to start a separate thread for the transaction loop - at least that is my assumption since two instances of the application appear in Task Manager. Just before the 'BEGIN TRANSACTION' statement, I disable various controls and enable an 'Abort' button. I have a 'DoEvents' statement in the loop to ensure that any abort button click will be actioned - and it IS actioned. It initiates the following code:

Close #1


The #1 being the input .csv file. The requirement is to be able to gracefully abort an import-in-progress.

Problem is that SQLite returns an error saying that there is no transaction in progress and there is no open SQL database, when there most certainly are both  since the transaction loop continues to run to successful completion.

I suspect it has to do with either the separate thread or 'sub-classing' (or both) - with which I am as yet in total ignorance.

Any help / observations welcome

Peter Presland
Is there a simple way to move backwards through a record set whilst inside a 'While slGetRow Wend'  construction?

I need to be able to back up by one record before continuing forward through a selection and can't figure out if or how it may be doable
Thanks Pat

I've already had a couple of email exchanges with Bogdan Ureche, the author, about it. He is very responsive to issues and suggestions. I've purchased the pro version now and am finding it very useful indeed.
Tried the pragma tweeks but they didn't make much difference to the import speed. Anyway, at just under 3 minutes to process 5 million records, I'm happy with things as they are.

That last question about single quotes was a manifestation of beginners ignorance. I thought it may have been possible to dispense with the single quotes completely. However, even if it were, it would only make the INSERT statement a bit shorter and simpler to construct and have little if any effect on execution speed. So something else learned.

I am currently reading each line with INPUT# (rather than LINE INPUT#) then building the SQL INSERT statement from the resulting array. It is a legacy of the code I used to write my manipulated input file to a sequential output file with WRITE#.

Haven't Checked out the pragma adjustments yet but will let you know.

BTW further to my SQLite Expert Pro observations, I reported a bug to them (The start button on the file Import dialog was not disabled during the import process making it possible for a second click to be buffered and actioned on completion etc etc). They've already tweeked the import speed on the latest version improving it by a factor of about 4 and offered me a discount for the bug report so I'll be purchasing the Pro version.

Thanks Fred, I'll check it out. I was going to have a good look at the pragmas anyway because, with the defaults, I can only get it to import text data that is delimited with single quotes in addition to the commas. Whereas all my text files are simply comma delimited with no quotes (double or single). I've constructed the import statement to add the single quotes OK but would maybe gain a bit more speed if they weren't needed. Worth investigating anyway.

Is there anything obvious I could do in that respect?

If I menu select 'Begin transaction' then try to initiate an import, an error 'Transaction already in progress' is returned and the import is not initiated. Also selecting the Import/Export menu first disables all other menus. So it does not appear possible on present version.

Also, there is no 'abort' (or any other options) available once an import has been initiated until complete, so the utility does not appear to have very large data sets in mind (yet?). In fact data manager reports the program as 'not responding' whilst an import is in progress so there's not even a 'doevents' in the import loop.
I have a trial installation of SQLite Expert Pro (It has a text file import facility). I tied importing the same text file and aborted after an hour. It had still only reached 1.5 million. I presume therefore that it does not use the "Begin Immediate Transaction" syntax when importing text files. That together with displaying the escalating 'No of records imported' number, the display overhead of which I presume gobbles up a good few milliseconds per record too.

Maybe my usage is a bit extreme but, nonetheless I reckon they'd do themselves a favour by addressing that import speed issue - unless of course there is a User tweekable parameter that I've missed somewhere - which is very likely.
Joe may recall suggesting I look at SQLitening in reply to a posting of mine on Chris Boss's site early last month. I'm very glad I did.

Briefly, I have hundreds of separate text files collected over several years containing financial time series tick data. Each one contains between 2 and 15 million records. My need is to parse these files into a form that is usable by my existing analytical software. I have made good progress in learning PB9 and had produced working prototypes of what I need. Apart from the PB9 learning curve, my biggest problem turned out to be processing time.

I started by using Input and Write statement to read from a text file, then following minor conditional manipulation, write the result to a separate output text file. A typical 5 million record file took just over 40 minutes to process. So I tried SQLitening - another leaning curve followed by serious disappointment. The same file took well over an hour to process.

Then I stumbled upon Pat Dooley's  'Slow insert speed' thread and BINGO !!! - it now takes just 3 minutes to process the same file, so I am one happy bunny.

It's still early days (for a rank beginner) but thanks a bunch to Joe for the steer and to Fred and whoever for a cracking product.