Sample Header Ad - 728x90

Derby/SQL - How to increase performance when left joining and retrieving varchar columns

1 vote
3 answers
3623 views
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!!
Asked by Stephane Grenier (191 rep)
Nov 26, 2012, 03:00 AM
Last activity: Nov 9, 2016, 06:56 AM