title: Why MemSQL is slower than SQL Server for Select SQL with Substring Operations on Binary Columns
body: <p>I have a table with two binary columns used to store strings that are 64 bytes long maximum and two integer columns.  This table has 10+ million rows and uses 2GB of memory out 7GB available memory, so there is plenty of available memory left. I also configured MemSQL based on <a href="http://docs.memsql.com/latest/setup/best_practices/" rel="nofollow">http://docs.memsql.com/latest/setup/best_practices/</a>.  </p>
  
  <p>For simple select SQL where binary columns are compared to certain values, MemSQL is about 3 times faster than SQL Server, so we could rule out issues such as configuration or hardware with MemSQL.  </p>
  
  <p>For complex SQLs that use </p>
  
  <ul>
  <li>substring operations in the Select clause and </li>
  <li>substring and length operations in the where clause</li>
  </ul>
  
  <p>MemSQL is about 10 times slower than SQL Server. The measured performance of these SQLs on MemSQL were taken after the first few runs to make sure that the SQL compilation durations were not included.  It looks like MemSQL’s performance issue has to do with how it handles binary columns and substring and string length operations. </p>
  
  <ol>
  <li><p>Has anyone seen similar performance issues with MemSQL? If so, what were the column types and SQL operations?</p></li>
  <li><p>Has anyone seen similar performance issues with MemSQL for substring and length operations on varchar columns?</p></li>
  </ol>
  
  <p>Thanks.</p>
  
  <p>Michael,</p>