Dynamic SQL DisplayFormatted split files on row count
I have the requirement to dump large result sets into files. I am currently using %SQL.Statement and the result set created by it.
The DisplayFormatted is used to write a CSV. The query I used to test does not contain any filters, it is just a select * from a table with 16 million rows.
The file created is 2GB in size. The issue I have is that it takes 8 hours to create this file.
I have seen in the past that file streams write fast at the start. As the file continues to grow, the process' memory usage increases until it reaches the max allowed. After that, the file writing slows down significantly. By monitoring the process I have seen that some form of memory management gets done, but it seems like some sort of swapping, as the memory used drops a little and then goes back to the max.
I have resolved this in other places in our applications where we write files, by adding a sequence to the file name, and only writing 20k records per file. This keeps the memory usage under control, and subsequently much faster file output.
Is there a standard way of splitting the files on a number of rows using the DisplayFormatted method of the resultset?