May 6, 2009 at 11:49 am
I'm troubleshooting a slow legacy query and have found part of the problem. The first 6 digits of the phone number (area code and exchange) are being used to compare between tables. There's an index on Area_Code and Exchange in the ACODE table, but because they are being concatenated, the index does not get used. As a test, I copied the ACODE table, and created a new column that's computed by concatenating AREA_CODE & EXCHANGE, and then creating an index, and that fixed the problem. Is there another way to solve it without adding a column ? Any suggestions ?
select rwa_id ,'P'
from leads_ce_work ce
where ProgramID = 'P'
and left(phonenbr, 6) not in (select area_code + exchange from common.dbo.acode)
Tables:
CREATE TABLE [dbo].[Leads_CE_work](
[RWA_ID] [decimal](10, 0) NOT NULL,
[List_ID] [int] NOT NULL,
[PolicyNbr] [char](21) NULL,
[FirstNm] [char](19) NULL,
[Surname] [varchar](50) NULL,
[LineDesc] [varchar](254) NULL,
[ConsumerId] [int] NULL,
[PhoneNbr] [varchar](10) NULL,
[StreetAddressLine1] [varchar](45) NULL,
[StreetAddressLine2] [varchar](45) NULL,
[CityCd] [char](30) NULL,
[StateCd] [char](2) NULL,
[Zip] [char](5) NULL
) ON [PRIMARY]
CREATE TABLE ACODE (
[AREA_CODE] [varchar](3) NULL,
[EXCHANGE] [varchar](3) NULL,
[TIME_ZONE] [varchar](1) NULL,
[STATE] [varchar](2) NULL) ON [PRIMARY]
May 6, 2009 at 11:56 am
Try this:
select
rwa_id,
'P'
from
leads_ce_work ce
left outer join common.dbo.acode ac
on (ac.area_code = substring(1,3,ce.phonenbr)
and ac.exchange = substring(4,3,ce.phonenbr)
where
ProgramID = 'P'
and ac.area_code is null;
May 6, 2009 at 12:28 pm
Thanks .... That looks like it should work, but I get "Argument data type int is invalid for argument 1 of substring function."
Maybe it doesn't like the columns being varchar ?? I'm fiddling around with CAST as CHAR to see if that helps.
May 6, 2009 at 12:31 pm
My fault, was looking at something else while typing. Move the last argument to the first, the first to the second, and the second to the third.
May 6, 2009 at 12:42 pm
I missed it too ... Thanks !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply