1

Topic: The optimal approach to sampling

There is table A in which there is 1 field "name" varchar (20). In the table 2 lines.
In the table In the same field + what that additional parameters contains.
It is necessary to obtain all data from the table In where a field "name" equally I weed "name" from the table And.
From for the huge size of the table which I write To any request dies, did not wait still any performance.
(Ps - the Table And I can , In possibility is not present)
Variants:
Select * from B inner join A on b.name = a.name
Select * from B where name in (Select name from A)
Select a.name, b.* from A inner join B on a.name = b.name
It is fulfilled nothing, I wait hours.

2

Re: The optimal approach to sampling

In the table In 3 billion lines

3

Re: The optimal approach to sampling

assmsk;
To suffer.  indexed view for B

4

Re: The optimal approach to sampling

And in b what field of type?
Certainly to cut tonsils through... But as a single variant "if very much it is necessary"
1) to result to result varchar (20) in And in type char (20), to hang up an index
2) to create  representation for In with converting name too in char (20) and too to hang up an index
Then

5

Re: The optimal approach to sampling

dao wrote:

and in b what field of type?
Certainly to cut tonsils through... But as a single variant "if very much it is necessary"
1) to result to result varchar (20) in And in type char (20), to hang up an index
2) to create  representation for In with converting name too in char (20) and too to hang up an index
Then

And in char (20) for  pains? Or indexes on varchar ceased to work?

6

Re: The optimal approach to sampling

Told lies smile
In both tables across the field name there is an index.

7

Re: The optimal approach to sampling

assmsk wrote:

It is necessary to obtain all data from the table In where a field "name" equally I weed "name" from table

to try

select... from B where exists (select 1 from A where A.name = B.name)

with the registration

assmsk wrote:

(Ps - the Table And I can , In possibility is not present)

8

Re: The optimal approach to sampling

assmsk wrote:

Told lies smile
In both tables across the field name there is an index.

Script of creation of these indexes

9

Re: The optimal approach to sampling

assmsk;
Anything there does not help, at such volume there will be a review . 2 million  will not twist. At you selectivity .

10

Re: The optimal approach to sampling

The request plan put.

11

Re: The optimal approach to sampling

assmsk wrote:

in the table In 3 billion lines

And physical volume on a disk how many?
And really it is necessary "Select * from B", or it is necessary to apply certain aggregating functions nevertheless?

12

Re: The optimal approach to sampling

And if at you in the table And a field name not unique the amount turned out and request of records can strongly exceed a record count in the table of Century

13

Re: The optimal approach to sampling

TaPaK wrote:

it is passed...
Script of creation of these indexes

In table A - UNIQUE NONCLUSTERED
IN IN - NONCLUSTERED

14

Re: The optimal approach to sampling

Kolosov wrote:

the request Plan put.

15

Re: The optimal approach to sampling

982183 wrote:

it is passed...
And physical volume on a disk how many?
And really it is necessary "Select * from B", or it is necessary to apply certain aggregating functions nevertheless?

26
Really

16

Re: The optimal approach to sampling

Still the moment, these labels on different servers

17

Re: The optimal approach to sampling

assmsk wrote:

Still the moment, these labels on different servers

to select the data from In on that server where it lies beforehand  there a unique dial-up name from And

18

Re: The optimal approach to sampling

assmsk wrote:

it is passed...
26
Really

I will repeat - suffer, architecture =

19

Re: The optimal approach to sampling

All type-top))
Pair of minutes fulfills, the server with the big table laid down it appears

20

Re: The optimal approach to sampling

TaPaK wrote:

it is passed...
And in char (20) for  pains? Or indexes on varchar ceased to work?

it was not necessary to you to compare the big arrays varchar'?))