Stéphane Wierzbicki

@Dmitry Arefiev yes I know 🙂 I'm so far pleased with the current speed. I'll check if I can use the Array DML within TMS scripter. You wrote a very efficient library... Kudos!

@edwinyzh the culprit was the use of TFDTable instead of TFDQuery objects. Speed is now almost the same.

Speed difference is so huge between TFDTable and TFDQuery. I naively thought that TFDTable was the right choice when dealing with tables. Do you know why there is such speed differences ? Anyway thank you for your help.

Hi, I need to import some Excel data, modify it and export it into another format. Input fields defs, output fields defs and script can be changed ar run time (lets say by a smart end user. Data is persisted into an SQLite database. Basically here is what I did: Creating Input and Output TFDMemtable at run time (based on fields defs previously defined Injecting XLS data into Input TFDmemtable objects Passing Input and output TFDmemtable objects to scripter object Running script (cannot use DML for various reasons) input.beginbatch; Output.beginbatch; try input.fisrt; while not input.eof do begin output.append; output.fieldbynem('blabla').AsString := input.fieldbyname('llkll').AsString; Output.post; input.next; end; finally input.endbatch; Output.endbatch; end; Persisting TFDMemtable data into SQLite database thanks to TBatchmove Exporting data into another format This works pretty well (few secs for 10000 rows) I decided then to remove all those TFDmemtable objects and replace them with TFDTable objects. Now it take 15x the time to complete. This is true specially on the script side. To speed up the things I tried to wrap my script on a transaction try FLocalConnection.StartTransaction; fScripter.Execute; if FLocalConnection.inTransaction then FLocalConnection.commit; Log('Script Executed') except on e: exception do begin if FLocalConnection.inTransaction then FLocalConnection.rollback; Log('An error occured while executing the script: ' + e.Message); end; end; I tried to set TFDTable with this options FetchOptions.RecsMax := 500000; // Sample value ResourceOptions.SilentMode := true; UpdateOptions.LockMode := lmNone; UpdateOptions.LockPoint := lpDeferred; UpdateOptions.FetchGeneratorsPoint := gpImmediate; And at least I tried to work with an :memory: database and pooled connection // close fdmanager FDManager.Close; // check if connection definition exists condef := FDManager.ConnectionDefs.FindConnectionDef(ConDefName); if not Assigned(condef) then condef := FDManager.ConnectionDefs.AddConnectionDef; // add connection definition condef.Name := ConDefName; condef.Params.DriverID := 'SQLite'; condef.Params.Database := ':memory:'; condef.Params.Pooled := true; condef.Params.UserName := ''; condef.Params.Password := ''; condef.Params.Add('SharedCache=False'); condef.Params.Add('LockingMode=Normal'); condef.Params.Add('Synchronous=Off'); condef.Params.Add('JournalMode=Off'); condef.Params.Add ('SQLiteAdvanced=auto_vacuum = 1;page_size = 4096;temp_store = MEMORY'); condef.Params.Add('LockingMode=Normal'); condef.Params.Add('CacheSize=60000'); condef.Params.Add('BusyTimeOut=30000'); condef.Apply; FDManager.Active := true; Nothing changed so far. Do some of you have any recommendation ?

Well, thank you so much, I wasn't aware of this. This worked.

@Dmitry Arefiev do you have some advice on how to get this working?

Testing cast(date('now') as date), will return 2019

Thank you Dany, forgot about CAST (and I heavily used it on mySQL many years ago...) That's strange. I'm getting year value "2018" instead of "01/02/2018" for example... select CAST(date(DATE_OF_LOSS,'start of month','+1 month','-1 day') as DATE) As DATE_OF_LOSS

Hello, I have a problem that is driving me nuts. FireDac somehow "lost" the ftDate type after using the SQLite date function. My SQLite table does have one field "DATE_OF_LOSS" defined as "DATE". Field "DATE_OF_LOSS" will correctly be retrieved as a ftDate field type (for exemple 01/02/2018) Select DATE_OF_LOSS from TABLE This is no more the case when doing this. Field "DATE_OF_LOSS" will be retrieved as a ftString field type (for example 2018-02-01). select date(DATE_OF_LOSS,'start of month','+1 month','-1 day') As DATE_OF_LOSS Does anyone knows how to solve this ? PS: using the latest Delphi RIO version, default SQLite connection parameters leaved to default. My OS Date time format is "dd/MM/yyyy"

It also happend here... but no way to reproduce !

@jbg many thanks

@jbg I've an issue while using the latest IDE Fixpack version. It seems there is an incompatibility with TMS Aurelius: I'm getting EInvalidPointer exception after validating the TAureliusDataset "Load field definition" dialog box. The error disappears after removing IDE FixPack Here is a call stack date/time : 2019-03-09, 11:41:04, 5ms operating system : Windows 10 x64 build 17763 system language : English system up time : 1 hour 57 minutes program up time : 47 seconds processors : 4x Intel(R) Core(TM) i7-8650U CPU @ 1.90GHz physical memory : 2698/6143 MB (free/total) free disk space : (C:) 12,31 GB display mode : 1920x1080, 32 bit process id : $d1c allocated memory : 340,85 MB largest free block : 1,98 GB command line : "C:\Program Files (x86)\Embarcadero\Studio\20.0\bin\bds.exe" -pDelphi executable : bds.exe current module : madExcept_.bpl exec. date/time : 2019-02-04 15:55 version : 26.0.33219.4899 compiled with : Delphi 10.3 Rio madExcept version : 5.0.0 callstack crc : $cc11aee6, $91e5296b, $e7ad32b4 exception number : 1 exception class : EInvalidPointer exception message : Invalid pointer operation. main thread ($22d0): 5005f7f5 +011 rtl260.bpl System 17404 +3 TObject.FreeInstance 5005f8e8 +008 rtl260.bpl System 17466 +1 TObject.Free 51110f2b +02b vclx260.bpl Vcl.CheckLst 248 +3 TCheckListBox.Destroy 50d06eee +0d6 vcl260.bpl Vcl.Controls 8600 +25 TWinControl.Destroy 50d6b0b0 +038 vcl260.bpl Vcl.ComCtrls 6235 +6 TTabSheet.Destroy 50d06eee +0d6 vcl260.bpl Vcl.Controls 8600 +25 TWinControl.Destroy 50d6a021 +085 vcl260.bpl Vcl.ComCtrls 5687 +5 TCustomTabControl.Destroy 50d6b750 +048 vcl260.bpl Vcl.ComCtrls 6469 +3 TPageControl.Destroy 50d06eee +0d6 vcl260.bpl Vcl.Controls 8600 +25 TWinControl.Destroy 50e52437 +04b vcl260.bpl Vcl.Forms 3122 +3 TScrollingWinControl.Destroy 50e537d0 +11c vcl260.bpl Vcl.Forms 3808 +32 TCustomForm.Destroy 23e0aaee +02e dclaurelius260.bpl Aurelius Design.Datasetdesigner.TfmFieldLoader.Destroy 5005f8e8 +008 rtl260.bpl System 17466 +1 TObject.Free 23e0ac3b +057 dclaurelius260.bpl Aurelius Design.Datasetdesigner.TfmFieldLoader.Execute 23e0db13 +033 dclaurelius260.bpl Aurelius Design.Dataseteditor.TAureliusDataSetEditor.ExecuteVerb 5283f00a +04e vcldesigner260.bpl VCLSurface 2918 +3 TVclDesignSurface.ComponentVerb 50e41dba +0aa vcl260.bpl Vcl.Menus 2561 +19 TMenuItem.Click 50e434e3 +013 vcl260.bpl Vcl.Menus 3505 +5 TMenu.DispatchCommand 50e4476e +082 vcl260.bpl Vcl.Menus 4678 +4 TPopupList.WndProc 50e446bd +01d vcl260.bpl Vcl.Menus 4653 +2 TPopupList.MainWndProc 753161ab +00b user32.dll DispatchMessageW 50e5e877 +0f3 vcl260.bpl Vcl.Forms 10724 +23 TApplication.ProcessMessage 50e5e8ba +00a vcl260.bpl Vcl.Forms 10754 +1 TApplication.HandleMessage 50e5ebed +0c9 vcl260.bpl Vcl.Forms 10892 +26 TApplication.Run 00518002 +06a bds.exe bds 214 +7 initialization 754a0177 +017 KERNEL32.DLL BaseThreadInitThunk

That's a *SHAME* Is EMB a 5 guys running business ?

Can you please eloborate ?