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?

  • You're not really saying you have four hundred trillion rows in table_c, are you? – Michael Green Jun 10 '14 at 11:08
  • 20 million rows in table_c – Marcus Thornton Jun 11 '14 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 Jul 15 '14 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 Aug 3 '15 at 23:45
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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