Increase search performance in sql server?

Question

I have below table in SQL server which stores all countries data. I have written a query to get the city and county by looking up the postcode and country

    ALTER PROCEDURE [dbo].[SPCleanAddresses]
AS
BEGIN


DECLARE @tableName NVARCHAR(100);
DECLARE @separator NVARCHAR(3);
SET @tableName = 'UKUNMATCHDATA_Version1_ten';
SET @separator = ' ';
--AS


BEGIN
    DECLARE @TableCleanedName NVARCHAR(100);

    SET @TableCleanedName = CONCAT(@tableName, '_DataCleaned');
    SET NOCOUNT ON;
    IF EXISTS
    (
        SELECT name
        FROM sysobjects
        WHERE Name = 'data'
    )
        BEGIN
            DROP TABLE dbo.data;
        END;
    IF EXISTS
    (
        SELECT name
        FROM sysobjects
        WHERE Name = 'DataCleaned_Version1'
    )
        DROP TABLE DataCleaned_Version1;
     EXEC ('select * into data  from '+@tableName+'');
     SELECT 

              CASE
                    WHEN i.Zipcode IS NOT NULL AND i.COUNTRY IS NOT NULL
                    THEN UPPER([dbo].[fnGetCityfromZipCode](i.Zipcode, i.[COUNTRY]))
                    ELSE NULL
                END AS City,
             CASE
                    WHEN i.Zipcode IS NOT NULL AND i.COUNTRY IS NOT NULL
                    THEN UPPER([dbo].[fnGetCountyfromZipCode](i.Zipcode, i.[COUNTRY]))
                    ELSE NULL
                END AS County,
             i.*

    INTO DataCleaned_Version1
    FROM
    (
          SELECT h.*   ,      CASE
                             WHEN h.[post-code] <> ('') AND h.[post-code] IS NOT NULL THEN REPLACE(REPLACE(LTRIM(RTRIM(h.[POST-CODE])),' ',''),'-','')
                             ELSE [dbo].[udf_fnLookupZipCodeAndFindCityCounty](h.Number1,h.Number2,h.Number3,h.Number4,h.Number5,h.Number6,h.Number7,h.[COUNTRY])
                          END AS Zipcode
          FROM
          (
             SELECT g.*,
                          CASE
                                WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(g.address11, 0, LEN(g.address11)+1)))) <> ''
                                THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(g.address11, 0, LEN(g.address11)+1))))
                          END AS Number6,
                          CASE
                                WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(g.address12, 0, LEN(g.address12)+1)))) <> ''
                                THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(g.address12, 0, LEN(g.address12)+1))))
                          END AS Number7          
             FROM
             (

                 SELECT f.*,
                          LTRIM(RTRIM(SUBSTRING(f.address10, 0, CHARINDEX(@separator, f.address10)))) AS address11,
                            CASE
                                WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(f.address9, 0, LEN(f.address9)+1)))) <> ''
                                THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(f.address9, 0, LEN(f.address9)+1))))
                            END AS Number5,
                            LTRIM(RTRIM(SUBSTRING(f.address10, CHARINDEX(@separator, f.address10)+1, 8000))) AS address12
                    FROM
                    (

                    SELECT e.*,
                             LTRIM(RTRIM(SUBSTRING(e.address8, 0, CHARINDEX(@separator, e.address8)))) AS address9,
                                CASE
                                   WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(e.address7, 0, LEN(e.address7)+1)))) <> ''
                                   THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(e.address7, 0, LEN(e.address7)+1))))
                                END AS Number4,
                                LTRIM(RTRIM(SUBSTRING(e.address8, CHARINDEX(@separator, e.address8)+1, 8000))) AS address10
                    FROM
                    (

                       SELECT d.*,
                                LTRIM(RTRIM(SUBSTRING(d.address6, 0, CHARINDEX(@separator, d.address6)))) AS address7,
                                CASE
                                   WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(d.address5, 0, LEN(d.address5)+1)))) <> ''
                                   THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(d.address5, 0, LEN(d.address5)+1))))
                                END AS Number3,
                                LTRIM(RTRIM(SUBSTRING(d.address6, CHARINDEX(@separator, d.address6)+1, 8000))) AS address8
                          FROM
                          (
                             SELECT c.*,
                                   LTRIM(RTRIM(SUBSTRING(c.address4, 0, CHARINDEX(@separator, c.address4)))) AS address5,
                                   CASE
                                      WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(c.address1, 0, LEN(c.address1)+1)))) <> ''
                                      THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(c.address1, 0, LEN(c.address1)+1))))
                                   END AS Number1,
                                   CASE
                                      WHEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(c.address3, 0, LEN(c.address3)+1)))) <> ''
                                      THEN dbo.udf_ExtractInteger(LTRIM(RTRIM(SUBSTRING(c.address3, 0, LEN(c.address3)+1))))
                                   END AS Number2,
                                   LTRIM(RTRIM(SUBSTRING(c.address4, CHARINDEX(@separator, c.address4)+1, 8000))) AS address6
                             FROM
                             (
                                SELECT b.*,
                                      LTRIM(RTRIM(SUBSTRING(b.aaddress2, 0, CHARINDEX(@separator, b.aaddress2)))) AS address3,
                                      LTRIM(RTRIM(SUBSTRING(b.aaddress2, CHARINDEX(@separator, b.aaddress2)+1, 8000))) AS address4
                                FROM
                                (
                                    SELECT a.*,
                                         LTRIM(RTRIM(SUBSTRING(a.address, 0, CHARINDEX(@separator, a.address)))) AS address1,
                                         LTRIM(RTRIM(SUBSTRING(a.address, CHARINDEX(@separator, a.address)+1, 8000))) AS aaddress2
                                    FROM data AS a
                                ) AS b
                             ) AS c
                       ) AS d
                    )AS e
                )f
             )g
             )h
    )i
END;
 END

But in my source table I have 68 thousand records ,but just to execute this query with 10 records it takes 13 second

SO far I have applied non clustered index

CREATE NONCLUSTERED INDEX ix_people_lastname

ON dbo.AllCountriesZipCode(ZipCode,County)

WITH (fillfactor=80);

GO

But still I am getting same results (13 sec) just for 10 records

any help in this regard is appropriated,note that the zip code has duplicate values,

My function is to lookup the city and county from postcode and country in this table.

Please help me ,I need to sort out this, for 68000 records on my source table, it is taking more than 8 hours, as for each record it travels around 98000 records in allcountriesZipcode table

Answer 1

If I was on your place I began to from the bottom.

EXEC ('select * into data  from '+@tableName+'');

The query that peaks the values from @tableName must be in another isolation level. If the data in your table is not really sensitive you can use the WITH(NOLOCK) hint (READ UNCOMMITTED isolation level). Otherwise use SNAPSHOT.

Example:

EXEC ('select * into data  from '+@tableName+' WITH(NOLOCK)');

Measure yours query execution time and if it will be still not enough look for your SELECTs above. Just do it step by step. Try to delete all query logic from the bottom to the top and add it step by step looking for execution time.