July 6, 2006 at 8:40 am
Hi,
Currently my system works like this.
UI calls SP which calls UDF which call some views in the linked servers.
This is having some performance probems. SO I have decided to do away with the above approach.
I decided to use openquery statement from my udf. but the problem is udf can not take dynamic sql or they can not call sps. Can u let me know a better option to handle this problem.
Thanx
July 6, 2006 at 10:28 am
An SP calling a UDF is fine, your bottleneck sounds like the views in the linked servers.
You could write an OPENQUERY directly in a stored procedure and use Output parameters, this has given me excellent performance against a DB2 linked server. Again, you can't really use dynamic SQL for this.
You could use the 4-part naming syntax to query your linked server, again - I've had excellent performance running against a multi-million record DB2 file.
SELECT
@ProcessedDate = UPHDAT
FROM
DB2.DATABASENAME.LIBRARYNAME.FILENAME
WHERE
UPHNUM = @BatchReference
AND
UPHSUP = @DistributorId
Hard to advise you on the best option without knowing exactly what you're trying to do - Are you trying to retrieve a resultset, or some values etc...?
Cheers
July 6, 2006 at 2:04 pm
Hi,
Thanks for the reply. Let me elaborate on what I am doing
I have a sp which has code like below
select * from tbl join udf1 etc.,
udf1 calls another udf2 which gets the data from a view from linked server.
Currently, due to perforamce bottlenecks, I have to move to openquery which is more effiicinet.
But udf can not call dynamic sql like this : select * from openquery(myserver,'select * from tbl where column = 'myname')
I have to find an alternate way of doing this.
Can you let me know your thoughts on it.
Can you explain what is the four part naming you have mentioned above.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply