RE: Encoded Vector Indexes

I just asked the question on Midrange-L, and since have started replacing
LFs with EVIs after seeing an actual difference in performance.

I am doing this for eRPG applications that use dynamic SQL.  I have to leave
the one LF that I have that I used native IO in a couple programs, but as
for the SQL, I ran it in debug mode to make sure it would use them and it
did.

So far, I like it.  If you're doing dynamic SQL, they make smaller objects
than LFs and seem to be faster access.

Brad

> -----Original Message-----
> From: Shaw, David [mailto:dshaw@spartan.com]
> Sent: Wednesday, January 31, 2001 8:05 AM
> To: 'RPG400-L@midrange.com'
> Subject: RE: Encoded Vector Indexes
> 
> 
> Russell,
> 
> I've tinkered a little bit with them.  As you probably 
> already know, EVI's
> are helpful only for record selection, not record ordering, 
> so specifying
> the EVI on a SELECT is pointless (I haven't tried it, it may 
> not even be
> allowed).  They're also only really useful if they're built 
> over fields with
> a finite number of possible values, for example something 
> like an inventory
> classification code.  If the number of values isn't AT LEAST 
> an order of
> magnitude smaller than the number of records in the based-on 
> table, an EVI
> over the field will not be helpful.  They work best if you 
> only specify ONE
> key field in each EVI - if you have several fields you want 
> to use in your
> WHERE clause, create a separate EVI for each one.  The query 
> engine will
> figure out which one(s) to use when it builds the access plan.
> 
> This is from memory and may be wrong, but I think it does 
> tell you when it
> uses one, just like it does for a regular index.  If it 
> doesn't use one, it
> will also provide the code for why not, also just like a 
> regular index.
> 
> In my case, I couldn't justify the EVI's I tested, mainly because the
> third-party application that we run doesn't use SQL and 
> rarely uses OPNQRYF.
> EVI's are useless for native RPG I/O, as I'm sure you're aware.  The
> selections our odd mix of queries and reports make most often 
> are for date
> ranges, which EVI's don't work well for (too many possible values).
> 
> I hope that helps a little.  If you have any specific 
> questions, holler - I
> might have an idea about how to find the answer, at least.
> 
> Dave Shaw
> Spartan International, Inc.
> Spartanburg, SC
> ---
> If you would like to subscribe to the MAPICS-L mailing list 
> send email to
> MAPICS-L-SUB@midrange.com or go to www.midrange.com and follow the
> instructions.
> 
> -----Original Message-----
> From: Russell Conerly [mailto:rconerly@netdoor.com]
> 
> I have been doing some studying on Encoded Vector Indexes 
> with SQL.  I've
> been
> looking at improving performance of an SQLRPGLE program 
> running on V4R4 
> that is using some rather
> large files.  The documentation I have found has been limited IMO.
> 
> Have any of you experimented with/used EVI's?  And if so, 
> what are the 
> pros/cons to using
> them?  Does the optimizer inform you that it is using the 
> EVI?  Do you use 
> the index
> in your select statement or is the index implied?  Please advise.
> 
> Thank you in advance.
> 
> Regards,
> Russell Conerly
> Artaban Solutions, Inc.
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to 
> RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: 
> david@midrange.com
> +---
> 
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---