Stephen, Thanks for your insight.
This particular application will have at most 50-100K records. Most records will be short (2000 bytes), but we don't want the application to fail because of an artificial records length limitation, so we use LONGs for some fields. Your worst case analysis is far from the typical usage. I am still interested in learning answers to the questions that I raised. Mike > Not trying to rain on your parade... > > But have you tested performance of such searches? > > My experience is that if the DBMS does not have some way to use an > outside full text index engine, performance of LIKE on big strings is > horrible. You have to do table scans... Unless you are searching only > a few hundred or thousands of records, way too much I/O. > > Doing a "offline" index yields much better performance (1000x or > better). The problem with a SQL database "native technique" is that each > index must be added on every insert/update. "Real-time" indexing is > painfully slow on lots of data. > > 8K is a lot of string in my book, especially if you are trying to SEARCH > on that string and not just pick it up off a primary key index. Many > have found the hard way SQL isn't for such purposes.... > > Stephen > > > > > -----Original Message----- > From: Michael Slinn [mailto:[EMAIL PROTECTED] > Sent: Friday, August 15, 2003 8:25 AM > To: [EMAIL PROTECTED] > Subject: Re: SQLJ > > > Hmm. My motivation is that I am looking for a standardized in-process > data manipulation language for a specific issue. I'm not about to start > work on a project as large as SQLJ. Perhaps DBPROC might have the > ability to do what I want for the problem I am trying to solve, but I > can't find enough documentation on it to know. > > My issue stems from SAPDB's 8K/record limitation, which forced us to use > BLOBs/LONGs instead of VARCHAR for several columns. We need to be able > to search for records that contain specific substrings in any of several > columns; some columns are VARCHAR and some are LONG. Searching LONG > columns is not directly supported in SAPDB. > > What I want to do is to return a result set. The following pseudo-code > shows what I have in mind: > > CREATE DBPROC SEARCH (STRING TARGET IN) > SELECT VARCHAR1, VARCHAR2, LONG1, LONG2 FROM MYTABLE; > /* somehow declare all local variables as string > (:varchar1, :varchar2, :long1, :long2) */ > WHILE $rc = 0 DO BEGIN > FETCH INTO :varchar1, :varchar2, :long1, :long2; > IF :varchar1 LIKE '%'+TARGET+'%' OR > :varchar2 LIKE '%'+TARGET+'%' OR > :long1 LIKE '%'+TARGET+'%' OR > :long1 LIKE '%'+TARGET+'%' THEN > INSERT INTO RESULTSET (a,b,c,d) Values > (:varchar1, :varchar2, :long1, long2); > /* even better would be to insert the entire record into the > result set*/ > END; > /* somehow return the resultset */ > > Any suggestions on how to do this? > > Mike > > >> I think it would be really usefull, but I doubt it will happen, >> unless you decide to implement it ! >> >> SQLJ would be a huge plus for devellopers, because they give the >> power of stored procs without compromising portability, but access to >> stored procs is a major 'differentiator'. >> >> So database vendors, aren't too interrested since it removes the >> remaining 'vendor lock in', which is why they don't hype SQLJ too >> much. >> >> Open source databases aren't subject to these constraints, so one >> would expect that they would adopt SQLJ. It might eventually happen, >> but I doubt that it's for the near future, because the develloper >> community is not asking for it, (the main reason being that the hype >> hasn't been created by commercial db vendor marketing, for obvious >> reasons), and open source DBs are busy matching functionnality of >> commercial databases (and doing a good job at it !). >> >> >> >> Michael Slinn wrote: >>> This article is over a year old, but I didn't find any mention of >>> SQLJ in the SAPDB listserv archive: >>> http://www.fawcette.com/javapro/2002_04/magazine/features/bbeauchemin/ >>> >>> Perhaps this might be useful to SAPDB users? >>> >>> Mike >> >> >> _______________________________________________ >> sapdb.general mailing list >> [EMAIL PROTECTED] >> http://listserv.sap.com/mailman/listinfo/sapdb.general > > > > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general > _______________________________________________ > sapdb.general mailing list > [EMAIL PROTECTED] > http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
