SQL with subquery: Unbelievable Reads

Last Post 24 Jan 2008 12:34 AM by Bittela. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Bittela
New Member
New Member

--
23 Jan 2008 05:48 AM
Indexes on:
Table tabKommAktiv:
idx_tabKommAktiv_intBerID

Table tabRightsMErfBer
PK_tabRightsMErfBer
IX_tabRightsMErfBer_intErfBerID
IX_tabRightsMErfBer_intRollenID
IX_tabRightsMErfBer_strTabCode


1. If I execute the following statement I have following reads:

SELECT distinct strSAPAuftrNr
FROM dbo.tabKommAktiv
WHERE (intBerID IN
(SELECT intErfBerID
FROM dbo.tabRightsMErfBer
WHERE (intRollenID = 46471) ))

reads
Table 'tabKommAktiv'. Scan count 1, logical reads 180,…
Table 'tabRightsMErfBer'. Scan count 1, logical reads 2,…

execPlan
|--Hash Match(Aggregate, HASH[UK_KRS].[dbo].[tabKommAktiv].[strSAPAuftrNr]), RESIDUAL[UK_KRS].[dbo].[tabKommAktiv].[strSAPAuftrNr] = [UK_KRS].[dbo].[tabKommAktiv].[strSAPAuftrNr]))
|--Nested Loops(Left Semi Join, OUTER REFERENCES[UK_KRS].[dbo].[tabKommAktiv].[tabBereich]))
|--Index Scan(OBJECT[UK_KRS].[dbo].[tabKommAktiv].[_dta_index_tabKommAktiv_148_2116202589__K7_K1_K6_K25_K4_K5_K27_K28_K24_K2]))
|--Concatenation
|--Filter(WHERESTARTUP EXPR([UK_KRS].[dbo].[tabKommAktiv].[tabBereich] IS NULL)))
| |--Constant Scan
|--Index Seek(OBJECT[UK_KRS].[dbo].[tabRightsMErfBer].[IX_tabRightsMErfBer_intErfBerID]), SEEK[UK_KRS].[dbo].[tabRightsMErfBer].[intErfBerID]=[UK_KRS].[dbo].[tabKommAktiv].[tabBereich] AND [UK_KRS].[dbo].[tabRightsMErfBer].[intRollenID]=(46471)) ORDERED FORWARD)


2. The same Statement on the same DB with additional where-Clause:

SELECT distinct strSAPAuftrNr
FROM dbo.tabKommAktiv
WHERE (intBerID IN
(SELECT intErfBerID
FROM dbo.tabRightsMErfBer
WHERE (intRollenID = 46471) ))
OR (tabBereich IS NULL)

reads
Table 'tabRightsMErfBer'. Scan count 20814, logical reads 41628,…
Table 'tabKommAktiv'. Scan count 1, logical reads 180,…


execPlan
|--Hash Match(Aggregate, HASH[UK_KRS].[dbo].[tabKommAktiv].[strSAPAuftrNr]), RESIDUAL[UK_KRS].[dbo].[tabKommAktiv].[strSAPAuftrNr] = [UK_KRS].[dbo].[tabKommAktiv].[strSAPAuftrNr]))
|--Nested Loops(Left Semi Join, OUTER REFERENCES[UK_KRS].[dbo].[tabKommAktiv].[tabBereich]))
|--Index Scan(OBJECT[UK_KRS].[dbo].[tabKommAktiv].[_dta_index_tabKommAktiv_148_2116202589__K7_K1_K6_K25_K4_K5_K27_K28_K24_K2]))
|--Concatenation
|--Filter(WHERESTARTUP EXPR([UK_KRS].[dbo].[tabKommAktiv].[tabBereich] IS NULL)))
| |--Constant Scan
|--Index Seek(OBJECT[UK_KRS].[dbo].[tabRightsMErfBer].[IX_tabRightsMErfBer_intErfBerID]), SEEK[UK_KRS].[dbo].[tabRightsMErfBer].[intErfBerID]=[UK_KRS].[dbo].[tabKommAktiv].[tabBereich] AND [UK_KRS].[dbo].[tabRightsMErfBer].[intRollenID]=(46471)) ORDERED FORWARD)


Thank you for all tipps
Aldo

Bittela
New Member
New Member

--
24 Jan 2008 12:34 AM
totaly there are 1400 Records in the table; tabBereich is a foreignkey and has an Index and it's only 1 NULL-Record. When I make an Index on tabBereich include (intberID) I have a minimum of reads. I don't understand what the optimizer does.
SwePeso
New Member
New Member

--
04 Feb 2008 03:50 AM
Tried this?

SELECT DISTINCT
a.strSAPAuftrNr
FROM dbo.tabKommAktiv AS a
WHERE EXISTS (SELECT * FROM dbo.tabRightsMErfBer AS b.WHERE b.intRollenID = 46471 AND b.intErfBerID = a.intBerID)
OR a.tabBereich IS NULL
SQLUSA
New Member
New Member

--
06 Feb 2008 12:28 AM
Reindex all your tables.

The query is good, it should not explode like that.

Retest it like this:

dbcc dropcleanbuffers
dbcc freeproccache
select....

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!
You are not authorized to post a reply.

Acceptable Use Policy