SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Script that is slower than a sloth.....HELP!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tim_Field
Yak Posting Veteran

United Kingdom
87 Posts

Posted - 06/21/2004 :  11:56:59  Show Profile
Hi All,

I've been working for a while on this and thanks to those who've had a look previously....

When chosen each of the roles (student, staff, contact, Agent) should have the following done...

• First character in each name is capitalised
• Hyphenated names will be additionally capitalised after each hyphen
• First letter after a space should be capitalised
• Gaelic type names must be capable of correctly placing capitalised letters, e.g. McGowan, Macdonald, O’Malley
• Numbers following a name must also be displayed, e.g. Lee Peters IV
• If Junior or Senior follows the surname, these must also be included e.g. Jnr, Snr.

As you can see there are many roles here.. but the script is slower than a cat waiting for a bath.
I don't need a full technical re-write just some pointers as to how to speed it up!

Thanks, Tim





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

set nocount on
go

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

exec sims.db_p_drop 'sims.sta_pix_FormatNames','P'
go

Alter procedure sims.sta_pix_FormatNames
(
-- Pass through parameters null will be no change TC will be true when changing to Type case and false
-- when changing to upper case.
@studentsSurnameTC T_pub_logical_false = null,
@studentsForenameTC T_pub_logical_false = null,
@studentsMiddleNameTC T_pub_logical_false = null,
@studentsChosenNameTC T_pub_logical_false = null,
@staffSurnameTC T_pub_logical_false = null,
@staffForenameTC T_pub_logical_false = null,
@staffMiddleNameTC T_pub_logical_false = null,
@staffChosenNameTC T_pub_logical_false = null,
@contactsSurnameTC T_pub_logical_false = null,
@contactsForenameTC T_pub_logical_false = null,
@contactsMiddleNameTC T_pub_logical_false = null,
@contactsChosenNameTC T_pub_logical_false = null,
@agentsSurnameTC T_pub_logical_false = null,
@agentsForenameTC T_pub_logical_false = null,
@agentsMiddleNameTC T_pub_logical_false = null,
@agentsChosenNameTC T_pub_logical_false = null
)
--with encryption
as
/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Results : No output... */
/* Formats names to Type Case (Microsoft standard) or Upper case. */
/*--------------------------------------------------------------------------------------------------------------*/

/*--------------------------------------------------------------------------------------------------------------*/
/* */
/* Format : Transact-SQL */
/* Object : sims.sta_pix_FormatNames */
/* */
/* Purpose : Format peoples names */
/* Comments : */
/* */
/* Author : T Field */
/* Date : Jun 2004 */
/* */
/*--------------------------------------------------------------------------------------------------------------*/

--print 'sims.sta_pix_FormatNames'
set nocount on

--
--- declare system variables
--

declare @errcode int
, @retcode int
, @rowcount int

--
--- initialise system variables
--

select @errcode = 0
, @retcode = 0
, @rowcount = 0

--
--- declare local variables
--

-- Temporary table to store all of the case changes for each person.
declare @tempTable table
(person_id int,
surname varchar(60),
legal_surname varchar(60),
forename varchar(60),
chosen_forename varchar(60),
midname varchar(60),
student char(1),
staff char(1),
contact char(1),
agent char(1))


-- Get the roles out of the database and they're "role_bit" used to identify them.
declare @Applicant_role_bit T_pub_tinyint
select @Applicant_role_bit = role_bit
from sims.sims_person_type
where description = 'Applicant'

declare @Student_role_bit T_pub_tinyint
select @Student_role_bit = role_bit
from sims.sims_person_type
where description = 'Student'

declare @Teacher_role_bit T_pub_tinyint
select @Teacher_role_bit = role_bit
from sims.sims_person_type
where description = 'Teacher'

declare @Contact_role_bit T_pub_tinyint
select @Contact_role_bit = role_bit
from sims.sims_person_type
where description = 'Contact'

declare @Agent_role_bit T_pub_tinyint
select @Agent_role_bit = role_bit
from sims.sims_person_type
where description = 'Agent'

-- Get the people out of the database and note their roles in the four role columns
insert into @temptable
(person_id
, surname
, legal_surname
, forename
, chosen_forename
, midname
, student
, staff
, contact
, agent)
SELECT sims.sims_person.person_id
, sims.sims_person.surname
, sims.sims_person.legal_surname
, sims.sims_person.forename
, sims.sims_person.chosen_forename
, sims.sims_person.midname
-- Check if applicant exists when 0 (no) then check if student exists (we want a 1 returned if either are true)
, case sims.sims_person.aggregate_role & (power(2,@Applicant_role_bit))
when 1
then 1
else sims.sims_person.aggregate_role & (power(2,@Student_role_bit))
end
, case sims.sims_person.aggregate_role & (power(2,@Teacher_role_bit))
when 1
then 1
else 0
end
, case sims.sims_person.aggregate_role & (power(2,@Contact_role_bit))
when 1
then 1
else 0
end
, case sims.sims_person.aggregate_role & (power(2,@Agent_role_bit))
when 1
then 1
else 0
end
FROM sims.sims_person

-- Remove all records that are not to be amended
delete from @temptable where student = 0 and staff = 0 and contact = 0 and agent = 0

-- Only do these for required rows.
Update @temptable set
forename = lower(forename)
, chosen_forename = lower(chosen_forename)
, surname = lower(surname)
, legal_surname = lower(legal_surname)
, midname = lower(midname)

--
--- check for connection
--
/*exec @retcode = sims.db_p_has_connection
if @retcode > 3 return @retcode*/

--
--- main process
--

-- Loop through the letters of the alphabet looking for a space then lower case e.g. ' a'
-- replace this with space and upper case ' A'
-- Only do this when the person matches the correct role and a case change has been asked for.
Declare @counter int
Set @counter = 0
While @counter < 26
Begin

----------------------------------------------
-- Forename
----------------------------------------------
Update @temptable Set forename =
rtrim( ltrim(
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + forename, ' mc' + lower(char(65+@counter)), ' Mc' + upper(char(65+@counter))) ) )
+ ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter)))
+ ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter)))
+ ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter)))
+ ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter)))
+ ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter)))
) )
From @temptable
Where (@studentsForenameTC = 1 and student = 1) or
(@staffForenameTC = 1 and staff = 1) or
(@contactsForenameTC = 1 and contact = 1) or
(@agentsForenameTC = 1 and agent = 1)


--------------------
--chosen forname
Update @temptable Set chosen_forename =
rtrim( ltrim(
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + chosen_forename, ' mc' + lower(char(65+@counter)), ' Mc' + upper(char(65+@counter))) ) )
+ ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter)))
+ ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter)))
+ ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter)))
+ ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter)))
+ ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter)))
) )
From @temptable
Where (@studentsForenameTC = 1 and student = 1) or
(@staffForenameTC = 1 and staff = 1) or
(@contactsForenameTC = 1 and contact = 1) or
(@agentsForenameTC = 1 and agent = 1)

----------------------------------------------
-- Surname
----------------------------------------------
Update @temptable Set surname =
rtrim( ltrim(
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + surname, ' mc' + lower(char(65+@counter)), ' Mc' + upper(char(65+@counter))) ) )
+ ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter)))
+ ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter)))
+ ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter)))
+ ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter)))
+ ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter)))
) )
From @temptable
Where (@studentsSurnameTC = 1 and student = 1) or
(@staffSurnameTC = 1 and staff = 1) or
(@contactsSurnameTC = 1 and contact = 1) or
(@agentsSurnameTC = 1 and agent = 1)

--------------------------
-- legal surname
Update @temptable Set legal_surname =
rtrim( ltrim(
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + legal_surname, ' mc' + lower(char(65+@counter)), ' Mc' + upper(char(65+@counter))) ) )
+ ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter)))
+ ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter)))
+ ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter)))
+ ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter)))
+ ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter)))
) )
From @temptable
Where (@studentsSurnameTC = 1 and student = 1) or
(@staffSurnameTC = 1 and staff = 1) or
(@contactsSurnameTC = 1 and contact = 1) or
(@agentsSurnameTC = 1 and agent = 1)

----------------------------------------------
-- Middle Name
----------------------------------------------
Update @temptable Set midname =
rtrim( ltrim(
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + midname, ' mc' + lower(char(65+@counter)), ' Mc' + upper(char(65+@counter))) ) )
+ ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter)))
+ ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter)))
+ ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter)))
+ ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter)))
+ ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter)))
) )
From @temptable
Where (@studentsMiddlenameTC = 1 and student = 1) or
(@staffMiddlenameTC = 1 and staff = 1) or
(@contactsMiddlenameTC = 1 and contact = 1) or
(@agentsMiddlenameTC = 1 and agent = 1)

Set @counter = @counter + 1
End


select * from @temptable

--update sims.sims_person set


-- Surname IV etc.
-- Used for Charles iv which would become Charles IV! Not all that important but requested by analysts.
Update sims.sims_person Set surname =
rtrim( ltrim (
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + surname + ' ', ' Ix ', ' IX ') ) )
+ ' ', ' Viii ', ' VIII ')
+ ' ', ' Vii ', ' VII ')
+ ' ', ' Vi ', ' VI ')
+ ' ', ' Iv ', ' IV ')
+ ' ', ' Iii ', ' III ')
+ ' ', ' Ii ', ' II ')
) )
From sims.sims_person

Update sims.sims_person Set legal_surname =
rtrim( ltrim (
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + legal_surname + ' ', ' Ix ', ' IX ') ) )
+ ' ', ' Viii ', ' VIII ')
+ ' ', ' Vii ', ' VII ')
+ ' ', ' Vi ', ' VI ')
+ ' ', ' Iv ', ' IV ')
+ ' ', ' Iii ', ' III ')
+ ' ', ' Ii ', ' II ')
) )
From sims.sims_person

-- Append temporary table back to the sims.sims_person table! NOT DONE YET.
--update sims.sims_person set


return 0
go

exec sims.db_p_check 'sims.sta_pix_FormatNames'
go

------------------------------------------------------------------------------------------------------------------
/* UINT TESTS GO HERE
grant exec on sims.sta_pix_FormatNames to simsuser

exec sims.sta_pix_FormatNames
*/



exec sims.sta_pix_FormatNames
@studentsSurnameTC =1,
@studentsForenameTC =1,
@studentsMiddleNameTC =1,
@studentsChosenNameTC =1,
@staffSurnameTC =1,
@staffForenameTC =1,
@staffMiddleNameTC =1,
@staffChosenNameTC =1,
@contactsSurnameTC =1,
@contactsForenameTC =1,
@contactsMiddleNameTC =1,
@contactsChosenNameTC =1,
@agentsSurnameTC =1,
@agentsForenameTC =1,
@agentsMiddleNameTC =1,
@agentsChosenNameTC =1





Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/21/2004 :  12:13:00  Show Profile  Visit Seventhnight's Homepage
have you looked at the execution plan?? What areas seemed to take the most processing?

That would help me know where to make specific suggestions...


maybe make the capitalizing portion of the script a UDF, and then run a single update:

Update @tempTable
Set firstname = dbo.doCaps(firstName),
surName = dbo.doCaps(surName)...
etc




Corey
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 06/21/2004 :  13:04:40  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
How many records are you throwing into that table variable?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 06/21/2004 :  13:32:25  Show Profile
I've found awful performance on Temp tables if they do not have Primary keys (and probably indexes where appropriate)

Kristen
Go to Top of Page

Tim_Field
Yak Posting Veteran

United Kingdom
87 Posts

Posted - 06/22/2004 :  10:19:27  Show Profile
The Replace statements themselves are taking the biggest hit, I think these need to be minimised. I think just one replace would be ideal!

The table variable will hold over 4000 records.....

However, I can't write directly into the tables (e.g. multiple times) as there are change triggers on the columns. So I only want to write back once (hence the use of the table).

Code to copy the temporary table back into the main table isn't here but it's pretty simple.

Thanks, Tim
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/22/2004 :  10:24:28  Show Profile  Visit Seventhnight's Homepage
I have an interesting query I have used in the past, and it can do 9 replaces with a 8 matches in each entry on an 80000 record table, and it runs very quickly...

Maybe you should use a #tempTable instead of a @tempTable (and go ahead and index it for fun)

The replaces shouldn't be THAT slow


Corey
Go to Top of Page

Tim_Field
Yak Posting Veteran

United Kingdom
87 Posts

Posted - 06/22/2004 :  12:25:36  Show Profile
Hi All,

Ah ha... this section is taking an age. Is there anyway to replace all the mcdonalds, mcandrews etc. etc.
with McDonald McAndrew without doing the loop though the alphabet...

I think that'll speed it up lots!

I'm currently trying # too....

Thanks, Tim



Declare @counter int
Set @counter = 0
While @counter < 26
Begin

----------------------------------------------
-- Name
----------------------------------------------
Update @temptable Set names =
rtrim( ltrim(
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
Replace(' ' +
rtrim( ltrim( Replace(' ' + names, ' mc' + lower(char(65+@counter)), ' Mc' + upper(char(65+@counter))) ) )
+ ' ', '''' + lower(char(65+@counter)), '''' + upper(char(65+@counter)))
+ ' ', '-' + lower(char(65+@counter)), '-' + upper(char(65+@counter)))
+ ' ', '.' + lower(char(65+@counter)), '.' + upper(char(65+@counter)))
+ ' ', ',' + lower(char(65+@counter)), ',' + upper(char(65+@counter)))
+ ' ', ' ' + lower(char(65+@counter)), ' ' + upper(char(65+@counter)))
) )
From @temptable


Set @counter = @counter + 1
End
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 06/22/2004 :  13:11:43  Show Profile
How about adding a WHERE clause so that only appropriate rows get selected for the REPLACE? Particularly as a LIKE can use poor-mans-regular-expression which should enable you to have somehting siple-ish that catches all possible conditions.

Have your tried a PK and/or some indexes on the Temp table as yet? I'd be curious to know if they help, or not, (as you aren't really JOINing your temporary table to anything)

Kristen
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/22/2004 :  15:04:26  Show Profile  Visit Seventhnight's Homepage
I have done a bit of testing, and here is my solution so far:

quote:

Drop Table #tempTable
Create Table #tempTable (firstName nvarchar(30), LastName nvarchar(30), fPos int, lPos int)

Insert Into #tempTable
Select top 100000 FirstName = ' ' + lower(FirstName), LastName = ' ' + lower(LastName), fPos = 0, lPos = 0 From myDb.dbo.Employees

Declare @delimiters table (delim nvarchar(5))
Insert Into @delimiters Select delim = ' '
Insert Into @delimiters Select delim = ','
Insert Into @delimiters Select delim = '-'
Insert Into @delimiters Select delim = '.'
Insert Into @delimiters Select delim = ''''
Insert Into @delimiters Select delim = '/'
Insert Into @delimiters Select delim = '\'
Insert Into @delimiters Select delim = ' mc'
--Insert Into @delimiters Select delim = ' mac'

Declare @curDelim nvarchar(5),
@len int

While exists(Select * From @delimiters)
Begin

Select top 1 @curDelim = delim From @delimiters
Select @len = case when len(@curDelim) = 0 then 1 else len(@curDelim) end

Select @curDelim, @len

While exists(Select * From #tempTable Where charindex(@curDelim,firstName,fPos+1)>0)
Begin
Update #temptable
Set
FirstName = case when charindex(@curDelim,FirstName,fPos+1)<len(FirstName)-@len then left(FirstName,charindex(@curDelim,FirstName,fPos+1)+@len-1) + upper(substring(FirstName,charindex(@curDelim,FirstName,fPos+1)+1,1)) + Right(FirstName,len(FirstName) - charindex(@curDelim,FirstName,fPos+1)-@len) else firstName end,
fPos = charindex(@curDelim,firstName,fPos+1)
From #temptable
Where charindex(@curDelim,firstName,fPos+1)>0
End

While exists(Select * From #tempTable Where charindex(@curDelim,lastName,lPos+1)>0)
Begin
if (@curDelim=' mc')
Begin
Select *,
LastName = case when charindex(@curDelim,LastName,lPos+1)<len(LastName)-@len then left(LastName,charindex(@curDelim,LastName,lPos+1)+@len-1) + upper(substring(LastName,charindex(@curDelim,LastName,lPos+1)+@len,1)) + Right(LastName,len(LastName) - charindex(@curDelim,LastName,lPos+1)-@len) else LastName end,
lPos = charindex(@curDelim,lastName,lPos+1)
From #temptable
Where charindex(@curDelim,lastName,lPos+1)>0
End

Update #temptable
Set
LastName = case when charindex(@curDelim,LastName,lPos+1)<len(LastName)-@len then left(LastName,charindex(@curDelim,LastName,lPos+1)+@len-1) + upper(substring(LastName,charindex(@curDelim,LastName,lPos+1)+@len,1)) + Right(LastName,len(LastName) - charindex(@curDelim,LastName,lPos+1)-@len) else LastName end,
lPos = charindex(@curDelim,lastName,lPos+1)
From #temptable
Where charindex(@curDelim,lastName,lPos+1)>0
End

Delete From @delimiters Where delim = @curDelim

Update #tempTable Set fPos = 0, lPos = 0
End

Update #temptable
Set
FirstName =
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(FirstName, ' Ix ', ' IX ')
, ' Viii ', ' VIII ')
, ' Vii ', ' VII ')
, ' Vi ', ' VI ')
, ' Iv ', ' IV ')
, ' Iii ', ' III ')
, ' Ii ', ' II '),
lastName =
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(
Replace(LastName, ' Ix ', ' IX ')
, ' Viii ', ' VIII ')
, ' Vii ', ' VII ')
, ' Vi ', ' VI ')
, ' Iv ', ' IV ')
, ' Iii ', ' III ')
, ' Ii ', ' II ')
From #TempTable

Select top 100 * From #TempTable Where not (LastName like '% %' or FirstName like '% %')
Union All Select top 100 * From #TempTable Where LastName like '% %' or FirstName like '% %'
Union All Select top 100 * From #TempTable Where LastName like '%-%' or FirstName like '%-%'
Union All Select top 100 * From #TempTable Where LastName like '%,%' or FirstName like '%,%'
Union All Select top 100 * From #TempTable Where LastName like '%.%' or FirstName like '%.%'
Union All Select top 100 * From #TempTable Where LastName like '%/%' or FirstName like '%/%'
Union All Select top 100 * From #TempTable Where LastName like '%\%' or FirstName like '%\%'
Union All Select top 100 * From #TempTable Where LastName like '% Mc%' or FirstName like '% Mc%'
--Union All Select top 100 * From #TempTable Where LastName like '% Mac%' or FirstName like '% Mac%'




Now, I had some trouble with the 'Mac' people as you don't want to always update the next character.

I have run this on the following sets:
100,000 records: 25 sec
10,000 records: 3 sec
1,000 records: instant

Hope this helps !!!


Corey
Go to Top of Page

Tim_Field
Yak Posting Veteran

United Kingdom
87 Posts

Posted - 06/23/2004 :  07:18:51  Show Profile
Hi again,

Seventhnight..... I have implemented the proc... FAR too slow... 7 seconds for 20000 rows.

Can you see anything wrong with what I've written??

Thanks, Tim

------------------------------------------------------------
Part of script...
------------------------------------------------------------

Drop Table #tempTable
-- Temporary table to store all of the case changes for each person.


--declare #tempTable table
Create Table #tempTable
(person_id int
, names varchar(60)
, namepart varchar(60)
, aggregate_role varchar(50)
, fPos int
, lPos int)

--, student char(1)
--, staff char(1)
--, contact char(1)
--, agent char(1))


-- Get the people out of the database and note their roles in the four role columns
insert into #tempTable
(person_id
, names
, namepart
, aggregate_role
, fPos
, lPos)
select
sims.sims_person.person_id
, sims.sims_person.surname
, 'surname'
, aggregate_role
, 0
, 0
from sims.sims_person

insert into #tempTable
(person_id
, names
, namepart
, aggregate_role
, fPos
, lPos)
select
sims.sims_person.person_id
, sims.sims_person.legal_surname
, 'legal_surname'
, aggregate_role
, 0
, 0
from sims.sims_person

insert into #tempTable
(person_id
, names
, namepart
, aggregate_role
, fPos
, lPos)
select
sims.sims_person.person_id
, sims.sims_person.forename
, 'forename'
, aggregate_role
, 0
, 0
from sims.sims_person

insert into #tempTable
(person_id
, names
, namepart
, aggregate_role
, fPos
, lPos)
select
sims.sims_person.person_id
, sims.sims_person.chosen_forename
, 'chosen_forename'
, aggregate_role
, 0
, 0
from sims.sims_person

insert into #tempTable
(person_id
, names
, namepart
, aggregate_role
, fPos
, lPos)
select
sims.sims_person.person_id
, sims.sims_person.midname
, 'midname'
, aggregate_role
, 0
, 0
from sims.sims_person


-- Get the roles out of the database and they're "role_bit" used to identify them.
declare @Applicant_role_bit T_pub_tinyint
select @Applicant_role_bit = role_bit
from sims.sims_person_type
where description = 'Applicant'

declare @Student_role_bit T_pub_tinyint
select @Student_role_bit = role_bit
from sims.sims_person_type
where description = 'Student'

declare @Teacher_role_bit T_pub_tinyint
select @Teacher_role_bit = role_bit
from sims.sims_person_type
where description = 'Teacher'

declare @Contact_role_bit T_pub_tinyint
select @Contact_role_bit = role_bit
from sims.sims_person_type
where description = 'Contact'

declare @Agent_role_bit T_pub_tinyint
select @Agent_role_bit = role_bit
from sims.sims_person_type
where description = 'Agent'

---------------------------------------------------------------------------------------
-- Inner join the roles calculated with the person table to write back.
---------------------------------------------------------------------------------------

/*
, sims.sims_person.legal_surname
, sims.sims_person.forename
, sims.sims_person.chosen_forename
, sims.sims_person.midname
-- Check if applicant exists when 0 (no) then check if student exists (we want a 1 returned if either are true)
, case sims.sims_person.aggregate_role & (power(2,@Applicant_role_bit))
when 1
then 1
else sims.sims_person.aggregate_role & (power(2,@Student_role_bit))
end
, case sims.sims_person.aggregate_role & (power(2,@Teacher_role_bit))
when 1
then 1
else 0
end
, case sims.sims_person.aggregate_role & (power(2,@Contact_role_bit))
when 1
then 1
else 0
end
, case sims.sims_person.aggregate_role & (power(2,@Agent_role_bit))
when 1
then 1
else 0
end
FROM sims.sims_person
*/

--, student
--, staff
--, contact
--, agent)


--
--- check for connection
--
/*exec @retcode = sims.db_p_has_connection
if @retcode > 3 return @retcode*/

--
--- main process
--


---------------------------------------------------------------------------------------
-- Current loop is too slow!!!
---------------------------------------------------------------------------------------
-- UPPER (SUBSTRING (surname, 1, 1))
-- + LOWER(SUBSTRING(surname, 2, (len(surname) - 1) ))



-- Table containing delimiters.
Declare @delimiters table (delim nvarchar(5))
Insert Into @delimiters Select delim = ' '
Insert Into @delimiters Select delim = ','
Insert Into @delimiters Select delim = '-'
Insert Into @delimiters Select delim = '.'
Insert Into @delimiters Select delim = ''''
Insert Into @delimiters Select delim = '/'
Insert Into @delimiters Select delim = '\'
Insert Into @delimiters Select delim = ' mc'

--update #tempTable
-- Set names = upper(substring (names, 1, 1)) + lower(substring(names, 2, (len(names) - 1) ))

-- Delimiter variables.
Declare @curDelim nvarchar(5)
, @len int

While exists(Select * From @delimiters)
Begin

-- Get the top delimiter and store it's length to @len variable.
Select top 1 @curDelim = delim From @delimiters
Select @len = case
when len(@curDelim) = 0
then 1
else len(@curDelim)
end
Select @curDelim, @len

While exists(Select * From #tempTable Where charindex(@curDelim,names,fPos+1)>0)
Begin
Update #tempTable
Set
names = case
-- Look for the character in the string.
when charindex(@curDelim,names,fPos+1)<len(names)-@len
-- if found then upper it.
then left(names,charindex(@curDelim,names,fPos+1)+@len-1) + upper(substring(names,charindex(@curDelim,names,fPos+1)+1,1))
+ Right(names,len(names) - charindex(@curDelim,names,fPos+1)-@len)
-- otherwise leave alone
else names
end
-- update the character position.
,fPos = charindex(@curDelim,names,fPos+1)
From #tempTable
Where charindex(@curDelim,names,fPos+1)>0
End

-- Remove the current delimiter
Delete From @delimiters Where delim = @curDelim

-- Reset the possition counters to 0
Update #tempTable Set fPos = 0, lPos = 0

End

select * from #tempTable
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/23/2004 :  08:12:25  Show Profile  Visit Seventhnight's Homepage
What speed are you shooting for??

it is 20,000 records after all



Corey
Go to Top of Page

Wanderer
Flowing Fount of Yak Knowledge

United Kingdom
1168 Posts

Posted - 06/23/2004 :  09:13:32  Show Profile
Just a question: Are you redoing all the ones you've done before, each time? I.e. your select's from sims.sims_person, has no where criteria.

Is this sims table a staging table that is cleaned every time, or is it updated, based on the work done? If it is updated, perhaps you can have a "capitalized_ind" column, and set it to 1. Wheneven a change is made to the surname, and the reset capitalization_ind to 0. Whenever an insert is done, default capitalization_ind to 0. Then change the select tables to select those rows where capitalization_ind is 0.

Just a thought...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000