Isso é para o SQL Server 2012.
Temos alguns processos de importação de arquivos FTP que são coletados e lidos em uma tabela de staging, a partir daí nós massageamos/verificamos os dados antes de passar para a produção. Uma das áreas que está causando alguns problemas são as datas, algumas são válidas, algumas são erros de digitação, outras são simplesmente sem sentido.
Eu tenho as seguintes tabelas de exemplo:
Create Table RawData
(
InsertID int not null,
MangledDateTime1 varchar(10) null,
MangledDateTime2 varchar(10) null,
MangledDateTime3 varchar(10) null
)
Eu também tenho uma tabela de destino (digamos em produção)
Create Table FinalData
(
PrimaryKeyID int not null, -- PK constraint here, ident
ForeighKeyID int not null, -- points to InsertID of RawData
ValidDateTime1 SmallDateTime null,
ValidDateTime2 SmallDateTime null,
ValidDateTime3 SmallDateTime null
)
Eu insiro o seguinte na tabela RawData:
Insert Into RawData(InsertID, MangledDateTime1, MangledDateTime2, MangledDateTime3)
Values(1, '20001010', '20800630', '00000000') -- First is legit, second two are not
Insert Into RawData(InsertID, MangledDateTime1, MangledDateTime2, MangledDateTime3)
Values(1, '20800630', '20130630', '20000000') -- middle is legit, first/third are not
Insert Into RawData(InsertID, MangledDateTime1, MangledDateTime2, MangledDateTime3)
Values(1, '00001010', '00800630', '20130630') -- Last is legit, first two are not
Eu escrevi uma função dbo.CreateDatepara resolver o (s) problema (s). Tentamos limpar os dados da melhor maneira possível (use NULLse não pudermos) e, em seguida, convertemos os dados no tipo de dados correto (neste caso, smalldatetime).
Insert Into FinalData(ForeighKeyID , ValidDateTime1, ValidDateTime2, ValidDateTime3)
Select
InsertID
,dbo.CreateDate(MangledDateTime1)
,dbo.CreateDate(MangledDateTime2)
,dbo.CreateDate(MangledDateTime3)
From RawData
Estamos enfrentando alguns problemas de desempenho com as funções. Eu estou querendo saber se/como eles funcionam em paralelo.
Estou assumindo aqui que a função CreateDateestá sendo executada em paralelo à medida que cada linha é inserida. De forma que cada coluna/valor tenha sua função "própria" e seja executada ao mesmo tempo em que insere.
Mas posso estar errado, está executando serial em cada coluna em cada linha à medida que insere?
Código CreateDate():
Alter Function dbo.CreateDate
(
@UnformattedString varchar(12)
)
Returns smalldatetime
As
Begin
Declare @FormattedDate smalldatetime
If(@UnformattedString Is Not Null)
Begin
Declare @MaxSmallDate varchar(8) = '20790606'
-- We got gibberish
If Len(@UnformattedString) = 1
Begin
return null
End
-- To account for date and time
If Len(@UnformattedString) = 12
Begin
Select @UnformattedString = Substring(@UnformattedString, 0,9)
End
If @UnformattedString = '20000000'
Begin
Select @UnformattedSTring = @MaxSmallDate
End
-- Some people are sending us two digit years, won't parse right
If Substring(@UnformattedString,0,3) = '00'
Begin
Select @UnformattedString = Replace(@UnformattedString, '00','20')
End
-- Some people are fat fingering in people born in 18??, so change to 19??
If Substring(@UnformattedString,0,3) in ('18')
Begin
-- We only want to change the year '18', not day 18
SELECT @UnformattedString = STUFF(@UnformattedString,
CHARINDEX('18', @UnformattedString), 2, '19')
End
-- We're getting gibberish
If Substring(@UnformattedString,0,3) not in ('19','20')
And Len(@UnformattedString) != 6
Begin
Select @UnformattedString = Replace(@UnformattedString,
Substring(@UnformattedString,0,3),'20')
End
-- If the 4 digit year is greater than current year, set to max date
If Convert(int, Substring(@UnformattedString,0,5)) > Year(getdate())
Begin
Set @FormattedDate = CONVERT(smalldatetime,@MaxSmallDate,1)
End
-- If the 4 digit year is less than 100 years ago, set to max date
Else If Year(getdate()) - Convert(int, Substring(@UnformattedString,0,5)) >= 100
Begin
Set @FormattedDate = CONVERT(smalldatetime,@MaxSmallDate,1)
End
Else -- valid date(we hope)
Begin
Set @FormattedDate = CONVERT(smalldatetime,@UnformattedString,1)
End
End
Return @FormattedDate
End
Go
O uso de funções escalares do T-SQL frequentemente leva a problemas de desempenho* porque o SQL Server faz uma chamada de função separada (usando um contexto T-SQL totalmente novo) para cada linha. Além disso, a execução paralela não é permitida para toda a consulta .
As funções escalares do T-SQL também podem dificultar a solução de problemas de desempenho (sejam esses problemas causados pela função ou não). A função aparece como uma 'caixa preta' para o otimizador de consulta : é atribuído a ela um custo fixo baixo estimado, independentemente do conteúdo real da função.
Veja isto e isto para saber mais sobre as armadilhas das funções escalares.
Você provavelmente ficará melhor usando a nova função TRY_CONVERT no SQL Server 2012:
Após a edição da pergunta
Vejo que a função contém alguma lógica específica. Você ainda pode usar
TRY_CONVERTcomo parte disso, mas definitivamente deve converter a função escalar em uma função in-line. As funções in-line (RETURNS TABLE) usam uma únicaSELECTinstrução e são expandidas na consulta de chamada e totalmente otimizadas da mesma forma que as exibições. Pode ser útil pensar em funções in-line como exibições parametrizadas.Por exemplo, uma tradução aproximada da função escalar para uma versão in-line é:
A função usada nos dados de amostra:
Resultado:
*As funções escalares CLR têm um caminho de invocação muito mais rápido do que as funções escalares T-SQL e não impedem o paralelismo.