SQL: very slow negative queries

Problem

The SQL negative queries take forever.

  1. SELECT empno, salesqty, salesprice FROM sales WHERE not (sal.salestype = 'B');
  2. go
[Get Code]

This due to the fact that the SQL engine always have to do a full table scan when having negative queries even if there is an index on that column.

Applies to

  • IBM DB2
  • Microsoft SQL Server
  • Oracle Database 9i+
  • I cannot give the usual data because small data sets are actually processed as table scans, since small tables are kept in memory.
  • I had to run the query at a customer against a multi-gigabyte and multiple tables that are more than a million rows. That customer did not give me the authorization to display the data.

SQL transformation

  • You should rewrite the SQL select query so that it becomes an AND/OR query. The rewriting of the select query is called a transformation.
  • You should remove the NOT clause.
  1. ----------------------------- Commands Entered ------------------------------
  2. SELECT emp.empno, emp.firstnme, emp.lastname, sal.salesqty, sal.salesprice
  3. FROM employee emp, sales sal
  4. WHERE emp.empno = sal.empno
  5. and
  6. not (sal.salestype = 'B');
  7. ------------------------------------------------------------------------------
  8. Access PLAN:
  9. employee: TABLE scan: 57.69
  10. sales: TABLE scan: 129.23
  11. hsjoin: 186.92
  12. Total cost: 186.92
  13.  
  14.  
  15. Total TIME FOR execution: 1 mn 22 sec
[Get Code]

Here is an alternative query with the same results.

  1. ------------------------------ Commands Entered ------------------------------
  2. SELECT emp.empno, emp.firstnme, emp.lastname, sal.salesqty, sal.salesprice
  3. FROM employee emp, sales sal
  4. WHERE emp.empno = sal.empno and
  5. sal.salestype in ('A','E','F','X','Z');
  6. ------------------------------------------------------------------------------
  7. Access PLAN:
  8. employee: TABLE scan: 57.69
  9. sales: TABLE scan: 63.34
  10. hsjoin: 121.03
  11. Total cost: 121.03
  12.  
  13.  
  14. Total TIME FOR execution: 46 sec
[Get Code]

That's a 41% improvement!