Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Indexing Issues

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-15 : 21:35:15
Okay, so first off, here is a sample query i'm using:


SELECT o.state_abbrv, count(o.state_abbrv) as kount FROM   dbo.mortgage o WHERE  1 = 1 

and per1_age>=20
and wealth_rating>=1 
and hm_purprice>=100 --6 sec / 3 sec
AND oo_mtg_amnt >= 100 
and est_inc >= 'B'
and per1_ms='M'
and hm_year_build>='1905'
and oo_mtg_lender_name<>' '
and oo_mtg_rate_t in ('f','v')
and oo_mtg_loan_t in ('c','f')
and hm_purdate>='20000101'
and child_pres='y'
and zip in (85302,85029) 
and state_abbrv in ('az')


and rtrim(city)+' '+state_abbrv in ('glendale az','phoenix az') 
and rtrim(county_name)+' '+state_abbrv in ('maricopa az')
and substring(phone,1,3) in ('602','623')


group by o.state_abbrv ORDER BY o.state_abbrv


i'm trying to fine tune the database to come back with quries in less then 30 seconds. EVERY query ran will be a count.

i've managed to fine tune it to the point where anything above the rtrim(city) comes back in about 3-7 seconds. my problem is everything below that. i cant seem to get a query to respond fast enough, any recommendations? i've tried pluging the whole query into the index tuning wizard and it gives me nothing.

here is the database layout: 


CREATE TABLE [dbo].[mortgage] (
	[fips_state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[state_abbrv] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[zip_four] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[del_point] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[car_rte] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[city_abbrv] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[city] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_house_num] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_pre_dir] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_st_name] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_st_suff] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_post_dir] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_unit_des] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[addr_unit_desnum] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[address1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[fips_cnty] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[county_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[census_tract] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[census_block] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lattitude] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[longitude] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[fips_ispsa] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[wealth_rating] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[time_zone] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phone] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[homeowner] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[est_inc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_fname] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_mi] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_lname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_title] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_dob] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_ageconf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_age] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_msconf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per1_ms] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_fname] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_mi] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_lname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_title] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_dob] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_ageconf] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[per2_age] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_pres] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_0_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_0_3_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_4_6] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_4_6_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_7_9] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_7_9_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_10_12] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_10_12_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_13_18] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[child_13_18_gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[religious_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[political_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[health_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[general_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[hm_purprice] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[hm_purdate] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[hm_year_build] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[donate_env] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[char_contrib] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pres_cc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pres_perm_cc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_mtg_amnt] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_mtg_lender_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_mtg_rate] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_mtg_rate_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_mtg_loan_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[dnc] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_refi_deed_date] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_refi_amnt] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_refi_lender_name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_refi_rate_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_refi_month_term] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[oo_refi_loan_t] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

 CREATE  INDEX [mortgage1] ON [dbo].[mortgage]([oo_mtg_rate_t], [state_abbrv], [wealth_rating], [est_inc], [per1_age], [per1_ms], [hm_purprice], [hm_purdate], [hm_year_build], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_loan_t]) ON [PRIMARY]
GO

 CREATE  INDEX [mortgage11] ON [dbo].[mortgage]([oo_mtg_rate_t], [state_abbrv], [zip], [wealth_rating], [phone], [est_inc], [per1_age], [per1_ms], [hm_purprice], [hm_purdate], [hm_year_build], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_loan_t]) ON [PRIMARY]
GO

 CREATE  INDEX [mortgage2] ON [dbo].[mortgage]([oo_mtg_rate_t], [state_abbrv], [wealth_rating], [phone], [est_inc], [per1_age], [per1_ms], [child_pres], [hm_purprice], [hm_purdate], [hm_year_build], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_loan_t]) ON [PRIMARY]
GO

 CREATE  INDEX [mortgage4] ON [dbo].[mortgage]([zip]) ON [PRIMARY]
GO


i assume thie issue is the substring.
any assistance would be GREAT!


[/code]

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-04-15 : 22:06:43
[code]
and rtrim(city)+' '+state_abbrv in ('glendale az','phoenix az')
and rtrim(county_name)+' '+state_abbrv in ('maricopa az')
and substring(phone,1,3) in ('602','623')
[/code]
These 3 are slowing it down. When you have function applied to the column in the where clause, you can effectively doing a table scan.
You can changing "substring(phone,1,3) in ('602','623')" to
[code]
phone like '602%'
or phone like '603%'
[/code]

KH
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-15 : 22:06:51
These lines are forcing a table scan. When you use a column in an index, SQL Server cannot use an index, so it has to scan the table.

and rtrim(city)+' '+state_abbrv in ('glendale az','phoenix az') 
and rtrim(county_name)+' '+state_abbrv in ('maricopa az')
and substring(phone,1,3) in ('602','623')


Try modifying the conditions like this:

and	city in ('glendale','phoenix')
and	county_name = 'maricopa'
and	state_abbrv = 'az'
and	( phone like '602%' or phone like '623%' )



CODO ERGO SUM
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-15 : 22:15:22
okay, the only issue is, i can't effectivly figure out a way to program a list of LIKES, and from what i've read, you cant incorporate a like with an In ()

what about just creating 3 new columns? citystate, countystate, and areacode do you guys think that would be an effective solution?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-15 : 22:29:40
[code]
declare @p table (phone_like varchar(20) not null primary key clustered)

insert into @p (phone_like)
select '602%' union all
select '623%'

select
...
from
dbo.mortgage o
inner join
@p p
on o.phone like p.phone_like
where
... other conditions ...
[/code]




CODO ERGO SUM
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-15 : 22:36:50
i'm trying to avoid using temp tables right now, i'm just starting out learning how to interface a web app with sql. i've sucessfully build the program to generate a query and pass parameters to a stored procedure, problem is, i dont know how to setup temp tables so that they dont interfere with other users connected to the server. know what i mean?

it is a solution i'm curious about, just have no CLUE where to start to look.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-15 : 23:00:22
Why do you assume that a temp table (which the code I provided is not using, by the way) would interfere with another user?




CODO ERGO SUM
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-15 : 23:30:36
because i'm a n00b :o)

so the table your code creates, is basically all in memory?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-04-15 : 23:35:49
declare @p table ...
this is a table variable. Like the temp table, it is existed locally within the connection only. Other user will not be able to access to that and hence will not interfere with other users.

KH
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-15 : 23:41:46
hummmm... that's REAL good to know. another question, is there a way to get around the argument limit in a stored procedure? i noticed it gives you an error at about 25 parameters (i am using sql server 2000 standard)
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-15 : 23:56:18
The limit is 1,024 parameters per stored procedure for all editions of SQL Server 2000.



CODO ERGO SUM
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-16 : 00:02:55
it gave me an argument error when i went past 25ish, i assume it was probably a syntex error then?
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2007-04-16 : 00:46:22
quote:
Originally posted by albertkohl

it gave me an argument error when i went past 25ish, i assume it was probably a syntex error then?


it's impossible to say for sure if you don't paste the text of the error msg here...

www.elsasoft.org
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-16 : 00:49:50
we'll i'm sure michael is certain of his answer, i had the issue the other day perhaps i'll revisit my idea now that i know it was probably a mistake on my part. well, i think i got my issues worked out, atleast for now, THANKS GUYS!
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-16 : 11:31:35
okay, so i tried the Variable table, and got the following error:

Server: Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK__@p__61F08603'. Cannot insert duplicate key in object '#60FC61CA'.
The statement has been terminated.

(0 row(s) affected)

here's my code: 


declare @p table (zip_like varchar(20) not null primary key clustered)

insert into @p (zip_like)
select '150%' union all
select '151%' union all
select '152%' union all
select '153%' union all
select '864%'


select
	'BIZ' as source,o."name",o."contact title1",rtrim(o."contact first1")+' '+rtrim(o."contact last1") as name,o."location address",
	o."location city",o."location state",o."location zip",substring(o."area code and phone",1,3)+substring(o."area code and phone",5,3)+substring(o."area code and phone",9,4),
	substring(o."sic code1",1,4)+substring(o."sic code1",6,2) as sic,o."sic name1",o."number of employees",o."sales volume",o.latitude,o.longitude,o.COUNTY
from
	biz200701 o
	inner join
	@p p
	on o.[location zip] like p.zip_like
where

	"sic code1" not in ('0721-03','1799-26','2879-98','4953-05','5169-03','7342-01','7389-41','7999-52','8221-03','8211-04','8211-06','8211-98')
	and substring("sic code1",1,2) not in (12,13,14,43,63,64,83,91,92,95,97)
	and "number of employees"  in ('1 TO 4','5 TO 9')
	and "Location type" not in ('Branch','Subsidiary')


this is for a different table, but same concept.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-04-16 : 11:39:25
It's fairly obvious that the error you posted is not from the code you posted.

You are trying to insert a duplicate value into the @p table, exactly what the error message says.





CODO ERGO SUM
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-04-16 : 11:44:36
OOOOHHH thanks, i was thinking it was talking about the index key, not the value i was inserting. i'll find the dupe and eliminate it. thank you.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site