I have a large table in which I store region/state/province codes (two letters) from different countries. I use these region codes further downstream for multiple process. One of the steps I do is a cleanup of regions to ensure it is a valid region/state/province code. I have a lookup table consisting of a country vs region code to validate each column.

6 columns are validated separately. Is there a way to do this better?

UPDATE  table_region
SET     a_region = NULL
WHERE   a_region IS NOT NULL
        AND country IS NOT NULL
        AND dbo.IsValidRegion(country,a_region) = 0;

UPDATE  table_region
SET     b_region = NULL
WHERE   b_region IS NOT NULL
        AND country IS NOT NULL
        AND dbo.IsValidRegion(country,b_region) = 0;

UPDATE  table_region
SET     c_region = NULL
WHERE   c_region IS NOT NULL
        AND country IS NOT NULL
        AND dbo.IsValidRegion(country, c_region) = 0;         

UPDATE  table_region
SET     d_region = NULL
WHERE   d_region IS NOT NULL
        AND country IS NOT NULL
        AND dbo.IsValidRegion(country,d_region) = 0;  

UPDATE  table_region
SET     e_region = NULL
WHERE   e_region IS NOT NULL
        AND country IS NOT NULL
        AND dbo.IsValidRegion(country, e_region) = 0;  

UPDATE  table_region
SET     f_region = NULL
WHERE   f_region IS NOT NULL
        AND country IS NOT NULL
        AND dbo.IsValidRegion(country, f_region) = 0;

The function is like this:

ALTER Function [dbo].[IsValidRegion](@country VARCHAR(20), @value 

Returns INT
    DECLARE @res INT
        IF EXISTS (SELECT 1 FROM mapping.region WHERE country = @country AND region_code = @value)
            SELECT @res = 1
            SELECT @res = 0
    RETURN @res
  • \$\begingroup\$ Please consider posting the DDL of both table table_region and mapping. This would makes things much easier for us to test in a fiddle. \$\endgroup\$ – dfhwze May 25 '19 at 8:41

There's an intrinsic problem in using a scalar UDF to validate your country and region. Your scalar function essentially answers the "is it valid?" question for just a single pair of values - that might be fine in an "as-the-insert-happens" sense of a live system that only occasionally has a need for validation of a single row, but SQLServer design principles have a particular emphasis on thinking in terms of sets of data, rather than thinking of rows of data in a line-by-line fashion, requiring individual attention.

If you're performing this on a large table/lot of rows (let's say 100,000) then you could conceive that SQLServer will run your select query 600,000 times. It's going to be considerably slower to run more than half a million queries that pick out one item of data at a time, than it is going to be to run a query that joins together 100,000 rows (SQLServer is good at joining data efficiently) and then loops over it, checking some conditions and making changes.

There's a process you can follow that will allow SQLServer to more effectively pick apart your function and incorporate/integrate it with the main query more effectively, particularly in terms of processing the query in parallel - You can read more about it in blogs and stackexchange answers discussing swapping scalar UDFs for inline TVFs. Some relatively minor performance improvements can be had by compiling the function. There is also work afoot to improve SQLServer's ability to process scalar UDFs inline with the rest of the query, but at the moment it's potentially an absolute showstopper for the performance of your query

You also have the option of doing away with the function (or not using it in this context) and doing the cleanup all in one go (and you could encapsulate this logic in a stored procedure if you wanted) :

    UPDATE t
       a_region = ra.region_code,
       b_region = rb.region_code,
       c_region = rc.region_code,
       d_region = rd.region_code,
       e_region = re.region_code,
       f_region = rf.region_code
       table_region t
       LEFT OUTER JOIN mapping.region ra WHERE t.country = ra.country AND t.a_region = ra.region_code
       LEFT OUTER JOIN mapping.region rb WHERE t.country = rb.country AND t.b_region = rb.region_code
       LEFT OUTER JOIN mapping.region rc WHERE t.country = rc.country AND t.c_region = rc.region_code
       LEFT OUTER JOIN mapping.region rd WHERE t.country = rd.country AND t.d_region = rd.region_code
       LEFT OUTER JOIN mapping.region re WHERE t.country = re.country AND t.e_region = re.region_code
       LEFT OUTER JOIN mapping.region rf WHERE t.country = rf.country AND t.f_region = rf.region_code

It basically works by left joining the region table 6 times, once for each of the *_region columns.

  • If the relation works out, then the r*.region_code will be populated with a value (which means the *_region column is set to the same value it currently is, i.e. a non-op).
  • If it doesn't work out, then the join fails, null is present in r*.region_code

You could run this regularly as a periodic fix. You could also turn this into a SELECT query and run it, to identify the ones that are failed. If you were then to move those out (to another table) so your table only contains valid related records, you could then consider making table_region(country,*_region) have multiple foreign keys to mapping.region(country,region_code) to ensure that records cannot be inserted into table_region that don't have a valid country/region mapping. This devolves responsibility to maintaining a valid country/region pairing to the constraints mechanism of the database and it will become impossible to insert data that has an invalid country/region pairing


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.