No improvement in speed, but thank you for the explanation. Duey
-----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of James Bentley Sent: Thursday, February 14, 2008 7:47 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: I need to better understand the sys_columns table Duane, First, Sys_Columns is really a "system view". Columns are stored in the ".RB1" file in a vastly different format (without the column description). Column descriptions along with table descriptions are stored in SYS_COMMENTS in ".RB2" file. SYS_COLUMNS contains rows for each TABLE and each VIEW, hence the number of rows you discovered. Try following: CHOOSE vfMrgCol_Col FROM #VALUES FOR + (LJS(SYS_column_NAME,22)+(LJA(SYS_COMMENT,30)), + SYS_column_NAME + FROM SYS_columns + WHERE SYS_TABLE_ID = 339 Note!! SYS_COMMENT from SYS_COLUMNS is a NOTE data type and may not be suitable for use in CHOOSE without limiting the size retrieved. Also make sure that there are dEscriptions for the columns in question. Try: BROWSE SYS_TABLE_ID=6,SYS_COLUMN_NAME=18,SYS_COMMENT FROM SYS_COLUMNS WHERE SYS_TABLE_ID=339 Jim Bentley --- "Heffelfinger, Duane" <[EMAIL PROTECTED]> wrote: > Good evening, > > > > I've been looking for a way to utilize some code I learned at > the > Advanced Training course, but it is very slow in my database. > What I > would like to do is display the column name and column > description from > sys_columns in a choose statement. The code below takes over > 1 minute: > > > > CHOOSE vfMrgCol_Col FROM #VALUES FOR + > > ((LJS(SYS_column_NAME,22))&SYS_COMMENT), + > > SYS_column_NAME + > > FROM SYS_columns + > > WHERE SYS_TABLE_ID = 339 > > > > This code takes only 1 second but doesn't display the column > description: > > > > CHOOSE vfMrgCol_Col FROM #COLUMNS + > > IN coreinfo > > > > Poking around I discovered: > > * There are approximately 5400 rows in sys_columns > > * Depending on how I look at the sys_columns table such > as in dos > 6.1a or Rbase 7.6 for Windows I get a different number of rows > > * If I project sys_columns to a temporary table in > Rbase7.6 it > takes the same couple of minutes as the choose and I get over > 26,800 > rows. It appears that the temporary table somehow includes > columns from > views, but I'm not sure the sys_columns table actually has the > columns? > > > * The temporary table displays the column names with > their > description very quickly, but I don't want to maintain a > separate table. > > > > Thanks for any insight anyone can provide. > > > > Duey > > Jim Bentley American Celiac Society [EMAIL PROTECTED] tel: 1-504-737-3293 ________________________________________________________________________ ____________ Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
