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.

@phill said "how do you know how to do that? ", experience, experience, experience, and this tip: when a query runs slow do the following: run SET ShowPlan_All ON, then run your query, look at the output for the word "scan". your problem is there. "Scan" = touch each row (table or index). would you like to "scan" a phone book or use the index? you can't use an index when you modify it, so I recommended that you don't modify it. You can modify the search string (bl.Invoice_Number) that you compare to the index and still use the index — KM., May 07 '09 at 20:07

KM. · Accepted Answer · 2009-05-08T21:20:49.203

2

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

edited May 08 '09 at 21:20

answered May 07 '09 at 19:42

KM.

101,727
34
178
212

that did the trick.. how do you know how to do that? can you recommend any articles to read up on this? – phill May 07 '09 at 19:52
1

@phill said "how do you know how to do that? ", experience, experience, experience, and this tip: when a query runs slow do the following: run SET ShowPlan_All ON, then run your query, look at the output for the word "scan". your problem is there. "Scan" = touch each row (table or index). would you like to "scan" a phone book or use the index? you can't use an index when you modify it, so I recommended that you don't modify it. You can modify the search string (bl.Invoice_Number) that you compare to the index and still use the index. – KM. May 07 '09 at 19:59
@phil, anytime you have a function in a join condition it is a bad thing. Functions can be as bad a cursors for performance and should be avoided as much as possible. – HLGEM May 07 '09 at 21:00
i went back and checked the output and its incorrect, I need to have the leading zeroes match up. – phill May 07 '09 at 22:07
@Phil, what are the datatypes of bl.Invoice_Number and ti.TranNo and how are they formatted? – KM. May 08 '09 at 12:40
the bl.invoice_number is a varchar(15) and ti.TranNo is varchar(10). the ti.TranNo looks like "0001205" format with leading zeroes. The bl.invoice_number just has "1205" format. – phill May 08 '09 at 14:14
best solution fix the bad design: best: make both INTs, 2nd best: permanently remove leading zeros from data and in application logic. You can still display them if necessary, just don't store them! if you can't redesign, you need to do this in the join: dbo._fnAddLeadZeros(bl.Invoice_Number)=ti.TranNo, you'll probably need to make the dbo._fnAddLeadZeros() function. If that doesn't work, you'll have to do one of the redesign options. – KM. May 08 '09 at 14:31
just thought of another option, the both INTs is the best by far, but this may be doable, if a redesign isn't: add a computed column onto your tarInvoice table where it is CONVERT(int,TranNo). add an index on this, and then join to it. – KM. May 08 '09 at 14:35
a redesign isn't doable.. how do i add an index to this? – phill May 08 '09 at 20:16
i'll be doing this on a test db first. I have concerns it might affect the rest of the accounting system if the table is modified. – phill May 08 '09 at 21:27

score 0 · Answer 2 · answered May 07 '09 at 19:26

0

Try adding the remote server as a linked server.

answered May 07 '09 at 19:26

Jamie Ide

48,427
16
81
117

The query seems to suggest he's already using a linked server? With the name of [CONN.domain.NET] – Andomar May 07 '09 at 19:29
He's using 4-part naming, unless it's possible to name a linked server [CONN.domain.NET]. – Jamie Ide May 07 '09 at 19:31
The first part of a four-part table name is a remote server, right? Switching database on the local server would be the second part. Like server.database.schema.table. – Andomar May 07 '09 at 19:36
I don't think the database is linked.. how would I check? – phill May 07 '09 at 19:47

score 0 · Answer 3 · answered May 07 '09 at 19:27

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.

I tried to execute the above and received the returned message "Deferred parepare could not be completed" Not sure what that means.. sorry, i'm a newbie at this — phill, May 07 '09 at 19:46
It means the query argument to openquery() is not correct sql. You could try it on the other server to get a more detailed message, but as KM's already solved the problem, that's kinda moot :) — Andomar, May 07 '09 at 19:59
true, but i'd still like to learn different ways on how to do the same thing. it is still a very interesting approach — phill, May 08 '09 at 14:15

score 0 · Answer 4 · answered May 07 '09 at 21:01

0

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.

answered May 07 '09 at 21:01

HLGEM

94,695
15
113
186

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

4 Answers4