CODEWITHSUNDEEP
sql-servert-sql
Gabriel McAdams
Gabriel McAdams

Reputation: 58251

How can I rewrite my query so that functions are not being called more than once?

I an working on a regular (monthly) import of a large amount of data. During the conversion, I am splitting a string into multiple columns, It is not just a simple split, though. There is a little bit of logic that decides which portion of the string goes into which field.

I have written an inline function that breaks a string into multiple parts and gives you the value at a specified index.

The parameters are:

  1. string value
  2. delimiter
  3. index

for example:

If the string value is X4-728Z5-121-84gff and you want the function to give you the 121, then you would call the function like this:

fn_MyFunc('X4-728Z5-121-84gff', '-', 3)

My issue is this:

In my import query, the index I need for a particular field value is dependent upon the value at another index. If the value at index 1 = X4 then I want index 3, else index 4.

In the single query, I call this function 4 or 5 times, depending on the result of some case statements.

The function is basically doing the same thing over and over again... but each time, I am getting a different index. How can I reduce the efforts, such that the hard work of splitting the string is only done once, and in the same query, I can get different indexes easily?

Keep in mind that this is during an import of data from an external source, and any answer suggesting normalization or indexed views, etc will not help.

EDIT

I was asked to post my query:

SELECT
    ComplexString,
    CAST(fn_MyFunc(ComplexString, '-', 1) AS NVARCHAR(2)) AS LocationCode,
    CAST(fn_MyFunc(ComplexString, '-', 2) AS NVARCHAR(25)) AS CompanyCode,
    NULLIF(CASE
        WHEN fn_MyFunc(ComplexString, '-', 1) = 'R1' THEN NULL
        ELSE CAST(fn_MyFunc(ComplexString, '-', 3) AS INT)
    END, 0) AS ManagementType,
    CASE
        WHEN fn_MyFunc(ComplexString, '-', 1) = 'R1' THEN CAST(fn_MyFunc(ComplexString, '-', 3) AS VARCHAR(25))
        ELSE CAST(fn_MyFunc(ComplexString, '-', 4) AS NVARCHAR(25))
    END AS Network,
    .
    .
    .
FROM MyTable

Upvotes: 4

Views: 243

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

If the max of all strings is 4 parts, instead of your function you could just do this inline:

SELECT PARSENAME(REPLACE(column, '-', '.'), 
  CASE WHEN (condition for 4th element) THEN 1 
       WHEN (condition for 3rd element) THEN 2
       WHEN (condition for 2nd element) THEN 3
       WHEN (condition for 1st element) THEN 4
   END
FROM ...

You could also consider:

(a) storing each portion of the string in a separate computed column. You can even persist / index the computed column.

(b) storing the separate parts of the string separately in the first place - concatenation is always easier than splitting.

EDIT given updated query

;WITH x AS
(
  SELECT ComplexString,
         p1 = LEFT(ComplexString, 2),
         p2 = dbo.fn_MyFunc(ComplexString, '-', 2),
         p3 = dbo.fn_MyFunc(ComplexString, '-', 3),    
         p4 = dbo.fn_MyFunc(ComplexString, '-', 4)
         -- , other columns
  FROM dbo.MyTable
)
SELECT
    ComplexString,
    p1 AS LocationCode,
    LEFT(p2, 25) AS CompanyCode,
    CASE WHEN p1 <> 'RI' THEN CONVERT(INT, LEFT(p3, 3)) ELSE 0 END 
      AS ManagementType,
    LEFT(CASE WHEN p1 = 'RI' THEN p3 ELSE p4 END, 25) AS Network
FROM x;

Upvotes: 4

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Create a split function the splits your string to columns and use the function in a cross apply.

A function to split to 5 columns could look like this.

alter function [dbo].[SplitString]
(
    @Value nvarchar(max),
    @Delim nchar(1)
)
returns table as return
(
    select substring(T.Value, 1, T1.P - 1) as C1,
           substring(T.Value, T1.P + 1, T2.P - T1.P - 1) as C2,
           substring(T.Value, T2.P + 1, T3.P - T2.P - 1) as C3,
           substring(T.Value, T3.P + 1, T4.P - T3.P - 1) as C4,
           substring(T.Value, T4.P + 1, T5.P - T4.P - 1) as C5
    from (select @Value+replicate(@Delim, 5)) as T(Value) 
      cross apply (select charindex(@Delim, T.Value)) as T1(P)
      cross apply (select charindex(@Delim, T.Value, T1.P + 1)) as T2(P)
      cross apply (select charindex(@Delim, T.Value, T2.P + 1)) as T3(P)
      cross apply (select charindex(@Delim, T.Value, T3.P + 1)) as T4(P)
      cross apply (select charindex(@Delim, T.Value, T4.P + 1)) as T5(P)
)

And it would be used like this.

select *
from YourTable as Y
  cross apply dbo.SplitString(Y.ColumnToSplit, '-') as S

The function will be called once for each row and you can use the columns C1, C2, C3, ... in your field list or where clause without a new call the the split function.

Upvotes: 4

Ray
Ray

Reputation: 21905

If your string has a fixed (or a definite maximum) number of parts, you could create a set of variables like @part1, @part2, etc (using better names if each part has a specific meaning). Use your function to populate the variables once, and use the variables instead of re-parsing.

-- OR --

Convert your string to a narrow temp table or table variable, with one varchar column and one tinyint identity column, using a parsing method (there are a number of posts on SO on the topic - and here is a nice article, geared toward csv, but easily adaptable to your situation). You can then use the sequence column in a select to locate the string part you need.

Upvotes: 0

Jonas H&#248;gh
Jonas H&#248;gh

Reputation: 10874

If the individual parts of the string are central to the operations you wish to perform on your data, an option could be to normalize further by storing the parts either as individual columns or in a related table.

Alternatively, a table valued function that returns the individual parts would enable you to improve performance if you need to access the parts many times.

Upvotes: 0

Related Questions