Saturday, February 11, 2012

Client side autocomplete searches

I was looking for an autocomplete for an addressbook kind of functionality. The requirement was that users can have FirstName,LastName,Email,Type and when user starts typing they need to be searched first by LastName,FirstName,Email and some types were to be given higher priority than others. Doing this on server side means too many calls to be sent to the server so doesnt seems like a perfect solution.  Also mostly users will have <100-1000 contacts in addressbook so why not do it on client side.  Looked into some autocomplete plugins but none of them were what I wanted and finally landed onto this http://code.flickr.com/blog/2009/03/18/building-fast-client-side-searches/

I have never found such a perfect solution to a complex problem before. Thanks flickr for sharing it ;).

Posted by at 10:26 PM 0 comments Links to this post

Monday, February 6, 2012

Mysql JDBC driver and Streaming large resultset

We are moving one legacy component from Berkeley db to mysql so that we can replicate it to distribute read requests and shard it to scale it. I had to dump the entire db contents and stream it over http to some other component. the db content for a single customer can range from 100K to 3-4 Million records. We are using spring JDBC to make the task of dealing with jdbc api simple. Now I was using a ResultSetExtractor to stream the resultset like this.

            ResultSetExtractor resultSet = new ResultSetExtractor() {
                @Override
                public Object extractData(ResultSet result) throws SQLException, DataAccessException {
                    while(result.next()){
                        XXXXX
                    }
                    return "";
                }
            };

getJdbcTemplate(context).getJdbcOperations().query( sql, resultSetExtractor);

But it appears that Mysql native JDBC driver loads entire resultset into memory before passing the control onto ResultSetExtractor and that was causing OOM.

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.
To enable this functionality, create a Statement instance in the following manner: 
stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.


This is explained here http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html 

It seems SimpleJdbcTemplate doesnt have a setFetchSize method and JdbcTemplate has a setFetchSize but that doesn not work when you call the query method and if you use statement then the applySettings method applies fetchSize only if its >0.  So the solution is to use PreparedStatement directly

callback = new PreparedStatementCallback() {
                @Override
                public Void doInPreparedStatement(PreparedStatement pstmt) throws SQLException, DataAccessException {
                    ResultSet rs = pstmt.executeQuery();
                    resultSetExtractor.extractData(rs);
                    rs.close();
                    return null;
                }
            };
        executeStreamed(jdbcTemplate, callback, sql);

    /**
     * http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html
     * Unless you specify the statement settings as below the mysql driver is going to load all results in memory.
     *
     * @param jdbcTemplate
     * @param callback
     * @param sql
     */
    protected void executeStreamed(SimpleJdbcTemplate jdbcTemplate, PreparedStatementCallback callback, final String sql) {
        PreparedStatementCreator creator = new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                PreparedStatement pstmt = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY,
                        java.sql.ResultSet.CONCUR_READ_ONLY);
                pstmt.setFetchSize(Integer.MIN_VALUE);
                return pstmt;
            }
        };
        jdbcTemplate.getJdbcOperations().execute(creator, callback);
    }
Posted by at 11:15 PM 0 comments Links to this post

self signed certificates and chrome caching

Ran into an issue today where despite setting cache tags on a swf file,  the chrome browser was downloading it everytime I moved from one page to other and come back to same page. This was working fine in Internet Explorer and FireFox.  After spending an hour with firebug, pagespeed and fiddler  I figured out that cache tags were set correctly but still chrome won't cache it. At last I found this issue reported to chrome developers http://code.google.com/p/chromium/issues/detail?id=103875 .  It appears that because  I was using a self signed certificate on local install, chrome wont cache any content until I use a valid certificate.  So the solution is to generate a free certificate using https://www.startssl.com/ and use it locally to avoid this issue.
Posted by at 10:27 PM 0 comments Links to this post
Subscribe to: Posts (Atom)