Date:         Tue, 30 Oct 2001 16:18:25 -0500
Reply-To:     Wanda Upole <wupole@KAI-RESEARCH.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Wanda Upole <wupole@KAI-RESEARCH.COM>
Subject:      update[2]: trouble accessing dictionary.columns
Content-Type: text/plain

Well, I made the problem disappear by switching to the
variable information functions (VTYPE, VFORMAT, etc.)
instead of dictionary.columns, but it took some re-programming,
and I'd hate to have to do that to all my macros.

I also checked to see how big dictionary.columns was,
and it only had 4900 rows with all my libnames assigned.
That shouldn't take minutes of processing time to go through,
even with the UPCASE function.  If I start a new SAS session,
with only the default libraries assigned, dictionary.columns
has 2200 rows (almost half), but takes less than a second to read.

Also, shouldn't SAS be smart enough not to try to build the
whole table when I have a WHERE clause to restrict the rows?

> -----Original Message-----
> From: Wanda Upole [SMTP:wupole@KAI-RESEARCH.COM]
> Sent: Tuesday, October 30, 2001 11:55 AM
> To:   SAS-L@LISTSERV.UGA.EDU
> Subject:      update: trouble accessing dictionary.columns
>
> A really wierd development:  I've found that if I take out the
> UPCASE functions, the code runs very fast.  (However, I
> can't do that in practice, because the code is part of a macro
> that accepts user input.)  Any ideas?
>
> Wanda
>
> --------------------------------------------------------------------------
> --
> -------------
>
> > -----Original Message-----
> > From: Wanda Upole
> > Sent: Tuesday, October 30, 2001 11:21 AM
> > To:   'SAS-L@listserv.uga.edu'
> > Subject:      trouble accessing dictionary.columns
> >
> > I'm having problems accessing dictionary.columns when I have
> > ODBC libname statements assigned in the same session.  It
> > eventually works, but it takes forever (between 2 and 8 minutes
> > for a simple PROC SQL select statement that should take a
> > few hundredths of a second).
> >
> > Here's an example of my code:
> >
> > proc sql noprint;
> >    select type, format into :vtype, :vformat from dictionary.columns
> >       where upcase(libname) = "WORK" and
> >             upcase(memname) = "DEMOG" and
> >             upcase(name) = "RACE";
> > quit;
> >
> > You can see that I'm not even trying to access information in
> > the ODBC tables, just a SAS data set in WORK.  Is SAS trying
> > to build the entire dictionary.columns table based on all the
> > libname statements I have set up?  Even so, it shouldn't take
> > more than 2 minutes--the ODBC libname statements are pointing
> > to tiny MS Access databases.
> >
> > I have the same problem when I instead use a data step and
> > sashelp.vcolumn, so the problem isn't with PROC SQL.  Also,
> > the ODBC libname statements are okay, because I can easily
> > and quickly read data from the Access tables using them.
> >
> > Does anyone have an idea what's going on?
> >
> > (By the way, I have version 8.2 on Windows 2000.)
> >
> > ~~~~~~~~~~~~~~~~~~~~~~
> > Wanda Upole
> > SAS Programmer
> > KAI
> > 6001 Monstrose Rd., Suite 920
> > Rockville, MD 20852
> > (301)770-2730
> > WUpole@kai-research.com
> > ~~~~~~~~~~~~~~~~~~~~~~
> >
> >