Strange SQL view, where clause, COALESCE perfomance issue
Posted on 2014-12-18
I have a weird sql issue.
I don't have control over these SQL statements. They are created and executed (behind the scenes) by an application (Sage CRM).
So please don't ask me to change the SQL statement because I can't and the product manufacturer (Sage CRM) says the issue is with my sql server...
The only reason I know what the sql statement looks like is because it times out and an error gets written to a log file.
This SQL statement takes 4 minutes to return 792 records -
SELECT * FROM z_vmailinglistreport WHERE
(( COALESCE(comp_type, '') = N'rescontractor' ) )
AND ((pers_secterr = -2034921070))
AND coalesce(pers_cstatus,'') = 'Active'
If I use the SELECT statement from the view and add the WHERE clause it runs in 1 second.
If I remove either one of COALESCE functions it runs in 1 second.