June 8, 2012 at 7:12 am
My Sql server version : Microsoft SQL Server 2000 - 8.00.2249 (Intel X86)
If run the following sql statement with the Execution plan , it show no of executive : 24
select tbl1.sys_key, tbl2.sys_key,tbl3.syskey from
tbl1,tbl2,tbl3
where tbl1.sys_key= tbl2.tbl1key
and tbl2.sys_key=tbl3.tbl2key
But if i run following statement, it shows number of executive: 1
select tbl1.sys_key, tbl2.sys_key,tbl3.syskey from
tbl1,tbl2,tbl3
where tbl1.sys_key= tbl2.tbl1key
and tbl2.sys_key=tbl3.tbl2key
and tbl1.sys_key=100
How i can reduce the number of executive of first query?
Please advice
Mathew
June 8, 2012 at 7:29 am
Those are two logically different queries. If I had to guess, I'd say the first returns 24 rows and has a nested loop join (so the inner table gets 'executed' 24 times) while the second query returns one row
Why is the execute count (which is just a measure of how many times an operator in the query runs) a problem? Is this query slow?
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
June 8, 2012 at 8:28 am
Why is the execute count (which is just a measure of how many times an operator in the query runs) a problem? Is this query slow?
Yes the query is slow and it took around 10-12 second to complete the sql statement. This cause lock to another user process and it slow down the entire system.
Following are the details of one object in execution plan.
Physical Operation : Index scan
Logical operation : index scan
Row count : 1,104,299
Est.Row size :33
I/O cost : .932
cpu cost : .0506
Number of executes : 24
cost : 1.905(4%)
SubTree cost : 1.91
estimate row count : 1,104,299
In processor of SQL Server shows : 24.
My processor is : intel xeon x 5650.(Hyper-Threading Technology)
So can i change "max degree of parallelism" to improve the performance?
Please advice
Regards
Mathew
June 8, 2012 at 8:34 am
Mathew M.Varghese (6/8/2012)
Why is the execute count (which is just a measure of how many times an operator in the query runs) a problem? Is this query slow?
Yes the query is slow and it took around 10-12 second to complete the sql statement. This cause lock to another user process and it slow down the entire system.
Following are the details of one object in execution plan.
Physical Operation : Index scan
Logical operation : index scan
Row count : 1,104,299
Est.Row size :33
I/O cost : .932
cpu cost : .0506
Number of executes : 24
cost : 1.905(4%)
SubTree cost : 1.91
estimate row count : 1,104,299
In processor of SQL Server shows : 24.
My processor is : intel xeon x 5650.(Hyper-Threading Technology)
So can i change "max degree of parallelism" to improve the performance?
Please advice
Regards
Mathew
max degree of parallelism has nothign to do with this - as stated, your queries are very different
try re-writing your queries with ANSI-92 syntax (it is after all 20 years old now 😉 )and you will see the difference
select tbl1.sys_key, tbl2.sys_key,tbl3.syskey
from
tbl1
inner join tbl2 on tbl1.sys_key= tbl2.tbl1key
inner join tbl3 on tbl2.sys_key=tbl3.tbl2key
select tbl1.sys_key, tbl2.sys_key,tbl3.syskey
from
tbl1
inner join tbl2 on tbl1.sys_key= tbl2.tbl1key
inner join tbl3 on tbl2.sys_key=tbl3.tbl2key
WHERE
tbl1.sys_key=100
the second query uses only a single value on t1.sys.key, therefore of course it will be do less work
MVDBA
June 8, 2012 at 8:37 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
June 8, 2012 at 9:47 am
Hi,
Please see the attached file,
1. execution plan of slow running query in query in production server ( 36 second)
2. execution plan of same query in query in test server ( 2 second only)
3. table structure of the table (PTS), that cause the issue of slowness ( I think so)
4.Index structure of PTS .
Both test and production are same vision of OS and SQL server
Test is a pC, still it is faster.
Please advice, how i can improve the performance
Regards
Mathew
June 8, 2012 at 10:03 am
your query plans are for the query
SELECT * FROM [PRINT_CASH_POINT] WHERE [CASH_POINT]=@1 AND [DAT1]>=@2 AND [DAT1]<=@3 ORDER BY [DOC_NO]
is this some sort of view on the data?
might i suggest that XML query plans are much more helpfull
MVDBA
June 8, 2012 at 10:10 am
michael vessey (6/8/2012)
might i suggest that XML query plans are much more helpfull
On SQL 2000?
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
June 8, 2012 at 10:30 am
Widen the index [dbo].[CBI].[inx_hc_rep1]. Add Cash_Point
Widen the index [dbo].[TRANS_HEAD].[IX_trans_head_3]. Add Payment_Method, [trans_type] and episode_key
That should help a bit to start. Post back the revised plan.
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
June 8, 2012 at 11:04 am
Hi,
Thanks.. Performance improved by 6 second
Before it was 36, now it took only 30 second
Please see the attached file, new execution plan
Regards
Mathew
June 8, 2012 at 11:22 am
Should have done more than that.... Might not have time til tomorrow to look again, but I'm sure that others here will help in the meantime.
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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply