Indexing Question - SQL Server Forums

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 Question

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-18 : 12:13:01

Okay, so I am writing a program that takes any combination of about 30 parameter passed via .ASPX and Visual Basic Code. my question is this, do have to create an index for each possible combination of parameters in order to get the query to come back REALLY fast?

or would it be maybe a better method to have the program pass Every parameter even if it would be selecting all the data and just setting up a few indexes?

What say you O' SQL Gods/Goddesses

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-18 : 12:16:33

It depends on what you columns you are searching on in your WHERE clause. could you provide some more information?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-18 : 12:21:54

well the way the procedure works, is it only adds the column to the where clause, if it's NOT set to the default, or if it's NOT null. see below


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



ALTER    PROCEDURE search_orders_1
                 
                 @lowage	int	=	null,
                 @highage	int	=	null,
                 @highwealth	int	=	null,
                 @lowwealth	int	=	null,
                 @highpurprice	int	=	null,
                 @lowpurprice	int	=	null,
                 @lowmtgamt	int	=	null,
                 @highmtgamt	int	=	null,
                 @lowincome	char(3)	=	null,
                 @highincome	char(3)	=	null,
                 @marriedstat	char(3)	=	null, 
                 @lowyearbuild	char(4)	=	null,
                 @highyearbuild	char(4)	=	null,
                 @mtgpresstat	char(3)	=	null,
                 @lenderpresstat	char(3)	=	null,
                 @ratets	char(3)	=	null,
                 @loants	char(10)	=	null,
                 @childstat	char(3)	=	null,
                 @homeownerstat	char(10)	=	null,
                 @lowpurdate	char(8)	=	null,
                 @highpurdate	char(8)	=	null,
		 @addresstype   char(3)	= 	null,
		 @SPSir		char(3)	= 	null,
		 @debttype	char(3)	= 	null,
		 @dwelltype	char(3)	= 	null,
                 @zipselect	nvarchar(4000)	=	null,
                 @acselect	nvarchar(4000)	=	null,
                 @stselect	nvarchar(4000)	=	null,
                 @cityselect	nvarchar(4000)	=	null,
                 @counselect	nvarchar(4000)	=	null,-- as
                 @debug     bit          = 0 AS                    

DECLARE @sql        nvarchar(4000),                         
        @paramlist  nvarchar(4000)                          
  
if @spsir='1'
   select @sql = @sql+ 'SELECT o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o left join dbo.Spanish on o.per1_lname = spanish.lname'

if @spsir='2'
   select @sql = @sql+ 'SELECT distinct o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o inner join dbo.Spanish on o.per1_lname = spanish.lname'

if @spsir='ALL' or @spsir is null
   SELECT @sql = 'SELECT o.state_abbrv, count(o.state_abbrv) as kount  FROM   dbo.mortgage o'

select @sql=@sql+' WHERE  1 = 1'



if @spsir='2'
   select @sql = @sql+ ' and spanish.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='1'
   SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''

if @dwelltype='2'
   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='3'
   select @sql = @sql + ' and o.address1<>'' '''





if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
   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 @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <> 'DF'
   SELECT @sql = @sql + ')'

SELECT @sql = @sql  + ' group by o.state_abbrv ORDER BY o.state_abbrv '   

IF @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)'
                                                                   
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


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I'm thinking about maybe changing it to have EVERYTHING accept a few of them just coded in. to the where with the MAX/MIN values as the default. then i'll only have to set up a few indexes if i'm right. also, is SQL Smart enough to look at a select and know that it's gunna result in EVERYTHING so it just skips it?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 12:21:57

Are there any such columns which are most frequently searched for? If yes, you can add indexes on them.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2007-04-18 : 12:25:39

No really, basically ALL of the options in the parameters are used, and i need to get the table tuned enough to return results in only a few seconds. it's a rather large table so i cant really afford much time for table scans. i have more columns in the table then what's listed on the parameters, but theses are all columns that are actively used.

mfemenel
Professor Frink

1421 Posts

Posted - 2007-04-18 : 16:19:24

I doubt the optomizer would care about most of your indexes given how the where clauses are being generated. It's going to have to do this on the fly anyway. Try clustering that column in your join statement per1_lname and perhaps the state_code that your'e returning as well.

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources