Architecting, installing and maintaining your SAS environment

Database we use is being migrated from Greenplum to Oracle | Performance Difference

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Database we use is being migrated from Greenplum to Oracle | Performance Difference

One of the database which we use is migrated from Greenplum to Oracle  

 

So our team is doing some performance test by pulling same data from Oracle and Green Plum (1000 Records Table) to SAS where table coming from oracle DB is getting created by occupying 600 - 800 MB of size where as table coming from Green Plum is getting created by occupying 300MB of space under SAS work area.

 

And also date coloumn is getting created along with timestamp in table that was created from Oracle Database (tnsnames.ora) and same issue is not there on Green Plum (ODBC Connection).

 

Is there any setting that need to be done to avoid this or is it expected?


Accepted Solutions
Solution
‎09-08-2017 08:59 AM
Contributor
Posts: 28

Re: Database we use is being migrated from Greenplum to Oracle | Performance Difference

[ Edited ]

I can think of 2 possibilities here.

 

  • It’s possible that character fields have different length specifications in the 2 databases, causing one SAS dataset to occupy more space than the other.
    1. Note that Oracle has some data types that are not supported in Greenplum. The ones I think could affect the resulting SAS dataset size are Oracle LONG, CLOB, LONG RAW, and BLOB RAW.  If the Oracle DB has any columns specified with these types, they could result in SAS Character fields up to 32K long.
  • It’s possible that one of the SAS datasets is being compressed using SAS compression but the other is not.

 

Root Cause :

 

The issue is with CLOB datatype, apparently in Greenplum the CLOB size is lesser compared to Oracle, the difference in file size is seen only for tables that have this CLOB datatype and all other are same,

 

View solution in original post


All Replies
Super User
Posts: 5,490

Re: Database we use is being migrated from Greenplum to Oracle | Performance Difference

Posted in reply to avinashginjupal

If the table structures in the source are "identical", it probably comes down to how SAS/ACCESS maps data types by default. Check the documentation for each SA/ACCESS engine. One way of handling is using the DBSASTYPE option. 

You don't have any different encoding? (DBCS issues)

 

For the date/timestamp part, can you describe the issue in more detail, with examples?

Data never sleeps
Solution
‎09-08-2017 08:59 AM
Contributor
Posts: 28

Re: Database we use is being migrated from Greenplum to Oracle | Performance Difference

[ Edited ]

I can think of 2 possibilities here.

 

  • It’s possible that character fields have different length specifications in the 2 databases, causing one SAS dataset to occupy more space than the other.
    1. Note that Oracle has some data types that are not supported in Greenplum. The ones I think could affect the resulting SAS dataset size are Oracle LONG, CLOB, LONG RAW, and BLOB RAW.  If the Oracle DB has any columns specified with these types, they could result in SAS Character fields up to 32K long.
  • It’s possible that one of the SAS datasets is being compressed using SAS compression but the other is not.

 

Root Cause :

 

The issue is with CLOB datatype, apparently in Greenplum the CLOB size is lesser compared to Oracle, the difference in file size is seen only for tables that have this CLOB datatype and all other are same,

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 154 views
  • 0 likes
  • 2 in conversation