Looking over the XML of the query plan, the fact that sixteen different fields have implicit
datetime data conversions is a red flag to me. This indicates that the data types across multiple fields are inconsistent, and will slow your processing significantly.
Also, since the tables appear to be
HEAP tables (that is, they have no clustered index) will frequently result in slower processing, especially with large tables. Combined, these two factors result in a totally non-indexed table scan.
Using correct data types (and since I don't know why the existing field data types are what they are, and I don't actually know the existing field data types, I can't conclude definitely that the data fields should be converted to a
datetime data type of some sort) -- my first instincts in this case say that the differing data types are a big part of the slow processing. This includes the
NOTE: Any data type conversion in a comparison will stop indexes from being used. Since essentially all of your comparisons use data type conversions, your indexes will not be used.
My recommendation: These tables need to be refactored to use correct data types. Whether this will be ensuring that textual dates need to be formatted consistently and all fields used are
varchar/nvarchar, or they all are converted to
datetime data type fields is not my call, but they should be consistent. As for the varchar(6) conversion - if this is being done to limit the comparison to the first six characters, use the
SUBSTRING() functions to be able to use an index (this is an exception - if you are only comparing the first N characters of a textual field, your index will still be used for the comparison). If this is being done to compare a
varchar value with an
nvarchar value, then normalize the data if you can.