1

I have 3 tables.

They have

table_a 5m records

table_b 20m records

table_c 20m records

I'm performing the query below. 

SELECT * FROM table_a a 
INNER JOIN table_b b ON a.a_id = b.a_id AND b.b_come='100' 
INNER JOIN table_c c ON b.c_id = c.c_id AND c.c_type IN ('A','B')
WHERE date(a.a_dt) = '01/01/2012 00:00:00.000'

The query is quite slow. I think it is because the data in its nature they have

about 1000 records of a.a_id = b.a_id

and

about 20m*20m records of b.c_id = c.c_id.

I think I should get the result of a.a_id = b.a_id then do the one of b.c_id = c.c_id so that the performance could be a lot better. But it seems INNER JOIN has no concept of order. How should I do this to improve the performance?

Improve this question
4
  • You're not really saying you have four hundred trillion rows in table_c, are you?
    – Michael Green
    Commented Jun 10, 2014 at 11:08
  • 20 million rows in table_c
    – Marcus Thornton
    Commented Jun 11, 2014 at 3:54
  • The RDBMS will optimize the join order, and usually does so more intelligently than any but the most skilled DBA. As Michael Green implies, join order is almost certainly not the real problem.
    – Jon of All Trades
    Commented Jul 15, 2014 at 21:45
  • Note that the Informix DATE function returns a value with no time component. Given that the constant is an approximation to an Informix DATETIME YEAR TO FRACTION(3) literal, there is conversion work going on there. It would be helpful to know the type of the a_dt column. The chances are that there are better ways to write that condition, but which is best depends on the type of a_dt. Which version of Informix and which platform you're running on might be relevant. It is always relevant to ask 'have you run UPDATE STATISTICS (or, depending on the version, has the system done it for you)?'
    – Jonathan Leffler
    Commented Aug 3, 2015 at 23:45

1 Answer 1

Reset to default
2

The easiest way to influence a query plan and make it run faster is to have good indexes and ensure the statistic on those indexes are up-to-date. In your case have indexes on the primary and foreign keys in each table. It would be best if you included b_come and c_type in their respecitive indeces, too. (Sorry, I'm not strong on Informix; if the syntax allows the include clause or filtered indexes investigate and implement these.)

Wrapping the date() function around a_dt generally precludes any use of an index on that column. If makes it "non-SARGable" in the jargon. It may be better to structure it as

WHERE a.a_dt >= '01/01/2012 00:00:00.000'
and   a.a_dt <  '01/02/2012 00:00:00.000' -- or whatever the day after 1/1 is in your locale.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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