Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Home
- /
- About Michael_SAS
04-02-2019
Michael_SAS
SAS Employee
Member since
06-23-2011
- 13 Posts
- 0 Likes Given
- 0 Solutions
- 3 Likes Received
About
I lead the product management and innovations efforts for SAS’ data management and emerging technology offerings. I specialize in data management architecture and applying advanced analytics to a wide variety of business and technical problems. My areas of expertise include data warehousing, real-time systems, and machine learning.
data warehousing, real-time systems, and machine learning
-
Latest posts by Michael_SAS
Subject Views Posted 2823 12-19-2013 02:17 PM 2671 12-11-2013 04:27 PM 2671 12-11-2013 11:00 AM 2740 09-11-2013 11:31 AM 1905 08-21-2013 03:04 PM 2335 04-25-2011 10:06 AM 4259 02-18-2011 04:01 PM 2565 02-08-2011 09:07 PM 1374 02-08-2011 12:13 PM 5478 01-07-2011 01:05 PM -
Activity Feed for Michael_SAS
- Got a Like for SAS/Access to Oracle & READBUFF Performance. 09-05-2018 08:14 AM
- Got a Like for The SQL tuning checklist. 02-19-2018 12:31 PM
- Posted Re: Use date filter in Extract - DI Studio on SAS Data Management. 12-19-2013 02:17 PM
- Posted Re: SCD Type 2 - DI - Beginning Date on SAS Data Management. 12-11-2013 04:27 PM
- Posted Re: SCD Type 2 - DI - Beginning Date on SAS Data Management. 12-11-2013 11:00 AM
- Posted Re: update a table based on the values of another table on SAS Data Management. 09-11-2013 11:31 AM
- Posted The SQL tuning checklist on SAS Data Management. 08-21-2013 03:04 PM
- Posted Re: HOW TO: Assign a column value to a global variable on SAS Data Management. 04-25-2011 10:06 AM
- Posted Re: Insert into a SQL table with identity column from DI Studio on SAS Data Management. 02-18-2011 04:01 PM
- Posted Re: Using effective dates in lookup transformation on SAS Data Management. 02-08-2011 09:07 PM
- Posted 4 Easy Tips to Dramatically Improve Integration Performance with Oracle on SAS Data Management. 02-08-2011 12:13 PM
- Posted SAS/Access to Oracle & READBUFF Performance on SAS Data Management. 01-07-2011 01:05 PM
-
My Liked Posts
Subject Likes Posted 1 01-07-2011 01:05 PM 1 08-21-2013 03:04 PM
12-19-2013
02:17 PM
an alternative to a join, would be to use a macro variable. I use the pre-code in the transform to set the macro variable something like PROC SQL NOPRINT; SELECT A.DATE into :LOAD_DATE FROM TABLE; QUIT; then reference the macro variable in your FIlter transform SOURCE_DATE > &LOAD_DATE if source_date is a "date" data type you'll want to cast your macro variable to a date give that a try mike
... View more
12-11-2013
04:27 PM
I actually think it should be as fast if not faster because the compare table can also use a perm XREF.
... View more
12-11-2013
11:00 AM
Another option is to use the Table Comparision Transform to produce two data sets of New and Update records then handle the new records with one path - just append them, then the update records pass them into your type 2 scd transform...
... View more
09-11-2013
11:31 AM
Take a look at the Update transform DI Studio 4.4. it supports Updates with Correlated Sub Queries update table A set column_A = ( SELECT column_B FROM B WHERE B.Var1 = A.Var1)
... View more
08-21-2013
03:04 PM
1 Like
The SQL query had been running for 36 hours before it was killed. The project team is panicking. The customer is upset. What can you, a SAS® programmer, do to get out of this dilemma? Something has to be done, but what? Chances are you have been there. You have felt the pain. You have lived through the helplessness and fear of imminent failure. Fortunately, it is possible to get out of this mess. A SAS colleagues, Tatyana Petrova, and Jeff Bailey wrote a SAS Global Forum 2013 paper that shows show you the steps (and the mindset) required to solve this all-too-common problem. The paper includes examples from DB2, Greenplum, Oracle, and Teradata. I want to know about your experiences with such situations. What have you done to resolve them?
... View more
04-25-2011
10:06 AM
Have you tried CURRVAL? it returns the current value of a sequence. here is an example.
create sequence seq_customers;
select seq_customers.nextval from dual;
NEXTVAL
----------------------
2
select seq_customers.currval from dual;
CURRVAL
----------------------
2
... View more
02-18-2011
04:01 PM
It is pretty easy, first in SAS when you create your connection turn on the ignore_read_only_columns in SAS management console when you setup the Libname it is just a libname option, by default it is set to NO. It is counter intuitive but by ignore_read_only_columns = yes lets SAS ignore mapping stuff to identity columns.
libname srv odbc user=maimes pass=’XXXX’ DATASRC=SQLServerExpress ignore_read_only_columns=yes;
in your job use a loader and just don't map anything to your identity column
I attached a doc with some screen shots and info on how to use sequences as well.
... View more
02-08-2011
09:07 PM
Just to further the hash discussion along, there are dozens of papers on the hashing but here are some good ones:
The SAS® Hash Object: It's Time To .find() Your Way Around
http://support.sas.com/resources/papers/proceedings10/151-2010.pdf
Getting started with hash object
http://support.sas.com/rnd/base/datastep/dot/hash-getting-started.pdf
How Do I Love Hash Tables? Let Me Count The Ways!
http://www2.sas.com/proceedings/forum2008/029-2008.pdf
mike
... View more
02-08-2011
12:13 PM
face=Calibri>Four simple things to do to improve Data Integration performance
with Oracle
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1"
class=MsoListParagraphCxSpFirst>
style="LINE-HEIGHT: 115%; mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin; mso-bidi-font-size: 13.0pt; mso-bidi-font-weight: bold">
style="mso-list: Ignore">1.
style="FONT: 7pt 'Times New Roman'">
Change default READBUFF
settings for Oracle!
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Cambria','serif'; COLOR: #4f81bd; FONT-SIZE: 13pt; mso-ascii-theme-font: major-latin; mso-fareast-font-family: 'Times New Roman'; mso-fareast-theme-font: major-fareast; mso-hansi-theme-font: major-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: major-bidi; mso-themecolor: accent1">
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpMiddle>
style="LINE-HEIGHT: 115%; mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri; mso-bidi-font-size: 13.0pt; mso-bidi-font-weight: bold">
style="mso-list: Ignore">-
style="FONT: 7pt 'Times New Roman'">
READBUFF=1 means that only
one row is retrieved at a time. Oracle defaults to 250, in almost every case
significant performance can be obtained changing that to 1000. For example:
face="Courier New">
color=#0000ff size=2 face="Courier New">
libname
face="Courier New"> ora oracle user=sh pass=sh path=ora10g
READBUFF=
color=#008080 size=2 face="Courier New">
face="Courier New">1000
face="Courier New">;
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Cambria','serif'; COLOR: #4f81bd; FONT-SIZE: 13pt; mso-ascii-theme-font: major-latin; mso-fareast-font-family: 'Times New Roman'; mso-fareast-theme-font: major-fareast; mso-hansi-theme-font: major-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: major-bidi; mso-themecolor: accent1">
size=3 face=Calibri>
face=Calibri>
face=Calibri>
size=3>NOTE: DB2 is the exception to this rule, SAS/Access to
DB2 automatically determines the optimal READBUFF setting based on the
row-length of your result set.
size=3 face=Calibri>
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1"
class=MsoListParagraphCxSpMiddle>
style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin">
style="mso-list: Ignore">2.
style="FONT: 7pt 'Times New Roman'">
Compress your SAS datasets!
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpMiddle>
style="mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri">
style="mso-list: Ignore">-
style="FONT: 7pt 'Times New Roman'">
There are two methods by
which to implement data set compression in SAS. The overhead of uncompressing is
negligible compared to the I/O performance gains.
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l1 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">
Data Set Option – manually
add an option to each Data Set that you create to compress it. For example DATA
A (COMPRESS=YES); SET X; RUN. In DI
Studio, simply go to the options tab of the table to set the option.
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l1 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">
System Option – the other
method is to set a system option COMPRESS=YES. When you set the system option
ALL SAS Data Sets are compressed. We recommend using the system option to insure
that all data sets are compressed.
class=MsoListParagraphCxSpMiddle>
face=Calibri>
class=MsoListParagraphCxSpMiddle>NOTE: While
compress doesn’t improve RDBMS performance it improves the I/O interactions
between SAS and the RDBMS.
class=MsoListParagraphCxSpMiddle>
face=Calibri>
class=MsoListParagraphCxSpMiddle>NOTE:
style="mso-spacerun: yes"> Not only can you compress your SAS Data
Sets but Oracle and other RDBMS also support Table level Compression. Oracle
table compression not only saves disk space, it also helps to increase cache
efficiency since more blocks can now fit in the memory. To do this you simply
need to pass an option when you create the table. For example data
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">ora.myOraTbl(DBCREATE_TABLE_OPTS=COMPRESS).
class=MsoListParagraphCxSpMiddle>
face=Calibri>
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l1 level1 lfo1"
class=MsoListParagraphCxSpMiddle>
style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin">
style="mso-list: Ignore">3.
style="FONT: 7pt 'Times New Roman'">
Use Bulk Loaders when you
need to load large stuff fast!
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri">
style="mso-list: Ignore">
style="FONT: 7pt 'Times New Roman'">
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="mso-ascii-font-family: Calibri; mso-fareast-font-family: Calibri; mso-hansi-font-family: Calibri; mso-bidi-font-family: Calibri">
style="mso-list: Ignore">
style="FONT: 7pt 'Times New Roman'">DI
Studio has made bulk loading easy. The Loader transform loads data into tables
using both inserts, updates and bulk load operations. to invoke the bulk loader
simply set the option BULKLOAD = YES. The loader also provides RDBMS specific
options, for example with the Oracle loader you can do direct path loads simply
by setting the direct path optoin BL_DIRECT_PATH=YES.
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>In SAS code,
face=Calibri>SAS has made it simple BULKLOAD=YES will invoke the bulk load
utilities for most RDBMS. Each SAS/Access Engine provides RDBMS specific loader
options for example to get the best performance with Oracle use the
BL_DIRECT_PATH=YES option to perform a direct path load.
style="mso-spacerun: yes"> Here are the three most common examples
of bulk loading:
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>Example 1: OC SQL - create a
new table and bulk load it OR
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">append
results to an existing table.
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">proc
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">sql
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">create
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">table
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
o.PROCSQLBL
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes"> (BULKLOAD=yes
BL_DIRECT_PATH=YES)
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">as
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">select
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
*
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">from
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
sales_1;
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">INSERT
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">INTO
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
TABLE o.PROCSQLBL
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes"> (BULKLOAD=yes
BL_DIRECT_PATH=YES)
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">as
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">SELECT
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
*
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">from
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
sales_1;
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">quit
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast> Example 2:
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">DATA
STEP – create a new table and load it.
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">DATA
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
o.DATASTEPBL
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes"> (BULKLOAD=yes
BL_DIRECT_PATH=YES);
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 1">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">set
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
sales_1;
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 56.25pt; mso-add-space: auto; mso-list: l0 level1 lfo2"
class=MsoListParagraphCxSpLast>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">Example
3.
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">PROC
APPEND – append results to an existing table.
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">PROC
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">APPEND
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">BASE
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">=O.PROCAPPBL
class=MsoNormal>
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">
(BULKLOAD=yes BL_DIRECT_PATH=YES)
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">DATA
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">=sales_1;
style="LINE-HEIGHT: 115%; FONT-FAMILY: 'Calibri','sans-serif'; FONT-SIZE: 11pt; mso-ascii-theme-font: minor-latin; mso-fareast-font-family: Calibri; mso-fareast-theme-font: minor-latin; mso-hansi-theme-font: minor-latin; mso-bidi-font-family: 'Times New Roman'; mso-bidi-theme-font: minor-bidi; mso-ansi-language: EN-US; mso-fareast-language: EN-US; mso-bidi-language: AR-SA">
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">run
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1"
class=MsoListParagraphCxSpFirst>
style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin">
style="mso-list: Ignore">
size=3>4. Gather, Collect, and Analyze
Table Statistics! The number one performance problem relational database (RDBMS)
is the lack of table level statistics for the RDBMS’ SQL optimizer. SAS
contributes to the problem because SAS makes it so easy to create tables; users
often overlook the gathering of optimizer statistics. You must gather statistics
periodically for table objects where the statistics become stale over time
because of changing data volumes or changes in column values. New statistics
should be gathered after a tables data or structure is modified. For example you
should gather statistics after any of the following:
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">
After loading a significant number of rows into a
table.
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">
After updating data in a table you might need new
statistics on the average row length.
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 0pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpMiddle>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">
After bulk load operations
style="TEXT-INDENT: -0.25in; MARGIN: 0in 0in 10pt 1in; mso-add-space: auto; mso-list: l0 level2 lfo1"
class=MsoListParagraphCxSpLast>
style="FONT-FAMILY: Symbol; mso-fareast-font-family: Symbol; mso-bidi-font-family: Symbol">
style="mso-list: Ignore">·
style="FONT: 7pt 'Times New Roman'">
After table structure has changed
Here is
an example of how to do this with Oracle
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">proc
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">sql
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">connect
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">to
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
Oracle( USER=XX PASSWORD=XX path=ORA10g);
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">execute
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
( execute
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">
style="mso-tab-count: 1">
style="mso-spacerun: yes">
style="mso-tab-count: 1">
style="mso-bidi-font-weight: normal">DBMS_STATS.GATHER_TABLE_STATS
(
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 2">
ownname =>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: purple; FONT-SIZE: 10pt">'SH'
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">,
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">
style="mso-tab-count: 1">
style="mso-spacerun: yes">
style="mso-spacerun: yes"> tabname =>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: purple; FONT-SIZE: 10pt">'SALES'
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">,
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-spacerun: yes">
style="mso-spacerun: yes"> estimate_percent =>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: teal; FONT-SIZE: 10pt">40
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
) )
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">by
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
oracle;
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="mso-tab-count: 2">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">disconnect
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: blue; FONT-SIZE: 10pt">from
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
oracle;
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: navy; FONT-SIZE: 10pt">quit
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">;
style="LINE-HEIGHT: normal; MARGIN: 0in 0in 0pt 0.5in; mso-layout-grid-align: none"
class=MsoNormal>
style="FONT-FAMILY: 'Courier New'; BACKGROUND: white; COLOR: black; FONT-SIZE: 10pt">
Note: a
best practice is to simply have an administrator setup a scheduled job to gather
schema statistics periodically.
... View more
01-07-2011
01:05 PM
1 Like
The default setting for SAS/Access to Oracle is 250 buffers. A READBUFF specifies the number of rows to read into SAS’ buffer. READBUFF can have a dramatic impact on performance. Here is an example of how you can test what a good READBUFF setting might be.
First I create three libnames with different READBUFF settings
libname ora1 oracle user=sh pass=sh path=ora10g READBUFF=250;
libname ora2 oracle user=sh pass=sh path=ora10g READBUFF=1000;
libname ora3 oracle user=sh pass=sh path=ora10g READBUFF=10000;
Next I identify a relatively large table in terms of column width and row count. For this example I have a table called INFORMS2009 which contains 79 columns and 1M records.
Finally, I make three runs with a datastep program to extract data
data test1;
set ora1.INFMS2009;
run;
data test2;
set ora2.INFMS2009;
run;
data test3;
set ora3.INFMS2009;
run;
And review the results:
NOTE: There were 1006114 observations read from the data set ORA1.INFMS2009.
NOTE: The data set WORK.TEST1 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 1:06.90
cpu time 20.01 seconds
NOTE: There were 1006114 observations read from the data set ORA2.INFMS2009.
NOTE: The data set WORK.TEST2 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 40.70 seconds
cpu time 21.54 seconds
NOTE: There were 1006114 observations read from the data set ORA3.INFMS2009.
NOTE: The data set WORK.TEST3 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 33.18 seconds
cpu time 20.90 seconds
How about 1m 6 seconds vs. 33 seconds! that is a 63% improvement in performance. Note your millage may vary, also like candy too much of a good thing can be bad too.
For example jacking up the READBUFF to 20k can actually result in worse performance than the default setting.
libname ora4 oracle user=sh pass=XX path=ora10g READBUFF=20000;
data test4;
set ora4.INFMS2009;
run;
NOTE: There were 1006114 observations read from the data set ORA4.INFMS2009.
NOTE: The data set WORK.TEST4 has 1006114 observations and 79 variables.
NOTE: DATA statement used (Total process time):
real time 1:11.90
cpu time 23.01 seconds
for more information on READBUFF and SAS/Access to Oracle check out http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001342369.htm
... View more
