Re: subquery in case expression

bozon wrote: > Really faster than the single join with a case statement? I would have > to see it to believe it. :-) I subscribe to Kagel's First Law of SQL: 'Every SQL query can be written at least three different ways.' And am a devout believer in its first correllary: 'If you haven't tried them all you are probably not using the most efficient version.' I always assume the one that hasn't been tried yet is the fastest ;-) In this case, IB that with PDQ on so that the several UNIONs parts can be performed in parallel and depending on table fragmentation and index efficiency, yes the UNION of simpler queries may certainly be faster than a single query with multiple critera. Of course, YMMV applies and you MUST test all versions using several different sets of values to be sure which will perform best most often and least bad in the worst case. Art S. Kagel > Art S. Kagel wrote: > <SNIP>

Sometimes I wish I could take a post back. I started thinking about it and I realized that you were using "union all" to run all of the seperate queries in parallel (assuming PDQ is on). This is a nice trick that I hadn't thought of before. Regular unions can't really do this because they each kind of depend on the others, in your query you just need to make sure each clause is disjoint from the other. This is a very nice trick. Art S. Kagel wrote: > bozon wrote: > > Really faster than the single join with a case statement? I would have > > to see it to believe it. :-) > > I subscribe to Kagel's First Law of SQL: > > 'Every SQL query can be written at least three different ways.' > > And am a devout believer in its first correllary: > > 'If you haven't tried them all you are probably not using the most efficient > version.' > > I always assume the one that hasn't been tried yet is the fastest ;-) > > In this case, IB that with PDQ on so that the several UNIONs parts can be > performed in parallel and depending on table fragmentation and index > efficiency, yes the UNION of simpler queries may certainly be faster than a > single query with multiple critera. > > Of course, YMMV applies and you MUST test all versions using several > different sets of values to be sure which will perform best most often and > least bad in the worst case. > > Art S. Kagel > > > Art S. Kagel wrote: > > <SNIP>