Разделение строки с разделителями в таблице файлов

РЕДАКТИРОВАТЬ. Чтобы было ясно, я поставлю целевое предложение вверху. Тест и мой вопрос заключаются в том, есть ли способ получить такую ​​​​же производительность, как у временной таблицы, без использования временной таблицы.

Я чувствую, что это должен быть простой вопрос, но я застрял. Я экспериментирую с FileTables в SQL2014. Я знаю несколько альтернатив, которые будут работать хорошо, но цель состоит в том, чтобы установить возможность извлечения подстрок текста из таблицы файлов.

В этом тесте есть 35 000 текстовых файлов с одной строкой текста, как показано ниже, каждый файл имеет в среднем 100 байтов текста, отличного от Unicode.

Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg

Желаемый результат — одна строка для каждого файла и строка с разделителями, которая должна быть разбита на семь столбцов.

Я нашел функцию быстрого синтаксического анализа строк, но работа с файловым потоком оказывает значительное влияние на производительность по сравнению со столбцом varchar.

Этот запрос выполняется 18 секунд. Я пытаюсь выполнить преобразование из файлового потока в varchar только один раз, но я думаю, что вызов UDF может привести к тому, что это произойдет для каждой строки (файла).

Create View vAddresses As
Select file_type, Convert(Varchar(8000),file_stream) TextData /* Into #Temp */ From InputFiles Where file_type = 'adr'
Go
Select  --TextData,
        dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
        dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
        dbo.udf_StringSplit(TextData, 7, '|')--, TextData
    From vAddresses

Я пробовал это как представление, cte и подзапрос. Единственное, что, кажется, помогает, - это создание временной таблицы. Создание временной таблицы занимает 1 секунду, а запрос занимает одну секунду. Таким образом, для 35 тыс. строк общее время запроса составляет 2 секунды против 18 секунд.

Drop Table #Temp
(Select file_type, Convert(Varchar(8000),file_stream) TextData Into #Temp From HumanaInputFiles Where file_type = 'adr')
Select  --TextData,
        dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),
        dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),
        dbo.udf_StringSplit(TextData, 7, '|')--, TextData
    From #Temp

Я прочитал много сообщений и блогов по темам файловых таблиц и временных таблиц по сравнению с производительностью одного запроса, но я не могу понять это. Это может иметь какое-то отношение к sargable или статистике? Любые советы очень ценятся.

Вот UDF, я нашел его в блоге/форуме MSDN, и это лучший исполнитель, который я нашел до сих пор.

ALTER FUNCTION [dbo].[udf_StringSplit](
 @TEXT      varchar(8000)
,@COLUMN    tinyint
,@SEPARATOR char(1)
)RETURNS varchar(8000)
AS
  BEGIN
       DECLARE @POS_START  int = 1
       DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)

       WHILE (@COLUMN >1 AND @POS_END> 0)
         BEGIN
             SET @POS_START = @POS_END + 1
             SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
             SET @COLUMN = @COLUMN - 1
         END 

       IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
       IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1 

       RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
  END

Это план выполнения для временной таблицы.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="17486" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.166487" StatementText="Select --TextData,&#xD;&#xA;       dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),&#xD;&#xA;      dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),&#xD;&#xA;      dbo.udf_StringSplit(TextData, 7, '|')--, TextData&#xD;&#xA; From #Temp" StatementType="SELECT" QueryHash="0xC4D6F0215D332F3D" QueryPlanHash="0xC50CFAF9494B5DBE" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="168">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838735" EstimatedPagesCached="419367" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="28023" EstimateCPU="0.0017486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.166487">
              <OutputList>
                <ColumnReference Column="Expr1003" />
                <ColumnReference Column="Expr1004" />
                <ColumnReference Column="Expr1005" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1007" />
                <ColumnReference Column="Expr1008" />
                <ColumnReference Column="Expr1009" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1003" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(1),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(2),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(2)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(3),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(3)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(4),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(4)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(5),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(5)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1008" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(6),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(6)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1009" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([tempdb].[dbo].[#Temp].[TextData],(7),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(7)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4011" EstimateCPU="0.0193131" EstimateIO="0.145426" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.164739" TableCardinality="17486">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" Column="TextData" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[tempdb]" Schema="[dbo]" Table="[#Temp]" IndexKind="Heap" Storage="RowStore" />
                  </TableScan>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Это план просмотра.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4100.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="17486" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.905265" StatementText="Select    --TextData,&#xD;&#xA;       dbo.udf_StringSplit(TextData, 1, '|'), dbo.udf_StringSplit(TextData, 2, '|'), dbo.udf_StringSplit(TextData, 3, '|'),&#xD;&#xA;      dbo.udf_StringSplit(TextData, 4, '|'), dbo.udf_StringSplit(TextData, 5, '|'), dbo.udf_StringSplit(TextData, 6, '|'),&#xD;&#xA;      dbo.udf_StringSplit(TextData, 7, '|')--, TextData&#xD;&#xA; From vAddresses" StatementType="SELECT" QueryHash="0xB4F8A0B288802C4E" QueryPlanHash="0x28DA02D774B1AF53" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="32" CompileTime="3" CompileCPU="3" CompileMemory="520">
            <Warnings>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(varchar(8000),[DmProd01].[dbo].[HumanaInputFiles].[file_stream],0)" />
            </Warnings>
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838735" EstimatedPagesCached="419367" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="28023" EstimateCPU="0.0017486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.905265">
              <OutputList>
                <ColumnReference Column="Expr1004" />
                <ColumnReference Column="Expr1005" />
                <ColumnReference Column="Expr1006" />
                <ColumnReference Column="Expr1007" />
                <ColumnReference Column="Expr1008" />
                <ColumnReference Column="Expr1009" />
                <ColumnReference Column="Expr1010" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1004" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(1),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(1)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1005" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(2),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(2)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1006" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(3),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(3)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1007" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(4),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(4)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1008" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(5),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(5)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1009" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(6),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(6)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Column="Expr1010" />
                    <ScalarOperator ScalarString="[DmProd01].[dbo].[udf_StringSplit]([Expr1011],(7),'|')">
                      <UserDefinedFunction FunctionName="[DmProd01].[dbo].[udf_StringSplit]">
                        <ScalarOperator>
                          <Identifier>
                            <ColumnReference Column="Expr1011" />
                          </Identifier>
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="(7)" />
                        </ScalarOperator>
                        <ScalarOperator>
                          <Const ConstValue="'|'" />
                        </ScalarOperator>
                      </UserDefinedFunction>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="4019" EstimateCPU="0.0034972" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.88673">
                  <OutputList>
                    <ColumnReference Column="Expr1011" />
                  </OutputList>
                  <ComputeScalar>
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Expr1011" />
                        <ScalarOperator ScalarString="CONVERT(varchar(8000),[DmProd01].[dbo].[HumanaInputFiles].[file_stream],0)">
                          <Convert DataType="varchar" Length="8000" Style="0" Implicit="false">
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                              </Identifier>
                            </ScalarOperator>
                          </Convert>
                        </ScalarOperator>
                      </DefinedValue>
                    </DefinedValues>
                    <RelOp AvgRowSize="4043" EstimateCPU="0.0386262" EstimateIO="0.844606" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="17486" LogicalOp="Table Scan" NodeId="2" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.883233" TableCardinality="34972">
                      <OutputList>
                        <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="17486" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_stream" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" IndexKind="Heap" Storage="RowStore" />
                        <Predicate>
                          <ScalarOperator ScalarString="[DmProd01].[dbo].[HumanaInputFiles].[file_type]=N'adr'">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[DmProd01]" Schema="[dbo]" Table="[HumanaInputFiles]" Column="file_type" ComputedColumn="true" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="N'adr'" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </TableScan>
                    </RelOp>
                  </ComputeScalar>
                </RelOp>
              </ComputeScalar>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

РЕДАКТИРОВАТЬ: Искал это по-разному и нашел ответ: использовать top и order by. Это сократило время до 4 секунд. Выглядит глупо и до сих пор не объясняет, как просмотр планов запросов помогает понять это, поэтому не буду отвечать на это сам, вместо этого оставьте его открытым.


sql-server tsql user-defined-functions filetable
person Joe C    schedule 04.07.2017    source источник
comment
Вместо скалярной UDF с несколькими операторами попробуйте использовать табличную функцию, например sqlperformance.com/2016/ 03/sql-server-2016/разделение строк   -  person Lukasz Szozda    schedule 04.07.2017
comment
Спасибо за предложение, но я попробовал эти предложения и несколько других блогов/сообщений, рекомендующих этот подход. Был даже один, использующий delimitedsplit8k и pivot. Ни один из тестов табличных значений не прошел менее 40 секунд с этими тестовыми данными.   -  person Joe C    schedule 04.07.2017
comment
Использование временной таблицы занимает всего 2 секунды, я что-то упустил? Вы пытаетесь выяснить причину разницы в производительности или ищете другое решение?   -  person Jason Byrd    schedule 05.07.2017
comment
Тест должен определить, можем ли мы получить производительность временной таблицы, используя один оператор sql с UDF, а не хранимую процедуру с временной таблицей.   -  person Joe C    schedule 05.07.2017
comment
Пробовали ли вы использовать следующий разделитель строк: stackoverflow.com/a/10182629/243373? Он показывает встроенный TVF, который должен работать лучше, чем обычный.   -  person TT.    schedule 06.07.2017
comment
Спасибо за предложение. Я попробовал это только сейчас, это занимает 10 секунд, что значительно больше, чем двухсекундная производительность подхода с временной таблицей. Также он возвращает одну строку для каждого элемента с разделителями, когда мне нужен столбец для каждого элемента.   -  person Joe C    schedule 06.07.2017
comment
Возможно, вам нужно решить, что важнее: скорость извлечения текста (таблица соблазнов (из вашего вопроса), BULK INSERT или SSIS) или чистый код с умеренным снижением производительности.   -  person Alex    schedule 07.07.2017
comment
@JoeC, пожалуйста, уточните свой вопрос. Вы знаете, что varchar быстрее, чем FileTable, и ищете самый быстрый способ конвертировать FileTable в varchar?   -  person Mikhail Lobanov    schedule 07.07.2017
comment
здесь приведен список нескольких функций разделения строк. Лучший неродной исполнитель, CLR, использует nvarchars, что означает, что вы, вероятно, захотите изменить код.   -  person Bacon Bits    schedule 07.07.2017
comment
Мне также интересно, можете ли вы создать вычисляемый столбец PERSISTED в любой базовой таблице как TextData AS CONVERT(Varchar(8000),file_stream), хотя это, безусловно, замедлит производительность INSERT и займет место на диске, это будет означать, что вам не нужно было бы также преобразовывать данные при запросе. .   -  person Bacon Bits    schedule 07.07.2017
comment
В конце концов, мысль о том, что вы хотите использовать FILESTREAM для текстовых файлов размером около 100 байт, когда эти текстовые файлы содержат данные с разделителями, просто кричит мне: вы проектируете свою таблицу так, чтобы она работала против реляционной модели. Перестаньте нарушать первую нормальную форму и ожидать, что база данных будет работать разумно. Не ленись. Разберите свои данные и сохраните их правильно. Если вам нужен исходный файл, это нормально, но это не означает, что вы должны анализировать этот FILESTREAM.   -  person Bacon Bits    schedule 07.07.2017
comment
Спасибо Bacon Bits, я попробовал все примеры в этом посте, за исключением CLR, который, я уверен, будет работать отлично. Надеясь избежать включения CLR в это время. Также я думал о вычисляемом столбце, но файловые таблицы не позволяют добавлять столбцы. Может быть, индекс поможет, мне придется изучить это.   -  person Joe C    schedule 07.07.2017
comment
@Bacon Bits, это доказательство концепции. Я могу легко использовать другую форму импорта. Однако использование файловой таблицы позволяет получать данные в реальном времени. Как только файл изменен, запрос sql отражает обновленные данные без запуска какого-либо процесса импорта/анализа. Тот факт, что временная таблица работает так хорошо, делает это решение очень заманчивым. Таблицы файлов были созданы не просто так, и они отлично подходят для полнотекстового поиска в надежде найти другое применение.   -  person Joe C    schedule 07.07.2017
comment
Вы можете эмулировать постоянный столбец — создайте другую таблицу и заполните ее триггерами.   -  person Mikhail Lobanov    schedule 07.07.2017
comment
Это интересное предложение. Интересно, приведет ли сохранение текстового файла к событию обновления. Я попробую это, спасибо.   -  person Joe C    schedule 07.07.2017
comment
@JoeC Не могли бы вы предоставить статистику для обоих SET STATISTICS TIME, IO ON? Плюс полный план выполнения для временной таблицы (с SELECT... INTO...)? Очищаете ли вы буферы/кеш перед выполнением каждого метода?   -  person Lukasz Szozda    schedule 08.07.2017
comment
@JoeC, SQL 2016 возможен? На моем компьютере STRING_SPLIT плюс развертка с XML в среднем составляли 1,3 секунды с временной таблицей varchar и 6,4 секунды с FileTable. Простое сканирование FileTable без преобразования или синтаксического анализа заняло на 4 секунды больше времени, чем временная таблица (201 мс версии 4300 мс), поэтому вы никогда не приблизитесь к производительности реляционной таблицы.   -  person Dan Guzman    schedule 09.07.2017
comment
@Mikhail Lobanov +1 за размещение триггера в таблице файлов   -  person David Browne - Microsoft    schedule 09.07.2017
comment
@DanGuzman 2016 не вариант   -  person Joe C    schedule 11.07.2017
comment
@lad2025 lad2025 Я не могу больше добавлять в свой пост, я достиг предела символов. Постараюсь выложить статистику в комментариях. Также я не заморачиваюсь очисткой кеша, кроме как в первый раз, так как не было никакой разницы, сделал я это или нет.   -  person Joe C    schedule 11.07.2017
comment
Представление — (затронуто 17486 строк) Таблица «InputFiles». Количество сканирований 1, логических операций чтения 1137, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения объектов 0. (затронуты 1 строка) Время выполнения SQL Server: процессорное время = 7223 мс, прошедшее время = 19552 мс.   -  person Joe C    schedule 11.07.2017
comment
Временная таблица pt 1 Таблица «InputFiles». Количество сканирований 9, логических чтений 1137, физических чтений 0, упреждающих чтений 0, логических чтений больших объектов 0, физических чтений больших объектов 0, упреждающих чтений больших объектов 0.   -  person Joe C    schedule 11.07.2017
comment
Временная таблица, часть 2. Время выполнения SQL Server: время ЦП = 1046 мс, истекшее время = 919 мс. (затронуто 17486 строк) Время синтаксического анализа и компиляции SQL Server: время ЦП = 0 мс, истекшее время = 1 мс. (Затронуто 17486 строк) Таблица '#Temp__000000000113'. Количество сканирований 1, логических операций чтения 193, физических операций чтения 0, операций упреждающего чтения 0, логических операций чтения 0 объектов, физических операций чтения 0 объектов, операций упреждающего чтения объектов 0. Время выполнения SQL Server: время ЦП = 1700 мс, истекшее время = 1854 РС.   -  person Joe C    schedule 11.07.2017
comment
Возможно, вам понадобится гибридное управление данными. В этом случае вам нужна такая настойчивость. Тем не менее, я не уверен, что строки должны быть распределены по файлам. За исключением случая, когда для владельцев важна изоляция.   -  person profimedica    schedule 13.07.2017
comment
У человека, который намного умнее меня, есть отличная статья об этом на SQLServerCentral, которая, я думаю, разбивает проблему и предлагает довольно впечатляющее собственное решение SQL. sqlservercentral.com/articles/Tally+Table/72993   -  person Roy Folkker    schedule 13.07.2017
comment
Спасибо @RoyFolkker за информацию. Однако этот вопрос касается не столько скорости сплиттера, сколько многократного выполнения представления/подзапроса. По крайней мере, так я считаю до сих пор, основываясь на моем исследовании сплиттеров (включая то, что вы опубликовали), и, поскольку мое последнее редактирование гласит, что использование top и order by дает производительность, почти равную использованию временной таблицы.   -  person Joe C    schedule 13.07.2017
comment
Спасибо за исправление @JoeC. Я предположил, что наблюдаемый вами скачок производительности больше напоминает дерево или аномалию сегментации, проявляющуюся на дисплее; Итак, я подумал, что больше внимания уделяется тому, как вы структурируете свои данные перед подготовкой, что может быть связано с проблемами отображения. Извините за отвлекающий маневр.   -  person Roy Folkker    schedule 13.07.2017

Ответы (5)


arrow_upward
6
arrow_downward

В MSDN есть рекомендации не использовать FileTable в вашем случае:

Функция FileTable основана на технологии SQL Server FILESTREAM.

Для небольших объектов производительность FileStream не очень хорошая. Filestream предназначен для работы с файлами размером около 1 МБ и более, но у вас есть только 100 байт (https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server):

Когда использовать FILESTREAM В SQL Server BLOB-объекты могут быть стандартными данными varbinary(max), которые хранят данные в таблицах, или объектами FILESTREAM varbinary(max), которые хранят данные в файловой системе. Размер и использование данных определяют, следует ли вам использовать хранилище базы данных или хранилище файловой системы. Если выполняются следующие условия, следует рассмотреть возможность использования FILESTREAM:

  • Средний размер сохраняемых объектов превышает 1 МБ.
  • Важен быстрый доступ для чтения.
  • Вы разрабатываете приложения, использующие средний уровень для логики приложения.

Для небольших объектов хранение больших двоичных объектов varbinary(max) в базе данных часто обеспечивает лучшую производительность потоковой передачи.

Вы можете эмулировать постоянный столбец — создайте другую таблицу и заполните ее триггерами. В этом случае вы можете получать бонусы от обоих случаев

P.S. Вы можете использовать встроенный TVF + union all для реализации комментария @TT

person Mikhail Lobanov    schedule 07.07.2017
comment
Спасибо за информацию. Однако я уже читал это, и преимущества для этой ситуации делают это желательным решением. Работа с временной таблицей обеспечивает отличную производительность. Однако тест и мой вопрос заключаются в том, есть ли способ получить такую ​​​​же производительность без временной таблицы. - person Joe C; 07.07.2017
comment
ps Я не понимаю, как добавление union all к миксу улучшит производительность. Я хотел бы попробовать это, хотя, если бы вы могли опубликовать пример. - person Joe C; 07.07.2017
comment
Объединение @JoeC all не улучшает производительность, я написал его, потому что думал, что у вас проблемы с разворотом данных из встроенной функции. - person Mikhail Lobanov; 07.07.2017

arrow_upward
3
arrow_downward

ИЗМЕНИТЬ – еще более быстрый подход

Вместо того, чтобы вызывать вашу функцию Parse/Split 7 раз, возможно, этот TVF может быть более эффективным. Следующее будет обрабатывать 35 000 уникальных записей за 0,773 секунды.

Пример

-- Create Some Sample/UNIQUE Data
Select N,TextData =concat(N,TextData )
Into #Temp
From  (values ('Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg') ) A (TextData )
Cross Apply (Select Top 35000 N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1,master..spt_values n2) B

Select B.*
 From  #Temp A
 Cross Apply (
                Select Pos1=max(case when RetSeq=1 then RetVal end)
                      ,Pos2=max(case when RetSeq=2 then RetVal end)
                      ,Pos3=max(case when RetSeq=3 then RetVal end)
                      ,Pos4=max(case when RetSeq=4 then RetVal end)
                      ,Pos5=max(case when RetSeq=5 then RetVal end)
                      ,Pos6=max(case when RetSeq=6 then RetVal end)
                      ,Pos7=max(case when RetSeq=7 then RetVal end)
                 From [dbo].[udf-Str-Parse-8K](A.TextData,'|') B1
             ) B

Пользовательская функция, если интересно

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/

Просто для визуализации

TVF как автономный элемент включает в себя последовательность элементов, которая может быть применена к условной агрегации.

Select * from [dbo].[udf-Str-Parse-8K]('Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg','|')

Возвращает

RetSeq  RetVal
1       Aaa
2       Bbb
3       Ccc
4       Ddd
5       Eee
6       Fff
7       Ggg
person John Cappelletti    schedule 07.07.2017
comment
Спасибо за хорошо сформированный ответ. Однако запрос выполнялся более 13 минут, поэтому я остановил его. Я перепробовал множество функций разделения, которые обычно работают очень хорошо. Как я уже говорил в своем вопросе, лучшие из найденных мной заканчиваются за 18 секунд. Цель состоит в том, чтобы найти подход, который приближается к 2-секундной производительности временной таблицы. - person Joe C; 07.07.2017
comment
@JoeC Не могу представить, почему вы наблюдаете такую ​​​​плохую производительность. 7 вызовов циклической функции против 1 подсчета? Ради интереса я проанализировал 91 861 адрес ветки (используя строку в формате конвейера), и это заняло 4,031 секунды. Я буду лапша это немного больше. - person John Cappelletti; 07.07.2017
comment
@JoeC Просто любопытно ... как работает следующее ... Выберите min (TextData), max (TextData), count (*) из vAddresses - person John Cappelletti; 07.07.2017
comment
Я считаю, что причина низкой производительности заключается в том, что он извлекает файловый поток каждый раз, когда вызывает функцию. Где использование временной таблицы для однократного преобразования в varchar работает намного лучше. Я не мог сказать по планам казни, действительно ли это происходит. Совокупный запрос, который вы разместили, занял 5 секунд. В очередной раз благодарим за помощь. - person Joe C; 10.07.2017
comment
@JoeC Увлекательно. Если совокупный запрос занял 5 секунд, предоставленный синтаксический анализ должен был занять чуть больше этого времени. Теперь мне действительно любопытно. Позже сегодня я посмотрю, смогу ли я воссоздать вашу структуру и результаты. - person John Cappelletti; 10.07.2017
comment
Спасибо, Джон. Таблица файлов была довольно простой --> Создать таблицу [dbo].[InputFiles] As FileTable On [PRIMARY] FileStream_On [FileStreamGroup1] с ( FileTable_Directory = N'HumanaInputFiles', FileTable_Collate_Filename = SQL_Latin1_General_CP1_CI_AS ) - person Joe C; 11.07.2017
comment
Файлы в основном представляют собой адреса с каждым «полем», в среднем 5 символов, поскольку обычно только первые три имеют данные, но я разрешаю до 7 - person Joe C; 11.07.2017

arrow_upward
0
arrow_downward

[1] Я много раз использовал следующее решение, которое (1.1) преобразует исходные строки в XML, а затем (1.2) каждый раз извлекает из XMl с использованием метода value таким образом:

USE tempdb
GO
IF OBJECT_ID('dbo.SourceTable') IS NOT NULL
    DROP TABLE dbo.SourceTable
GO
CREATE TABLE dbo.SourceTable (
    ID      INT IDENTITY PRIMARY KEY,
    Col1    VARCHAR(100) NOT NULL
);
INSERT  dbo.SourceTable (Col1) VALUES ('Aaa|Bbb|Ccc|Ddd|Eee|Fff|Ggg')
INSERT  dbo.SourceTable (Col1) VALUES ('hhh|iii|JJJ|kkk')

SELECT  b.ID, c.XmlCol.value('.', 'VARCHAR(100)') AS ItemVal--, ROW_NUMBER() OVER(PARTITION BY b.ID ORDER BY c.XmlCol) AS RowNum
FROM (
    SELECT  a.ID, CONVERT(XML, '<root><i>' + REPLACE(a.Col1, '|', '</i><i>') + '</i></root>') AS Col1AsXML
    FROM    dbo.SourceTable a
) b OUTER APPLY b.Col1AsXML.nodes('root/i') c(XmlCol)
--OPTION(FORCE ORDER)

[2] Я бы провел тест производительности, если вы предоставите операторы DDL и DML.

[3] Было бы полезно, если бы вы могли предоставить следующую информацию

  1. Существует максимальное количество элементов для каждой строки?
  2. Если все элементы имеют одинаковую длину (например, 3 символа)?
person Bogdan Sahlean    schedule 09.07.2017

arrow_upward
0
arrow_downward

Я немного поиграл с этим, и самое быстрое решение, запрещающее написание сборки CLR (что не очень быстро), похоже, является одним из вариантов следующего:

CREATE FUNCTION [UDF_Split] (
    @InputStr NVARCHAR(Max),
    @Delimiter NVARCHAR(255)
)
RETURNS TABLE
AS RETURN(

   WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv5)

 SELECT SUBSTRING(@InputStr, N, CHARINDEX(@Delimiter, @InputStr + @Delimiter, N) - N) AS TextLine
 FROM   Tally
 WHERE  N BETWEEN 1 AND LEN(@InputStr) + LEN(@InputStr)
        AND SUBSTRING(@Delimiter + @InputStr, N, LEN(@Delimiter)) = @Delimiter);

Однако, начиная с MS SQL 2016, мы получаем нашу собственную функцию разделения: Sql Server 2016 STRING_SPLIT (строка, разделитель)

person Jay Wheeler    schedule 09.07.2017

arrow_upward
0
arrow_downward

Поскольку у вас есть фиксированное/предопределенное количество столбцов для разделения, вам вообще не нужно использовать функцию разделения строк.

Следующее может пережевывать 100 000 строк примерно за 2 секунды на довольно анемичном сервере разработки.

Примечание... Это решение включает в себя MakeParallel Адама Маханика. function для принудительного выполнения плана параллельного выполнения.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

WITH 
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (n) AS (
        SELECT TOP 100000
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
SELECT 
    ID = ISNULL(CAST(t.n AS INT), 0),
    FilePath = CAST(fp.FilePath AS VARCHAR(1000))
    INTO #TestData
FROM
    cte_Tally t
    CROSS APPLY ( VALUES (CONCAT(
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000, '|',
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000, '|',
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000, '|',
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000, '|',
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000, '|',
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000, '|',
                                ABS(CHECKSUM(NEWID())) % 9999 + 1000
                                )
                            ) ) fp (FilePath);

ALTER TABLE #TestData ADD PRIMARY KEY CLUSTERED (ID);

--=============================================================================
DECLARE     -- Dump values into variables to eliminate display rendering from execution time.
    @id INT,
    @Col_1 VARCHAR(5),
    @Col_2 VARCHAR(5),
    @Col_3 VARCHAR(5),
    @Col_4 VARCHAR(5),
    @Col_5 VARCHAR(5),
    @Col_6 VARCHAR(5),
    @Col_7 VARCHAR(5);

SELECT 
    @ID = td.ID,
    @Col_1 = SUBSTRING(td.FilePath, 1, ABS(d1.DelimLocation - 1)),
    @Col_2 = SUBSTRING(td.FilePath, d1.DelimLocation + 1, ABS(d2.DelimLocation - d1.DelimLocation - 1)),
    @Col_3 = SUBSTRING(td.FilePath, d2.DelimLocation + 1, ABS(d3.DelimLocation - d2.DelimLocation - 1)),
    @Col_4 = SUBSTRING(td.FilePath, d3.DelimLocation + 1, ABS(d4.DelimLocation - d3.DelimLocation - 1)),
    @Col_5 = SUBSTRING(td.FilePath, d4.DelimLocation + 1, ABS(d5.DelimLocation - d4.DelimLocation - 1)),
    @Col_6 = SUBSTRING(td.FilePath, d5.DelimLocation + 1, ABS(d6.DelimLocation - d5.DelimLocation - 1)),
    @Col_7 = SUBSTRING(td.FilePath, d6.DelimLocation + 1, 1000)
FROM
    #TestData td
    CROSS APPLY ( VALUES (LEN(td.FilePath) - LEN(REPLACE(td.FilePath, '|', ''))) ) dc (DelimiterCount)
    CROSS APPLY ( VALUES (IIF(dc.DelimiterCount < 1, 1000,  CHARINDEX('|', td.FilePath, 1))) ) d1 (DelimLocation)
    CROSS APPLY ( VALUES (IIF(dc.DelimiterCount < 2, 1000,  CHARINDEX('|', td.FilePath, d1.DelimLocation + 1))) ) d2 (DelimLocation)
    CROSS APPLY ( VALUES (IIF(dc.DelimiterCount < 3, 1000,  CHARINDEX('|', td.FilePath, d2.DelimLocation + 1))) ) d3 (DelimLocation)
    CROSS APPLY ( VALUES (IIF(dc.DelimiterCount < 4, 1000,  CHARINDEX('|', td.FilePath, d3.DelimLocation + 1))) ) d4 (DelimLocation)
    CROSS APPLY ( VALUES (IIF(dc.DelimiterCount < 5, 1000,  CHARINDEX('|', td.FilePath, d4.DelimLocation + 1))) ) d5 (DelimLocation)
    CROSS APPLY ( VALUES (IIF(dc.DelimiterCount < 6, 1000,  CHARINDEX('|', td.FilePath, d5.DelimLocation + 1))) ) d6 (DelimLocation)
    CROSS APPLY dbo.MakeParallel() mp;  -- Forces a parallel execution plan.
                                        -- http://dataeducation.com/next-level-parallel-plan-forcing-an-alternative-to-8649/

Конечно, функцию MakeParallel также можно использовать в сочетании с функцией Splitter. В этом случае используется функция DelimitedSplit8K Джеффа Модена.

--=============================================================================
DECLARE     -- Dump values into variables to eliminate display rendering from execution time.
    @id INT,
    @Col_1 VARCHAR(5),
    @Col_2 VARCHAR(5),
    @Col_3 VARCHAR(5),
    @Col_4 VARCHAR(5),
    @Col_5 VARCHAR(5),
    @Col_6 VARCHAR(5),
    @Col_7 VARCHAR(5);

SELECT 
    @ID = td.ID,
    @Col_1 = MAX(CASE WHEN sc.ItemNumber = 1 THEN sc.Item END),
    @Col_2 = MAX(CASE WHEN sc.ItemNumber = 2 THEN sc.Item END),
    @Col_3 = MAX(CASE WHEN sc.ItemNumber = 3 THEN sc.Item END),
    @Col_4 = MAX(CASE WHEN sc.ItemNumber = 4 THEN sc.Item END),
    @Col_5 = MAX(CASE WHEN sc.ItemNumber = 5 THEN sc.Item END),
    @Col_6 = MAX(CASE WHEN sc.ItemNumber = 6 THEN sc.Item END),
    @Col_7 = MAX(CASE WHEN sc.ItemNumber = 7 THEN sc.Item END)
FROM
    #TestData td
    CROSS APPLY dbo.DelimitedSplit8K(td.FilePath, '|') sc
    CROSS APPLY dbo.MakeParallel() mp       -- Forces a parallel execution plan.
                                            -- http://dataeducation.com/next-level-parallel-plan-forcing-an-alternative-to-8649/
GROUP BY
    td.ID;

ХТХ, Джейсон

person Jason A. Long    schedule 13.07.2017