Início / dba / Perguntas / 276617
Accepted
J.D.
Asked: 2020-10-07 05:27:55 +0800 CST

Os tipos de dados definidos pelo usuário sempre causam a conversão implícita quando os campos desses UDDTs são usados ​​como predicados?

  • 772

Eu tenho um banco de dados (de um aplicativo de fornecedor) que por algum motivo desconhecido tem um UDDT em quase todas as colunas, mesmo quando o UDDT é logicamente o mesmo que o tipo subjacente que ele implementa. Por exemplo, uma coluna neste banco de dados pode ser de UDDT bTinyInt(TinyInt).

Duas tabelas neste banco de dados têm colunas que compartilham exatamente o mesmo UDDT. Por exemplo TableA.Field1, é do tipo bVarchar(VARCHAR(10))e TableB.Field2também é bVarchar(VARCHAR(10)).

Ao unir essas duas tabelas pelos campos mencionados, observo um aviso no plano de execução informando um problema de cardinalidade porque esses campos estão sendo convertidos implicitamente para VARCHAR(MAX).

É sempre o caso de UDDTs serem convertidos implicitamente quando usados ​​como predicados, mesmo ao comparar o mesmo UDDT?

Também não tenho certeza se isso importa também, mas o banco de dados ao qual essas duas tabelas pertencem é de agrupamento Latin1_General_BIN.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Não consigo reproduzir nenhum aviso ou diferença de desempenho neste cenário, mesmo ao ingressar em bVarchararquivos varchar(10). POR EXEMPLO

    create database tt 

alter database tt collate Latin1_General_BIN
use tt 
go
create type bVarchar from varchar(10)

create table a(id int primary key identity, b bVarchar, c varchar(10));
create table b(id int primary key identity, b bVarchar, c varchar(10));

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.messages, sys.objects 
)
insert into a (b,c) 
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;  

with q as
(
  select top 1000000 row_number() over (order by (select null)) i
  from sys.messages, sys.objects 
)
insert into b (b,c) 
select concat(i%10000,'abc'), concat(i%10000,'abc')
from q;  

go


set statistics time on
set statistics io on

select count(*)
from a
join b 
  on a.b = b.b

select count(*)
from a
join b 
  on a.c = b.c

select count(*)
from a
join b 
  on a.b = b.c

set statistics time off
set statistics io off

    saídas

        SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 360 ms, elapsed time = 360 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 3510, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 2656, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 268 ms,  elapsed time = 117 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 184 ms.

(1 row affected)
Table 'a'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'b'. Scan count 9, logical reads 4125, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 280 ms,  elapsed time = 111 ms.

Completion time: 2020-10-17T09:56:35.6734439-05:00
    • 2

  2. Não há problema de desempenho com o UDDTs. Não há mensagem de Aviso quando UDDTsé usado em Predicado.

    Razão :

    select * from sys.columns

    Nesta consulta observe que o otimizador system_type_id& user_type_id.Sql já conhece e usa o tipo de dados do sistema.

    • 1

relate perguntas