troubleshooting Question

Strange SQL view, where clause, COALESCE perfomance issue

Avatar of kevinvw1
kevinvw1 asked on
Microsoft SQL ServerMicrosoft SQL Server 2008SQL
7 Comments1 Solution184 ViewsLast Modified:
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.

Any ideas?

Thanks,

Kevin.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros