Alternative to ODBC stage while doing reference lookup

[ahmediftikhar]

Hi

While doing lookup using ODBC stage, we have defined primary SQL something like select a, b, c from tbl
and the reference SQL is something like select * from (select a,b,max(c) from tbl where a=? and b=? and c < ? group by a,b) where rownum < 2

We are facing with performance issue (sometime back we got mutex error too). We are finding an alternative to replace the ODBC stage to overcome the performance issue. Let us know how can we go about it.

We are using SQL Server database.

Thanks
Ahmed Iftikhar

[roy]

Hi,
I don't think that SQL has a strict logic regarding which row should be returned as your DB decides what row you get.
You might try peparing the final result set and then simply read it instead of running this poor performance SQL foreach row.

IHTH,

Hi

While doing lookup using ODBC stage, we have defined primary SQL something like select a, b, c from tbl
and the reference SQL is something like select * from (select a,b,max(c) from tbl where a=? and b=? and c < ? group by a,b) where rownum < 2

We are facing with performance issue (sometime back we got mutex error too). We are finding an alternative to replace the ODBC stage to overcome the performance issue. Let us know how can we go about it.

We are using SQL Server database.

Thanks
Ahmed Iftikhar

[chulett]

As Roy notes, you overcome this 'performance issue' by running the sql once ( not once per row)and storing the result. Then it's up to you how you leverage that stored data. Sometimes you can kind of turn a job 'sideways' and use this as a stream input. Typically, though, this would be stored in a hashed file and the reference lookup done against the hash.