SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution plan shows Number of executes 24


Execution plan shows Number of executes 24

Author
Message
Mathew M.Varghese
Mathew M.Varghese
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 127
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228680 Visits: 46344
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mathew M.Varghese
Mathew M.Varghese
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 127
[b]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
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 860
Mathew M.Varghese (6/8/2012)
[b]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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228680 Visits: 46344
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mathew M.Varghese
Mathew M.Varghese
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 127
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
MVDBA
MVDBA
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5595 Visits: 860
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228680 Visits: 46344
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228680 Visits: 46344
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Mathew M.Varghese
Mathew M.Varghese
SSC-Addicted
SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)SSC-Addicted (433 reputation)

Group: General Forum Members
Points: 433 Visits: 127
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search