tsql query analyzer : how do you reduce "cost"?

Question

I'm running sql analyzer on the following query

SELECT bl.Invoice_Number, bl.Date_Invoice, ti.TranNo, bt.Description,
    CONVERT(decimal(15,2), bl.Invoice_Amount) AS Invoice_Amount, co.Company_ID, co.Account_Nbr,
    isnull(bl.Reference,' ') as Reference, bl.Billing_Log_RecID AS BillingKey
    FROM [CONN.domain.NET].cwwebapp.dbo.Billing_Log bl
    LEFT JOIN [App].dob.tarInvoice ti
     ON bl.Invoice_Number = dbo._fnStripLeadZeros(ti.TranNo)
    INNER JOIN [CONN.domain.NET].cwwebapp.dbo.Billing_Type bt
     ON bl.Billing_Type_ID = bt.Billing_Type_ID
    LEFT JOIN [CONN.domain.NET].cwwebapp.dbo.Company co
     ON  bl.Company_RecID = co.Company_RecID
    WHERE bl.Date_Invoice >= '2009-05-05'
     AND ti.TranNo IS NULL
     AND bl.Invoice_Amount <> 0
     AND bl.Billing_Type_ID <> 'D'
     AND bl.Billing_Type_ID <> 'P'
--   AND bl.Billing_Type_ID <> 'M'
Order By bl.Invoice_Number

The query runs on the [App] server and connects to [Conn] sql server to do a join and the diagram is telling me

remote query cost : 62%
customered index scan [App].[dbo].tarInvoice.[PK__...  Cost : 34%

This query is taking 2 mins to run. Any ideas on how would I go about figuring out how to make this run more efficiently? I"m guessing it has to do with connecting to another sql server on the same network.

thanks in advance.

Answer 1

Try adding the remote server as a linked server.

Answer 2

There's only one local table being used, so you could shift more of the query to the other server:

select *
from openquery([CONN.domain.NET],'
SELECT bl.Invoice_Number, bl.Date_Invoice, ti.TranNo, bt.Description,
    CONVERT(decimal(15,2), bl.Invoice_Amount) AS Invoice_Amount,
    co.Company_ID, co.Account_Nbr, isnull(bl.Reference,'' '') as Reference,
    bl.Billing_Log_RecID AS BillingKey
    FROM cwwebapp.dbo.Billing_Log bl
    INNER JOIN cwwebapp.dbo.Billing_Type bt
        ON bl.Billing_Type_ID = bt.Billing_Type_ID
    LEFT JOIN cwwebapp.dbo.Company co
        ON      bl.Company_RecID = co.Company_RecID
    WHERE bl.Date_Invoice >= ''2009-05-05''
        AND bl.Invoice_Amount <> 0
        AND bl.Billing_Type_ID <> ''D''
        AND bl.Billing_Type_ID <> ''P''
') remote
LEFT JOIN tarInvoice ti
    ON remote.Invoice_Number = dbo._fnStripLeadZeros(ti.TranNo)
WHERE ti.TranNo IS NULL
Order By remote.Invoice_Number

Not sure about the exact syntax, just trying to point in a possible improvement direction.

Answer 3

you are doing a complete scan of the tarInvoice clustered index (touch every index entry), see if you can remove the function call dbo._fnStripLeadZeros(ti.TranNo) so it will use the index.

possibly add leading zeros onto bl.Invoice_Number and join to unaltered ti.TranNo

EDIT

add computed column without leading zeros and add an index:

ALTER TABLE dbo.tarInvoice ADD TranNoZeroFree AS Convert(int,TranNo) PERSISTED 
GO
CREATE NONCLUSTERED INDEX IX_tarInvoice_TranNoZeroFree ON dbo.tarInvoice (TranNoZeroFree) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Answer 4

I would also suggest that if you have to convert data in queries such as: CONVERT(decimal(15,2), bl.Invoice_Amount) then you need to consider refactoring your database to use the correct datatype.