SqlDbx
You are not logged in.
Hi,
I am using the SQLDBX personal edition and querying a CUSTOMER table in an Oracle data warehouse with over 2 million rows.
I need to export consumer customers from the CUSTOMER table into a CSV file as requested by one of the departments.
The query is SELECT * FROM CUSTOMER WHERE MARKET_SEGMENT = 'CONSUMER'
I have the row limit set to 100 but the actually query would return around 900K rows (or customers).
SQLDBX is running on a Windows XP computer - unfortunately this is a client machine so no choice in the matter.
If I decided to display the 900K rows i.e. remove the row limit I would end up with an out-of-memory error.
I would like to export the entire 900K without having to remove the row limit due to the memory error? In other words, when exporting - re-execute the query without the row limit and pipe all data to the data file without it being displayed on the results grid.
Is this possible?
Interesting if I right-clicked on the CUSTOMER table in the object browser on the left and exported the entire 2 million rows, it works fine.
Thansk
Sunil
Offline
Yes, it is possible to export query results directly to file in SqlDbx Professional.
This is not supported in Personal Edition
Offline
How do you do it in SqlDbx Professional?
I don't see such option.
So far you can only export from the Result tab.
Offline
You have to execute something like this:
cmd export=file C:SQL
esults.csv
select * from table_name
Offline
Great idea, I did not know of its existence. Well... I know... RTFM!
But I hoped that the behaviour of SqlDbx was to act as a sort of "pipeline" to the export file, but no: the same limitations apply to this technique. I still get an "Out of memory. Partial results returned" error where I hoped to get the whole result set on the export file!
Apparently the behavior is just the same as 1.do the query in memory, without showing the result on the screen 2.export the result on the file. Why not taking a step further, and just read data from the database connection and writing it directly to the output file, thus allowing the application to export a virtually unlimited amount of rows, not limited by the app memory?
Offline
That is not how it works. Export to file is not limited by available memory.
Not sure why you experience out of memory error.
What database and SqlDbx version you use?
Offline
SqlDbx 4.1 professional 32bit, on a sqlserver database.
When I launched the command, the rowcount in the lower-right pane started counting, and the file I specified for the "cmd" export did not change, and so I assume that sqldbx was not writing to it. After reaching 300'000 rows or so, the command stopped with the memory error.
I tried again with the 64bit version (actually, I've just discovered it, I did not even know it existed!) and everything went fine: all the 790'000 rows have been written to the file. But... the behavior was the same: until all the rows have been completely received (counted) the file did not change. So, even for the 64bit version I assume that the behavior of sqldbx is to load everything and only at the end commit the rows to the file system!
My problem has now been solved by the 64bit version, but if you think that the behavior of the app is to pipeline the data directly on disk, you should double check!!!
Pietro
BTW: is there any drawback in using the 64bit version over the 32bit, obviously for a pc that can support both? Is there any reason why I should use the 32bit version?
Offline
Ok. You are right. Somehow it got disabled in one of the previous version.
It will be enabled in the next release.
x64 and x86 are identical.
If you can use x64 then you should. There's no drawback.
Offline
Great, thanks!
Pietro
Offline