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
Alternative to ODBC stage while doing reference lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 22
- Joined: Thu Jul 29, 2004 8:10 am
Re: Alternative to ODBC stage while doing reference lookup
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,
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,
ahmediftikhar wrote: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 R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers