Spirit, i like the sound of where your going, but i didnt even know profiler existed until about 3 days ago :\. if you wouldnt mind walking me though some stuff that would be great.Jezemine, i know the tables need a little re-designing, but here is the DDL of the largest of the 4 tables. they are all laid out the same, only difference is the qty of data in the table./****** Object: Database Test Script Date: 5/25/2007 11:34:56 AM ******/CREATE DATABASE [Test] ON (NAME = N'Test_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Test_Data.MDF' , SIZE = 275975, FILEGROWTH = 10%) LOG ON (NAME = N'Test_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\Test_Log.LDF' , SIZE = 105075, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_ASGOuse [Test]GO/****** Object: Table [dbo].[mailable] Script Date: 5/25/2007 11:34:56 AM ******/CREATE TABLE [dbo].[mailable] ( [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 , [citystate] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [countystate] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [areacode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]GO CREATE CLUSTERED INDEX [mailable1] ON [dbo].[mailable]([child_pres], [state_abbrv], [address1], [wealth_rating], [homeowner], [est_inc], [per1_age], [per1_ms], [hm_purprice], [hm_purdate], [hm_year_build], [pres_cc], [oo_mtg_amnt], [oo_mtg_lender_name], [oo_mtg_rate_t], [oo_mtg_loan_t]) ON [PRIMARY]GO CREATE INDEX [mailable7] ON [dbo].[mailable]([state_abbrv], [wealth_rating], [homeowner], [est_inc], [per1_age], [county_name], [citystate], [areacode], [countystate], [city], [per1_lname], [per1_ms], [child_pres]) ON [PRIMARY]GO
Here is the Stored procedureCREATE PROCEDURE search_orders_2 @lowage nvarchar(10) = null, @highage nvarchar(10) = null, @highwealth nvarchar(10) = null, @lowwealth nvarchar(10) = null, @highpurprice nvarchar(10) = null, @lowpurprice nvarchar(10) = null, @lowmtgamt nvarchar(10) = null, @highmtgamt nvarchar(10) = null, @lowincome varchar(3) = null, @highincome varchar(3) = null, @marriedstat varchar(3) = null, @lowyearbuild varchar(4) = null, @highyearbuild varchar(4) = null, @mtgpresstat varchar(3) = null, @lenderpresstat varchar(3) = null, @ratets varchar(3) = null, @loants varchar(10) = null, @childstat varchar(3) = null, @homeownerstat varchar(10) = null, @lowpurdate varchar(8) = null, @highpurdate varchar(8) = null, @addresstype varchar(3) = null, @SPSir varchar(3) = null, @debttype varchar(3) = null, @dwelltype varchar(3) = null, @zipselect nvarchar(4000) = null, @acselect nvarchar(4000) = null, @stselect nvarchar(4000) = null, @cityselect nvarchar(4000) = null, @counselect nvarchar(4000) = null, @tablename nvarchar(2) = null, @orderby nvarchar(2) = null, @Zipcode char(11) = null, @miles float = null,-- as @debug bit = 0 AS DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000), @highlatitude float, @lowlatitude float, @HighLongitude float, @LowLongitude float, @StartLatitude float, @StartLongitude float, @LatitudeRange float, @LongitudeRange floatif @miles>0 SELECT @StartLatitude = lat, @StartLongitude = lng FROM zipinfo2 WHERE zip = @Zipcode Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60) Set @LatitudeRange = @Miles / 69.045454545454545454545454545455 Set @LowLatitude = @StartLatitude - @LatitudeRange Set @HighLatitude = @StartLatitude + @LatitudeRange Set @LowLongitude = @StartLongitude - @LongitudeRange Set @HighLongitude = @StartLongitude + @LongitudeRangeset @sql = '' if @orderby is null set @orderby='1'if @tablename is null set @tablename='1'if @spsir='1' select @sql = @sql+ 'SELECT ' +case @orderby when 1 then 'o.state_abbrv, count(o.state_abbrv) ' when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) ' when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) ' when 4 then 'o.zip, count(o.zip) ' end + 'as kount FROM dbo.' + case @tablename when 1 then 'mortgage' when 2 then 'homeowner' when 3 then 'condnc' when 4 then 'mailable' end +' o with (nolock) left join dbo.Spanish s on o.per1_lname = s.lname'if @spsir='2' select @sql = @sql+ 'SELECT distinct ' +case @orderby when 1 then 'o.state_abbrv, count(o.state_abbrv) ' when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) ' when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) ' when 4 then 'o.zip, count(o.zip) ' end + 'as kount FROM dbo.' + case @tablename when 1 then 'mortgage' when 2 then 'homeowner' when 3 then 'condnc' when 4 then 'mailable' End +' o with (nolock) inner join dbo.Spanish s on o.per1_lname = s.lname'if @spsir='ALL' or @spsir is null SELECT @sql = 'SELECT ' +case @orderby when 1 then 'o.state_abbrv, count(o.state_abbrv) ' when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) ' when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) ' when 4 then 'o.zip, count(o.zip) ' end + 'as kount FROM dbo.' + case @tablename when 1 then 'mortgage' when 2 then 'homeowner' when 3 then 'condnc' when 4 then 'mailable' End + ' o with (nolock)'select @sql=@sql+' WHERE 1 = 1'if @spsir='1' select @sql = @sql+ ' and s.lname is null' IF @lowage<>0 and @lowage is not null SELECT @sql = @sql + ' AND o.per1_age >= @xlowage' IF @highage<>0 and @highage IS NOT null SELECT @sql = @sql + ' AND o.per1_age <= @xhighage' IF @lowincome<>'DF' and @lowincome IS NOT NULL SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome' IF @highincome<>'DF' and @highincome IS NOT NULL SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome' IF @highwealth<>0 and @highwealth IS NOT NULL SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth' IF @lowwealth<>0 and @lowwealth IS NOT NULL SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth' IF @highpurprice<>0 and @highpurprice IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice' IF @lowpurprice<>0 and @lowpurprice IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice' IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild' IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild' IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'IF @highmtgamt<>0 and @highmtgamt IS NOT NULL SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt' IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate' IF @highpurdate<>'DF' and @highpurdate IS NOT NULL SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate' IF @marriedstat in ('M','S') SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat' IF @ratets in ('V','F') SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets' IF @homeownerstat in ('H','R') SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'IF @childstat in ('Y','N') SELECT @sql = @sql + ' AND o.child_pres = @xchildstat' IF @lenderpresstat ='Y' SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>'' '''IF @lenderpresstat ='N' SELECT @sql = @sql + ' AND o.oo_mtg_lender_name='' ''' IF @mtgpresstat='Y' SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'IF @mtgpresstat='N' SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'IF @loants<>'DF' and @loants IS NOT NULL SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'if @dwelltype='S' SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''if @dwelltype='M' SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''if @DebtType='1' select @sql = @sql + ' and o.pres_cc=''y'''if @DebtType='2' select @sql = @sql + ' and o.pres_perm_cc=''y'''if @addresstype in ('1','3') select @sql = @sql + ' and o.address1<>'' '''if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF' or @miles<>0 SELECT @sql = @sql + ' and (1=2'IF @acselect<>'DF' and @acselect IS NOT NULL SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )' IF @stselect<>'DF' and @stselect IS NOT NULL SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )' IF @cityselect<>'DF' and @cityselect IS NOT NULL SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )' IF @counselect<>'DF' and @counselect IS NOT NULL SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'IF @zipselect<>'DF' and @zipselect IS NOT NULL SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'if @miles>0 select @sql = @sql+ ' or o.zip in (SELECT z.zip FROM dbo.zipinfo2 z WHERE (z.Lat <= @HighLatitude AND z.Lat >= @LowLatitude) AND (z.Lng <= @HighLongitude AND z.Lng >= @LowLongitude))'if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF' or @miles<>0 SELECT @sql = @sql + ')'select @sql=@sql +case @orderby when 1 then ' group by o.state_abbrv order by o.state_abbrv ' when 2 then ' group by o.state_abbrv,o.county_name order by o.state_abbrv,o.county_name ' when 3 then ' group by o.state_abbrv,o.city order by o.state_abbrv,o.city ' when 4 then ' group by o.zip order by o.zip 'endIF @debug = 1 PRINT @sql SELECT @paramlist = '@xlowage int , @xhighage int , @xlowincome nvarchar(4000) , @xhighincome nvarchar(4000) , @xmarriedstat nvarchar(4000) , @xhighwealth int , @xlowwealth int , @xhighpurprice int , @xlowpurprice int , @xlowyearbuild nvarchar(4000) , @xhighyearbuild nvarchar(4000) , @xlowmtgamt int , @xhighmtgamt int , @xmtgpresstat nvarchar(4000) , @xlenderpresstat nvarchar(4000) , @xratets nvarchar(4000) , @xloants nvarchar(4000) , @xchildstat nvarchar(4000) , @xhomeownerstat nvarchar(4000) , @xlowpurdate nvarchar(4000) , @xhighpurdate nvarchar(4000) , @xzipselect nvarchar(4000) , @xacselect nvarchar(4000) , @xstselect nvarchar(4000) , @xcityselect nvarchar(4000) , @xcounselect nvarchar(4000) , @highlatitude float, @lowlatitude float, @HighLongitude float, @LowLongitude float, @StartLatitude float, @StartLongitude float, @LatitudeRange float, @LongitudeRange float' EXEC sp_executesql @sql, @paramlist, @lowage , @highage , @lowincome , @highincome , @marriedstat , @highwealth , @lowwealth , @highpurprice , @lowpurprice , @lowyearbuild , @highyearbuild , @lowmtgamt , @highmtgamt , @mtgpresstat , @lenderpresstat , @ratets , @loants , @childstat , @homeownerstat , @lowpurdate , @highpurdate , @zipselect , @acselect , @stselect , @cityselect , @counselect , @highlatitude, @lowlatitude , @HighLongitude, @LowLongitude, @StartLatitude, @StartLongitude, @LatitudeRange, @LongitudeRangeGOi have a workload.sql that i was using to try to opitmize quries, but i dont think it was working. (it was about 2 day's worth of saved quiries)would you mind fetching the workload.sql and text file for bulk insert off my ftp? 
Indexing Help
Posted - 2007-05-25 : 14:09:02
_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp