SQL Query tuning

Question

Post reply

SQL Query tuning

Madhura A

SSC Enthusiast

Points: 170
More actions
September 19, 2007 at 3:23 pm

#374312

Please help me in giving the suggestions for tuning the below query. The execution time is 12 min.
Below is the record count of each table :
FTProductUsage
- 35737259
FTContractValue - 1980410
FTContractRole - 130644561
VDTJobStartDT - 5480
LKCostCenter - 243
select a14.JSD_Month_ID Month_ID,
max(a14.JSD_MonthYr_DS) MonthYr_DS,
a13.CostCenter costcenter_DS,
max(a15.CostCenterName) CostCenterName,
a15.RegionID RegionID,
max(a15.RegionName) RegionName,
a15.ChannelID ChannelID,
max(a15.ChannelName) ChannelName,
sum(a11.Quantity) WJXBFS1
from FTProductUsage a11
join FTContractValue a12
on (a11.Account_ID = a12.Account_ID and
a11.Contract_KY = a12.Contract_KY)
join FTContractRole a13
on (a12.Contract_ID = a13.Contract_ID)
join VDTJobStartDT a14
on (a11.JobStartDT_KY = a14.JobStartDT_KY)
join LKCostCenter a15
on (a13.CostCenter = a15.CostCenter)
where (a11.ProductCapability in ('JobListing')
and a11.UsedFlg in (1)
and a11.HybridJobFlg in (0)
and a14.JSD_Month_ID = 200708
and a13.YearMonth = 200708
and a13.RoleType in ('PRIMREP'))
group by a14.JSD_Month_ID,
a13.CostCenter,
a15.RegionID,
a15.ChannelID

Viewing 15 posts - 1 through 15 (of 21 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

start by changing all those "in" for "="

You need to know if the indexes are being used ( can you post the query plan )

In addition you should also check for fragmentation levels and satitstics

Cheers

* Noel

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Noel's most certainly correct (he usually is).

Make sure you place indices on the columns that are being used to

a) Join the tables

b) Perform the grouping

Include in the indices the fields that you're returning. This will prevent "bookmark/row lookups".

Is this the query that's always executed, or do you have the in's there because this is dynamically generated and thus you could have several values in each IN clause?

Could you perhaps post

a) The table definitions

b) Describe in words what you're trying to achieve rather than just posting the SQL... There could be a better way of doing it.

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

In addition, the index definitions would assist and, if possible, the xml query plan (link, not post, it will be huge)

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

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 4

Hi,

In a test environment, take the query in the query analyzer then run (Index Tuning Wizard), it will recommend you creating/droping indexes.

Ahmed

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

One of the most important things was not given: the distribution of the values in the various tables. That will help determine if/which indexing will help.

Also, the optimizer 'should' be converting those single INs to equals for you I would expect.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQL_ABD SSCertifiable Points: 5832 More actions · Answer 6

Hi TheSQLGuru,

It sure, you're rigth 🙂 after converting the Ins to equals, it will be recommend to use the index tuning wizard. After you can you the profiler, yo want to go deeply.

Ahmed

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 7

The use of IN is a red herring and in this case makes no difference to the query. The DTA may well help you but really you need to analyse the query plan, just pasting a query into the forum has no real use as your hardware platform may well be a significant factor. I'm not sure if there's a good book on this for sql 2005 but the sql 2000 performance tuning handbook has a very good section and would certainly be a good place to start.

read up on indexing in BOL or/and get some inside SQL server books.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

To add to that... are there Primary Keys on these tables? Is there a clustered index? Are the tables in a constant state of flux do to inserts, updates, and deletes? What is the Fill Factor for the tables? And what kind of locking/blocking are you experiencing when you run the code? Are any of these "tables" really "views"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 9

After almost a month I don't think this OP is coming back ... 🙂

* Noel

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Heh... Thanks, Noel... I keep forgetting to check the date on the original post...

Oh well... keeps my hand at typing and I have seen it where the OP actually does come back after a month... very rare, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jurriaan Themmen SSC-Addicted Points: 457 More actions · Answer 11

Ian Yates (9/19/2007)
Make sure you place indices on the columns that are being used to
a) Join the tables
b) Perform the grouping

c) Are used in the where clause

In case of multiple fields in one table, make indexes on the combined fields and also in the order in which you call them from the SQL statement, i.e. if your where clause looks like 'WHERE tbl1.fld1 = 'A' AND tbl1.fld2 = 'B', make sure the index for tbl1 is on fld1 and fld2 in that order.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 12

Just for the record... I stumbled across this post by accident and found everyone's suggestions very helpful for my own query tuning.

Someone mentioned grouping on indexed fields.

I knew you should always try to join on indexed fields, but I wasn't aware that grouping had an impact.

Could someone elaborate on that a bit?

EDIT:

I've also heard that if you create an index on (for example): OrderID, CustomerID (say in tblCustomerOrders) that if you query that table you need to make sure you call it in the same order as it is in the index. However, I've ALSO heard that doesn't matter as the query optimizer will sort it out. Is there any truth to that?

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

In my experience it has the most impact when the aggregated columns are also in the index.

Consider a simple table

CREATE TABLE Payments (

PaymentKey INT IDENTITY PRIMARY KEY,

CustomerID int,

InvoiceID int,

PaymentDate datetime,

Amount Numeric(17,5)

)

Assume that the clustered index (and hence the physical order of the rows) is on the identity column. Assume there are lots of rows.

Take the following query.

SELECT CustomerID, SUM(Amount) FROM Payments group by customerID

Without any additional indexes, SQL will execute that as either a hash aggregate or a sort. That is because the data is not ordered by the grouped column, and hence requires hashing or sorting before it can be grouped.

Now add an index on CustomerID, Amount and run the same query. Now, the aggregate can be satisfied just with the non clustred index and, more importantly, the data is in order of customerID, so just a stream aggregate is required and the data does not have to be resorted or run through a hashing algorithm.

Does that make sense?

I think I'll write up a blog entry on this over the weekend, complete with some working examples.

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

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

Maxer (10/12/2007)
I've also heard that if you create an index on (for example): OrderID, CustomerID (say in tblCustomerOrders) that if you query that table you need to make sure you call it in the same order as it is in the index. However, I've ALSO heard that doesn't matter as the query optimizer will sort it out. Is there any truth to that?

The query optimiser can sort it out, providing they are equality queries.

An index on two columns OrderID, CustomerID is seekable for any of the following query forms.

OrderId = x

OrderID = x and CustomerID = y

OrderID = x and CustomerID != y

Order ID = x and CustomerID > y

CustomerID = y and OrderID = x

It is not seekable for the following (can you see why? Imagine a phone book as an index on surname, firstname to see reasons). These will probably require partial or complete index scans or may not be used at all.

CustomerID = y

OrderID !=x and customerID = y

OrderID > x and customerID = y

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