Hi All, Any way to make exporting data any faster in DataEase 8.5? If I'm exporting several hundred thousand records it can take hours. Anything under 100k records seems to export much quicker. It can also take a long time to delete a large number of records. Your advice would be greatly appreciated.

hi! It is obvious to delete and export just in time … before exceeding that critical  amount of records … other tricks are risky in de … indexing ….. reindexing etc.

Download Sample (big)

there is several ways to export data from 8.5 so to start solving this we would need to know what method you use?

The "quirky" workaround in DQL with 
export to "c:\blat\export.txt" .

RecordID~TransactionDescription ~Text ~Value ~Currency ~CurrencyValue ~Rate

.items ole

@f[1,1]~@f[1,2]~@f[2,1]~@f[2,2]~@f[2,3]~@f[2,4]~@f[2,5]

.end

Is the absolutely slowest and also seem to be very variable from slow to extremely slow to never..

Over the years we have looked at several workarounds and even back in early 8 we changed the export function so it would take a definition etc. so one could make a "hack" in DQL to use a export definition instead which is much faster than the "export to" stuff which was a bad implmentation of a bad idea.

DataEase 8.5 is a long time ago now and a lot of stuff has moved forward since then, also in the areas of export etc.

In LE9/DE9 you can basically either use ExecDQL or the built in export feature in DQL which is blitzingly fast. (see above).

You can also open any DQL for Export etc. via DocumentRun() where you can tell it what you want it to do and where you want to send the export.
https://www.dataease.com/dg3_HelpView/?PageID=1382...

There has been a "habit" of using temporary tables in DataEase/Ffenics to move data to before exporting/manipulating/reporting etc.

Most likely this was done as things didn't work as they should in DQL/Reporting but it was never very good advice as one of the slowest things you do in DataEase is to do a conditional delete i.e. "delete records in temptable with owner = current username ." 

But if one use temporary tables then one should flush it rather than delete row by row i.e. delete records in tablename . (this drop the table). rather than For Tablename with etc ... delete records or delete records in mytable with owner etc. as above.

And when you use temporary tables DON'T USE INDEXES! it is the index updating that slow down the process most as this is done for each row.

This is good advice in general - to be very careful with index us!!! Most people wrongly assume that indexes speed things up but that is only true when used correctly and wisely. 

Maintaining the indexes is what slow down insert/modify and delete and it only help with searching up in very big tables when you search in the correct field in the correct way... So mostly avoid if you don't understand them

Try to search for anyting in the Companies table in our Toolbar freetext search (remember this is close to 2 million records). You will see that it takes time but not anything like you think and then it has searched through ALL the records for ALL occurences.




In the sample, you can see the code and speed for exporting data in different ways and you will see how fast the DE9/LE9 way is compared to the traditonal and you will see how flexible ExecDQL is in creating the output format you want.

besides indexing reindexing the next tip is obvious even more … but shoking .. ready ? Let’s go ;)

I am very sorry … excuse my Ladies & Gentlemen ..

The only way… is binary coding not assembler for sure no no nio

Direct access to physical devices as memory , disks and etc. via machine coding 

Nothing with high level but generally very dlowcnon effective code 

Don't forget in that world of 0 and 1 … yup … 1+1=0

This id from my working practice since 1978 … to be exact with Soviet clone op Hewlett-Packard mini terminal of data connection for power energetics … the only way to get time shortage . So even dBase db in the case was more speedy than … keep silence nevertheless