On 08/03/13 21:03, Jeffrey Butera wrote: > While I'm on a roll... I often look at how to make queries run faster. > In short, we index all the commonly used data fields we can and (of > course) it makes world of difference. However, I have some questions > about optimal ways to query data using a mix of indexed data, > non-indexed data and i-descriptors. > > Let's say that in a table FOO I want to do SELECT FOO WITH A='foo' AND > B='bar' AND C='bang' > where: > > A = indexed data field > B = non-index data field > C = I-descriptor (assume it's time-consuming: 2 seconds per record) > > Which is the optimal way to attack? > > 1) I could just go for it with: > > SELECT FOO WITH A='foo' AND B='bar' AND C='bang' > > 2) I could do the following: > > SELECT FOO WITH A='foo' AND B='bar' > SELECT FOO WITH A='foo' AND B='bar' AND C='bang' REQUIRE.SELECT > > 3) I could do the following: > > SELECT FOO WITH A='foo' > SELECT FOO WITH A='foo' AND B='bar' AND C='bang' REQUIRE.SELECT > > > I've done benchmarks, but really curious about the innards of Unidata > and how/when it does short-circuit evaluation of AND clauses, etc. > > My gut tells me that (3) should be good because it first weeds out bad > records based solely on an indexed data field, thereby reducing the > number of records that "C" needs to be evaluated. Conversely, if it's > doing a good job with short-circuit evaluation then (3) and (1) > shouldn't be terribly different because failure of A='foo' would imply > that C never gets evaluated. > While I don't know UniData, I'm guessing that it's the same as UniVerse in this sense, and imho the second select of both (2) and (3) is broken (as in, the first select is a waste of time ...)
I notice you're using REQUIRE.SELECT. So... SELECT FOO WITH A='foo' ;* will use the index SELECT FOO WITH B='bar' REQUIRE.SELECT ;* will now find the records with A equal to foo and B equal to bar SELECT FOO WITH C='bang' REQUIRE.SELECT ;* now finishes off the select. Whether you want to combine the second two selects as SELECT FOO WITH B='bar' AND C='bang' REQUIRE.SELECT depends on what C does. If, in order to evaluate C, you need to read the contents of FOO, then you should combine the two. If UniData reads @RECORD regardless of whether it's required when evaluating an i-desc, then likewise. If, however, evaluating C can be done without reading @RECORD, then you may be better doing two selects. Whatever happens, there is no point (indeed, it could easily be positively harmful) in repeating an earlier select. The REQUIRE.SELECT keyword guarantees that if the previous select fails to find any records, the subsequent select will also fail rather than starting again from scratch. Cheers, Wol _______________________________________________ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
