Performance tuning

Question

Performance tuning

ANn -425914

Right there with Babe

Points: 733
More actions
February 26, 2015 at 5:57 pm

#315694

For SQL performance tuning for a query, does the order of columns in where filter clause matters?
Also does order of columns matter in join statement?
Is the SQL internal optimizer smart enough to reorganize the order to use whatever is more efficient order?
When doing performance tuning, Oracle catches waits and use it as an import factor to analyze performance, is SQL server the same - using wait time and types as important identifier for performance?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

ANn -425914 (2/26/2015)
For SQL performance tuning for a query, does the order of columns in where filter clause matters?

No, column order is irrelevant.

Also does order of columns matter in join statement?

Same as previous answer.

Is the SQL internal optimizer smart enough to reorganize the order to use whatever is more efficient order?

Yes, that's the magic of declarative languages such as SQL.

When doing performance tuning, Oracle catches waits and use it as an import factor to analyze performance, is SQL server the same - using wait time and types as important identifier for performance?
Thanks

Yes, using wait time and types is part of performance tuning. You can find a complete explanation here: http://www.sqlservercentral.com/articles/books/76296/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 2

ANn -425914 (2/26/2015)
For SQL performance tuning for a query, does the order of columns in where filter clause matters?

In most cases, no. But, that's because the optimizer will arrange and rearrange the order as needed. In some edge cases, especially if the optimizer is timing out, you can actually affect the plan outcome by changing the order yourself, putting more restrictive commands first. But, this is an extremely, hyper-rare event.

Also does order of columns matter in join statement?
Is the SQL internal optimizer smart enough to reorganize the order to use whatever is more efficient order?

Same thing as above. In most cases, it doesn't matter at all.

When doing performance tuning, Oracle catches waits and use it as an import factor to analyze performance, is SQL server the same - using wait time and types as important identifier for performance?
Thanks

The basics are found in the DMO sys.dm_os_wait_statistics. You can get really fancy by capturing query metrics and wait metrics using Extended Events to see the exact order and duration of each wait and associate it with a given query execution.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ANn -425914 Right there with Babe Points: 733 More actions · Answer 3

I forgot to ask:

for indexes, does the order of column matters?

For example I want to create an index of ID, SchoolID, on terms table.

Does the order of the column matter in the index?

Thanks,

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 4

ANn -425914 (2/27/2015)
I forgot to ask:
for indexes, does the order of column matters?
For example I want to create an index of ID, SchoolID, on terms table.
Does the order of the column matter in the index?
Thanks,

Absolutely. In your example above, the best you could get out of such an index when looking for SchoolID would be an index scan and only if you're lucky enough for SQL Server to realize that the narrow NCI would be quicker to scan than doing a clustered index scan. The column order of (especially the first column) of any index is uber important.

--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)

ANn -425914 Right there with Babe Points: 733 More actions · Answer 5

Thanks, a little not clear,

For our case I first created an index on terms table by using ID, SchoolID,

Note the ID is not a clustered index, there is another prirmay key column called DCID. ID is not even an index. (This is vendors database.)

But a lot of functions using below join, schoolID first, then terms.ID second

....

Inner Join Terms t On t.schoolid = 0 --

And t.id = get_current_school_year

So I ended up to change the order for the index to SchoolID + ID.

For my case, the program runs almost the same time comparing with the two different orders, but I am just interested if this is always true that order makes little difference

And do you think which order is better for our case.

Thanks

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 6

ANn -425914 (2/27/2015)
Thanks, a little not clear,
For our case I first created an index on terms table by using ID, SchoolID,
Note the ID is not a clustered index, there is another prirmay key column called DCID. ID is not even an index. (This is vendors database.)
But a lot of functions using below join, schoolID first, then terms.ID second
....
Inner Join Terms t On t.schoolid = 0 --
And t.id = get_current_school_year
So I ended up to change the order for the index to SchoolID + ID.
For my case, the program runs almost the same time comparing with the two different orders, but I am just interested if this is always true that order makes little difference
And do you think which order is better for our case.
Thanks

I would put it back the way it was because ID is more selective than SchoolID and you are using both columns in the criteria of the query you've given.

--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)

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 7

ANn -425914 (2/27/2015)
I forgot to ask:
for indexes, does the order of column matters?
For example I want to create an index of ID, SchoolID, on terms table.
Does the order of the column matter in the index?
Thanks,

Yes, primarily because the historgram of the statistics is only created on the first column. So, generally, but not always, you want the most restrictive column to be first because it will give you the best data distribution within the histogram.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ANn -425914 Right there with Babe Points: 733 More actions · Answer 8

Thank you all.

When you say most restrictive what does it mean, is it regarding the number of the rows it results?

In our case, using the following join:

Inner Join Terms t On t.schoolid = 0 And t.id = get_current_school_year()

The first join by schoolID will return much fewer records than the second one ID column.

So it means the schoolID is more restrictive, then we should put schoolID first , ID second.

It is different than what Jeff says.

Thanks,

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

ANn -425914 (3/1/2015)
Thank you all.
When you say most restrictive what does it mean, is it regarding the number of the rows it results?
In our case, using the following join:
Inner Join Terms t On t.schoolid = 0 And t.id = get_current_school_year()
The first join by schoolID will return much fewer records than the second one ID column.
So it means the schoolID is more restrictive, then we should put schoolID first , ID second.
It is different than what Jeff says.
Thanks,

Think "deck of playing cards". Saying "Ace of Diamonds" is much more restrictive than saying a card in the suit of diamonds.

As to this...

The first join by schoolID will return much fewer records than the second one ID column.

... and this...

Inner Join Terms t On t.schoolid = 0 --
And t.id = get_current_school_year

... you are correct if "t.id" represents a span of time. But, therein lies another problem that someone like Joe Celko would have great fun at your expense. By itself, "ID" is one of the worst names you could give a column for something of this nature. It's totally non-descript even in the presence of the table name.

--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)

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 10

Pretty much what Jeff says. Actually, that's just a good rule to live by.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning