Performance for tables with so many columns via ODBC.

[hogego]

Hi All.

I'm worried about query performance from DB.

DBMS is Postgres.
I'm connecting with ACCESS interface to ODBC and issuing queries, but the problem is that Postgres has a table with so many columns. It has 500 columns and the observation length is about 1Mbyte.

Simply trying to join this table will occupy memory and create a very large WORK file.

Is there a way to improve performance without changing the layout.....?

Regards.

[japelin]

Try using compress option.

In the Query Builder, select Options, then Options for this Query. In the Results tab, add compress=yes in the Options field.

Or in data step, describe "options compress=yes;".

This can be used to compress SAS data sets.

A large compression effect can be expected for large data sets.

[AlanC]

Compression can negatively impact a dataset and performance by a lot, in either direction. The characteristics of the data must be in line for compression to help.

Without compression, all records in the SAS dataset are fixed width. If the columns are relatively tight (width matches content), compression will hurt. If the contents vary a lot, for example, lots of wasted address space (addr1, addr2, addr3), compression 'may' help. test it to find out. Lots of char columns with wasted space probably results in a positive impact. Lots of numerics probably hurts. Numerics in SAS are always doubles so compression does not help.

I have seen dramatic changes by simply telling a client to turn off compression. Compression also has other negative impacts.

Can it help? Yes. Should you default to using it all of the time? No. 

[Patrick]

Write the query syntax in a way that it executes in-database (implicit or explicit pass-through SQL). Only load the result set to SAS.

[AlanC]

Make sure to minimize the columns desired. Your biggest impact will be to simply reduce the information obtained by only choosing what is needed. Columns are far, far more costly than rows. Use SELECT var names vs SELECT *.

Agreed with what was said by pushing the query back to source but really, are all 500 columns used?

This is not a SAS problem, per se. It is a design issue. Having 500 columns is a problem. SELECTs, WHEREs, GROUP BYs to overcome it. Again, reemphasizing, push the problem to the db.

[SASKiwi]

One of SAS's golden rules is only select the columns you need. That way you should dramatically reduce the size of your SAS WORK tables. Also test with the COMPRESS = YES or BINARY SAS option which can reduce table sizes further and even improve performance if the compression ratio is high.