Speed up a Stored Proc

[enak]

Can anyone tell me if there is a way to speed up the following stored proc?

INSERT INTO Temp_TrustTransDetail
SELECT fn_TrustTrans.FileNumber, e.trindex, e.trline, e.trdesc, 'nkane'
FROM (fnBP_UniqueFileNumbers() INNER JOIN fn_TrustTrans() ON fnBP_UniqueFileNumbers.FileNumber = fn_TrustTrans.FileNumber)
INNER JOIN elitedb.son_db.dbo.trdesc AS e ON fn_TrustTrans.trindex = e.trindex
ORDER BY fn_TrustTrans.FileNumber, e.trindex, e.trline

The function fn_TrustTrans pulls data from a linked server. The procedure takes about 51 seconds to run.

Thanks
enak

 

[eramgarden]

I'd say run your query in QA and look at the execution plan.That would give u a better idea of what join is taking time.

Also, look into "set no-count on/off". I think if you're not returning data from the SP, it should be off or something..but look at up.

 

[enak]

Thanks for the hints. SET NOCOUNT ON/OFF did nothing.

I found that the join on the table from the linked server is the problem. "INNER JOIN elitedb.son_db.dbo.trdesc AS e ON fn_TrustTrans.trindex = e.trindex"

How can I speed this connection up? When I query the table by itself I don't have any problems.

Thanks
enak

 

[SQLSister]

What indexes do you have?

 

[enak]

I changed the INNER Join to a LEFT JOIN and that cut the time in half but now I get more records than I want.

I don't think that there are any indexes unless they exist on the linked server. I don't have permissions to look at that server.

 

[SQLSister]

INdexes are what you need to speed up processing. ASk the dba of the linked server what indexes you have and look at your own. THere should be ndexes on the fields you link on and on the ones in the order by clause.

 

[funcrecall12]

INSERT INTO Temp_TrustTransDetail
SELECT XX.FileNumber, e.trindex, e.trline, e.trdesc, 'nkane'
FROM (fnBP_UniqueFileNumbers() INNER JOIN fn_TrustTrans() ON fnBP_UniqueFileNumbers.FileNumber = fn_TrustTrans.FileNumber) XX
INNER JOIN elitedb.son_db.dbo.trdesc AS e ON XX.trindex = e.trindex
ORDER BY XX.FileNumber, e.trindex, e.trline

I have revise the statement pls. check. hope this help.

Jerry

 

[enak]

I don't understand the XX. I can not get it to work with it. Please explain.

Thanks
enak

 

[jmeckley]

if you use a left outer join then you would also need a where clause to select your records.
[tt]
FROM (
fnBP_UniqueFileNumbers() INNER JOIN fn_TrustTrans() ON
fnBP_UniqueFileNumbers.FileNumber =
fn_TrustTrans.FileNumber
)
LEFT OUTER JOIN elitedb.son_db.dbo.trdesc AS e
ON fn_TrustTrans.trindex = e.trindex
WHERE e.trindex IS NOT NULL
[/tt]

Jason Meckley
Database Analyst
WITF