Performance paradox with Joins

  • 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

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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