1

Topic: The index will not be involved - I ask to help with reason search

Good afternoon!
Already very much for a long time did not deal with a DBMS, probably forgot any basic thing, I ask to prompt.
There is a request of such type:
select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt//the table approximately on 3-4 million records with clustered index on id
inner join #SmallTable flt//the temporary table approximately on 10-20  records,
on flt.id = bt.id_contract//the field id_contract tables bt - is indexed
inner join BigTable_Child bt_child//the table approximately on 300 million records with clustered index on id
on bt_child.id_subcontract = bt.id//the field id_subcontract tables bt - is indexed
where bt_child.stringfield in (' string1 ', ' string2 ')//the field stringfield - is not indexed.
The request is fulfilled long (tens minutes).
The request plan shows that that the index across the field bt_child.id_subcontract is not involved, instead of it clustered index BigTable_Child.id is used, and this operation gives 95 % of cost request. Recommends index creation across the field bt_child.stringfield.
But it it would not would be desirable to do, as for the table insertion time is critical.
I thought that an index across the field bt_child.id_subcontract will be enough. What did I do not consider? In what there can be a problem?

2

Re: The index will not be involved - I ask to help with reason search

I am sorry - forgot a tag. I am corrected.

select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt//the table approximately on 3-4 million records with clustered index on id
inner join #SmallTable flt//the temporary table approximately on 10-20  records, 
on flt.id = bt.id_contract//the field id_contract tables bt - is indexed
inner join BigTable_Child bt_child//the table approximately on 300 million records with clustered index on id
on bt_child.id_subcontract = bt.id//the field id_subcontract tables bt - is indexed
where bt_child.stringfield in (' string1 ', ' string2 ')//the field stringfield - is not indexed.

3

Re: The index will not be involved - I ask to help with reason search

Kateryne;
Correctly the server made all.... At you no conditions of a filtration on index fields are present. Accordingly, to it all should be sorted out one all the line long. It is logical that it began with clustered index reading on the biggest tables....

4

Re: The index will not be involved - I ask to help with reason search

Kateryne;
Also remaining adherents did not come yet...
Statistics under tables-indexes - is actual?
Indexes not  in rags-shreds?

5

Re: The index will not be involved - I ask to help with reason search

Kateryne;
select a, s, c
from table1
join table2 on table1.id = table2.id with (index (indexname), nolock)

6

Re: The index will not be involved - I ask to help with reason search

  - Eh wrote:

Kateryne;
Correctly the server made all.... At you no conditions of a filtration on index fields are present. Accordingly, to it all should be sorted out one all the line long. It is logical that it began with clustered index reading on the biggest tables....

Thanks for the answer! But not absolutely understood - at me actually and there was a question why the optimizer in this case does not want to use at first a connection condition in sampling on an indexed field (it would give 10-20  lines), and then on them already to filter on not indexed?
I correctly remember, what at inner join the order - that will be earlier - where or join - selects the optimizer? Or I am mistaken?
Statistics is actual, the index across the field id_subcontract is created from zero so on idea should not be flattened out on a disk. But - generally somewhere in this direction the problem can be, as the data in this big table on 300 million  from zero from other same big table with almost same structure.

7

Re: The index will not be involved - I ask to help with reason search

, passing by wrote:

Kateryne;
select a, s, c
from table1
join table2 on table1.id = table2.id with (index (indexname), nolock)

Thanks! It is possible and so try, we make tomorrow the test, but it would be desirable to understand an original cause without similar cunnings...

8

Re: The index will not be involved - I ask to help with reason search

Kateryne wrote:

it is passed...
Thanks! It is possible and so try, we make tomorrow the test, but it would be desirable to understand an original cause without similar cunnings...

Receive one more
In the theory if  want nothing OPTION (FORCE ORDER) can help

9

Re: The index will not be involved - I ask to help with reason search

Kateryne;
You did not show the request plan how it is possible to speak something was more specific? (And if you will show it is better not a picture as it is done by some, and full xml enclose to the message)
But as a method "bad shot" I will assume - probably, the server is mistaken in an estimation of cardinality of sets
To unwinding I will not recall, therefore it is necessary to you to esteem - as estimates the server an amount of lines in the temporary table... Probably, he does not understand that at you there 10-20 . Lines, instead of one or not one million....

10

Re: The index will not be involved - I ask to help with reason search

Kateryne wrote:

on bt_child.id_subcontract = bt.id//the field id_subcontract tables bt - is indexed

confuse Nothing? Can all the same tables bt_child?
Index make on bt_child as (id_subcontract) include (value1, value2)
If it is simple on id_subcontract, it is required Key Lookup to get value1, value2 from the table. And it is expensive operation.
To be convinced, can compare final cost of request in estimate plans for

select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt//the table approximately on 3-4 million records with clustered index on id
inner join #SmallTable flt//the temporary table approximately on 10-20  records, 
on flt.id = bt.id_contract//the field id_contract tables bt - is indexed
inner join BigTable_Child bt_child//the table approximately on 300 million records with clustered index on id
on bt_child.id_subcontract = bt.id//the field id_subcontract tables bt - is indexed
where bt_child.stringfield in (' string1 ', ' string2 ')//the field stringfield - is not indexed.

and

select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt//the table approximately on 3-4 million records with clustered index on id
inner join #SmallTable flt//the temporary table approximately on 10-20  records, 
on flt.id = bt.id_contract//the field id_contract tables bt - is indexed
inner loop join BigTable_Child bt_child with (index =...)//the table approximately on 300 million records with clustered index on id
on bt_child.id_subcontract = bt.id//the field id_subcontract tables bt - is indexed
where bt_child.stringfield in (' string1 ', ' string2 ')//the field stringfield - is not indexed.

11

Re: The index will not be involved - I ask to help with reason search

invm wrote:

confuse Nothing? Can all the same tables bt_child?

Yes, it is finite - it was sealed up.
Then all thanks big for councils, tomorrow I will try - now access is not present.