Slow report using ODBC

[sdonaldson]

CR 8.5 Developer Edition, reporting on an MS-SQL Server 7.0 database.
I have a report which takes 2 or 3 seconds to run using the SQL Server database driver, p2ssql.dll.
If I switch the same report to using the ODBC driver, p2sodbc.dll, it takes at around 40 seconds to run when the report is called from our (Clarion) application. When the report is previewed in CR using this driver it only takes a couple of seconds to run, as per the SQL Server driver.
I have to use the ODBC driver as this is how all reports are configured in our application.
There are many other reports using the same ODBC driver which run as quickly as they did when using the SQL Server driver.

Does anyone have any ideas as to why the report is so slow ?

Thanks,
Shaun

 

[sdonaldson]

I've fixed it.
The record selection formula included an SQL Expression, thus:
{%AbsoluteValue}
abs(PLitems.total)

In the Record Selection formula:
{%AbsoluteValue} = abs({?MyParameter})

Although this resulted in the desired addition to the WHERE clause of the SQL SELECT statement, it appears to have greatly reduced the performance of the report.
I deleted the SQL Expression and changed the Record Selection formula to read:
({PLitems.total} = {?MyParameter} or {PLitems.total} = {?MyParameter} * -1)

I understood SQL Expressions to be fast and that as long as the Record Selection formula was replicated in the WHERE clause of the SQL SELECT statement one could reasonably expect the SQL Server to filter the records, thereby resulting in rapid report generation.
Maybe not, then ?

Shaun.