G'day Ben, Perhaps a view with the NOT IN clause and a SELECT statement to find what is in the view?
Regards Clive Williams ----- Original Message ----- From: <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[EMAIL PROTECTED]> Sent: Saturday, March 06, 2004 6:25 AM Subject: [RBASE-L] - Re: How to speed up rbase - Razzak's Reply > Hey Dennis, > > I've pretty much eliminated subselects from inserts and updates by > correlating the tables, but I have not figured out how to replace > "NOT in (.....)". How would a person do that? > > Thanks > > Ben Petersen > > > > On 5 Mar 2004 at 10:27, Dennis McGrath wrote: > > > I frequently use DISTINCT in subselects to speed up processing > > > > > > SELECT ... WHERE colname IN ( SELECT DISTINCT colname FROM .....) > > > > Try your queries with and without the DISTINCT. > > > > > > Updates benefit even more, often reducing the time drasticly. > > UPDATE ... WHERE colname IN ( SELECT DISTINCT colname FROM .....) > > > > Dennis McGrath > > > > > > --- Jim Limburg <[EMAIL PROTECTED]> wrote: > > > Thank You Mr R:> > > > > > > This is a real good lesson. I see how this would be more effective. > > > Now if I can get some of our db's more normalized and > > > setup to utiilize this it will be a great benefit. > > > > > > Again. Thank You > > > Jim Limburg > > > > > > A. Razzak Memon wrote: > > > > > > > At 01:17 PM 3/3/2004 -0500, Jim Limburg wrote: > > > > > > > >> I have a question about this that you wrote: > > > >> > > > >> 08. Use correlated sub-selects or multi-table selects instead > > > >> -- of sub-selects > > > >> > > > >> A multi-table select uses indexes to join the tables. A correlated > > > >> sub-select uses indexes to restrict the list of values to be > > > >> compared within the sub-select. A plain sub-select will not use > > > >> indexes. Each row of data from the main query must be compared to > > > >> every row selected by the sub-select. > > > >> > > > >> My question is basically along the lines of not understanding the > > > >> above comment. Please remember those of us more SQL challenged > > > >> individuals. When you say plain sub-select, do you mean something > > > >> like this SELECT * FROM ITEMS WHERE custnum IN (SELECT custnum > > > from > > > >> SPECIALORDERS WHERE custnum contains '1234'). > > > >> Now, does a corrlated sub-select use the T1, T2 type interface? > > > >> > > > >> Just wanting to learn. > > > > > > > > > > > > > > > > Jim, > > > > > > > > To illustrate, compare the results obtained by using the different > > > > techniques on two tables, each with 1,000 unique rows (each row in > > > > Table1 has only one match in Table2). > > > > > > > > (1) A multi-table select, shown below, took 00:05. > > > > > > > > SELECT collist FROM table1,table2 + > > > > WHERE Table1.LinkCol = Table2.LinkCol > > > > > > > > (2) A correlated sub-select, which looked at every row in Table1 > > > > but needed to look at only one row in Table2 for each row in > > > > Table1, takes less than 00:10. The example is shown below: > > > > > > > > SELECT collist FROM Table1 WHERE LinkCol IN + > > > > (SELECT LinkCol FROM Table2 WHERE > > > Table2.LinkCol=Table1.LinkCol) > > > > > > > > (3) A plain sub-select, which had to look for a match in every row > > > > in Table2 for each of the 1,000 rows of Table1, will take more > > > > than 00:10. (three times, maybe..) to complete. This example, > > > > by far the slowest, is shown below: > > > > > > > > SELECT collist FROM Table1 WHERE LinkCol IN + > > > > (SELECT LinkCol FROM Table2) > > > > > > > > SELECT is a command in which your data really affects the > > > performance. > > > > The results you see and the method you find best will depend on the > > > > number of rows and distribution of data in the tables you are > > > combining. > > > > > > > > You should try those techniques in your real time database and see > > > what > > > > you find. > > > > > > > > Hope that helps! > > > > > > > > Very Best R:egards, > > > > > > > > Razzak. > > > > > > > > > > > > A. Razzak Memon wrote: > > > > > > > >>> At 03:48 PM 3/2/2004 -0800, Victor Timmons wrote: > > > >>> > > > >>>> I would like some tips on how to speedup rbase when it > > > >>>> running on a network. > > > >>> > > > >>> > > > >>> Victor, > > > >>> > > > >>> Here are a few more tips in addition to the suggestions > > > >>> posted earlier by fellow R:BASE users/developers: > > > >>> > > > >>> -- > > > >>> 01. Use the most current version of R:BASE > > > >>> -- (I know you are using the latest & greatest version!) > > > >>> > > > >>> R:BASE Technologies is continually making speed improvements, > > > >>> and each new release is faster than the previous one. For > > > >>> example, building indexes in R:BASE 6.5/7.x is significantly > > > >>> faster than in previous versions. > > > >>> > > > >>> You can even PACK the INDEXes faster in 6.5++, even in a > > > >>> Multi-User environment. > > > >>> > > > >>> PACK INDEX > > > >>> or > > > >>> PACK INDEX FOR TableName > > > >>> > > > >>> -- > > > >>> 02. Think globally instead of row by row > > > >>> -- > > > >>> This is one of the best ways to achieve some significant speed > > > >>> improvements. Instead of doing row-by-row processing using a > > > >>> DECLARE CURSOR, try using a single UPDATE or INSERT command, > > > >>> which will take advantage of the SQL select capabilities of > > > >>> R:BASE. > > > >>> > > > >>> For example, suppose you want to create a table to hold > > > >>> information about how many of each product a customer has > > > >>> purchased. The most logical approach is to set up a cursor > > > >>> to go through the detail table and count the rows for each > > > >>> customer/product combination and then insert that data into > > > >>> the new table. On a table with 3500 rows, processing all > > > >>> the rows may take few minutes. By replacing the DECLARE > > > >>> CURSOR loop with a single insert command, the time required > > > >>> to process all the rows may take 75% less. > > > >>> > > > >>> Compare the two pieces of code: > > > >>> > > > >>> DECLARE c1 CURSOR FOR SELECT CustID,PartNo FROM CPProdDet + > > > >>> GROUP BY CustID,PartNo > > > >>> OPEN c1 > > > >>> FETCH c1 INTO + > > > >>> vCustID INDIC ivCustID, + > > > >>> vPartNo INDIC ivPartNo > > > >>> WHILE SQLCODE < > 100 THEN > > > >>> SELECT COUNT(*) INTO vCount FROM CPProdDet WHERE + > > > >>> CustID = .vCustID AND PartNo =.vPartNo > > > >>> INSERT INTO CustProd (CustID,PartNo,Copies) VALUES + > > > >>> (.vCustID,.vPartNo,.vCount) > > > >>> FETCH c1 INTO + > > > >>> vCustID INDIC ivCustID, + > > > >>> vPartNo INDIC ivPartNo > > > >>> ENDWHILE > > > >>> DROP CURSOR c1 > > > >>> > > > >>> vs. > > > >>> > > > >>> INSERT INTO CustProd (CustID,PartNo,Copies) + > > > >>> SELECT CustID,PartNo,Count(*) + > > > >>> FROM CPProdDet GROUP BY CustID,PartNo > > > >>> > > > >>> -- > > > >>> 03. Be Creative > > > >>> -- > > > >>> R:BASE almost always offers two or more ways of doing something. > > > >>> Look for an alternative method for accomplishing a task - it > > > >>> might just be considerably faster. > > > >>> > > > >>> -- > > > >>> 04. Change the environment > > > >>> -- > > > >>> The R:BASE environment settings that have the most effect on > > > >>> performance are SET MESSAGES. > > > >>> > > > >>> SET MESSAGES OFF eliminates the time required for screen display > > > >>> of messages. This is particularly noticeable if you are doing > > > >>> repetitive UPDATE or INSERT commands. > > > >>> > > > >>> -- > > > >>> 05. Reduce Number of Expressions in Reports > > > >>> -- > > > >>> Because all report expressions are processed for each row of > > > >>> data read from the reports driving table or view, reducing the > > > >>> number of expressions in a report increases the printing speed > > > >>> of a report. > > > >>> > > > >>> -- > > > >>> 06. Abbreviate commands to four characters > > > >>> -- > > > >>> R:BASE parses command lines into 4-byte tokens - the fewer > > > >>> tokens to read, the faster the command executes. > > > >>> > > > >>> -- > > > >>> 07. Combine commands whenever possible > > > >>> -- > > > >>> Many R:BASE commands allow access to more than one column or > > > >>> variable and don't require using separate commands for each. > > > >>> This reduces the number of times R:BASE must parse and read a > > > >>> command. The fewer commands to read, the faster R:BASE executes. > > > >>> > > > >>> -- > > > >>> 08. Use correlated sub-selects or multi-table selects instead > > > >>> -- of sub-selects > > > >>> > > > >>> A multi-table select uses indexes to join the tables. A > > > correlated > > > >>> sub-select uses indexes to restrict the list of values to be > > > >>> compared within the sub-select. A plain sub-select will not use > > > >>> indexes. Each row of data from the main query must be compared to > > > > > === message truncated === > > > >
