Problem with SQL Server 2005

[jello212]

We have some clients that are on SQL Server 2000 and others that are on SQL Server 2005.

I have a report with the following query:


SELECT "vwGenSvcInfo"."Department_Abbr", "vwGenSvcInfo"."Actual_Dr_Name",
"vwGenSvcInfo"."Patient_Number", "vwGenSvcInfo"."Service_Date_From",
"vwGenSvcInfo"."Department_Descr", "vwGenSvcInfo"."Actual_Dr_LFI",
"vwGenSvcInfo"."Actual_Dr_Abbr", "vwGenSvcInfo"."Service_Fee",
"vwGenSvcInfo"."Procedure_Code", "vwGenSvcInfo"."Update_Status",
"vwGenSvcInfo"."Location_Abbr", "vwGenSvcInfo"."Proc_Category_Abbr"
FROM "vwGenSvcInfo"
WHERE ("vwGenSvcInfo"."Service_Date_From" >='2007-06-01' AND "vwGenSvcInfo"."Service_Date_From"<'2007-07-01')
AND ("vwGenSvcInfo"."Update_Status"=0 OR "vwGenSvcInfo"."Update_Status"=1)
AND ("vwGenSvcInfo"."Location_Abbr"='HC-Main' OR "vwGenSvcInfo"."Location_Abbr"='HC-Spec' OR "vwGenSvcInfo"."Location_Abbr"='NEMEDIN' OR "vwGenSvcInfo"."Location_Abbr"='NEMEDOUT')
AND NOT ("vwGenSvcInfo"."Proc_Category_Abbr"='20' OR "vwGenSvcInfo"."Proc_Category_Abbr"='99')


This report runs like a champ on SQL Server 2000 (100,000 rows per minute or faster). On SQL Server 2005, it's returning about 300 rows per minute.

I'm able to force the performance by taking the Service_Date_from out of the select expert and into a calculation and using the calculation in the select expert. This causes the Date to not be a part of the where clause - Crystal applies this constraint after the fact.

Does anyone have any ideas as to why the Date in the where clause is causing the report to choke on SQL Server 2005?

 

[Turkbear]

Hi,
Are the databases configured the same ( memory allocations, etc) - is the Date field indexed in both?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jello212]

I support about 4000 clients and the database is the same on all clients - same tables, views, indexes, columns, etc.

The hardware/setup of the servers for each client are controlled by the client, so, that's out of my control.

But, I've taken the query and run it on about a dozen different clients plus a few inhouse databases and the results are consistent - SQL Server 05 crawls while 2000 is fine.