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 Times vs Logical Reads/Scan Count Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 11:38 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 175, Visits: 515
GilaMonster (10/30/2013)
Don't look at scan count. It is not the number of times a table has been scanned, it's not consistently the number of times a table has been accessed either.


it means that for example

If I have SCAN 1 and logical reads 100
and scan 2 and logical reads 50 ,

it same or the second stats are better ? and the 50 is last value and not x2 .. yes?
Post #1510024
Posted Thursday, October 31, 2013 1:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
tony28 (10/30/2013)
GilaMonster (10/30/2013)
Don't look at scan count. It is not the number of times a table has been scanned, it's not consistently the number of times a table has been accessed either.


it means that for example

If I have SCAN 1 and logical reads 100
and scan 2 and logical reads 50 ,

it same or the second stats are better ? and the 50 is last value and not x2 .. yes?


Query 1 has 100 logical reads, query 2 has 50 logical reads. Ignore the scan count. If you're just looking at logical reads and nothing else (time, CPU), then query 2 does half the reads that query 1 does.



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 #1510052
Posted Thursday, October 31, 2013 4:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 175, Visits: 515
GilaMonster (10/31/2013)
tony28 (10/30/2013)
GilaMonster (10/30/2013)
Don't look at scan count. It is not the number of times a table has been scanned, it's not consistently the number of times a table has been accessed either.


it means that for example

If I have SCAN 1 and logical reads 100
and scan 2 and logical reads 50 ,

it same or the second stats are better ? and the 50 is last value and not x2 .. yes?


Query 1 has 100 logical reads, query 2 has 50 logical reads. Ignore the scan count. If you're just looking at logical reads and nothing else (time, CPU), then query 2 does half the reads that query 1 does.


OK, for best understanding last question ... it means that the query loaded data to memory and then is just scan inside memory, also it doesnt matter , maybe it can be little more time, but for me is not important, right ?
Post #1510092
Posted Thursday, October 31, 2013 6:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
Logical reads are reads done from memory. Not necessarily scans.

Slower queries isn't important to you? Really?



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 #1510134
Posted Thursday, October 31, 2013 6:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 175, Visits: 515
GilaMonster (10/31/2013)
Logical reads are reads done from memory. Not necessarily scans.

Slower queries isn't important to you? Really?


yes thx ,,..

slower queries are important but i think the CPU time = 78 ms, elapsed time = 79 ms are ok no??
or do you think that is it problem ?

and I read the info about this solution with database snapshot (I will read some example next time ), I am not sure about it, we have lot of procedures or few programs have adhoc queries or i dont have source code from this ... and I am not alone who has access to DB, but I try optimize most running queries ( logical reads and etc... ) .. , it is very dangerous, because we work 24h/5days.

now we have full RAM -- 30GB from 32GB,, but next months we will increase to 64 or 128.. depends on few issue, you know I work in Korea company and buy something is for looooong time... very long time...
Post #1510139
Posted Thursday, October 31, 2013 7:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
tony28 (10/31/2013)
slower queries are important but i think the CPU time = 78 ms, elapsed time = 79 ms are ok no??
or do you think that is it problem ?


If the times are OK, why are you optimising the query in the first place?



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 #1510168
Posted Thursday, October 31, 2013 10:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 175, Visits: 515
GilaMonster (10/31/2013)
tony28 (10/31/2013)
slower queries are important but i think the CPU time = 78 ms, elapsed time = 79 ms are ok no??
or do you think that is it problem ?


If the times are OK, why are you optimising the query in the first place?


logical reads? ... But ok, I made new query, tommorow I will post and I have to test this ... thx for disscussing..
Post #1510318
Posted Thursday, October 31, 2013 11:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 39,905, Visits: 36,245
The logical reads you posted in the OP look quite low.

What about this query made you look at it?



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 #1510340
Posted Thursday, October 31, 2013 12:09 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 175, Visits: 515
GilaMonster (10/31/2013)
The logical reads you posted in the OP look quite low.

What about this query made you look at it?


This procedure is in timer 1second and on 3positions, and when I used select for top procedures order by logical reads, it was there with lot of logical reads...and I think if it can be better, why dont do it.
I can learn more.
Post #1510372
Posted Friday, November 1, 2013 12:19 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:15 PM
Points: 175, Visits: 515
Hello, I think that this one is better, I will see, but I tested on test server and it looks good -- sqlplan in attachment

What do you think about it ?


    SELECT distinct C.ORDER_DATE, C.COMMIT_NO, C.BODY_NO, C.CAR_CODE, "@@PROD_FLAG" FROM (
SELECT * FROM ( SELECT TOP 2 ORDER_DATE, COMMIT_NO, BODY_NO, CAR_CODE, PROD_FLAG "@@PROD_FLAG" FROM TB_WORK_ORDER_MFD01 (NOLOCK)
WHERE ORDER_DATE<=@LAST_ORDER_DATE AND ORDER_DATE + COMMIT_NO <= @LAST_ORDER_DATE + @LAST_COMMIT_NO AND DATA_TYPE <> 'SD'
AND ORDER_DATE+COMMIT_NO NOT IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_REINPUT (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID)
-- AND ORDER_DATE+COMMIT_NO IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_MASTER_MFD01 (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID )
ORDER BY ORDER_DATE DESC, COMMIT_NO desc ) A
UNION ALL
SELECT * FROM (SELECT TOP 5 ORDER_DATE, COMMIT_NO, BODY_NO, CAR_CODE, PROD_FLAG "@@PROD_FLAG" FROM TB_WORK_ORDER_MFD01 (NOLOCK)
WHERE ORDER_DATE>=@LAST_ORDER_DATE AND ORDER_DATE + COMMIT_NO > @LAST_ORDER_DATE + @LAST_COMMIT_NO AND DATA_TYPE <> 'SD'
AND ORDER_DATE+COMMIT_NO NOT IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_REINPUT (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID)
-- AND ORDER_DATE+COMMIT_NO IN (SELECT ORDER_DATE+COMMIT_NO FROM TB_MASTER_MFD01 (NOLOCK) WHERE ORDER_DATE>=@LAST_ORDER_DATE AND STATION_ID=@P_STATION_ID )
ORDER BY ORDER_DATE, COMMIT_NO ) B
)C
LEFT JOIN TB_MASTER_MFD01 M
ON M.ORDER_DATE=C.ORDER_DATE
AND M.COMMIT_NO = C.COMMIT_NO
WHERE M.STATION_ID=@P_STATION_ID
ORDER BY C.ORDER_DATE,C.COMMIT_NO

(6 row(s) affected)
Table 'TB_MASTER_MFD01'. Scan count 7, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_REINPUT'. Scan count 2, logical reads 114, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDER_MFD01'. Scan count 2, logical reads 70, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.


So now I think it looks more better, thx for discussing, after this I found another solution and refresh and learn next/new issue. thx


  Post Attachments 
mfd_NEW.sqlplan (0 views, 167.32 KB)
Post #1510504
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse