July 14, 2014 at 2:35 pm
I have this query that I am trying to tune(Just to practice)
SELECT TOP 7401 T270.C1,T270.C18133,T270.C18005,convert(nvarchar(15),B270.CO18004)+N';'+convert(nvarchar(15),B270.CC18004)+N';'+B270.C18004 B270C18004 FROM T270 LEFT OUTER JOIN B270 ON (T270.C1 = B270.C1) WHERE (T270.C18133 LIKE N'%PeopleResources.jpg') ORDER BY 1 ASC
Table ' B270' -Logical reads 576
Table ' T270' -Logical reads 750
CPU TIME:156 ms elapsed times: 288 ms
I ended up removing % sign in a WHERE clause and this what I got:
Table ' B270' -Logical reads 576
Table ' T270' -Logical reads 82
CPU TIME:15 ms elapsed times: 143 ms
I have tried adding indexes on T.270 and B270 but it didn't improve anything.
Question:
What is the best approach to tune this query?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 14, 2014 at 2:43 pm
Include the Actual Execution Plan for the query and find the most costly node? Did you do that for the query?
We don't know what indexes you have added, and what you already have, and as well the tables and indexes definitions...
Igor Micev,My blog: www.igormicev.com
July 14, 2014 at 2:55 pm
Read this to get better answers for performance questions.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 14, 2014 at 2:59 pm
Igor Micev (7/14/2014)
Include the Actual Execution Plan for the query and find the most costly node? Did you do that for the query?We don't know what indexes you have added, and what you already have, and as well the tables and indexes definitions...
Please see the attachment.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 14, 2014 at 3:13 pm
You need to realize that excluding the wildcard (%) will generate different results. It doesn't matter if the query runs 1000x faster if the results aren't correct.
The leading wildcard will affect performance, but it's sometimes necessary. If you'll always look for the same string, you could make a persisted calculated column with an index on it. However, I'm not sure that's an option and the string might vary.
The query is really simple, but has 2 basic problems: the leading wildcard and the order by. Both seem necessary to return the correct results, but I might be wrong.
July 14, 2014 at 3:35 pm
Luis Cazares (7/14/2014)
You need to realize that excluding the wildcard (%) will generate different results. It doesn't matter if the query runs 1000x faster if the results aren't correct.The leading wildcard will affect performance, but it's sometimes necessary. If you'll always look for the same string, you could make a persisted calculated column with an index on it. However, I'm not sure that's an option and the string might vary.
The query is really simple, but has 2 basic problems: the leading wildcard and the order by. Both seem necessary to return the correct results, but I might be wrong.
I do understand that excluding the % will probably give me the different result but this is not the case in my situation. I can leave it or remove it (%), I get the same result, same number of rows I am expecting and that's why I ended up removing it.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 15, 2014 at 3:09 am
New Born DBA (7/14/2014)
Igor Micev (7/14/2014)
Include the Actual Execution Plan for the query and find the most costly node? Did you do that for the query?We don't know what indexes you have added, and what you already have, and as well the tables and indexes definitions...
Please see the attachment.
It seems that you have a clustered index on B270.C1. Do you? you didn't post the indexes and tables definitions.
You could probably reduce the logical reads by using non-clustered index on B270.C1 including only the required columns in the query.
CPU TIME:15 ms elapsed times: 143 ms is pretty fast execution, however it's possible to improve.
Igor Micev,My blog: www.igormicev.com
July 15, 2014 at 9:02 am
It seems that you have a clustered index on B270.C1. Do you? you didn't post the indexes and tables definitions.
You could probably reduce the logical reads by using non-clustered index on B270.C1 including only the required columns in the query.
CPU TIME:15 ms elapsed times: 143 ms is pretty fast execution, however it's possible to improve.
I have actually tried adding a nonclustered index on B270.C1 including other columns but it didn't improve anything.
As long as you think that CPU TIME:15 ms elapsed times: 143 ms is pretty fast execution, then I am ok with that. I was just trying to make it a little better by reducing the number of logical reads.
Thanks for your help.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 15, 2014 at 9:43 am
New Born DBA (7/15/2014)
It seems that you have a clustered index on B270.C1. Do you? you didn't post the indexes and tables definitions.
You could probably reduce the logical reads by using non-clustered index on B270.C1 including only the required columns in the query.
CPU TIME:15 ms elapsed times: 143 ms is pretty fast execution, however it's possible to improve.
I have actually tried adding a nonclustered index on B270.C1 including other columns but it didn't improve anything.
As long as you think that CPU TIME:15 ms elapsed times: 143 ms is pretty fast execution, then I am ok with that. I was just trying to make it a little better by reducing the number of logical reads.
Thanks for your help.
Keep an eye on a good maintenance as well: Statistics update, control of fragmentation, low wait stats and etc...
Igor Micev,My blog: www.igormicev.com
July 15, 2014 at 10:15 am
Keep an eye on a good maintenance as well: Statistics update, control of fragmentation, low wait stats and etc...
How often you think I should update statistics? Do I do it manually or is it something that can be done through maintenance plan?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 15, 2014 at 11:11 am
New Born DBA (7/15/2014)
Keep an eye on a good maintenance as well: Statistics update, control of fragmentation, low wait stats and etc...
How often you think I should update statistics? Do I do it manually or is it something that can be done through maintenance plan?
This is the "complete" solution - http://ola.hallengren.com/
Igor Micev,My blog: www.igormicev.com
July 15, 2014 at 11:15 am
Igor Micev (7/15/2014)
New Born DBA (7/15/2014)
Keep an eye on a good maintenance as well: Statistics update, control of fragmentation, low wait stats and etc...
How often you think I should update statistics? Do I do it manually or is it something that can be done through maintenance plan?
This is the "complete" solution - http://ola.hallengren.com/
It depends how busy is your server. It's usually on daily basis, but you can schedule a different frequency via sql server jobs.
Igor Micev,My blog: www.igormicev.com
July 15, 2014 at 12:57 pm
Here's a few thoughts:
If T270.C18133 might often be less than 19 bytes, add this to the WHERE clause:
WHERE
LEN(T270.C18133) >= 19
Since you don't have an ending %, the LIKE '%string' is functionally equivalent to a RIGHT, so I'd just code it as a RIGHT:
WHERE
RIGHT(T270.C18133, LEN('PeopleResources.jpg')) = 'PeopleResources.jpg'
If this query is worth the expense of the index, you could create a covering index on:
T270 ( C18133, C1, C18005 )
Edit: Yes, SQL will have to scan that index, but that could be much less overhead than scanning the entire table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply