Fastest way to read SAS table in C#?

[keV]

I am currently reading SAS tables using DbCommand.ExecuteReader with CommandType.TableDirect.

Is this the fastest way to read data?

Thanks

Kevin

[Oligolas]

I'll suspect XMLs to be faster if you have them. But with sas7bdat, maybe you can have a look at these papers: SESUG paper SAS Support and Blog.

[Ksharp]

Maybe @ChrisHemedinger could give you a hand.

[ChrisHemedinger]

It depends on the dimensions of the table and whether you're reading it as a bulk set or paging in across groups of observations.

I have a simple example in the SASHarness application in GitHub.  If you don't need all the data at once, I suggest applying data set options like FIRSTOBS, OBS=, KEEP and/or DROP, and WHERE= to limit the data that SAS will return.  This is assuming that you are connecting to a SAS session to read this data.  The SAS data providers will read the entire row, so wider data is naturally slower to read.  You can limit this if you don't need all columns by applying KEEP or DROP when you open the data.

If reading sas7bdat files directly using the SAS Local OLE DB provider (no SAS session), then options are more limited, I think.  I have a Windows PowerShell example that you may be able to adapt.

[AhmedAl_Attar]

If you don't have SAS software licensed, you may try to integrate the following command-line tool and MIT-licensed C library into your C# code

Hope this helps,

Ahmed

[keV]

Thanks to everyone for your speedy replies. There doesn't seem any obvious alternative to what I'm already doing.

I have been using the Local provider with the ADO .Net recipe referenced in one of the replies for several years now, but one of our clients has complained of slow reading with large datasets. However, the reading from the SAS file is just one part of the overall process so I will go and do some further investigation of the whole process and see where the bottlenecks are.

Thanks again for everyone's interest..

[AlanC]

I have a lot of C# around SAS. Here is a method to handle the read. I would defer to Chris H.'s suggestions and drop/keep as many variables as you can. Contact me directly for a dll if needed. The below is in Framework: I have not converted to Core as of yet. OleDb is in flux on core and I haven't looked at latest info.

public DataTable GetDataSet(string sasLibrary, string dataset)
{
OleDbConnection sas = null;
DataTable dt = new DataTable(dataset);
try
{
sas = new OleDbConnection(@"Provider=SAS.LocalProvider.1; Data Source=" + sasLibrary);
sas.Open();
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = dataset;
OleDbDataReader sasRead = sasCommand.ExecuteReader();
dt.Load(sasRead);
sas.Close();
}
catch (Exception ex)
{
sas.Close();
string errMessage = "Unable to get the SAS dataset. Library: " + sasLibrary + ", DataSet: " + dataset + ", " +
ex.TargetSite.Name;
HandleError(MethodBase.GetCurrentMethod().Name, ex);
}
finally
{
sas.Close();
}
return dt;
}

public DataTable GetSasDataSetMetadata(string sasLibrary, string dataset)
{
OleDbConnection sas = null;
try
{
sas = new OleDbConnection(@"Provider=sas.LocalProvider; Data Source=" + sasLibrary);
sas.Open();
OleDbCommand sasCommand = sas.CreateCommand();
sasCommand.CommandType = CommandType.TableDirect;
sasCommand.CommandText = dataset;
DataTable dt = sas.GetSchema("Columns");
DataView dv = dt.DefaultView;
dv.RowFilter = "TABLE_NAME = '" + dataset.ToUpper() + "'";
sas.Close();
return dv.ToTable();
}
catch (Exception ex)
{
HandleError(MethodBase.GetCurrentMethod().Name, ex);
return null;
}
}

[keV]

Thanks - this is exactly what I'm already doing, but exposing the DataReader rather than pre-reading into a table.

I think this is safer and less memory intensive for large tables.

[AlanC]

What are you using for .NET? Is it Framework or Core and what version,please.

[keV]

.Net Framework 4.6.1

[AlanC · Accepted solution]

If possible, switch to .NET Core 3.1.1. OleDb is supported on .NET Core 3+. Why? It is a lot faster than Framework and you will probably get an intrinsic boost. Plus, Core is the way to go for all future stuff as well.

Take a look at this SO article:https://stackoverflow.com/questions/40718537/how-to-convert-idatareader-into-stream-in-c-sharp

Specifically,

public IEnumerable<Order> GetOrders()
{
   IDbCommand cmd = ...  <<build your command here>> ...
   using(var rdr = cmd.ExecuteDataReader())
   {
      while(rdr.Read())
      {
          Order order = new Order {Id=rdr.GetDecimal(1), Name=rdr.GetString(2)};
          yield return order;
      }
   }
}

I may be able to test something but I am pretty busy right now. I think switching to Core is needed unless there is something compelling keeping you away. I have been using Core for a long time and honestly, Core 3.1 is fantastic.

[keV]

Thanks for the tip Alan. SAS data is just one part of a data reading component, which we have already partially migrated to .Net Core. OleDb wasn't available at the time in .Net Core. We'll definitely look at that soon.

Why do you think it is faster when it is still using SAS's OleDB component?

[AlanC]

OleDb support in Core was a hot topic for a number of shops migrating from Framework to Core due to things like Access DB. It was added in in May 2019 and was put into Core 3.0 in Sept as a release. 

I cannot say for sure that it will be faster. It requires testing. Heck, it may not work but give it a try in a small, isolated test project.

You are welcome to reach out to me directly as well. If I get a chance, I will try and test on my own. 

[keV]

Yep, we've been waiting for the OleDb support. Hopefully we'll find some time to address it shortly.  Thanks again for all your help. I'll keep you posted.

[AlanC]

This works in Core 3.1.1 with nuget System.Data.OleDb and SAS Provider for OleDb 9.48. Solution attached:

        static void Main(string[] args)
        {
            Console.WriteLine("Starting test...");
            var dt = ReadSasDataset(@"z:\scratch", "shoes");
        }

        private static DataTable ReadSasDataset(string sasLibrary, string dataset)
        {
            OleDbConnection sas = null;
            DataTable dt = new DataTable(dataset);
            try
            {
                sas = new OleDbConnection(@"Provider=SAS.LocalProvider.1; Data Source=" + sasLibrary);
                sas.Open();
                OleDbCommand sasCommand = sas.CreateCommand();
                sasCommand.CommandType = CommandType.TableDirect;
                sasCommand.CommandText = dataset;
                OleDbDataReader sasRead = sasCommand.ExecuteReader();
                dt.Load(sasRead);
                sas.Close();
            }
            catch (Exception ex)
            {
                sas.Close();
                string errMessage = "Unable to get the SAS dataset. Library: " + sasLibrary + ", DataSet: " + dataset + ", " +
                    ex.TargetSite.Name;
            }
            finally
            {
                sas.Close();
            }
            return dt;
        }