March 13, 2014 at 6:34 am
Table name with records
Table 'TABLE_ONE' 20000
Table 'TABLE_TWO' 600000
Table 'TABLE_THREE' 5000000
Table 'TABLE_ONE'. Scan count 1, logical reads 62, physical reads 0, read-ahead reads 61.
Table 'TABLE_TWO'. Scan count 1, logical reads 10216, physical reads 2, read-ahead reads 458.
Table 'TABLE_THREE'. Scan count 1, logical reads 200846, physical reads 3, read-ahead reads 64635.
SQL Server Execution Times:
CPU time = 8953 ms, elapsed time = 149358 ms.
SQL Server Execution Times:
CPU time = 8953 ms, elapsed time = 149358 ms.
SQL Server Execution Times:
CPU time = 8953 ms, elapsed time = 149361 ms.
Select Count(*) from Table_ONE a, Table_TWO b,Table_THREE c
WHERE A.Col1=B. Col1
AND C.Col1=B.Col2
and c.col4>0
and c.col5 !=1
and ((c.col6 & 0XOEF) =0)
Cluster index scan on table_three and cost id 89 %
RowCount 5,449,052
Extimated RowCount 95,052
i have placed count(*) instead of my logic.
how to improve the query
logic is taking more time it is having min ,max, avg in a single query
Thanks!
March 13, 2014 at 7:08 am
Please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
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
March 13, 2014 at 7:17 am
I'd love to see the execution plans, but this little critter is going to prevent index use:
and ((c.col6 & 0XOEF) =0)
That's going to lead to scans, all the time every time. As long as you're doing functions on columns, performance is likely to remain slow.
"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
March 13, 2014 at 7:37 am
have attached SETSTATISTICSPROFILE ON
can u peoples help on urgent basis
March 13, 2014 at 7:52 am
Text plans... blech!
Anyway, like I said, you're getting a scan because of the function:
[SCM].[MEAS_VALUE]<>-1 AND [SCM].[COL3]&239=0
The other two scans are not as obvious. For example, this one:
[M].[COL4]>=0 OR [M].[COL4]=-2.01019e+009
It's not at all clear to me where that second comparison is coming from in the OR clause. Is that a view or a table valued function, Table_Two? Because that value 2.019, etc., isn't in the query, so it must be coming from somewhere, but that OR statement is leading to the scan there.
The last scan looks like it's occurring just because the other indexes are scanning and not getting any real filtering, so all rows are getting returned. I'd focus on getting rid of that function and finding out where the OR statement is coming from.
"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
March 13, 2014 at 8:06 am
Yes Col4 is having condition as (Col4>0 OR COL4 =200000000)
March 13, 2014 at 8:10 am
yuvipoy (3/13/2014)
can u peoples help on urgent basis
If you posted what was asked, maybe.
Table definitions please, index definitions and the xml plan would make things a hell of a lot easier.
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
March 13, 2014 at 8:30 am
yuvipoy (3/13/2014)
Yes Col4 is having condition as (Col4>0 OR COL4 =200000000)
Fine, but where is it coming from? It's not a part of the query as outlined, so is that thing that appears to be a table a view or a table valued function? Regardless, I've identified the likely culprits for your performance issues. You must eliminate that function on that WHERE clause because no matter how good your indexes are (and we have no indications of this since you won't address Gail's request) they can't be used because of that function.
"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
March 13, 2014 at 10:21 pm
Hi,GilaMonster
Due business reasons i am unable to give u the entire table definitions
Table_one having 2 uniqueidentifier(one key is generated in the table by using DEFAULT newid()) , 6 decimals,6 real,4 flaot ,8 varchar(50),6 int,4 bit coluimns
Table_two having 3 uniqueidentifier(one key is generated in the table by using DEFAULT newid()) , 4 decimals,3 real,5 flaot ,4 varchar(50),4 int coluimns
Table_three having 2 uniqueidentifier(one key is generated in the table by using DEFAULT newid()) , 2varchar(50),4 int ,6
decimals,2 varchar coluimns
Index
WHERE M.TABLE_TWO_ID (Pkey of table_one)= SCM.TABLE_TWO_ID (Pkey of table_two) AND WR.COL2 = M.COL2
each unique identifier having noncluster index
WR.COL2 is also having nonclustered
M.COL2 is also having nonclustered combined with float data type.
AND (COL4 >= 0 ) AND ((COL3 & 0x0EF) = 0 )
COL4 is float no index
COL3 is int no index
March 13, 2014 at 10:27 pm
Grant Fritchey
Fine, but where is it coming from? It's not a part of the query as outlined, so is that thing that appears to be a table a view or a table valued function?
it is also the part of query it is not a function or view or tvf it is one more condition.
March 14, 2014 at 2:31 am
yuvipoy (3/13/2014)
Due business reasons i am unable to give u the entire table definitions
You're really not helping yourself here. Obfuscate the table, change the column names, do the same with the indexes, do a search and replace through the xml plan.
Trying to figure out what columns have what types and what indexes from your description will take way more time and effort than I'm able to put in. The clearer you make things, the more you provide for people trying to help you, the more likely people are to help you with this 'urgent' problem.
As it is, the query posted doesn't match what the execution plan shows (as Grant mentioned), I can't figure out what data types the various columns involved in the query have, I can't figure out which of the indexes you describe are on which 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
March 14, 2014 at 3:22 am
Yeah, I'm with Gail on this. I spotted the one issue, but I'm pretty sure you have others. But working off this vague set of descriptions the best I can offer is a vague set of answers.
1) That function is killing your performance. You need to get rid of it.
2) Assuming the scans go away because of the function, you may have other scans, possibly due to selectivity and/or statistics being out of date, but based on what you've provided, I just don't know.
"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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply