What is most important when comparing 2 statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

What is most important when comparing 2 statements

Author

Topic

kudzen
Starting Member

12 Posts

Posted - 2012-05-08 : 05:24:16

Hi there,

I'm trying to understand whats going on in my server and i detected one statement that's is reading a lot of pages to get my results.

My statistics IO says:

(35 row(s) affected)
Table 'TMP_Diario_Tentativas'. Scan count 4, logical reads 224, physical reads 85, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TMP_Vendas_TPSalesControl'. Scan count 4, logical reads 423, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
----> Table 'TMP_Diario_CT'. Scan count 4, logical reads 49464, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(35 row(s) affected)
---->Table 'TMP_Diario_CT'. Scan count 651116, logical reads 2085870, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TMP_Vendas_TPSalesControl'. Scan count 1, logical reads 48411, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TMP_Diario_Tentativas'. Scan count 1, logical reads 212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As you can see, in the second statement for the table TMP_Diario_CT have logical reads 2085870
and in the first one it's just 49464.

When i check the 2 executions plans, it says that the lower page reads has 76% cost of the 2 batchs and the 2 statement only used 24%.

So my question is, should i be concerned about higher pages read or the higher % of the batch?

What statement should i use ????

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-08 : 05:52:44

How much time does first query take?
How much time does second query take?

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-08 : 06:27:26

Why don't you post the queries? We may be able to suggest improvements. In addition the table structure and indexes may not be optimal....

Also:

quote:

physical reads 85

OUCH. What are the queries doing that need to make that many physical reads?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kudzen
Starting Member

12 Posts

Posted - 2012-05-08 : 06:51:20

I leave here the 2 queries that i'm using.

------------------------------------------
--- QUERY 1
------------------------------------------
SELECT dataload, UPPER(agent_login) AS Login, COUNT(*) AS Total_CC
FROM [TMP_Diario_Tentativas]
where data_diario = '2012-05-04' and campanha_id = '70'
and name = '1' AND easycode in (

select ct.easycode from dbo.TMP_Diario_CT ct
join [TMP_Vendas_TPSalesControl] controlada
on ct.tp_code = controlada.easycode
where
data_venda<=[TMP_Diario_Tentativas].data_diario
and ct.campanha_id =[TMP_Diario_Tentativas].campanha_id
group by ct.easycode

) and campanha_id='70' AND (data_diario = '2012-05-04' )

GROUP BY dataload, UPPER(agent_login)
GO
------------------------------------------
--- QUERY 2
------------------------------------------
SELECT dataload, UPPER(agent_login) AS Login, COUNT(*) AS Total_CC
FROM [TMP_Diario_Tentativas]
where data_diario = '2012-05-04' and campanha_id = '70'
and name = '1' AND easycode in (

select ct.easycode from dbo.TMP_Diario_CT ct
join [TMP_Vendas_TPSalesControl] controlada
on ct.tp_code = controlada.easycode and controlada.campanha_id='70' AND (ct.data_diario = '2012-05-04' )
where
data_venda<=[TMP_Diario_Tentativas].data_diario
and ct.campanha_id =[TMP_Diario_Tentativas].campanha_id
group by ct.easycode

)

GROUP BY dataload, UPPER(agent_login)

This is a stupid question, but i wanted to compare the differences of filtering in the join or in the where clause :)

quote:
Originally posted by Transact Charlie

Why don't you post the queries? We may be able to suggest improvements. In addition the table structure and indexes may not be optimal....

Also:
quote:

physical reads 85

OUCH. What are the queries doing that need to make that many physical reads?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources