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)
You must be logged in to reply to this topic. Login to reply