November 7, 2013 at 5:40 pm
Hi, I have a query like below and I have strange results depending if I inlcude that BX.* columns into Select or not,
table BX joined and I don't touch it.
If I don't include BX.* columns or include only 1 then query runs in 4 sec, but If I include all 5 BX.* columns then it pretty much hanging.. > 10 min.
I tested it multiple times, I don't think it does make any sense, JOIN is plugged in anyway, why bringing that columns causes that huge difference?
I see in Plan huge 78% resource on Index Scan for column Bx.c1 also for both cases, in all cases number of records is same.
BX table is normal table not v_ with a lot of indexes.
select A.c1,
/*
,BX.c1
,BX.c2
,BX.c3
,BX.c4
,BX.c5
/*
C.C1,
C.C2
FROM A
JOIN C ON A.c = C.c
JOIN BX ON BX.ID = A.ID
Where what I can check to understand it.
Tx
Tx much
November 7, 2013 at 6:04 pm
There are a couple of things that can happen here. To be sure you need to provide DDL of the tables and indexes and execution plan.
These are the ideas that come to my mind.
- Amount of data transferred and showed increases.
- Columns are not present in the index.
- Operation to create the join might not be the correct one.
- Bad statistics.
- etc.
November 7, 2013 at 6:18 pm
BX:
PK: Clustered
ID,
LEVEL,
COL_PROC
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
1 Clustered idx on PK of course;
+10 idx, all Non-Unique, Non-Clust
non on my columns is indexed
Row count: 95, BX columns are varchar(20sh)
all BX.Columns I selecting are not part of index or PK.
I have the only option to JOIN on BX.ID = A.ID.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply