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
 Database Engin Tuning advisor, not helpful

Author  Topic 

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-27 : 04:53:02
Okay, so i'll just let you know right now, that i suck at making quries

i'm testing out a 2k5 eval copy of Sql Server (if i like, i'll migrate from 2k)
i have a db of almost 4 million names, with no indexes, and i'm trying to have
Sql tune a procedure that i wrote to see what it recommends for adjustments.
but for some reason it comes up with no results. any ideas why?

--Update
just noticed that if i type in the full query (not in the procedure form)
it recommends some indexes for me. is there any why to get recommendations
though the stored procedure below? i have about 112 quries that i want
to analyse, i could change them to actual quries if i had to, but it'd
take awhile

Current Table DDL:

USE [Test]
GO
/****** Object:  Table [dbo].[mortgage]    Script Date: 05/27/2007 01:52:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
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,
	[FullName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Source] [varchar](3) 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
SET ANSI_PADDING OFF


Stored Procedure:

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[search_orders_2]    Script Date: 05/27/2007 01:53:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================


create PROCEDURE [dbo].[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 float

if @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 + @LongitudeRange
set @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 '
end
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)	,
			@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,
		   @LongitudeRange



Query to analyse:

Exec Search_orders_2
@mtgpresstat = 'Y',
@homeownerstat = 'H',
@stselect = 'PA,RI,SC',
@addresstype = '2',
@spsir = 'ALL',
@debttype = 'ALL',
@dwelltype = 'ALL',
@tablename = '1',
@orderby = '1';

PSamsig
Constraint Violating Yak Guru

384 Posts
Posted - 2007-05-27 : 05:48:53
No way you will ever be able to write a fully tuneable query out of that, simply because it isn't only one query. There are some technics mentioned somewhere else on this site to rewrite dynamic SQL, but that mostly has to do with the WHERE clause, but here you even change which tables that are selected from, it's proberly very clever, but bound to be in dynamic SQL. I will leave that part to more quallyfied people than me to either praise or bash you for.

But ... in 2005 there is other ways to get what you need, run you app and make sure you at least get through different routes of this sp, and then try run this query:
SELECT TOP 100 migs.group_handle, CONVERT(int, avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)), mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON (mig.index_handle = mid.index_handle)
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC
you may find what you look for here.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-28 : 01:08:08
Okay, so i ran that, and it provided 4 rows back, i'm a total n00b, and just started using sql2k5, so i dont know what it means :o) pointers?

Also, this is after i ran a bunch of quries though DETA. i came up with about 6 indexes, ran a batch, isolated 2 problem quries, and ran them though DETA by them selves. i then picked a could of recommendations (discarded the drops mainly) and added them to the database.

now i got 2 quries that are still taking up about 20% of the batch, kinda high for how easy the quries should be.

looking at the execution, i dont see any table scans, or bookmark lookups (which from what i've been told are the main killers)
what should i be looking for now?
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2007-05-28 : 02:50:24
one thing you need to be aware of is, you don't want to go adding an arbitrary number of indexes to this table, unless the table is read only. the more indexes you add, the slower insert/update/delete perf will become. any index that is not used by common queries ought to be dropped (unless it's the pk of course!)

indexes generally help selects, and can hurt the other 3 operations (although they can help update/delete as well, since an index can help sql server find the right rows to update/delete). an index will never speed up an insert.

part of indexing a table properly is finding a balance between these. of course if the table is only read from, you can add as many indexes as you like without penalty.

finally, it looks like this proc is all about dynamic search conditions. You should read this if you haven't already: http://www.sommarskog.se/dyn-search.html

www.elsasoft.org
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-28 : 13:29:05
it is an olap table (read only) we run a delete on it once a week to remove records added to the National Do Not Call List.
i'll check out that article too, i'm a self tought n00b so any knowledge i can collect to better the app i'm designing, and for future use is greatly appreciated. -update, that is also the article i read for the bases of the stored procedure i posted.

Thank you.


but how do i read those four rows from the query psamsig posted? is that telling me what columns the quries could have used an index that wasnt there? is if so, do i add them to another index, create four separate indexes? or one index that would cover all four?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-28 : 16:00:04
"it is an olap table (read only)"

If you have a "sand pit" where you can have a play I think it would be worth putting indexes on all the fields in your Dynamic Where clause (well, all the easy ones at least) and then try replaying "average" queries and seeing how they perform before/after adding the index.

Make sure that you clear the cache before each test so you are comparing like with like.

If you see a huge improvement go with that approach, if its "modest" then it may not be worth the aggro.

Only other thought is if there are a relatively small number of queries that recur often. If that's the case I would review the Query Plan for each of those, and try putting indexes in place to favour those queries, and then the remainder can be ignored - let them take however long they take!

My gut feeling is to have a different SProc for each of your possible @tablename - but I haven't looked closely to see if there is lots of overlap int eh dynamic SQL between all of them.

I would also be inclined to generate the Dynamic SQL from the application, rather than within a SProc, and have the application call sp_ExecuteSQL directly. (Application language usually much better at string manipulation than SQL Server, and it offloads that CPU activity to the "client")

Kristen
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-28 : 16:47:43
quote:
putting indexes on all the fields in your Dynamic Where clause (well, all the easy ones at least) and then try replaying "average" queries and seeing how they perform before/after adding the index


with sql2k5 is there a limit to the number of columns you can have in an index? also, what do i have to do to clear the cache prior to testing? remember, i'm a n00b to sql2k5, and everything total different then what i'm used to.

quote:
My gut feeling is to have a different SProc for each of your possible @tablename - but I haven't looked closely to see if there is lots of overlap int eh dynamic SQL between all of them.


the code is all the same if that's what you mean, the only part the @tablename matters is where it decides what table to pull from.

another question, i read somewhere that if the execution plan changes from time to time with stored procedures, you should add a "with recompile" or something like that, that way it doesnt use a bad execution plan that is already in cache. is that something i should do? and if so, where do i add it? near the with (nolock)?
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2007-05-28 : 18:06:52
if the table is read only, why do you use the nolock hint? are you aware reading nolock is a dirty read? this means you may be reading uncommitted data. I supposed data couldn't be dirty if nobody ever writes to the table, but in that case i ask again, why use nolock?

usually, using nolock is to "fix" deadlock issues. (really all it does is cover them up and you get other types of errors anyway, most common one being something like "could not read nolock due to data movement.") but you'd only get deadlocks if others were trying to insert/update/delete rows at the same time...

www.elsasoft.org
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-28 : 18:11:47
i thought i read somewhere (it was a while ago i started using it) that with nolock the server doesnt waste resources locking the record, and other quries being ran at the same time wont have to wait to read the same record. is that not true?
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2007-05-28 : 21:45:12
I wouldn't call it a "waste" of resources to prevent dirty reads - reading dirty means you can be returned data that is no longer valid or inconsistent. Using nolock universally is not a good habit to get into IMO.

in your case if you remove the nolock hint, all the locks would be shared locks (since you only have readers) so there wouldn't be any blocking to other processes trying to read the same rows/pages/tables anyway. shared locks are what are taken for readers. writers take exclusive locks - they are the ones that would have to wait if readers were active. and once a writer takes an exclusive lock, all readers would have to wait until the lock is released.

other gurus here may have more to say on this - i'm just a lowly dev after all...

www.elsasoft.org
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-05-28 : 23:38:07
quote:
Originally posted by albertkohl

it is an olap table (read only) we run a delete on it once a week to remove records added to the National Do Not Call List...


Telemarketers!



CODO ERGO SUM
Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts
Posted - 2007-05-28 : 23:43:21
hah! i missed that.

in that case we ought to be recommending dynamic sql with triply nested cursors!

www.elsasoft.org
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-28 : 23:46:48
LOL just register, and we wont need to call you. actully, we arnt a telemarketing firm, we're a list broker, we have a consumer database that we've purchased about 2 years ago. we've been scraping by with me and someone else running quries manualy in query analyser, i'm sick of it :o) so i've been writing a be based form for the rep's to use to run counts.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-29 : 04:27:57
"with sql2k5 is there a limit to the number of columns you can have in an index?"

Don't add them all to a single index, create a separate index for each column. (Ideally you would create composite indexes where they would "cover" the query, but I think for a straightforward first step just creating individual indexes for commonly used columns, and testing the perform or some queries, would give you a good indication of how much difference they will make.

"what do i have to do to clear the cache prior to testing?"

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Be careful using these on a Production server as they will throw away all the cached queries! Good for getting a level-playing-field in a Test environment though.

"if the execution plan changes from time to time with stored procedures, you should add a "with recompile""

You won't have that problem with sp_ExecuteSQL.

Your dynamic SQL will create a number of different queries. e.g.

Query 1
-------

SELECT ...
WHERE 1 = 1
AND o.per1_age >= @xlowage
AND o.per1_age <= @xhighage

Query 2
-------

SELECT ...
WHERE 1 = 1
AND o.est_inc >= @xlowincome
AND o.est_inc <= @xhighincome

the query-plan for each of these will be separately cached. The REALLY GOOD news is that you are using a parameterized query, so every time you do either a per1_age (i.e. Query 1) or a est_inc (i.e. Query 2) query then the query plan will be already cached.

If you happen to do a brand new combination SQL will have to make a query plan (which takes a while), then it will cache it. if you don't use that query again then eventually the query plan will become "stale" and will be removed from the cache.

When you delete the Do Not Call numbers (or add more data) you will need to rebuild the indexes or update the statistics, so that the queries are efficient again, and that may require that you clear the cache - otherwise the cached queries are not going to use the revised statistics (although I think that works better now in SQL2k5 than it did in SQL2k, so the clear-cache may not be necessary). IF you had Sprocs, instead of sp_EXecuteSQL dynamic SQL, then you could use Recompile to force specific SProcs to make a new query plan - rather than the blunt-instrument of throwing away the whole of the cache.

Also, get rid of the nolock hint, as others have said.

Kristen
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-30 : 12:58:47
Kristen, okay, so i'm setting up my "Sand Pit" and i have a question? i just imported the largest table, and when i get the data from our vender, i add 3 fields at the end of it for my program. i'm updating the columns right now to reflect that change, and in prerformance manager the disk queue lenth is tacked out at 100%. that's just because i'm writing a CRAP load of data right? i've also noticed from time to time that whenever my server is running a query, the disk que is at 100% for the duration of the query. i have (4) 10k RPM Raptor SATA drivers hooked up that house the database, is it tacked out that bad due to high indexes? or is it something more hardware? i know people like me have a bad tendency to blam hardware, but i do know this machine is fairly strong. do you think this symptom will go away once i create good indexes? or is it normal?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-30 : 13:39:56
Hehehhe ... "It Depends" .... What did you expect me to say?

If you are going to update ALL rows to add suitable values for 3 columns its going to take as long as it takes. Indexes aren't going to help - you still need to update all three columns on all rows (if I've understood you correctly?)

However, what you ARE doing is creating a huge (assuming there are lots of rows in the table) Transaction. This transaction has to be logged ... maybe the LDF / Log file is having to be extended to contain it all?

It might be better to do it in batches so that the "impact" on the Log is reduced. (If the Recovery Model is SIMPLE then the Log File won't grow so big ... if the Recovery Model is FULL then there is a chance that the Log Backup will run during the overall process, backup the Completely transaction, and thus that part of the Log File can be reused).

Only other alternative is to provide the extra 3 columns as part of the data import - either by adding that data outside SQL Server, or by pulling the import data into a staging table and then providing the extra data as part of the "insert" into the product tables.

If you have Indexes on any of the three columns it might be worthwhile dropping the indexes before the Import, and then recreating them After the extra-3-columns have been populated.

As ever, rebuilding indexes and updating statistics after Data Import will help the subsequent 3-column-update process

Kristen
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-30 : 13:54:03
there is nothing on the database yet for indexing, i got sick of how slow one of my home sample boxes was running (barely meets SQL Requirements) so i moved my eval copy to one of my production servers (no worries, only me and one other guy uses that server right now, so he'll have to just use another one while i play in my sand box :o) )

how could you run an update like this in batches? i'm moving from foxpro, and something i would do would be like:

repl all citystate with alltrim(city)+' '+state for recno()>0 and recno()<10000 but i know SQL doesnt really have "Record Numbers" from what i've been told (by you a while ago i beleve). is it realitively easy to whip up something like this?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-05-30 : 14:07:29
"how could you run an update like this in batches?"

DECLARE @intRowCount INT
SET @intRowCount = 1 -- Force first loop iteration

WHILE @intRowCount >= 1
BEGIN
SET ROWCOUNT 10000   -- Restrict number of rows processed
    UPDATE MyTable
    SET MyColumn1 = ...,
        MyColumn2 = ...,
        ...
    WHERE MyColumn1 IS NULL
    SELECT @intRowCount = @@rowcount
    SET ROWCOUNT 0       -- Reset to "All rows"
END

However, the query to find the next "batch" where "MyColumn1 IS NULL" may be slow, so it may be better to Select all PKs into a #TempTable and then process it batches of 10,000 (or whatever) by JOINing the #TempTable to the actual table

Not quite what you are looking for, but this might fill in some of the "blanks":

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084

Kristen
Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts
Posted - 2007-05-31 : 13:55:54
cool, got the data all loaded. thank you.

now i have a question, after i get it all loaded/indexed. when i do my DNC update, what's the best way to update the statistics/indexes? do i have to drop/recreate them? or is there just a command to do it? do i have to update each index/stat individually?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-06-01 : 02:53:48
You can "reindex" the tables, which will have the side effect of updating their statistics.

You could DROP the indexes before the Update, and then recreate them afterwards. That too would create fresh statistics.

Both those will get rid of any fragmentation in the index as well.

Alternatively just update the statistics on the relevant table / index(es). Or use

EXEC sp_UpdateStat

to freshen the indexes on everything. (However, it uses defaults for the scan size, which may not be ideal)

Kristen
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2007-06-01 : 10:32:29
You can also use DBCC INDEXDEFRAG to defragment the indexes. It has the advantage of being an 'online' operation, so it can be run while the table is being used, and it can be interrupted if necessary. After it completes, it is necessary to run UPDATE STATISTICS.

A disadvantage is that for a database in full recovery mode, it will generate a lot of transaction log output, so make sure your transaction log is being dumped at short intervals, 15 minutes or less, or your transaction log will get very large.


CODO ERGO SUM
Go to Top of Page
    Next 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