Execution speed

[BorgeJorge]

Thank you so much for your work on this! I thought you might be interested in a report on execution speed in R.

I'm running R 4.4.2 in Windows 10 with DuckDB 1.2.1 and read_stat installed today, and reading in a SAS file with ~1M records and 3 variables:

library(tidyverse)
library(haven)
library(duckdb)
library(tictoc)
con <- dbConnect(duckdb(), dbdir="test.duckdb")
dbExecute(con, "load read_stat")

tic()
read_sas("d:/duckdb/temp.sas7bdat") %>% 
  dbWriteTable(con,"test1", ., overwrite=TRUE)
toc()

tic()
dbExecute(con, "create or replace table test2 as
                  select * from read_stat('d:/duckdb/temp.sas7bdat')")
toc()

dbDisconnect(con, shutdown=TRUE)

The haven/DBI runtime is 2.71 seconds. The DuckDB/read_stat runtime is 10.93 seconds.

[mettekou]

Thank you so much for your work on this! I thought you might be interested in a report on execution speed in R.

I'm running R 4.4.2 in Windows 10 with DuckDB 1.2.1 and read_stat installed today, and reading in a SAS file with ~1M records and 3 variables:

library(tidyverse)
library(haven)
library(duckdb)
library(tictoc)
con <- dbConnect(duckdb(), dbdir="test.duckdb")
dbExecute(con, "load read_stat")

tic()
read_sas("d:/duckdb/temp.sas7bdat") %>%
dbWriteTable(con,"test1", ., overwrite=TRUE)
toc()

tic()
dbExecute(con, "create or replace table test2 as
select * from read_stat('d:/duckdb/temp.sas7bdat')")
toc()

dbDisconnect(con, shutdown=TRUE)

The haven/DBI runtime is 2.71 seconds. The DuckDB/read_stat runtime is 10.93 seconds.

@BorgeJorge Thank you for checking this. Could you please share your SAS file with me, so I can reproduce your issue?

[BorgeJorge]

Here you go (attached below). I should mention that I also tried to read this into DuckDB using the CLI and the same read_stat() command, and after 1-2 seconds the CLI window closes without warning.

If it helps, here's the header information from a SAS PROC CONTENTS:

temp.zip

[mettekou]

Here you go (attached below). I should mention that I also tried to read this into DuckDB using the CLI and the same read_stat() command, and after 1-2 seconds the CLI window closes without warning.

If it helps, here's the header information from a SAS PROC CONTENTS:

temp.zip

@BorgeJorge I have had a look and the suboptimal performance you experience is to be expected, as DuckDB reads data 2048 rows at a time and this requires ReadStat to open and seek in the file once every 2048 rows. You can observe similar suboptimal performance from pyreadstat when you read the file 2048 rows at a time, and I think haven does not provide the option at all. To avoid these open, seek, and close cycles, I will have to store the state of the parser myself, since ReadStat does not support this.

[StaffanBetner]

@mettekou Since I work with much larger SAS files, it is, yet, too slow, but very promising! Wouldn't an alternative be to have an internal buffer, that could be much bigger than DuckDB's vector size? (My workflow is usually to partioned read the SAS file into R, and then write as parquet files, so I don't need to work with the SAS data source repeatedly.)

[mettekou]

@mettekou Since I work with much larger SAS files, it is, yet, too slow, but very promising! Wouldn't an alternative be to have an internal buffer, that could be much bigger than DuckDB's vector size? (My workflow is usually to partioned read the SAS file into R, and then write as parquet files, so I don't need to work with the SAS data source repeatedly.)

@StaffanBetner @BorgeJorge The correct solution does not require an ad hoc buffer, but avoids repeatedly opening, seeking in, and closing the file. To do this, we need to store the state of the parser, including a reference to the open file and the position in it. That is a pull-based or library approach: the read_stat extension controls when the ReadStat parser advances and when it produces a new row. Currently, ReadStat implements a push-based or framework approach instead, determining control flow itself by calling callbacks we provide to it from the read_stat extension. To change that, we have to write the control flow ourselves and split it up over the bind, init, and table functions. It is not hard to do, but I do not have time for it now. Feel free to contribute!

[jmcloughlin]

Thanks for starting this important project! I will echo that I've seen similar performance issues. My use case is converting sas7bdat to parquet format using

COPY (SELECT * FROM read_stat('myfile.sas')) TO 'myfile.parquet' (FORMAT parquet)

from the CLI. This works great for small SAS files. However, for a larger-than-memory 17GB file, the process gets bogged down (still running since last night having only produced ~800M in the target parquet file). In contrast, on my 8GB RAM computer, I am able to convert the file to parquet in under 10 minutes using the parquetize package in R (which uses haven et al). The resulting parquet files total about 2.4G using parquetize.

Interestingly, duckdb is not getting bogged down by a lack of memory when I am running the above CLI conversion: duckdb never seems to go above 10M of memory usage, even though it's internal memory limit is 6.3G according to the command line. In contrast, the R parquetize program maxes out the available RAM while running (using close to 8GB).

All that's to say: to me, it seems like there is somewhere in read_stat that is not properly using the available buffer memory in duckdb. There might be clues as to how to address this in the source for other readers (eg csv) or in non-duckdb packages like haven. Of course, it could also be an independent issue with duckdb itself.

I will keep experimenting and report back if I solve any of the above. Thanks again for your efforts to make sas7bdat accessible via duckdb!

[HenricoWitvliet]

I've made some changes to the code to keep the parser active, see https://github.com/HenricoWitvliet/duckdb-read-stat. But I would like to add some tests, better understand the code and make the code more robust before I make a pull request. But if you would like to use these changes that would also be fine.

[mettekou]

I've made some changes to the code to keep the parser active, see https://github.com/HenricoWitvliet/duckdb-read-stat. But I would like to add some tests, better understand the code and make the code more robust before I make a pull request. But if you would like to use these changes that would also be fine.

@HenricoWitvliet Thank you for getting started on the issue! While keeping the parser around is a good first step, we also need to split up the readstat_parse_* functions into functions which only read metadata to be used in the DuckDB table bind and init functions and functions which read data, to be used in the actual table function. Right now, you also never free the memory you use for the parser, so you have a memory leak.

[HenricoWitvliet]

I thought I freed the memory for the parser, but looking back at the code in github, I see I do not.
I'll look at the documentation for the readstat_parse_ functions to see how they work. I thought you read zero rows in the bind-function to get the metadata and then used duckdb_vector_size() in duckdb_read_stat_function. But the performance was still slower than I expected.
I also got some weird behavior when I mistyped a file name in duckdb, instead of a file-not-found error.

[gregorywaynepower]

I've made some changes to the code to keep the parser active, see https://github.com/HenricoWitvliet/duckdb-read-stat. But I would like to add some tests, better understand the code and make the code more robust before I make a pull request. But if you would like to use these changes that would also be fine.

This seems like something best served as a Pull Request instead of a conversation in this thread.

[jrothbaum]

If it's helpful, I'm doing something similar for polars to read data using readstat. I couldn't figure out a way to do this efficiently that didn't keep the reader open. My solution (in rust...) was to have a background thread that kept reading and streams results in batches to the main thread (https://github.com/jrothbaum/polars_readstat/blob/master/crates/readstat/src/stream.rs).

The api I expose is to polars but it's pretty simple and I think it could expose c bindings to be called as part of a duckdb extension (if the build could be made to work....), You can see the api at: https://github.com/jrothbaum/polars_readstat/blob/master/polars_readstat/polars_readstat/__init__.py (where it's eventually called) and https://github.com/jrothbaum/polars_readstat/blob/master/crates/polars_readstat_rs/src/pybindings.rs (everything from 111 on is just returning python dictionaries of metadata),