Re: intermittent commit on insert ?

On 5 Jun 2002, galenboyer_at_hotpop.com wrote:
> On Wed, 5 Jun 2002, stephen.bell_at_cgi.ca wrote:
> HERE'S THE AUTHOR:
>
> About the author:
>
> Prashant Sarode is currently working with Syntel India Ltd as a
> Technical Analyst. Syntel is a Global Technlogy Company
>
> that is adept at developing and managing the latest advanced
> technologies such as CORBA, J2EE, .NET, XML, WAP, etc. Prashant's
> skill sets include Java, EJB, C, C++, Coldfusion, VB 6.0, CGI
> scripts in C on the Linux platform, MySQL, and Oracle/ Developer
> 2000. Prashant can be reached at prashuss_at_hotmail.com.

Here's another brilliant thought!

    14.Use WHERE in Place of HAVING

    Avoid including a HAVING clause in SELECT statements. The HAVING clause     filters selected rows only after all rows have been fetched. This could     include sorting, summing, etc. Restricting rows via the WHERE clause,     rather than the HAVING clause, helps reduce these overheads. For     example:

    Least Efficient :

        SELECT  REGION, AVG(LOC_SIZE)
        FROM     LOCATION
        GROUP BY REGION
        HAVING   REGION != ž±SYDNEYž²
        AND      REGION != ž±PERTHž²

    Most Efficient :
        SELECT   REGION, AVG(LOC_SIZE)
        FROM     LOCATION
        GROUP BY REGION
        WHERE    REGION != ž±SYDNEYž²
        AND      REGION != ž±PERTHž²

Of course you don't use having in the case he talks about! You use having when you need to further restrict on the results of a group by. But, some people will read this and decide to never use having, when it is probably one of the more underused features of SQL, IMHO (I went at least 4 years in databases until I was introduced to having.) I use having all the time. Here's the type of query I run daily, many times.

select fld1,fld2,...,fldn,count(*)
from table
group by fld1,fld2,...,fldn
having count(*) > some_number;

I use it so much I have it bound to an interactive abbreviation, "scgh"

I guess I should avoid having? How the flip do people get to publish such bunk?

-
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground. Received on Wed Jun 05 2002 - 23:37:07 CDT