LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (March 1998, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Thu, 19 Mar 1998 01:14:20 -0800
Reply-To:     "kmself@ix.netcom.com" <kmself@ix.netcom.com>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Karsten M. Self" <kmself@IX.NETCOM.COM>
Organization: Self Analysis
Subject:      Re: Overview of creating and using indexes
Comments: To: Jack Hamilton <jack_hamilton@HCCOMPARE.COM>
Content-Type: text/plain; charset="us-ascii"

I also ran some experimentation of different methods of updating indexed and non-indexed datasets. While both SQL (INSERT INTO) and PROC APPEND will automatically update an existing SAS index as records are added to an existing dataset, the process is very inefficient when dealing with large appends (append obs are approximately the same as base), compared with breaking and recreating the index.

Using sample data (two variables, 100,000 records, first var sequential, second random integers 1 <= var <= 1000), timings were approximately as follows:

(Timings on an un-loaded Sun Enterprise 3000, approximate average-from-memory of three runs) Create data: 1.5 seconds Copy data (data new; set old; run;): 0.75 seconds Index data (simple index on random var): 3 seconds APPEND or INSERT into indexed dataset: 12 minutes APPEND or INSERT into non-indexed dataset: 3 seconds

The moral:

- for large datasets to which records are added transactionally (append size is very small fraction of base), inserting or appending to an indexed dataset makes sense.

- for bulk loading (append size is a significant fraction of base), breaking and rebuilding the index makes more sense. This is particularly true if loading new data can be scheduled during regular (nightly, weekly,...) system downtime.

This is consistent with general guidelines and recommendations for most RDBMS systems.

On Monday, March 16, 1998 10:54 AM, Jack Hamilton [SMTP:jack_hamilton@HCCOMPARE.COM] wrote: > The use of indexes to subset data has been discussed recently. Some > sample code to create and use indexes is shown below.

<snip (-- see the original post, it's good) >

Karsten M. Self (kmself@ix.netcom.com)

What part of "gestalt" don't you understand? (Welchen Teil von "Gestalt" verstehen Sie nicht?)


Back to: Top of message | Previous page | Main SAS-L page