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.

Through trial and error I found that if I do a left join such as:

SELECT 
    firsttable.id,
    secondtable.id,
    secondtable.varcharColumn
FROM 
    firsttable
LEFT JOIN 
    secondtable
ON 
    firsttable.id=secondtable.id

The performance is terrible for larger tables. If I EITHER remove secondtable.varcharColumn as a column in the resultset OR change the type then the performance is about an order of magnitude better. The column is a varchar 255, so it should not make that much of a difference. If I change the type of column to integerColumn or dateColumn than I also see an order of magnitude difference in the performance. Even a varchar of a few characters seems to result in the same performance degradation.

Please note that the WHERE clause is NOT the issue here. I've omitted it because the issue is the same whether or not a WHERE clause is included. The issue has to do with the type of data in the column and not the quantity of data returned. I could also use LIMIT 1000 and have the very same issue.

Indexing the column will not help as the column is not used for joining. It is also not part of the where clause if there was one.

Any suggestions on how to improve the performance would be greatly appreciated!!

share|improve this question

migrated from stackoverflow.com Nov 26 '12 at 11:28

This question came from our site for professional and enthusiast programmers.

    
If you are not sure whether this is SQL issue or Derby. Try running the same query on console and using code and check the difference. –  Hardik Mishra Nov 26 '12 at 7:05
    
How long does it take if do a count on the query: select count(*) from ( ... your query ...) t –  a_horse_with_no_name Nov 26 '12 at 11:44

2 Answers 2

The lack of a WHERE clause or an ORDER BY clause probably means that your query is not using an index (and is most certainly selecting all rows in the table).

So, I'd suggest:

  1. Add a "WHERE" clause
  2. Add an "ORDER BY" clause
  3. Make sure that you have appropriate indexes on the two tables for the ids and the columns in the WHERE and ORDER BY clauses.
  4. Look at the query execution plan to make sure that your indexes are used.
share|improve this answer
    
I didn't include the WHERE, etc. clauses as these aren't the cause (in the real query they are included). Adding all of these would do no difference. If I remove "secondtable.varcharColumn" from the query I get an order of magnitude difference in performance, regardless of anything else in the query. And I change it to a say intColumn, dateColumn, etc. then everything is fast again. It only occurs with a varchar column, even if the length is defined. –  Stephane Grenier Nov 26 '12 at 4:31
    
Doesn't Order By clause requires sorting and may cause more delay ? Using WHERE is correct –  Hardik Mishra Nov 26 '12 at 6:25
    
Where is irrelevant for this question if you read the details: "If I remove secondtable.varcharColumn as a column in the resultset then the performance is about an order of magnitude more" Again, the WHERE is irrelevant. I could even add a LIMIT 100 and the same issue would be present. –  Stephane Grenier Nov 26 '12 at 6:41
    
Ooops. I forgot to mention it was for @GreyBeardedGeek –  Hardik Mishra Nov 26 '12 at 6:46
    
I do agree with you about the ORDER BY can only slow things down. –  Stephane Grenier Nov 26 '12 at 7:06

Check out your query plan and see whether columns included is using indexing.

When you use LEFT JOIN it does matter (results-wise) which table to start with; So, check out that also.

Create index on secondtable.varcharColumn

Here is the link to read more on performance : http://db.apache.org/derby/docs/10.8/tuning/index.html

share|improve this answer
    
Creating an index on this table is not going to do anything as it's an appended column, nothing is searched by or joined by the varcharColumn. As for the order it has to be this way because of the type of query. –  Stephane Grenier Nov 26 '12 at 6:44
    
The thing is that indexing that column does nothing. It's not used in the WHERE or JOIN clause. I would agree with you if it was used but unfortunately it's not. –  Stephane Grenier Nov 26 '12 at 6:59

Your Answer

 
discard

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