January 12, 2016 at 5:44 pm
Hi Guys,
I have been tuning this query for a while and no improvement so far .
From my database performance monitor it says this query has a plan that indicate a FULL TABLE SCAN .
SELECT A.F_Name, A.LName , A.Data , A.Email, A. UserName, A.type, A.Zip, A.A_Id , A.Create_Date, A. Create_User, A.Edit_User, A.Edit_Date, (cast(A.Rn_Edit_Date as float) + 2) Rn_Float_Edit_Date
FROM A
WITH (READCOMMITTED)
WHERE (1 = 1
OR exists
(SELECT * FROM AB
WHERE AB.Customer_A_Id = A.A_Id AND (AB.Number LIKE @T AND 1=1)))
What I did :
-Add Index on A. A_Id as key column ( Non Clustered Index )
-Add Index on AB.Customer_A_Id as key column with include columns : A.A_Id, A.Create_Date, A. Create_User, A.Edit_User, A.Edit_Date
-Add Index on AB.number as key column with include columns : A.A_Id, A.Create_Date, A. Create_User, A.Edit_User, A.Edit_Date
Table A has 58226 rows
The execution plan is as below :
Select – Cost 0%
Compute Scalar – Cost 0%
Clustered Index Scan
A.A_Id - Cost 100%
Basically I have added the indexes on some columns in WHERE area
But the execution plan still show FULL SCAN
I update the statistics on the first index which is A_Id but no impact at all. I also change LIKE to “=” but no impact as well .
Any feedback are much much appreciated
Cheers
January 12, 2016 at 8:16 pm
There are no effective predicates in this query, it will return all rows and the most effective way for the server to do that is a table scan.
😎
January 12, 2016 at 8:26 pm
What's the query hint doing?
"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
January 12, 2016 at 8:51 pm
Grant Fritchey (1/12/2016)
What's the query hint doing?
😎
WITH (READUNCOMMITTED)???
January 12, 2016 at 9:10 pm
Thanks for the feedback .
What do mean by Query hint in this case ?
I assume you are referring to “OPTIMIZE FOR “ (Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.)
If my assumption is correct then the problem is I don’t know what is the best value to increase the query performance
What do you think ?
January 12, 2016 at 9:22 pm
WhiteLotus (1/12/2016)
Thanks for the feedback .What do mean by Query hint in this case ?
The query hint is the READCOMMITED, why are you using it? Is it because you are using a different isolation level?
😎
Question, can you describe what the query is supposed to do? As it is it will always return all rows from the [A] table so why not just leave it to the select statement and skip the ineffective / redundant where clause? The only tuning option here is a covering index for the query's output, depends on the width of the table though.
January 13, 2016 at 1:59 am
The execution plan you describe is optimal for this query, you will not be able to speed it up.
For future maintenance, you can rewrite the query though. To this:
SELECT A.F_Name, A.LName , A.Data , A.Email, A. UserName, A.type, A.Zip, A.A_Id , A.Create_Date, A. Create_User, A.Edit_User, A.Edit_Date, cast(A.Rn_Edit_Date as float) + 2 AS Rn_Float_Edit_Date
FROM A;
January 13, 2016 at 5:25 am
Eirikur Eiriksson (1/12/2016)
Grant Fritchey (1/12/2016)
What's the query hint doing?😎
WITH (READ
UNCOMMITTED)???
I know what it does. I just don't understand why it's included.
"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
January 13, 2016 at 5:54 am
Wait, wait, wait, what????
WHERE (1 = 1 OR EXISTS (SELECT * FROM AB WHERE AB.Customer_A_Id = A.A_Id AND (AB.Number LIKE @T AND 1=1)))
1=1 is always TRUE.
TRUE OR <anything> is TRUE. Hence that WHERE clause will always evaluate to TRUE, no matter what the EXISTS does.
Hence that query is essentially, as Hugo already observed
SELECT A.F_Name, A.LName ,
A.Data , A.Email, A. UserName, A.type, A.Zip, A.A_Id , A.Create_Date,
A. Create_User, A.Edit_User, A.Edit_Date,
(cast(A.Rn_Edit_Date as float) + 2) Rn_Float_Edit_Date
FROM A
That will always execute with a single table scan of A, because there's no useful predicate that applies to the rows. It returns all rows in the table A.
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
January 13, 2016 at 3:55 pm
Hi guys
You are right ! Where criteria is pointless haha
It’s not me who write this . I guess someone who worked as a constructor wrote it and gone now .
Thanks so much for the feedback !!
January 31, 2016 at 4:21 pm
Hi ,
Out of curiosity if we only run this query :
SELECT A.F_Name, A.LName ,
A.Data , A.Email, A. UserName, A.type, A.Zip, A.A_Id , A.Create_Date,
A. Create_User, A.Edit_User, A.Edit_Date,
(cast(A.Rn_Edit_Date as float) + 2) Rn_Float_Edit_Date
FROM A
The duration is the same with that pointless statement (as discussed)
My question is : Can we still optimize this query ?
Thanks
January 31, 2016 at 7:44 pm
If it runs in a single table scan, then what do you hope to optimize? Since you're reading the entire table because you effective don't have a WHERE clause, a scan is going to be the fastest execution you're going to get.
You could add a covering index, but that's only going to be useful if you have other columns in the table that aren't being returned. It would mean that the engine could go to the index instead of the table, but would be a waste if you don't have extra columns.
February 1, 2016 at 2:27 am
WhiteLotus (1/31/2016)
My question is : Can we still optimize this query ?
There's nothing there to optimise. It's a read of an entire table. There's no predicates that can be used with indexes, no joins to tweak. It reads the entire table.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply