Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Execution plan shows Number of executes 24 Expand / Collapse
Author
Message
Posted Friday, June 8, 2012 7:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:06 AM
Points: 86, Visits: 123

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
Post #1313112
Posted Friday, June 8, 2012 7:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1313118
Posted Friday, June 8, 2012 8:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:06 AM
Points: 86, Visits: 123
[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
Post #1313168
Posted Friday, June 8, 2012 8:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1313174
Posted Friday, June 8, 2012 8:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1313178
Posted Friday, June 8, 2012 9:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:06 AM
Points: 86, Visits: 123
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
Post #1313245
Posted Friday, June 8, 2012 10:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1313256
Posted Friday, June 8, 2012 10:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1313258
Posted Friday, June 8, 2012 10:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1313270
Posted Friday, June 8, 2012 11:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:06 AM
Points: 86, Visits: 123
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
Post #1313294
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse