1

Topic: sql server 2008 r2 Request

Good afternoon
Whether it is possible to accelerate request of a type somehow
select COUNT (*) from table1 t1
where exists
(
select * from table2 t2
where (t2.dep = t1.dep or t2.dep = ")
and (t2.acc = t1.acc or t2.acc = ")
)
If to make request more low all is fulfilled quickly, but one of fields (t2.dep or t2.acc) can be empty, i.e. from the first table all data, if in table 2 this field empty is necessary. The request above fulfills in 2 times longer.
select COUNT (*) from table1 t1
where exists
(
select * from table2 t2
where t2.dep = t1.dep
and t2.acc = t1.acc
)

2

Re: sql server 2008 r2 Request

PetrIII;
To alter exists on join

3

Re: sql server 2008 r2 Request

PetrIII wrote:

Good afternoon
Whether it is possible to accelerate request of a type somehow
select COUNT (*) from table1 t1
where exists
(
select * from table2 t2
where (t2.dep = t1.dep or t2.dep = ")
and (t2.acc = t1.acc or t2.acc = ")
)
If to make request more low all is fulfilled quickly, but one of fields (t2.dep or t2.acc) can be empty, i.e. from the first table all data, if in table 2 this field empty is necessary. The request above fulfills in 2 times longer.
select COUNT (*) from table1 t1
where exists
(
select * from table2 t2
where t2.dep = t1.dep
and t2.acc = t1.acc
)

It is necessary to look the plan and to build indexes. But, at first to rewrite normal request with usage JOIN. There is a suspicion that the form of record resulted by the author forcedly imposes LOOP JOIN.

4

Re: sql server 2008 r2 Request

Was through join, altered on exists, became faster

5

Re: sql server 2008 r2 Request

In the plan key search (clustered) on table1 in the first variant of 36 %, in the second variant almost in 2 times is more

6

Re: sql server 2008 r2 Request

PetrIII wrote:

in the plan key search (clustered) on table1 in the first variant of 36 %, in the second variant almost in 2 times is more

No means always percent are shown correctly. Value also has, how many time this search becomes. If millions times, here and brakes. The index on dep and acc under both tables in this case can help. Will be super, if it unique in both cases. There is a chance on Merge join. And logical reads will be repeatedly less if initially in tables it is a lot of big fields.

7

Re: sql server 2008 r2 Request

  - Eh wrote:

PetrIII;
To alter exists on join

I on the contrary would alter

8

Re: sql server 2008 r2 Request

It is possible in EXISTS to alter a subquery in UNION ALL from 4 SELECT for possible combinations of values dep and acc.
To get rid of rattling compound AND and OR (the horror as it does not love the server, but UNION eats with pleasure).

9

Re: sql server 2008 r2 Request

Yes, tried too through unions, but there it is a lot of possible conditions, as a result it appeared more slowly.
But after here such variant
select count (*) from table1 t1
join table2 t2 on t2.field = t1.field
where (t1.field1 = t2.field1 or t2.field1 = ")
and (t1.field2 = t2.field2 or t2.field2 = ")
and (t1.field3 = t2.field3 or t2.field3 = "
And  indexes on t1 and t2 on binding fields the request began to fulfill second on 1.7 million records
I do not know, whether it is possible to accelerate still

10

Re: sql server 2008 r2 Request

PetrIII wrote:

yes, tried too through unions, but there it is a lot of possible conditions, as a result it appeared more slowly.
But after here such variant
select count (*) from table1 t1
join table2 t2 on t2.field = t1.field
where (t1.field1 = t2.field1 or t2.field1 = ")
and (t1.field2 = t2.field2 or t2.field2 = ")
and (t1.field3 = t2.field3 or t2.field3 = "
And  indexes on t1 and t2 on binding fields the request began to fulfill second on 1.7 million records
I do not know, whether it is possible to accelerate still

Certainly it is possible.
As those it is explicit  on "that under request" - simply delete request absolutely.
Faster - does not happen.
. The last request is nonequivalent to request from the first post.  in any way.

11

Re: sql server 2008 r2 Request

Well at you one excuse on all posts - to delete request))
And here to offer that-thread efficient figs there, only ...  in comments we can, yes?)

12

Re: sql server 2008 r2 Request

PetrIII wrote:

yes, tried too through unions, but there it is a lot of possible conditions, as a result it appeared more slowly.
But after here such variant
select count (*) from table1 t1
join table2 t2 on t2.field = t1.field
where (t1.field1 = t2.field1 or t2.field1 = ")
and (t1.field2 = t2.field2 or t2.field2 = ")
and (t1.field3 = t2.field3 or t2.field3 = "
And  indexes on t1 and t2 on binding fields the request began to fulfill second on 1.7 million records
I do not know, whether it is possible to accelerate still

Here, already well. It is necessary to look the ACTUAL plan further. In an ideal should be  two indexes and merge join. Uniqueness of an index on t1.field, t1.field1, t1.field2, t1.field3 is important. It is unique? If is not present, merge join falls down in spooling and there will be a big overhead projector on I/O. Probably, the sense in merge join and the total will not be, and in the plan will be loop join with all that it implies.
However, here it or t2.field = "most likely does not allow to use effectively an index on t2 and to achieve merge join it does not turn out.

13

Re: sql server 2008 r2 Request

V. Liseev;

wrote:

uniqueness of an index on t1.field, t1.field1, t1.field2, t1.field3 Is important. It is unique? If is not present, merge join falls down in spooling and there will be a big overhead projector on I/O.

It generally whence such undertook? merge and uniqueness...

wrote:

it is possible, the sense in merge join and the total will not be, and in the plan will be loop join with all that it implies.

Well also it will not be possible in respect of sense in loop and will be hash, all variants sorted out?

14

Re: sql server 2008 r2 Request

TaPaK wrote:

it is passed...
It generally whence such undertook? merge and uniqueness...

http://www.t-sql.ru/post/JoinHints.aspx
[quote =] MERGE JOIN "one-to-many" it will be always more effective than merge "many to many" as for it the temporary table is not required. For this purpose, what to involve merge "one-to-many", the optimizer should have possibility to define that one of input flows consists of unique lines. As a rule, it means that such input flow has a unique index or in respect of request there is evidently an operator (for example, sorting at DISTINCT or grouping) which guarantees that lines on an input will be unique.

TaPaK wrote:

it is passed...
Well also it will not be possible in respect of sense in loop and will be hash, all variants sorted out?

Can and will be. It is necessary to look the plan, a record count in both tables, the size of tables in mbytes, data types in fields, on which index, and to statistican.