# Character Iteration script is not very efficient

• Hi SQL Server Central.  The following script takes at least a couple of hours to run and I'm wondering if anyone has any ideas on how I can improve on the efficiency.  It produces all capital letters between AAAAA and ZZZZZ which should be around 11 million rows.

Thanks,

Terry

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

CREATE TABLE [dbo].[tblitAAAAA] (

[itAAAAA] [char] (5) NOT NULL

) ON [PRIMARY]

GO

declare @position tinyint, @string char(5)

set @string = 'AAAAA'

set @position = 5

while ASCII(SUBSTRING(@string, @position-4, 1))<91 begin  --90 is ASCII for Z

while ASCII(SUBSTRING(@string, @position-3, 1))<91 begin

while ASCII(SUBSTRING(@string, @position-2, 1))<91 begin

while ASCII(SUBSTRING(@string, @position-1, 1))<91 begin

while ASCII(SUBSTRING(@string, @position, 1))<91 begin

insert tblitAAAAA (itAAAAA)

values (@string)

set @string=CHAR(ASCII(SUBSTRING(@string, @position-4, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-3, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-2, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-1, 1)))+char(ASCII(SUBSTRING(@string, @position, 1))+1)

end

set @string=CHAR(ASCII(SUBSTRING(@string, @position-4, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-3, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-2, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-1, 1))+1)+'A'

end

set @string=CHAR(ASCII(SUBSTRING(@string, @position-4, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-3, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-2, 1))+1)+'A'+'A'

end

set @string=CHAR(ASCII(SUBSTRING(@string, @position-4, 1)))+CHAR(ASCII(SUBSTRING(@string, @position-3, 1))+1)+'A'+'A'+'A'

end

set @string=CHAR(ASCII(SUBSTRING(@string, @position-4, 1))+1)+'A'+'A'+'A'+'A'

end

• This generates 11881376 rows in 71 seconds (without sorting) on a slow server

Select dtA.Frst + dtB.Sec + dtC.ThD + dtD.Fourth + dtE.Fifth As GenList FROM

(Select CHAR(PkNumber) as Frst from dbo.Numbers where PkNumber between 65 and 90) dtA

cross join

(Select CHAR(PkNumber) as Sec from dbo.Numbers where PkNumber between 65 and 90) dtB

cross join

(Select CHAR(PkNumber) as Thd from dbo.Numbers where PkNumber between 65 and 90) dtC

cross join

(Select CHAR(PkNumber) as Fourth from dbo.Numbers where PkNumber between 65 and 90) dtD

cross join

(Select CHAR(PkNumber) as Fifth from dbo.Numbers where PkNumber between 65 and 90) dtE

--Order by GenList --the sort accounts for 96% of the work load...

here's the script for the numbers table :

IF Object_id('Numbers') > 0

DROP TABLE dbo.Numbers

GO

CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)

GO

INSERT INTO dbo.Numbers (dude)

SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80

GO

ALTER TABLE dbo.Numbers

DROP COLUMN dude

GO

--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers

--1, 8000

GO

--DROP TABLE dbo.Numbers

• Your current statement performs 11,881,376 seperate insert statements and for each insert, space management and transaction log writes must be performed. A single statement that inserts all of the rows at once will probable be faster.

For 4 characters, this runs in less than one minute for 456,976 rows:

insert tblitAAAAA (itAAAAA)

select char(p1.seq) + char(p2.seq) + char(p3.seq) + char(p4.seq)

from sequences as P1

,sequences as P2

,sequences as P3

,sequences as P4

wherep1.seq between 65 and 90

and p2.seq between 65 and 90

and p3.seq between 65 and 90

and p4.seq between 65 and 90

Since you probably have some resource limits, such as disk space for the transaction log, you might want to run the sql statement 26 times with the last character value set in the WHILE loop such as:

declare @l integer

set @l = 64

while @l < 90

begin

set @l = @l + 1

insert tblitAAAAA (itAAAAA)

select char(p1.seq) + char(p2.seq) + char(p3.seq) + char(p4.seq)

+ char(@l)

from sequences as P1

,sequences as P2

,sequences as P3

,sequences as P4

wherep1.seq between 65 and 90

and p2.seq between 65 and 90

and p3.seq between 65 and 90

and p4.seq between 65 and 90

END

The sequences table:

-- drop table Sequences

set nocount on

set xact_abort on

create table Sequences

( Seq smallint not null

, constraint Sequences_PK primary key (Seq) )

-- Create a new table to turn columns into rows or rows into columns

declare @SmallIntMaxinteger

,@SeqMaxinteger

set@SmallIntMax= power(2,15) - 1

-- Populate the Sequences table:

-- zero is also useful.

Insert into Sequences (Seq) values (0)

Insert into Sequences (Seq) values (1)

-- Now repeat the following insert 10 times to get 1024 sequence Sequences

set @SeqMax = 1

while @SeqMax < @SmallIntMax

begin

Insert into Sequences

SELECT NewSeq

FROM (select Seq + @SeqMax + 1 AS NewSeq

from Sequences

) as S

Order by NewSeq

select @SeqMax = max(Seq) from Sequences

end

go

select 'Largest Seq is ' , max(Seq) from Sequences

-- Check for gaps

select top 1 * from sequences p

where seq < 32767

and not exists

(select 1 from sequences as n where n.seq = p.seq + 1 )

-- Do not rebuild the index !!!

go

SQL = Scarcely Qualifies as a Language

• just ran some more test inserting in a temp table

--17576 rows in 11 seconds

--456976 rows in 43 seconds

ran out of space in tempdb for the final pass (after almost 9 minutes which should be about 50% done)

• if resources allow it use SELECT INTO instead of insert for speed

* Noel

• Good point... assuming you can afford a lock on the system tables for a few minutes.

• I don't think this type of operation is meant to be performed a lot of times of very often that's why I thought about select into

* Noel

• Obviously ! Here are the results on a slow server (in production) :

Select TOP 100 PERCENT

dtA.Frst

+ dtB.Sec

+ dtC.ThD

+ dtD.Fourth

+ dtE.Fifth

As GenList

INTO dbo.TestInsertInto

FROM

(Select CHAR(PkNumber) as Frst from dbo.Numbers where PkNumber between 65 and 90) dtA

cross join

(Select CHAR(PkNumber) as Sec from dbo.Numbers where PkNumber between 65 and 90) dtB

cross join

(Select CHAR(PkNumber) as Thd from dbo.Numbers where PkNumber between 65 and 90) dtC

cross join

(Select CHAR(PkNumber) as Fourth from dbo.Numbers where PkNumber between 65 and 90) dtD

cross join

(Select CHAR(PkNumber) as Fifth from dbo.Numbers where PkNumber between 65 and 90) dtE

Order by GenList --the sort accounts for 96% of the work load...

GO

DROP TABLE TestInsertInto

--17576 rows in 1 seconds

--456976 rows in 4 seconds

--11881376 rows in 173 seconds

• Thanks to all who answered my question here!

Much appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)