Databases
StackOverflow2013
Postsdbo
POComparing SQL Server table using hashbytes slower than without
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComparing SQL Server table using hashbytes slower than without
    primarykey
    data
    text
    <p>I am using SQL Server 2012 LocalDB. </p> <p>A client wants me to compare 2 tables which consist each (believe it or not) out of 200 (!) columns of <code>nvarchar(max)</code> values. There are no indexes and there are no unique keys.</p> <p>I have googled my way to find (<a href="http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx" rel="nofollow noreferrer">http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx</a>) and the method works. However using 200 columns in the union all / group by expression is a little bit slow. </p> <p>The query looks like this</p> <pre><code>SELECT MIN(TableName) as TableName , header1, header2, header3, header.... INTO RESULTS FROM (SELECT 'table1' as TableName, table1.header1, table1.header2, table1.header3, table1.header... FROM table1 UNION ALL SELECT 'table2' as TableName , table2.header1, table2.header2, table2.header3, table2.header... ) tmp GROUP BY header1, header2, header3, header... HAVING COUNT(*) = 1 </code></pre> <p><img src="https://i.stack.imgur.com/yEDcu.png" alt="Excecution Plan of first query"></p> <p>My thought was to calculate the hashbyte values for any row as described here (<a href="http://www.bidn.com/blogs/TomLannen/bidn-blog/2265/using-hashbytes-to-compare-columns" rel="nofollow noreferrer">http://www.bidn.com/blogs/TomLannen/bidn-blog/2265/using-hashbytes-to-compare-columns</a>) and save it in an extra column (one more doesn't hurt in this case :-) ). The hashbytes themselves are calculated very fast but when I run my query afterwards using only the hashbyte value in my union all / group by /having clause the query is very much slower. Instead of about 25 seconds in the first case it runs about 15 minutes!</p> <p>The second query looks like this (with compareHash being the inserted hashbyte column):</p> <pre><code>SELECT MIN(TableName) as TableName, compareHash INTO RESULTS FROM (SELECT 'table1' as TableName , compareHash FROM table1 UNION ALL SELECT 'table2' as TableName , compareHash FROM table2) tmp GROUP BY compareHash HAVING COUNT(*) = 1 </code></pre> <p><img src="https://i.stack.imgur.com/1nIK0.png" alt="Execution Plan for second query"> I would have expected the exact opposite. Can anybody think about a reason for this behaviour?</p> <p>Best regards Sebastian</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbash74
    1. USbash74
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POComparing SQL Server table using hashbytes slower than without
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POComparing SQL Server table using hashbytes slower than without
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIt would help to see the query you're executing against the new hash column. It would also help to know if you've created a new index that includes the hash column.
      singulars
      1. POComparing SQL Server table using hashbytes slower than without
      1. USMichael Petito
    2. COMichael: I have updated it in the question. I have not created an index.
      singulars
      1. POComparing SQL Server table using hashbytes slower than without
      1. USbash74
    3. COThe queries look okay, so I'd recommend comparing the execution plan generated for each query and copying a screenshot of the plans into your question. The plans may reveal some optimization sql server can perform in the first query and not the second.
      singulars
      1. POComparing SQL Server table using hashbytes slower than without
      1. USMichael Petito
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload