To WHERE or not to WHERE (Was fastest...)

> > > There may have been some other error on the same line that was corrected > when the where 1 = 1 was added. 4gl _assumes_ 1 = 1 if no where clause > is included. > > It's not a bad idea however, to include a "where 1 = 1" clause anyway. > A good example of this is the construct statement: Build > a construct, don't enter any values and then look at the "output: from > the construct. Ta da 1=1. > I just tried this in 4gl against first a 11717 row table with an update and then against a 63156 row table (same program). I tried it both ways grabbing CURRENT MINUTE to FRACTION(2) to measure time. I did 4 update statements, the first just to load the engines buffers, the second without a where clause, the third with 1=1 and the fourth to reset things. I tried this 3 times against each table then reversed the order of the with/ without updates to allow for further buffer advantages. I did a LOCK EXCLUSIVE on the entire table first. For the second table I ran the bottom half first ( I am suspicious of that one positive time therein). Results: First set done without and then with. Second set done with and then without. 11717 rows 63156 rows without a where clause with without with 22.01 21.03 ( .98) 1:55.02 1:56.05 (-1.03) 29.03 26.97 (2.06) 2:02.97 2:02.96 ( .01) 25.02 24.95 ( .07) 1:49.99 2:05.05 (-15.06) avg 1.04 ignoring the -15 -.51 swapped the order (advantage buffer) 23.97 25.01 (-1.04) 2:01.05 1:58.95 (2.10) 24.04 24.95 (- .91) 1:56.06 1:58.00 (-1.94) 24.01 24.96 (- .95) 1:56.97 1:57.03 (- .06) avg -.97 avg .03 ignoring 2.10 gives -1 Observation I suspect that by the time the effect of inclusion or exclusion of the WHERE clause, if any, became meaningful the engine would be choking on a long transaction. This is all a moot point since Willy is talking 2million rows in a production environment, but my curiosity was aroused so I chased it down. j. _____________________________________________________________________________ Jack Parker | Hewlett Packard, BSMC Boise, Idaho, USA| If you put all of the lawyers in jparker@hpbs3645.boi.hp.com | this country end to end, (208) 396-5388 (W) (208) 384-1623 (H) | it would be a good thing. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________