If your source dataset is not too big (e.g. can be kept in operating memory without causing out of memory error), here are some recommendations on how to export it with optimal performance in Database Tour software.
First, try to use a SQL query instead of a Table object whenever possible. If you are new to SQL, and you need to export just one table, your SQL code is quite simple: select * from <table_name>, where <table_name> is the name of the table to export data from.
Second, if the source database is a remote database, fetch all records before exporting. The fetched records are allocated in your local operating memory, so the exporting process will not interact with the server and will perform much faster. Again, remember: we are talking about average sized datasets.
Exporting Average Dataset from GUI
- Open the source database.
- Click New SQL Window button. A new SQL window will appear.
- In the SQL editor, type your SQL query text and click Execute Query button.
- Click Last button above the data grid to fetch all records.
- Click Export data button.
- Choose the target export format.
- Specify needed export options. If the target format is Database, don't forget to turn on the Memory saving mode option and its related options if available; choose the correct Export mode.
- Click Export.
Exporting Average Dataset from the Command Line
Note: The command line functionality is available in Database Tour Pro.
- Save your SQL query to file.
- Build you command line manually using documentation or from the GUI by choosing Tools | Build Command Line | Export / Import Data....
- Create a .bat file and put created command line there. Or use the command line in other environment you are familiar with.
Here is an example of the command line for exporting data from remote PostgreSQL database to local SQLite file:
dbtour.exe /silent /export /ExportType=DATABASE /ExportMode=REPLACE+INSERT /CommitInterval=1000 /MemorySaving /UseSQLParameters /UseBatchMode /SrcDBInterface=fd /SrcDBKind=DSN /SrcDBDriver=POSTGRESQL /SrcDB=dwh /SrcServer=MyPgServer /SrcPort=5432 /SrcVendorLibrary=C:\Program Files (x86)\PostgreSQL\9.6\bin\libpq.dll /SrcDBUserName=servicedesk /SrcDBPassword=ant2%trH% /SrcSQLFile=c:\MyExportLib\DWH\SelectContracts.sql /TrgDBInterface=fd /TrgDBKind=FILE /TrgDBDriver=SQLITE /TrgTableName=contracts /TrgDB=C:\db\DWHCopy.db /CreateTargetContainer
At the end of the topic, all used command line parameters are described.
Exporting Average Dataset using Action File
It is recommended to use action files instead of classic command lines because of several benefits:
- Action files are more readable
- Action files allows comments
- If the command line contains entries, which look like environment variables, but are not (e.g. like /SrcDBPassword parameter in the command above), action files prevent their expanding
- Action files can be executed either from GUI or from command line
Let's rewrite the command line above as action file (please note that /silent parameter must not be included in the action file):
;Exporting from data warehouse to local SQLite db
/export
/ExportType=DATABASE
/ExportMode=REPLACE+INSERT
/CommitInterval=1000
/MemorySaving
/UseSQLParameters
/UseBatchMode
;source database:
/SrcDBInterface=fd
/SrcDBKind=DSN
/SrcDBDriver=POSTGRESQL
/SrcDB=dwh
/SrcServer=MyPgServer
/SrcPort=5432
/SrcVendorLibrary=C:\Program Files (x86)\PostgreSQL\9.6\bin\libpq.dll
/SrcDBUserName=servicedesk
/SrcDBPassword=ant2%trH%
/SrcSQLFile=c:\MyExportLib\DWH\SelectContracts.sql
;target database:
/TrgDBInterface=fd
/TrgDBKind=FILE
/TrgDBDriver=SQLITE
/TrgTableName=contracts
/TrgDB=C:\db\DWHCopy.db
/CreateTargetContainer
Let's save the file as c:\MyExportLib\DWH\SelectContracts.txt. Now, we can execute it either from GUI (Database | Open | Action File menu) or from the command line:
dbtour.exe /silent /ActionFile=c:\MyExportLib\DWH\SelectContracts.txt
And, finally, let's describe the used command line parameters:
/silent
Tells the app to perform the action in the 'silent' mode (without any windows)
/export
Tells the app to perform the data exporting
/ExportType=DATABASE
Specifies the target export format
/ExportMode=REPLACE+INSERT
The target table will be recreated if exists
/CommitInterval=1000
The commit command will be issued after exporting every 1000 records
/MemorySaving
Exporting will be done without opening the target table
/UseSQLParameters
To speed up the execution, SQL parameters will be used
/UseBatchMode
To speed up the execution, the SQL commands will be combined into batches; the size of each batch depends on the /CommitInterval
/SrcDBInterface=fd
The source database will be connected by FD engine
/SrcDBKind=DSN
The source database kind is not a file, folder, or connection string, so use DSN here
/SrcDBDriver=POSTGRESQL
The type of the source database is PostgreSQL
/SrcDB=dwh
The current PostgreSQL catalog will be dwh
/SrcServer=MyPgServer
Specifies the source database server
/SrcPort=5432
Specifies the source database port
/SrcVendorLibrary=C:\Program Files (x86)\PostgreSQL\9.6\bin\libpq.dll
Specifies the vendor library to work with the source database; in most cases, the app can locate it automatically
/SrcDBUserName=servicedesk
The source database will be connected by user servicedesk
/SrcDBPassword=ant2%trH%
Specifies the user password to connect the source database
/SrcSQLFile=c:\MyExportLib\DWH\SelectContracts.sql
Specifies the path to SQL file with the source database query
/TrgDBInterface=fd
The target database will be connected by FD engine
/TrgDBKind=FILE
The target database kind is file
/TrgDBDriver=SQLITE
The type of the target database is SQLite
/TrgTableName=contracts
The target table is contracts
/TrgDB=C:\db\DWHCopy.db
Specifies the path to the target database
/CreateTargetContainer
Tells the app to create the target database file if it does not exist yet
See also
