please help us to improve this query performance level without affecting the existing functionality

Question

Post reply

please help us to improve this query performance level without affecting the existing functionality

HelloHai

SSC Rookie

Points: 33
More actions
June 4, 2015 at 6:10 am

#304714

nil

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I believe Chris asked for the execution plan already. Table definitions and index definitions are also kinda useful.

Why a left join? Do you really have invoices that don't have customers?

Why an unfiltered list of every single invoice in your system going right back to the beginning? Who's going to use such a list?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

mohanaprabhu.v (6/4/2015)
SELECT tblInvoice.*, tblCustomer.FirstName + SPACE(1) +
tblCustomer.LastName as CustomerName FROM tblInvoice WITH(NOLOCK)
LEFT JOIN tblCustomer WITH(NOLOCK) ON tblInvoice.CustomerNo = tblCustomer.CustomerNo

See the link in my signature about the best way to get help. DDL and sample data would help.

In the meantime... if you can add a WHERE clause to filter out unneeded rows that might speed things up and maybe even get you an index seek. An index onblCustomer.CustomerNo that in includes blCustomer.FirstName and LastName might speed things up too.

If it's important that your query always return the correct data then those NOLOCK hints will have to be removed.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

HelloHai SSC Rookie Points: 33 More actions · Answer 3

HelloHai

SSC Rookie

Points: 33

June 4, 2015 at 6:43 am

#1802960

nil

Lowell SSC Guru Points: 323518 More actions · Answer 4

mohanaprabhu.v (6/4/2015)
SELECT tblInvoice.*, tblCustomer.FirstName + SPACE(1) +
tblCustomer.LastName as CustomerName FROM tblInvoice
LEFT JOIN tblCustomer ON tblInvoice.CustomerNo = tblCustomer.CustomerNo WHERE
TransactionDate = '2014-10-23' AND ReceiptNo = '1962' AND TillNo = '004' AND StoreNo = '1101'

the query doesn't properly alias all the columns int he WHERE.

here's an assumption: all the WHERe are on tblInvoice:

SELECT tblInvoice.*,

tblCustomer.FirstName + Space(1)

+ tblCustomer.LastName AS customername

FROM tblInvoice

LEFT JOIN tblCustomer

ON tblInvoice.CustomerNo = tblCustomer.CustomerNo

WHERE tblInvoice.TransactionDate = '2014-10-23'

AND tblInvoice.ReceiptNo = '1962'

AND tblInvoice.TillNo = '004'

AND tblInvoice.StoreNo = '1101'

if that was true, an index like this might help, but it totally depends ont he selectivity of the columns:

CREATE INDEX IX_tblInvoice_FourColumns ON tblInvoice(TransactionDate,ReceiptNo,TillNo,StoreNo) INCLUDE(CustomerNo)

if you don't need ALL the columns from tblInvoice (if you don't need ALL the columns from tblInvoice.*, and can do just the specific columns you really need, you could make that same index supply the columns for you.

CREATE INDEX IX_tblInvoice_FourColumns ON tblInvoice(TransactionDate,ReceiptNo,TillNo,StoreNo) INCLUDE(CustomerNo,InvoiceNumber,OtherColumns)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mohanaprabhu.v (6/4/2015)
SELECT tblInvoice.*, tblCustomer.FirstName + SPACE(1) +
tblCustomer.LastName as CustomerName FROM tblInvoice
LEFT JOIN tblCustomer ON tblInvoice.CustomerNo = tblCustomer.CustomerNo WHERE
TransactionDate = '2014-10-23' AND ReceiptNo = '1962' AND TillNo = '004' AND StoreNo = '1101'

And the execution plan?

The table definitions?

The index definitions?

And is this the actual query this time?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

For a handful of reasons, besides performance, it's best not to return all columns from a table using * but rather to specify only the minimum columns your application or report requires. For example, if all your report needs is invoice number, order date, and customer name; that could be covered by an index on each table.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho