Here's a SSCCE benchmark I ran (related to what I had to implement) with these results:
elapsed time with checks at every iteration: 12055(ms)
elapsed time with deletion at the end: 11977(ms)
On my example at least, skipping the check at every iteration isn't noticeably faster especially for sane volumes of data, but it is faster.
import java.util.ArrayList;
import java.util.List;
public class TestCommas {
public static String GetUrlsIn(int aProjectID, List<String> aUrls, boolean aPreferChecks)
{
if (aPreferChecks) {
StringBuffer sql = new StringBuffer("select * from mytable_" + aProjectID + " WHERE hash IN ");
StringBuffer inHashes = new StringBuffer("(");
StringBuffer inURLs = new StringBuffer("(");
if (aUrls.size() > 0)
{
for (String url : aUrls)
{
if (inHashes.length() > 0) {
inHashes.append(",");
inURLs.append(",");
}
inHashes.append(url.hashCode());
inURLs.append("\"").append(url.replace("\"", "\\\"")).append("\"");//.append(",");
}
}
inHashes.append(")");
inURLs.append(")");
return sql.append(inHashes).append(" AND url IN ").append(inURLs).toString();
}
else {
StringBuffer sql = new StringBuffer("select * from mytable" + aProjectID + " WHERE hash IN ");
StringBuffer inHashes = new StringBuffer("(");
StringBuffer inURLs = new StringBuffer("(");
if (aUrls.size() > 0)
{
for (String url : aUrls)
{
inHashes.append(url.hashCode()).append(",");
inURLs.append("\"").append(url.replace("\"", "\\\"")).append("\"").append(",");
}
}
inHashes.deleteCharAt(inHashes.length()-1);
inURLs.deleteCharAt(inURLs.length()-1);
inHashes.append(")");
inURLs.append(")");
return sql.append(inHashes).append(" AND url IN ").append(inURLs).toString();
}
}
public static void main(String[] args) {
List<String> urls = new ArrayList<String>();
for (int i = 0; i < 10000; i++) {
urls.add("http://www.google.com/" + System.currentTimeMillis());
urls.add("http://www.yahoo.com/" + System.currentTimeMillis());
urls.add("http://www.bing.com/" + System.currentTimeMillis());
}
long startTime = System.currentTimeMillis();
for (int i = 0; i < 300; i++) {
GetUrlsIn(5, urls, true);
}
long endTime = System.currentTimeMillis();
System.out.println("elapsed time with checks at every iteration: " + (endTime-startTime) + "(ms)");
startTime = System.currentTimeMillis();
for (int i = 0; i < 300; i++) {
GetUrlsIn(5, urls, false);
}
endTime = System.currentTimeMillis();
System.out.println("elapsed time with deletion at the end: " + (endTime-startTime) + "(ms)");
}
}