May 12, 2008 at 4:35 am
Please help with this.
Im trying to search a table that contains addresses for a specific suburbs and postal codes. The address table has 30,000,000 addresses in it. After applying basic criteria (ie:Eliminating invalids etc) and in an effort to increase speed I have created a physical temp table, reducing the records and leaving me with approx 12,000,000. I have also created a clustered index on the temp table, each addressline has been indexed as well.
Using a table variable i insert each suburb and code in. I then use a coursor to search the temp address table using the table variable.
This ran for more than 14 hours and still wasnt done.
I need to get this running faster.
See example below.
--Load the physical temp table with addresses
INSERT INTO Temp_Address (Addressline1, Addressline2, Addressline3)
SELECT
Addr1, Addr2, Addr3
FROM
Database.dbo.AddressSource
WHERE
(Basic criteria used).
-- Load table variable
DECLARE @Tbl TABLE (
ID int identity (1,1) PRIMARY KEY,
Area varchar (100),
Code varchar (50))
INSERT INTO @Tbl (Area, Code)
VALUES ('Area1', 'Code1')
INSERT INTO @Tbl (Area, Code)
VALUES ('Area2', 'Code2')
INSERT INTO @Tbl (Area, Code)
VALUES ('Area3', 'Code3')
--Fetch addresses that have the area or code in it.
DECLARE @Area varchar (100)
DECLARE @Code varchar (50)
DECLARE
C CURSOR FOR
SELECT
Area, Code
FROM
@Tbl
OPEN C
FETCH NEXT FROM C
INTO
@Area, @Code
WHILE
@@Fetch_Status = 0
BEGIN
INSERT INTO AD_Final (Address1, Address2, Address3)
SELECT
Addressline1, Addressline2, Addressline3
FROM
Temp_Address
WHERE
(CHARINDEX(@Area,Addr1)>=1
OR
CHARINDEX(@Area,Addr2)>=1
OR
CHARINDEX(@Area,Addr3)>=1)
FETCH NEXT FROM C
INTO
@Area, @Code
END
CLOSE C
DEALLOCATE C
Thanx.
May 12, 2008 at 5:50 am
Try this
INSERT AD_Final
(
Address1,
Address2,
Address3
)
SELECT s.Addr1,
s.Addr2,
s.Addr3
FROM Database.dbo.AddressSource AS s
WHERE {Basic criteria used}
CROSS JOIN @Tbl AS f
WHERE s.Addr1 LIKE '%' + f.Area + '%'
OR s.Addr2 LIKE '%' + f.Area + '%'
OR s.Addr3 LIKE '%' + f.Area + '%'
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 5:51 am
Using CHARINDEX function prohibits you from using the index.
N 56°04'39.16"
E 12°55'05.25"
May 12, 2008 at 5:58 am
Thank you very much for your reply, let me check, ill get back to you.
May 12, 2008 at 10:57 pm
To optimise this query, rewrite the cursor as a join, perhaps utilizing a second temp table, and ensure an index is employed for the join criteria - for example by using a like clause. Make sure all columns involved in the join are indexed and any columns retrieved are part of a covering index.
May 13, 2008 at 12:49 am
In my initial query I used a join as part of the cursor, all joined fields had indexes on, doing that took even longer. My posted example was an improvement but still not fast enough. Peso's suggestion works the best at this point. It ran for 2 hours, from 14 to 2, I’m not complaining. Thanx for the suggestion anyway.
May 13, 2008 at 3:40 am
7 times faster?
Not that bad for a LIKE '%' + Col1 + '%' query.
N 56°04'39.16"
E 12°55'05.25"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply