[sqr-users] query runs slow with integer variables in where clause?

["Kurien, Binto" <Binto.Kurien@FMR.COM>]

Hi,
    
 I have a simple query with a where clause that has an integer variable, as
given below:
        begin-select
        ssn_n (+1,1)
        from t_txn_hst
        where clnt_id_n=#clientId 
        end-select

 The above takes a very long time to run. But it runs very fast, if instead
of the variable, a value is used in the where clause as given below:
        begin-select
        ssn_n (+1,1)
        from t_txn_hst
        where clnt_id_n=12345 
        end-select

 After some trial and error, I found that one way to make the query run fast
with the variable, is to use a convert function as given below:
        begin-select
        ssn_n (+1,1)
        from t_txn_hst
        where clnt_id_n=convert(int,#clientId)
        end-select

 But I do not understand why a convert function is needed. Though the
database column is defined as a int, the variable too is read as a integer (
input #clientId type=integer ).  Can anyone please shed some light on this?

I use Brio Report Builder version 6.0 ( Build 85 ) connected to Sybase 11.1
via ODBC.

Regards,
 Binto



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users