Ray, Thanks for explaining some of these things you mentioned below, hopefully some of the below would open some eyes... and help developers analyze some performance areas of UV.
>yacc. Leroy might like to chip in on this one. On this basis I >would be loath to agree that the "SQL Interface is Faster than any >other Interface within UV". A well-constructed query *should* >generate the same parse tree. Personally.. i belive "other" interfaces were built to be backward compatible and hence that can possibly make those interfaces alot slower than SQL Interface. >I suspect that there is a large body of U2 practitioners out there >(maybe Joe's colleagues among them) who are "living in the past" >to some extent, who have not kept up with new and different (and >sometimes more performant) ways of doing things. This is VERY VERY True... I have found this to be Very True, especially in our UV Shop. Most UV Developers in OUR Shop are Not Concerned with Performance, never criticize their code Or try to find better ways of development. Most of OUR UV Developers come from the OLD School.. where most Processes Took Minutues. Some of us have worked with Other Enterprise Tools and Minutes are A BIG SURPRISE To US. The other day.. i was inspecting a UV File with a UV Developer, he ran a "COUNT FILENAME" on our Customer Master... (BTW Quad CPU 4GHZ)... It took 12-15 Minutes to get a result back from UV. The file only had 800,000 Records. This kind of Operation normally takes "ZERO" Milliseconds in any Enterprise RDBMS. I had nothing to say but LAUGH!. Joe Eugene >-----Original Message----- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Behalf Of Ray Wurlod >Sent: Sunday, April 04, 2004 3:03 AM >To: U2 Users Discussion List >Subject: RE: Modern Universe (TESTING) > > >There is only one query engine in UniVerse. There are two query >parsers, one for RetrieVe and one for SQL, developed independently >(15 or so years apart) but both, as far as I am aware, based on >yacc. Leroy might like to chip in on this one. On this basis I >would be loath to agree that the "SQL Interface is Faster than any >other Interface within UV". A well-constructed query *should* >generate the same parse tree. > >UniVerse's history began as a quest for Prime INFORMATION's market >share. Therefore the wildcard characters from Prime INFORMATION >("..." and "0X") are used with the LIKE keyword in RetrieVe if the >account flavor is Prime-based; that is, IDEAL, PIOPEN or >INFORMATION flavors. >LIST filename WITH lastname LIKE "...SMITH..." >In these flavors, "wildcard" is actually a particular example of a >more general concept called pattern matching. Patterns can include >explicit or "any" numbers of alphabetic, numeric and "don't care" >character classes. > >Later, UniVerse went after some of Pick's market share, and >therefore accommodated Pick's style of wildcard character ("[" for >leading wildcard and "]" for trailing wildcard). Therefore these >wildcard characters are seen used in RetrieVe in Pick-style >accounts; that is, PICK, REALITY and IN2 flavors. >Interestingly (or not) the wildcard operators work with the "=" >operator (rather than LIKE) in these accounts. >LIST filename WITH lastname = "[SMITH]" > >Prime-style wildcards, in phrases commencing with WITH or WHEN, >work in all account flavors. > >UniVerse SQL complies with ODBC 2.0 (there's a compliance >statement for the exact levels for grammar and API in the UniVerse >SQL manual). Here the "%" character is a multi-character-matching >wildcard, and the "_" character is a single-character-matching >wildcard. [I know you know that, Joe, but other readers may not.] > >SQL is explicit about which quote characters are used where, >RetrieVe is more laissez-faire. This may add a few microseconds >to the parse phase. > >Finally, there are some things that SQL can do that RetrieVe can >not do, and there are some things that RetrieVe can do that SQL >cannot do. There are some things that UniVerse SQL can do that >standard SQL cannot do (even that some vendor's extensions cannot >do, such as detail and summary lines in the same report - BREAK ON >rather than GROUP BY). So I would counsel everyone who's >interested to learn the full capabilites of all the bits and >pieces, so that you can always choose the best tool for any particular job. > >I suspect that there is a large body of U2 practitioners out there >(maybe Joe's colleagues among them) who are "living in the past" >to some extent, who have not kept up with new and different (and >sometimes more performant) ways of doing things. As a quick f'r >instance, if I were set the task of selecting records based on an >indexed field (column) from a UniVerse BASIC program, I'd use >SELECTINDEX to process the index directly, rather than use a query >to generate the Select List. But SELECTINDEX hasn't been in the >product since version 1 (I think it only came in in version 7.3 or >thereabouts, but even that's quite a few years ago). Alas, there >is a LOT of legacy code dating from the early days of UniVerse >(and even earlier, if it was migrated from Prime INFORMATION or >Pick) that no-one's ever bothered to examine with a view to >implementing more efficient processing. One can understand the >"if it's not broken don't fix it" mentality but to these folks >employ a person carry > ing a red flag to precede their automobile, as used to be >required until the law was changed? > > >----- Original Message ----- >From: "Joe Eugene" <[EMAIL PROTECTED]> >Date: Wed, 31 Mar 2004 16:37:54 -0500 >To: "U2 Users Discussion List" <[EMAIL PROTECTED]> >Subject: RE: Modern Universe (TESTING) > >> Ray, >> >> I see you are doing a few things here, am not quite sure i understand. >> >> The only way i have OUR UV Programmers using BASIC/PICK do this >> is like >> >> SELECT [FILENAME] WITH [FIELDNAME] LIKE 'SARA]' (NOTE **]* - >Syntax might >> a bit OFF) >> >> Something like the above produces a CASE-INSENSITIVE Search and returns >> all the below >> >> Sara >> sarra >> saRraA etc. >> >> The UPCASE and LOWCASE only Returns those results right? >> >> So in your Experience, would you say SQL Interface is Faster than >> any other Interface within UV? >> >> Thanks, >> Joe Eugene >> >> >> >-----Original Message----- >> >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >> >Behalf Of Ray Wurlod >> >Sent: Wednesday, March 31, 2004 3:57 PM >> >To: U2 Users Discussion List >> >Subject: RE: Modern Universe (TESTING) >> > >> > >> >On this basis here are the RetrieVe equivalents for my earlier >> >post. The main difference is that there's more than one verb (for >> >different query result formats) and the selection criterion begins >> >with the keyword WITH rather than WHERE. And the pattern matching >> >specifiers are different (for example ... rather than % for >> >multi-character wildcard). >> > >> >verb PEOPLE NAME WITH NAME CONV "MCU" LIKE "SARA..." >> >verb PEOPLE NAME WITH EVAL "UPCASE(NAME)" LIKE "SARA..." >> >verb PEOPLE NAME WITH EVAL "OCONV(NAME)" LIKE "SARA..." >> >verb PEOPLE NAME WITH EVAL "UPCASE(LEFT(NAME))" = "SARA" >> > >> >where verb can be any of: >> >LIST SORT columnar report >> >SELECT SSELECT Select List >> >LIST.ITEM SORT.ITEM raw format >> >LIST.LABEL SORT.LABEL mailing labels >> >REFORMAT SREFORMAT target is second file/table >> >COUNT >> >SUM not really relevant for NAME >> > >> >HTH >> >----- Original Message ----- >> >From: "Joe Eugene" <[EMAIL PROTECTED]> >> >Date: Wed, 31 Mar 2004 15:33:20 -0500 >> >To: "U2 Users Discussion List" <[EMAIL PROTECTED]> >> >Subject: RE: Modern Universe (TESTING) >> > >> >> Tim, >> >> >> >> My apologies... Yes, i know UV has a SQL Interface but i didnt think >> >> many UV Programmers used this Interface... >> >> >> >> As a matter of Fact, i like the UV SQL Interface. I have a PE >> >Edition of UV >> >> on all my machines and i have only used the SQL Interface within UV. >> >> >> >> In our UV Shop, UV Guys are NOT supposed to use this SQL Interface >> >> as they claim its very slow than using the Native "SELECT WITH ....." >> >> >> >> I have read the UV Manual that reflects RDBMS, this manual >explains you >> >> can setup Tables Exactly like any RDBMS using Data Types >> >(Varchar, char, Int etc) >> >> >> >> All the the testing i have done does NOT involve using the SQL >> >Interface in UV, >> >> Our UV Shop uses FILE Types, NOT RDBMS Tables and we use >> >> PICK/BASIC/REDBACK to Interfact with these FILES. >> >> >> >> Perhaps the problem might be our UV Shop using PICK/BASIC... >> >> Maybe the SQL Interface on UV is much faster. I dont know. >> >> >> >> Thanks, >> >> Joe Eugene >> >> >> >> >> >> >> >> ________________________________ >> >> >> >> From: [EMAIL PROTECTED] on behalf of Timothy Snyder >> >> Sent: Wed 3/31/2004 3:12 PM >> >> To: U2 Users Discussion List >> >> Subject: RE: Modern Universe (TESTING) >> >> >> >> >> >> >> >> >> >> Joe Eugene wrote on 03/31/2004 02:59:29 PM: >> >> >> >> > Please post your PICK/BASIC and SQL Query.. so we i can learn >> >> > the magic you did on the PICK Side. >> >> >> >> Joe, >> >> >> >> Unless I'm missing something, Sara used the SQL statement against the >> >> UniVerse database. Perhaps you weren't aware that UniVerse >supports SQL >> >> statements to query the database. I don't think she used any "magic". >> >> Therefore, in her original post, she provided all the >> >information you need >> >> to do a comparison. >> >> >> >> Also, you keep referencing PICK/BASIC. BASIC is the programming >> >language, >> >> not the query language. There is also a native query language, >> >but that's >> >> only one of many ways to access the database. >> >> >> >> I hope this helps to clarify your expectations. >> >> >> >> >> >> Tim Snyder >> >> IBM Data Management Solutions >> >> Consulting I/T Specialist , U2 Professional Services >> >> >> >> Office (717) 545-6403 (rolls to cell phone) >> >> [EMAIL PROTECTED] >> >> -- >> >> u2-users mailing list >> >> [EMAIL PROTECTED] >> >> http://www.oliver.com/mailman/listinfo/u2-users >> >> >> >> >> > >> >> >> >> -- >> >> u2-users mailing list >> >> [EMAIL PROTECTED] >> >> http://www.oliver.com/mailman/listinfo/u2-users >> > >> >-- >> >u2-users mailing list >> >[EMAIL PROTECTED] >> >http://www.oliver.com/mailman/listinfo/u2-users >> >> -- >> u2-users mailing list >> [EMAIL PROTECTED] >> http://www.oliver.com/mailman/listinfo/u2-users >> > >-- >u2-users mailing list >[EMAIL PROTECTED] >http://www.oliver.com/mailman/listinfo/u2-users -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
