Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When executing this query below it takes too long. And the result is too much records, more than possibly in the database. The query should return around 117.000 records. That is the amount of records enterred into the database during a period of half a year.

Why is this query multiplying, resulting in millions of records? If someone could give me the clu where I'm stuck, I'll be very happy.

/* Formatted on 2011/08/05 17:09 (Formatter Plus v4.8.7) */
SELECT   t2.flag, t3.reg_number, t1.reportdate, t1.latitude, t1.longitude,
     t1.value2 course, ((t1.measspeed) * 3.6) / 1.852, t1.status alarms
FROM traffic t1, details t2, registernumbers t3
WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)
ORDER BY reportdate DESC

Thanks in advance,

Maarten

share|improve this question

3 Answers 3

The part of the filter that performs the join is t1.mobileno = t2.mobile AND t2.mobile = t3.mobileno. For that to be sensible, it is likely that you need two of the following three statements to be true:

  1. mobileno is unique in traffic
  2. mobile is unique in details
  3. mobileno is unique in registernumbers

Because the join is returning more rows than you expect, I guess this is not the case. For further help we'll need to know the DDL for the tables (in particular the primary keys)

---EDIT

on second thoughts, I think

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND latitude > 48
  OR (latitude < 52 AND longitude > 2)
  OR (latitude > 52 AND longitude < 2)

needs to have some more brackets:

WHERE     t1.mobileno = t2.mobile
     AND t2.mobile = t3.mobileno
     AND UPPER (t2.flag) = UPPER ('BEL')
     AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
     AND (latitude > 52 AND longitude > 2)
     AND ( latitude > 48 
           OR (latitude < 52 AND longitude > 2) 
           OR (latitude > 52 AND longitude < 2) )

although some of the conditions seems to be redundant

share|improve this answer
    
hi, mobileno,mobile and mobileno are the primary keys in the tables. I'll check that again. The traffic table is where al the positions are stored. The other tables are only the information about the mobiles name, power etc. They contain only one row per mobile. I think it has something to do with the tables as I ran a similar query in MapInfo with only the latitude and longitude restraints. Regards Maarten –  casier Aug 6 '11 at 7:54

Your WHERE clause is poorly formed for your needs. AND and OR operations have equal precedence in evaluation, so with the clause you've listed:

 WHERE     
 t1.mobileno = t2.mobile
 AND t2.mobile = t3.mobileno
 AND UPPER (t2.flag) = UPPER ('BEL')
 AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
 AND (latitude > 52 AND longitude > 2)
 AND latitude > 48
 OR (latitude < 52 AND longitude > 2)
 OR (latitude > 52 AND longitude < 2)

you've got 3 conditions that are being OR'd together... a record is selected if any of the following is true (I listed them in reverse order to make the issue clearer):

  • its latitude and longitude meet one criterion
  • its latitude and longitude meet another criterion
  • its mobileno, flag, reportdate, latitude, and longitude all meet other conditions.

You need to add more parentheses in order to force the OR conditions to be evaluated within the context of a single AND condition, rather than having the ORs all stand on their own:

 WHERE     
 t1.mobileno = t2.mobile
 AND t2.mobile = t3.mobileno
 AND UPPER (t2.flag) = UPPER ('BEL')
 AND t1.reportdate BETWEEN '1/jan/2011' AND '1/jul/2011'
 AND (latitude > 52 AND longitude > 2)
 AND ( 
         latitude > 48
     OR (latitude < 52 AND longitude > 2)
     OR (latitude > 52 AND longitude < 2)
 )
share|improve this answer
    
+1 very well put –  Jack Douglas Aug 5 '11 at 19:37

You should consider using the more modern INNER JOIN syntax. Using table1, table2, table3 often leads to cartesian product. It's still possible with table1 INNER JOIN table2 ON ... INNER JOIN table3 ON ... however it is far less likely when you logically separate the join criteria from the filter criteria.

While the following post deals with SQL Server specifically, the concept is essentially the same:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx

share|improve this answer
    
Hi, I'll rewrite it with the inner join. –  casier Aug 6 '11 at 7:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.