Hi, I have a query that joins an nvarchar field from one table to a varchar field from another. The execution plan indicates that this one join will consume 92% of the resources for the entire query. When I comment out the join, it runs in 51 seconds, but with it, 8.5 minutes. I don't have admin access to the source db and cannot change the data types of the fields in the tables I am querying. What is the best approach to improve performance for the query?
Thanks in advance!
Jon
The problem is that the VARCHAR column will need to be converted to NVARCHAR before the join works... the ENTIRE column. The implicit conversions are killing you and any chance on there being index seeks on the column. Manually converting the column will take just as long.
One of the two columns is going to have to be changed to match the other or such queries against the table are pretty much relegated to being slow. As an alternative to keep from breaking any managed or other stored procedure code that may be written incorrectly or would die because of such a datatype change, you could add a persisted computed column with the datatype conversion built in that and then join on that instead of the VARCHAR column.
Of course, since you don't have admin privs, you'd have to write some code to do it and go through the proper approval and deployment channels to get that done. At least you'd make people aware of the disparity in the datatypes.
Thanks, I definitely will bring the issue up with the admin.