Exporting very large data sets

Top  Previous 

In SQL Blob Viewer, you usually run a SQL script to retrieve the data set you want to export.  For very large data sets, retrieving the entire data set into your system's memory may not be feasible or exceeds your hardware capacity.


For example, say you want to export a thousand images from your database, and each image is around 5 MB in size.  The total size of the data set is then approximately 5 GB.  Say your system memory is only 4 GB.  It's then not possible to export the data set the usual way.





What you need to do is to tell SQL Blob Viewer to just return the first few rows of the data set, using the Return option.  Say you select 5 rows.





SQL Blob Viewer then returns just the number of rows you specify.  It does not need to retrieve all 1000 images.  It just retrieves the first 5 rows and display the data, allowing you to check if it's the data you want, and also to tell SQL Blob Viewer the columns that will be returned, so that you can use them in your file naming convention.




Now, when you choose one of the export options, SQL Blob Viewer will still retrieve and export all 1000 images, but it does this in batches.  Say you choose the Export to text files option.




You will see the Batch size option in the top right.  What this value represents is the number of rows SQL Blob Viewer retrieves each time from your server.  Thus, in our example, a value of 20 means SQL Blob Viewer will retrieve only 20 rows each time (consuming approximately 100 MB of memory, as each image is 5 MB in size), export the 20 images to disk, discard the 20 rows, fetch the next 20 rows, and repeat the process again.  In this way, SQL Blob Viewer will only use a maximum of 100 MB to store the data set.




Generally, the larger the batch size, the faster the export process, but consumes more memory.  Thus, you have to know the average row size, the RAM available on your system, and work out the appropriate batch size.