Update with a WHILE statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update with a WHILE statement

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-09 : 13:21:10

Hi,

I've just been going though some stored procedures that are running slowly and I'm hoping you can help me optimise them basically we are trying to make 1 table and all this is doing is assign the country value ie England, Scotland or Wales, this is done by referencing another table which contains postcode values and reduces the postcode to get the nearest value, ie:

Postcode: PL189QL
we have a match in the data of: PL18

the stored proc will run through a while, removing the last char each time till it gets a match like this:


CREATE  procedure sp_LEISURE_GetCountryFromPostCode(
@Post 		nvarchar(50) = null,
@Country	int output
)
as
SELECT @Post=REPLACE(@Post,' ','')
WHILE @Post<>''
BEGIN
  IF (SELECT COUNT(ID)FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE (@Post+'%')) > 0
    BEGIN
      SELECT DISTINCT @Country=[Country] 
      FROM LEISURE_CountryVsPostcodeNumeric 
      WHERE [Post Code] LIKE (@Post+'%')
      BREAK
    END
   SELECT @Post=LEFT(@Post, LEN(@Post)-1)
END
--Print @Country
GO

Now, I don't know if its faster but I've changed that to:


CREATE  procedure sp_LEISURE_GetCountryFromPostCode(
@Post nvarchar(50) = null,
@Country int output
)
as

Declare @tmpPost varchar(50)

WHILE @Post<>''
BEGIN
--Set the new postcode search value
SET @tmpPost = @Post+'%'
  IF EXISTS(SELECT DISTINCT [ID] FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE @tmpPost)
      BEGIN
	  --Select the record and send it back to the user
	  SELECT DISTINCT @Country=[Country] FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE @tmpPost
  	  BREAK
      END
  ELSE
    --Nothing was found reduce the postcode string and try again
    SET @Post=LEFT(@Post, LEN(@Post)-1)
END

This SP above is called on a row level, now, I know you can update from another table etc as you've helped me there before but is there anyway I can write this while statement in the UPDATE statement to speed it up at all? Or at least reduce the code required?

Cheers for any advice.

Tim

Other SP:


CREATE  procedure sp_LEISURE_PopulateAllPlacesFrom_CountryVsPostcodeNumeric
as
declare @PostCode as varchar(50)
declare @Country as int
declare @ID as numeric
DECLARE V_cursor CURSOR FOR
SELECT [ID] 
FROM LEISURE_Places order by [ID]
OPEN V_cursor
-- Perform the first fetch.
FETCH NEXT FROM V_cursor into @ID
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  select @postCode=[Post Code] from LEISURE_Places where @ID=[ID]
  PRINT @ID

  EXEC sp_LEISURE_GetCountryFromPostCode  @PostCode, @Country output

    UPDATE LEISURE_Places
    SET [Country]=@Country
    WHERE [ID]=@ID


  FETCH NEXT FROM V_cursor into @ID
END
CLOSE V_cursor
DEALLOCATE V_cursor
GO

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-12-09 : 13:42:22

One option would be to get rid of those procs and perform an inline update using a udf:


CREATE  FUNCTION  UDF_CountryVsPostcodeNumeric ( @Post nvarchar(50) )
RETURNS INT

AS
BEGIN
	DECLARE @tmpPost varchar(50),@Country int
	WHILE @Post <> ''
		BEGIN
			--Set the new postcode search value
			SET @tmpPost = @Post+'%'
		   IF EXISTS(SELECT DISTINCT [ID] FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE @tmpPost)
		      BEGIN
				  --Select the record and send it back to the user
				  SELECT DISTINCT @Country=[Country] FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE @tmpPost
			  	  BREAK
		      END
		   ELSE
	    --Nothing was found reduce the postcode string and try again
	    SET @Post=LEFT(@Post, LEN(@Post)-1)
		END
	RETURN @Country
END

GO

UPDATE LEISURE_Places
SET Country = UDF_CountryVsPostcodeNumeric([Post Code])

GO

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-09 : 14:56:17

Hi,

thanks for getting back to me, I should have mentioned we're on SQL Server 7 and can't create functions but that would be a solution, I think whats slowing it all down is the FETCH statement, is that correct?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-09 : 15:45:07

how about some more sample data, with some expected results? I'd need to be able to see some sample data that covers the various possibilites before I can help you optimize this. keep it simple, but be sure to include data for each case you need to handle. and indicate what you'd like to see returned.

- Jeff

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-09 : 16:43:50

hmm, I'll try, it should be straighforward but I'll mess it up:

Table to be updated:
ComanyName | Postcode | Country
A PL18 9QL Null
B DY5 3YD Null
C SW7 5EW Null

Reference table:
Postcode | Country
PL18 9QL 2
DY5 4YD 1
SW8 5TW 1

Returned values:
A: 2
B: 1
C: 1

From what I understand we're getting the nearest value so for B it'll match DY5 4YD by triming the last three chars and for C it'll go right to the SW. Does that make sense?

Tim

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-09 : 17:21:53

Will this fit the bill?

create table dbo.Leisure_Places (
id int primary key,
PostCode varchar(20), -- Full Postal Code
Country varchar(50)
)
go

create table dbo.Leisure_CountryVsPostCodeNumeric (
id int primary key,
PostCode varchar(20), -- Full Postal Code
Country varchar(50)
)

create table dbo.Numbers (
Number int primary key identity(1, 1)
)

insert into dbo.Leisure_Places values (1, 'abc123', 'England')
insert into dbo.Leisure_Places values (2, 'abc456', 'England')
insert into dbo.Leisure_Places values (3, 'abc178', 'England')

insert into dbo.Leisure_Places values (4, 'def123', 'Scotland')
insert into dbo.Leisure_Places values (5, 'deg456', 'Scotland')
insert into dbo.Leisure_Places values (6, 'def178', 'Scotland')

insert into dbo.Leisure_Places values (7, 'xyz123', 'Wales')
insert into dbo.Leisure_Places values (8, 'xyz456', 'Wales')
insert into dbo.Leisure_Places values (9, 'xyz178', 'Wales')

insert into dbo.Leisure_CountryVsPostCodeNumeric values (1, 'abc123', 'England')
insert into dbo.Leisure_CountryVsPostCodeNumeric values (5, 'deg456', 'Scotland')
insert into dbo.Leisure_CountryVsPostCodeNumeric values (9, 'xyz178', 'Wales')

select *
from dbo.Leisure_Places

select *
from dbo.Leisure_CountryVsPostCodeNumeric

select *
from dbo.Numbers

--- Update Places with Country based on Postal Code

update dbo.Leisure_Places
set Country = pc1.Country
from dbo.Leisure_CountryVsPostCodeNumeric pc1
join (
select p.id, pc2.PostCode, max(n.Number) nn
from dbo.Leisure_CountryVsPostCodeNumeric pc2
join dbo.Numbers n
on n.Number <= len(pc2.PostCode)
join dbo.Leisure_Places p
on p.PostCode like left(pc2.PostCode, n.Number) + '%'
group by p.id, pc2.PostCode
) pc3
on pc1.PostCode like left(pc3.PostCode, pc3.nn) + '%'
where Leisure_Places.id = pc3.id

Just as an aside, I see that you are using the DISTINCT keyword in conjunction with the EXISTS operator. This may add some overhead, unnecessarily, to the processing. Do you really want to eliminate duplicates before seeing if even one exists?

HTH

=================================================================
Hear the sledges with the bells - Silver bells!
What a world of merriment their melody foretells!
How they tinkle, tinkle, tinkle,
In the icy air of night!
While the stars that oversprinkle
All the heavens, seem to twinkle
With a crystalline delight;
Keeping time, time, time,
In a sort of Runic rhyme,
To the tintinnabulation that so musically wells
From the bells, bells, bells, bells,
Bells, bells, bells
From the jingling and the tinkling of the bells.

Happy Holidays!

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-10 : 03:58:59

Hi Bustaz,

Many thanks for your help but I couldn't get your ecample to work, I've tried to replicate what we've got here with the script below, hopefully it'll demonstrate the example a little better

I'm not sure how famiar with postcodes people are but from my (basic) understanding the work like a standard grouping, looking at PL18 9QL:
PL = The county of the address
18 = Area in the county
9QL = Area in the town

So, when trying to find the correct county removing the last char each time until you get a match *should* give you the correct county. My colleague who wrote this processed one line at a time and over about 200k records its rather slow, is this because of the FETCH?

Cheers for your help so far

Tim

-------------------------------------------------


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LEISURE_Places]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LEISURE_Places]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LEISURE_CountryVsPostcodeNumeric]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LEISURE_CountryVsPostcodeNumeric]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_LEISURE_GetCountryFromPostCode]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_LEISURE_GetCountryFromPostCode]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_LEISURE_GetCountryFromPostCode_NEW]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_LEISURE_GetCountryFromPostCode_NEW]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_LEISURE_PopulateAllPlacesFrom_CountryVsPostcodeNumeric]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_LEISURE_PopulateAllPlacesFrom_CountryVsPostcodeNumeric]
GO

CREATE TABLE [dbo].[LEISURE_Places] (
	[ID] [decimal](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Company Name] [varchar] (100) NULL ,
	[Address Line 1] [varchar] (100) NULL ,
	[Address Line 2] [varchar] (100) NULL ,
	[TOWN] [varchar] (50) NULL ,
	[Post Code] [varchar] (10) NULL ,
	[Country] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LEISURE_CountryVsPostcodeNumeric] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Post Code] [nvarchar] (10) NULL ,
	[Country] [int] NULL 
) ON [PRIMARY]
GO

insert into dbo.Leisure_Places values ('Company 1 -England', 'Line 1', 'Line 2', 'Town', 'UK189GB', NULL)
insert into dbo.Leisure_Places values ('Company 2 -Scotland', 'Line 1', 'Line 2', 'Town', 'SC122SC', NULL)
insert into dbo.Leisure_Places values ('Company 3 -Wales', 'Line 1', 'Line 2', 'Town', 'WL121WL', NULL)
insert into dbo.Leisure_Places values ('Company 4 -England', 'Line 1', 'Line 2', 'Town', 'UK209GB', NULL)
insert into dbo.Leisure_Places values ('Company 5 -Wales', 'Line 1', 'Line 2', 'Town', 'WLWL', NULL)
insert into dbo.Leisure_Places values ('Company 6 -Scotland', 'Line 1', 'Line 2', 'Town', 'SC201SC', NULL)
insert into dbo.Leisure_Places values ('Company 7 -England', 'Line 1', 'Line 2', 'Town', 'UK251GB', NULL)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('UK189GB', 1)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('SC111SC', 2)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('UK201GB', 1)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('SC202SC', 2)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('WL121WL', 3)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('WL122WL', 3)
insert into dbo.Leisure_CountryVsPostCodeNumeric values ('SC782SC', 2)

SELECT * FROM dbo.Leisure_CountryVsPostCodeNumeric
GO
SELECT * FROM dbo.Leisure_Places
GO

--Create Stored Procs
CREATE  procedure sp_LEISURE_GetCountryFromPostCode(
@Post 		nvarchar(50) = null,
@Country	int output
)
as
SELECT @Post=REPLACE(@Post,' ','')
WHILE @Post<>''
BEGIN
  IF (SELECT COUNT(ID)FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE (@Post+'%')) > 0
    BEGIN
      SELECT DISTINCT @Country=[Country] 
      FROM LEISURE_CountryVsPostcodeNumeric 
      WHERE [Post Code] LIKE (@Post+'%')
      BREAK
    END
   SELECT @Post=LEFT(@Post, LEN(@Post)-1)
END
GO

CREATE  procedure sp_LEISURE_GetCountryFromPostCode_NEW(
@Post nvarchar(50) = null,
@Country int output
)
as

Declare @tmpPost varchar(50)

WHILE @Post<>''
BEGIN
--Set the new postcode search value
SET @tmpPost = @Post+'%'
  IF EXISTS(SELECT [ID] FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE @tmpPost)
      BEGIN
	  --Select the record and send it back to the user
	  SELECT @Country=[Country] FROM LEISURE_CountryVsPostcodeNumeric WHERE [Post Code] LIKE @tmpPost
  	  BREAK
      END
  ELSE
    --Nothing was found reduce the postcode string and try again
    SET @Post=LEFT(@Post, LEN(@Post)-1)
END
GO

CREATE  procedure sp_LEISURE_PopulateAllPlacesFrom_CountryVsPostcodeNumeric
as
declare @PostCode as varchar(50)
declare @Country as int
declare @ID as numeric
DECLARE V_cursor CURSOR FOR
SELECT [ID] 
FROM LEISURE_Places order by [ID]
OPEN V_cursor
-- Perform the first fetch.
FETCH NEXT FROM V_cursor into @ID
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
  select @postCode=[Post Code] from LEISURE_Places where @ID=[ID]
  PRINT @ID

  EXEC sp_LEISURE_GetCountryFromPostCode  @PostCode, @Country output

    UPDATE LEISURE_Places
    SET [Country]=@Country
    WHERE [ID]=@ID


  FETCH NEXT FROM V_cursor into @ID
END
CLOSE V_cursor
DEALLOCATE V_cursor
GO

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-10 : 12:16:29

I should have annotated the code better for you but my real job was calling... (Doncha hate it when that happens?)

I assume the existence of a table of integers (dbo.Numbers). This is a useful object to have around for a variety of reasons. You may find that you want to add other useful columns to this table based on your needs. I haven't shown the the inserts into this table but I'm sure that you can work that out.

Now as to the update code. This replaces all of your existing code. There are three basic components:

1) SELECT subquery - This finds the longest substring of Leisure_CountryVsPostCodeNumeric.PostCode that is still LIKE the Leisure_Places.PostCode.

2) FROM - Now that we know which Leisure_CountryVsPostCodeNumeric.PostCode to use (See #1) we can pick the Country associated with it.

3) UPDATE/SET - Performs the actual data modification.

update dbo.Leisure_Places
set Country = pc1.Country
from dbo.Leisure_CountryVsPostCodeNumeric pc1
join (
select p.id, pc2.PostCode, max(n.Number) nn
from dbo.Leisure_CountryVsPostCodeNumeric pc2
join dbo.Numbers n
on n.Number <= len(pc2.PostCode)
join dbo.Leisure_Places p
on p.PostCode like left(pc2.PostCode, n.Number) + '%'
group by p.id, pc2.PostCode
) pc3
on pc1.PostCode like left(pc3.PostCode, pc3.nn) + '%'
where Leisure_Places.id = pc3.id

You say that you can't get it to work. Are you having syntax errors? Logic errors?

If you could supply some specific data items, that might allow us to give you better feedback. I've supplied some dummy data values but they clearly are not representative of your actual data.

HTH

=================================================================
Hear the sledges with the bells - Silver bells!
What a world of merriment their melody foretells!
How they tinkle, tinkle, tinkle,
In the icy air of night!
While the stars that oversprinkle
All the heavens, seem to twinkle
With a crystalline delight;
Keeping time, time, time,
In a sort of Runic rhyme,
To the tintinnabulation that so musically wells
From the bells, bells, bells, bells,
Bells, bells, bells
From the jingling and the tinkling of the bells.

Happy Holidays!

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-10 : 12:33:46

hehe yeah tell me about it, I hate it when you have to do work... hehe

Well I tried again but its just responding "(0 row(s) affected)" even when there is an exact match. I posted some SQL that should create what I've got here in my last post which includes some example data to test. We haven't got a "Numbers" table on the live server at the moment but I can easily add it...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-10 : 13:07:38

Im using your data from above and I get all seven rows updated. The results are:

ID POST CODE COUNTRY
1 UK189GB 1
2 SC111SC 2
3 UK201GB 1
4 SC202SC 2
5 WL121WL 3
6 WL122WL 3
7 SC782SC 2

Have you populated the Numbers table with at least 1 through 7?

HTH

=================================================================

Happy Holidays!

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-10 : 13:42:46

Ah, superb thanks, I was just being stupid and didn't have any numbers, thanks ever so much, I'll let you know if this runs faster, I assume it should?

Cheers again.

Tim

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-10 : 13:52:37

If it doesn't, I'll buy you a pint...

HTH

=================================================================

Happy Holidays!

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-12-10 : 14:15:17

hehe well if you want to come to the UK, I think I owe you one....

:)

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources