Date: Fri, 31 Aug 2001 13:38:20 -0400
Reply-To: "Dorfman, Paul" <Paul.Dorfman@BCBSFL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Dorfman, Paul" <Paul.Dorfman@BCBSFL.COM>
Subject: Re: V6.12 Win NT, V6.09E OS/390, Access to Db2 tables,
dreadful p erfo rmance
Content-Type: text/plain; charset=iso-8859-1
David,
I do not think you are overlooking anything. That is what I would expect
from a view descriptor (whatever is the reason behind its being a slug).
If you are using V8, you can use the libname with DB2 engine instead, and it
will do much faster, on par with Pass-Thru. Plus, you will be able to use
the 'driver-file', 'quasi-cursor' processing with DBKEY= option, should
there happen to be an index on the column of interest.
Actually, I would not expect the Pass-Thru to take 7 minutes unloading such
a small table, either, but you just might happen to have a slow machine.
Check it by rewriting a SAS data set of the same size - on a more or less
adroit machine, it should not take longer than 3 CPU seconds. And if you are
getting upwards of 6% CPU, the clock time should be close. To improve the
Pass-thru performance, try appending the clause WITH UR (uncommitted read)
to the end of your query.
If you need it even faster, another option would be to dump the table to a
flat file using the DSNTIAUL unload DB2 utility, and then read the file.
DSNTIAUL is quite fast and now allows WHERE clauses of more than one line
long, too. The file will be automatically formatted exactly as a DCLGEN
declared on the table would dictate (so you will know the layout right off
hand).
There are tuning options in DB2 that only DBA can control. If the table you
are reading were constantly updated online, it would make sense to tune it
to maximize its online performance at the expense of sequential speed.
However, it should not be the case with a periodically refreshed data mart.
You might be able to convince to tip the performance in favour of serial,
uncommitted-read processing.
However, in any case, DB2 sequential performance (and any RDBMS, for that
matter) will be nowhere near the agility of SAS reading SAS or flat files.
It is because of the nature of DB2 itself build on linear VSAM files and
trading sequential speed for OLTP speed for basically by the same mechanism
as the 'user' VSAM files - short control interval size (read: block size).
Moreover, even though DB2 has a number of advantages (mainly, the fully
implemented rollback and referential integrity and ability to hold almost
unlimited amount of data) over SAS, they are utterly insignificant in terms
of DM implementation. Moreover, while preparing a SUGI 26 paper on hybrid
indexing, I found to my surprise that DB2's indexed access is no faster, and
in fact even slower, than that of SAS. I do not have the figures in front of
me but do remember that I managed to join two hybrid-hash-indexed SAS tables
(1 million by 40 million rows) in just 8 CPU seconds. On the same machine, a
'double set' with SAS index would do it in just over a CPU minute, and DB2
joining two equivalent DB2 tables would lag far behind.
BTW, are you posting the CPU or wall time?
Kind regards,
=====================
Paul M. Dorfman
Jacksonville, FL
=====================
> We hold 1.24M rows of data on a DB2 data mart. Size wise, as
> a SAS data set
> this would be around 13000 optimised tracks on 3390 DASD.
>
> Reading 1 variable 11 bytes wide into a SAS Data set using a
> View through
> the ACCESS Procedure took around 48 minutes. Simple data step:
>
> Data FRED;
> Set LIB.DSN( Keep = V11BYTE);
> Run;
>
>
> I tried the same approach with SQL passthrough, log is below.
> The table
> build took 7 1/4 minutes.
>
> 46 Proc Sql _Method STimer;
> NOTE: The SQL Statement used the following resources:
> Task memory - 619K (20K data, 599K program)
> Total memory - 14940K (11840K data, 3100K program)
> 47
> 48 Connect To DB2 As MYDB2( SSID = ****);
> NOTE: The SQL Statement used the following resources:
> Task memory - 733K (24K data, 709K program)
> Total memory - 14940K (11840K data, 3100K program)
> 49 %Put &SqlXMsg;
>
> 50
> 51 Create Table FRED As
> 52 Select V11BYTES
> 53 From Connection To MYDB2( Select V11BYTES From LIB.DSN);
>
> NOTE: SQL execution methods chosen are:
>
> sqxcrta
> sqxextr
> NOTE: Table WORK.FRED created, with 1246202 rows and 1 columns.
>
> NOTE: The SQL Statement used the following resources:
> Task memory - 1277K (24K data, 1253K program)
> Total memory - 14940K (11840K data, 3100K program)
> 54
> 55 %Put &SqlXMsg &SqlRc;
> 0
> 56
> 57 Disconnect From MYDB2;
> NOTE: The SQL Statement used the following resources:
> Task memory - 1277K (24K data, 1253K program)
> Total memory - 14940K (11840K data, 3100K program)
> 58
> 59 %Put &SqlXMsg &SqlRc;
> 0
> 60
> 61 Quit;
>
>
> My Data step query started 11 processes (the table is
> partitioned in ten
> parts) and was drawing 6.9% CPU when it was checked. So,
> there doesn't
> appear to be a problem finding processing resources. The mainframe is
> running five engines, and these queries are running on the
> Production LPAR,
> so there is some degree of priority for my queries.
>
> Can anyone offer some suggestions, no matter how blindingly
> obvious please?
> I am waiting on SAS Tech Support to call me back, but I am
> also facing a
> weekend working on this... and my support here and at SAS
> will be severely
> restricted.
>
>
> ** ##*
>
> David Johnson
>
> > Group Technology & Systems - Mortgages
> Business Information Systems Consultant
> Collinsons 2nd Floor
> * (01422) 39 1214
> * 31214
> * 07092 25 9556
> * sasuser@dkvj-cons.com
> * http://www.dkvj-cons.com
> This message is attributable to the sender and does not
> necessarily reflect
> the view of Halifax Group plc or its subsidiaries.
>
>
>
>
> --------------------------------------------------------------
> ----------------
>
> Part of the Halifax Group, Halifax plc, Registered in England
> No. 2367076. Registered Office: Trinity Road, Halifax, West
> Yorkshire HX1 2RG. Represents only the Halifax Financial
> Services Marketing Group for the purposes of advising on and
> selling life assurance, pensions and unit trust business.
> The Marketing Group is regulated by the Personal Investment
> Authority. Switchboard 01422 333333.
>
> ==============================================================
> ================
>
>
Blue Cross Blue Shield of Florida, Inc., and its subsidiary and
affiliate companies are not responsible for errors or omissions in this e-mail message. Any personal comments made in this e-mail do not reflect the views of Blue Cross Blue Shield of Florida, Inc.
| 
