Date:         Mon, 11 Oct 2010 21:26:35 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: SAS/ACCESS ODBC simple query results in long real time
Comments: To: "Viel, Kevin" <kviel@SJHA.ORG>
In-Reply-To:  <279E0CB25CBDEF4DB4A50E89328780E223D710CE@CHEXCMS08.one.ads.che.org>
Content-Type: text/plain; charset="us-ascii"

Kevin:
I'd go with Gerhard's insight. Another database system will not understand a SAS missing value. A WHERE NOT IS NULL will handle missing values and other representations of NULL's across multiple SQL processors.
S
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Viel, Kevin
Sent: Monday, October 11, 2010 2:04 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SAS/ACCESS ODBC simple query results in long real time

Within our hospital's domain, I am connecting to a table in a Unify database as part of the GE Centricity Ultra Lab system.  The first query resulting in 6.6E7 records.  Adding the WHERE clause greatly increased the real time to process the query.  Might anyone have insight or suggestions?


24   Proc SQL ;
25     Select Count( * ) Format = Comma15.
26     From lab.Resultv
27     ;

ODBC_7: Prepared: on connection 0
SELECT * FROM lab.Resultv

ODBC_8: Prepared: on connection 0
 select COUNT(*) from lab.Resultv

ODBC_9: Executed: on connection 0
Prepared statement ODBC_8

ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data.
28   Quit ;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

29
30   Proc SQL ;
31     Select Count( * ) Format = Comma15.
32     From lab.Resultv
33     Where val ne .
34     ;

ODBC_10: Prepared: on connection 0
SELECT * FROM lab.Resultv

ODBC_11: Prepared: on connection 0
 select COUNT(*) from lab.Resultv where lab.Resultv."val" is not  NULL

ODBC_12: Executed: on connection 0
Prepared statement ODBC_11

ACCESS ENGINE:  SQL statement was passed to the DBMS for fetching data.
35   Quit ;
NOTE: PROCEDURE SQL used (Total process time):
      real time           13:56.53
      cpu time            0.12 seconds


Thank you,

Kevin


Kevin Viel, PhD
Senior Research Statistician
Patient Safety & Quality
International College of Robotic Surgery
Saint Joseph's Translational Research Institute

Saint Joseph's Hospital
5671 Peachtree Dunwoody Road, NE, Suite 330
Atlanta, GA  30342

(678) 843-6076:   Direct Phone
(678) 843-6153:   Facsimile
(404) 558-1364:   Mobile
kviel@sjha.org
Confidentiality Notice:
This e-mail, including any attachments is the
property of Catholic Health East and is intended
for the sole use of the intended recipient(s).
It may contain information that is privileged and
confidential.  Any unauthorized review, use,
disclosure, or distribution is prohibited. If you are
not the intended recipient, please delete this message, and
reply to the sender regarding the error in a separate email.