SQL Server 2000 access via ODBC performance issue

[Pete56]

This is really an OCBC performance issue question but I got there through Crystal.

I am using Crystal reports 10.0 / SQL Server 2000 and an ODBC Connection with the SQL Server Driver. I am having a real performance problem. So I look at the SQL that it is sending to SQL Server and it is the following:

SELECT HCPCS, Payments, WriteOffs, Charge, Approved, BDOS, EDOS, RespPyrName

FROM ACPREPORTS.dbutstandingAR OutstandingAR

ORDER BY RespPyrName, HCPCS

I execute this in Query Analyzer and it takes 5-10 minutes to return 65,000 records (I need them all).

I change it in Query Analyzer to remove the odbc driver (ACPREPORTS) –

SELECT HCPCS, Payments, WriteOffs, Charge, Approved, BDOS, EDOS, RespPyrName

FROM OutstandingAR

ORDER BY RespPyrName, HCPCS


It now runs in 5 seconds.

I rerun the first one to make sure it is not faster because it is in cache and it still runs slow.

Do you have any suggestions? I've read the other posts on using SPROCS and Views but I'd like to see if I can solve this without doing that. If this is an ODBC issue I can't get around then any help in accessing SQL Server using the native SQL Server access would be helpful.

Thanks in advance for your help.


Pete Tanguay
pete@rock-pond.com

 

[Pete56]

Problem was the report criteria was based on a date and the fields are date-time fields in SQL Server. I was using date(field) >= fromdate and date(field) <= thrudate. The function was not resolved to an SQL where clause and so all the records were returned. I changed to date >= fromdate and date <= thrudate+1.

This works fine, does anyone have any better suggestions?

Thanks.

Pete