stored procedure not as efficient as query

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

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure not as efficient as query

Author

Topic

GrandVizier
Starting Member

14 Posts

Posted - 2007-05-19 : 16:35:00

Hej
When I run the following query on the database it takes approx. 10 seconds - however when this query is used in a stored proc, it takes approx. 50 seconds

---------------------------------------
QUERY:
---------------------------------------

DECLARE @StartDate int
DECLARE @EndDate int
DECLARE @Property_Number int
DECLARE @Outlet int
DECLARE @Order int
DECLARE @sp_Total int
DECLARE @sp_Food int
DECLARE @sp_Bev int
Set @Property_Number = 705
Set @StartDate = 20060404
Set @EndDate = 20070404
set @Outlet = null
set @Order = 4
set @sp_Total = 0
set @sp_Food = 0
set @sp_Bev = 0

set rowcount 50
Select * From (
SELECT   Roster_Master.MEMBER_NO, Roster_Master.MEMB_TITLE, Roster_Master.LASTNAME, Roster_Master.FIRSTNAME, Roster_Master.PROJNO, 
		COUNT(*) AS Visits, SUM(h.Cov_Cnt) as Covers, SUM(h.Itemizer1) as Food, 
		Sum(h.Itemizer2) as Bev, SUM(h.Sub_Ttl - h.Dsc_Ttl) as Total_Check,
		max(bd.business_date) as LastUse
FROM         hm.tbl_f_dtl_chk_header h INNER JOIN
		hm.tbl_f_dtl_chk_dsc d ON h.seq_num = d.fk_chk_headers INNER JOIN
		dbo.Roster_Master Roster_Master ON d.Ref_Info_1 = Roster_Master.Member_No_DW  INNER JOIN
		hm.tbl_d_business_date bd on h.fk_business_date = bd.seq_num inner join
		hm.tbl_d_location_def loc on h.fk_location_def = loc.seq_num inner join
		hm.tbl_d_property_def prop ON loc.fk_property_def = prop.seq_num
where (d.Is_HotelMark_Promo = 1) and 
	(h.fk_Business_Date >= @StartDate) and (h.fk_Business_Date <= @EndDate) and
	(prop.Property_Number = @Property_Number) and
	 (loc.Rvc_Number = COALESCE(@Outlet,Rvc_Number))
GROUP BY MEMB_TITLE, LASTNAME, FIRSTNAME, PROJNO, MEMBER_NO
) a
WHERE Total_Check >= COALESCE(@sp_Total,Total_Check) AND
     	 Food 	>= COALESCE(@sp_Food,Food) AND
      	Bev 	>= COALESCE(@sp_Bev,Bev)
ORDER BY CASE WHEN @Order = 1 THEN Total_Check 
              WHEN @Order = 2 THEN Food
              WHEN @Order = 3 THEN Bev 
              WHEN @Order = 4 THEN Visits 
         END Desc
set rowcount 0

---------------------------------------
STORED PROC:
---------------------------------------

exec  hm.sp_TopSpenders @Show = 50, @Order = 4 , @Prop= 705, @StartDate = 20060404, 
@EndDate = 20070404, @sp_Total = 0, @sp_Food = 0, @sp_Bev = 0, @Outlet = 0

---------------------------------------

CREATE PROCEDURE [hm].[sp_TopSpenders]
@Show int,
@Order int,
@Prop int,
@StartDate int,
@EndDate int,
@sp_Total int,
@sp_Food int,
@sp_Bev int,
@Outlet int
 AS
if @Outlet = 0 Set @Outlet = NULL

set rowcount @show
Select * From (
SELECT   Roster_Master.MEMBER_NO, Roster_Master.MEMB_TITLE, Roster_Master.LASTNAME, Roster_Master.FIRSTNAME, Roster_Master.PROJNO, 
		COUNT(*) AS Visits, SUM(h.Cov_Cnt) as Covers, SUM(h.Itemizer1) as Food, 
		Sum(h.Itemizer2) as Bev, SUM(h.Sub_Ttl - h.Dsc_Ttl) as Total_Check,
		max(bd.business_date) as LastUse
FROM         hm.tbl_f_dtl_chk_header h INNER JOIN
		hm.tbl_f_dtl_chk_dsc d ON h.seq_num = d.fk_chk_headers INNER JOIN
		dbo.Roster_Master Roster_Master ON d.Ref_Info_1 = Roster_Master.Member_No_DW  INNER JOIN
		hm.tbl_d_business_date bd on h.fk_business_date = bd.seq_num inner join
		hm.tbl_d_location_def loc on h.fk_location_def = loc.seq_num inner join
		hm.tbl_d_property_def prop ON loc.fk_property_def = prop.seq_num
where (d.Is_HotelMark_Promo = 1) and 
	(h.fk_Business_Date >= @StartDate) and (h.fk_Business_Date <= @EndDate) and
	(prop.Property_Number = @Prop) and
	 (loc.Rvc_Number = COALESCE(@Outlet,Rvc_Number))
GROUP BY MEMB_TITLE, LASTNAME, FIRSTNAME, PROJNO, MEMBER_NO
) a
WHERE Total_Check >= COALESCE(@sp_Total,Total_Check) AND
     	 Food 	>= COALESCE(@sp_Food,Food) AND
      	Bev 	>= COALESCE(@sp_Bev,Bev)
ORDER BY CASE WHEN @Order = 1 THEN Total_Check 
              WHEN @Order = 2 THEN Food
              WHEN @Order = 3 THEN Bev 
              WHEN @Order = 4 THEN Visits 
         END Desc
set rowcount 0
GO

I'm new to SQL Statistics, but its clear to me that there was a huge difference in these - particularly in the logical reads for the 'tbl_f_dtl_chk_header' table
---------------------------------------
QUERY:
---------------------------------------

Table 'tbl_d_location_def'. Scan count 4, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'tbl_d_property_def'. Scan count 524, logical reads 1048, physical reads 0, read-ahead reads 0.
Table 'tbl_d_business_date'. Scan count 4, logical reads 17, physical reads 0, read-ahead reads 0.
Table 'tbl_f_dtl_chk_header'. Scan count 4, logical reads 69492, physical reads 0, read-ahead reads 0.
Table 'tbl_f_dtl_chk_dsc'. Scan count 138, logical reads 30254, physical reads 0, read-ahead reads 0.
Table 'Roster_Master'. Scan count 4, logical reads 26796, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 5922 ms,  elapsed time = 6098 ms.

---------------------------------------
STORED PROC:
---------------------------------------

Table 'tbl_f_dtl_chk_header'. Scan count 33573, logical reads 193559, physical reads 0, read-ahead reads 0.
Table 'tbl_d_business_date'. Scan count 19, logical reads 323, physical reads 0, read-ahead reads 0.
Table 'tbl_d_property_def'. Scan count 524, logical reads 1048, physical reads 0, read-ahead reads 0.
Table 'tbl_d_location_def'. Scan count 4, logical reads 6, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 244, logical reads 490, physical reads 0, read-ahead reads 0.
Table 'Roster_Master'. Scan count 1455, logical reads 4322, physical reads 0, read-ahead reads 0.
Table 'tbl_f_dtl_chk_dsc'. Scan count 76, logical reads 30068, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 124, logical reads 250, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 578, logical reads 1168, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 404, logical reads 818, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 2798 ms,  elapsed time = 53942 ms.

What it all comes down to though is that I'm using the SP in a Crystal report thats run through ASP - at the present time this is taking about 8 minutes for the same parameters to generate the report.

GrandVizier
Starting Member

14 Posts

Posted - 2007-05-19 : 16:41:43

If this helps - I did notice that there is a lot of usage of User defined data types


CREATE TABLE [tbl_f_dtl_chk_header] (
	[seq_num] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_business_date] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_location_def] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_emp_def] [PRIMARYKEYTYPE] NULL ,
	[Batch_Id] [FOREIGNKEYTYPE] NOT NULL ,
	[Status_Flag] [STATUSFLAG] NULL ,
	[Chk_Num] [NUMERICDIMENSIONTYPE] NULL ,
	[Chk_Id] [NAMETYPE] NULL ,
	[Ot_Number] [NUMERICDIMENSIONTYPE] NULL ,
	[Ot_Name] [NAMETYPE] NULL ,
	[Tbl_Number] [NUMERICDIMENSIONTYPE] NULL ,
	[Chk_Open_Date_Time] [DATETYPE] NOT NULL ,
	[Chk_Closed_Date_Time] [DATETYPE] NOT NULL ,
	[Uws_Number] [NUMERICDIMENSIONTYPE] NULL ,
	[Is_HotelMark_Promo] [NUMERICDIMENSIONTYPE] NOT NULL ,
	[Sub_Ttl] [MONEYTYPE] NOT NULL ,
	[Tax_Ttl] [MONEYTYPE] NOT NULL ,
	[Auto_Svc_Ttl] [MONEYTYPE] NOT NULL ,
	[Other_Svc_Ttl] [MONEYTYPE] NOT NULL ,
	[Dsc_Ttl] [MONEYTYPE] NOT NULL ,
	[Pymnt_Ttl] [MONEYTYPE] NOT NULL ,
	[Chk_Prntd_Cnt] [COUNTTYPE] NOT NULL ,
	[Cov_Cnt] [COUNTTYPE] NOT NULL ,
	[Num_Dtl] [COUNTTYPE] NOT NULL ,
	[Itemizer1] [MONEYTYPE] NOT NULL ,
	[Itemizer2] [MONEYTYPE] NOT NULL ,
	[Itemizer3] [MONEYTYPE] NOT NULL ,
	[Itemizer4] [MONEYTYPE] NOT NULL ,
	[Itemizer5] [MONEYTYPE] NOT NULL ,
	[Itemizer6] [MONEYTYPE] NOT NULL ,
	[Itemizer7] [MONEYTYPE] NOT NULL ,
	[Itemizer8] [MONEYTYPE] NOT NULL ,
	[Itemizer9] [MONEYTYPE] NOT NULL ,
	[Itemizer10] [MONEYTYPE] NOT NULL ,
	[Itemizer11] [MONEYTYPE] NOT NULL ,
	[Itemizer12] [MONEYTYPE] NOT NULL ,
	[Itemizer13] [MONEYTYPE] NOT NULL ,
	[Itemizer14] [MONEYTYPE] NOT NULL ,
	[Itemizer15] [MONEYTYPE] NOT NULL ,
	[Itemizer16] [MONEYTYPE] NOT NULL ,
	[Tip_ttl] [MONEYTYPE] NOT NULL ,
	 PRIMARY KEY  NONCLUSTERED 
	(
		[seq_num]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	 FOREIGN KEY 
	(
		[fk_business_date]
	) REFERENCES [tbl_d_business_date] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_emp_def]
	) REFERENCES [tbl_d_emp_def] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_location_def]
	) REFERENCES [tbl_d_location_def] (
		[seq_num]
	)
) ON [PRIMARY]
GO

----------------------------------------------------------

CREATE TABLE [tbl_f_dtl_chk_dsc] (
	[seq_num] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_business_date] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_location_def] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_emp_def] [PRIMARYKEYTYPE] NULL ,
	[fk_chk_headers] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_dsc_def] [PRIMARYKEYTYPE] NULL ,
	[fk_auth_emp_def] [PRIMARYKEYTYPE] NULL ,
	[Transaction_Date_Time] [DATETYPE] NOT NULL ,
	[Status_Flag] [STATUSFLAG] NULL ,
	[Round_Num] [NUMERICDIMENSIONTYPE] NULL ,
	[Dtl_Num] [NUMERICDIMENSIONTYPE] NULL ,
	[Dsc_Count] [COUNTTYPE] NOT NULL ,
	[Dsc_Total] [MONEYTYPE] NOT NULL ,
	[Ref_Info_1] [NAMETYPE] NULL ,
	[Is_HotelMark_Promo] [NUMERICDIMENSIONTYPE] NOT NULL ,
	 PRIMARY KEY  NONCLUSTERED 
	(
		[seq_num]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	 FOREIGN KEY 
	(
		[fk_auth_emp_def]
	) REFERENCES [tbl_d_emp_def] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_business_date]
	) REFERENCES [tbl_d_business_date] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_chk_headers]
	) REFERENCES [tbl_f_dtl_chk_header] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_dsc_def]
	) REFERENCES [tbl_d_dsc_def] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_emp_def]
	) REFERENCES [tbl_d_emp_def] (
		[seq_num]
	),
	 FOREIGN KEY 
	(
		[fk_location_def]
	) REFERENCES [tbl_d_location_def] (
		[seq_num]
	)
) ON [PRIMARY]
GO

----------------------------------------------------------


CREATE TABLE [Roster_Master] (
	[mID] [int] NULL ,
	[mCustomerID] [int] NULL ,
	[RECORD] [int] NULL ,
	[PS] [bit] NULL ,
	[CODE] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROJNO] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PROJ_NAME] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CLUB_NAME] [char] (29) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MEMBER_NO] [bigint] NULL ,
	[END_NO] [bigint] NULL ,
	[QTY] [float] NULL ,
	[DATE] [smalldatetime] NULL ,
	[MEMB_TITLE] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LASTNAME] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FIRSTNAME] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BUSINESS] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TITLE] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MEMBSINCE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ADDRESS] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CURRENT] [bit] NULL ,
	[ADDRESS2] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RENEWAL] [bit] NULL ,
	[CITY] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[STATE] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CNTRY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PCODE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FAXAC] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FAX] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BPHONEAC] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BPHONE] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BPHONEX] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HPHONEAC] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HPHONE] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MPHONEAC] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MPHONE] [char] (18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EMAIL] [char] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DDT] [smalldatetime] NULL ,
	[PAYTYPE] [char] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ACCTNO] [nchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EXPDATE] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DELD] [bit] NULL ,
	[CCNAME] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SPECIAL] [float] NULL ,
	[PRICE] [float] NULL ,
	[AMOUNT] [float] NULL ,
	[CCAPPROV] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DELVCONT] [char] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BIRTHDAY] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SPOUSBDAY] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SPOUSNAME] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ANNIVERSAR] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[REMARKS] [char] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[REMARKS2] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SOLDBY] [nchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[card1track1] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[card1track2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[card2track1] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[card2track2] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[hmexpiry] [smalldatetime] NULL ,
	[History] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email_Confirmed] [bit] NULL ,
	[DOB_Day] [int] NULL ,
	[S_DOB_Day] [int] NULL ,
	[Ann_Day] [int] NULL ,
	[ACL] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Processed] [bit] NULL ,
	[ProcessDate] [smalldatetime] NULL ,
	[LeadID] [int] NULL ,
	[InMaster] [bit] NULL ,
	[CallGroup] [int] NULL ,
	[AddressType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CCMemberID] [int] NULL ,
	[ify] [bit] NULL ,
	[ExpMM] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ExpYY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CardType] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Department] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OffPCode] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OffCity] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OffAd] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OffCountry] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[JoinedDate] [smalldatetime] NULL ,
	[AmtInKor] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PaymentMethod] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comments] [nchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Lists] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ExpiryDateText] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NameF] [nchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FaxHome] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[BizAd] [bit] NULL ,
	[DoNotExport] [bit] NULL ,
	[rmID] [int] NULL ,
	[Member_No_DW] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PropNumDW] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

----------------------------------------------------------


CREATE TABLE [tbl_d_business_date] (
	[seq_num] [PRIMARYKEYTYPE] NOT NULL ,
	[Batch_Id] [FOREIGNKEYTYPE] NOT NULL ,
	[Business_Date] [DATETYPE] NOT NULL ,
	[Year] [NUMERICDIMENSIONTYPE] NULL ,
	[Month] [NUMERICDIMENSIONTYPE] NOT NULL ,
	[DayOfMonth] [NUMERICDIMENSIONTYPE] NULL ,
	[WeekInYear] [NUMERICDIMENSIONTYPE] NULL ,
	[DayOfWeek] [NUMERICDIMENSIONTYPE] NULL ,
	[Quater] [NUMERICDIMENSIONTYPE] NOT NULL ,
	 PRIMARY KEY  NONCLUSTERED 
	(
		[seq_num]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO


----------------------------------------------------------


CREATE TABLE [tbl_d_location_def] (
	[seq_num] [PRIMARYKEYTYPE] NOT NULL ,
	[fk_property_def] [numeric](23, 0) NULL ,
	[Batch_Id] [FOREIGNKEYTYPE] NOT NULL ,
	[Creation_Date] [DATETYPE] NOT NULL ,
	[Rvc_Number] [OBJNUMTYPE] NOT NULL ,
	[Rvc_Name] [NAMETYPE] NULL ,
	[Sales_Itemizer1_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer2_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer3_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer4_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer5_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer6_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer7_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer8_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer9_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer10_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer11_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer12_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer13_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer14_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer15_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Sales_Itemizer16_Name] [STRINGDIMENSIONTYPE] NULL ,
	[Property_Number] [OBJNUMTYPE] NOT NULL ,
	 PRIMARY KEY  NONCLUSTERED 
	(
		[seq_num]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK__tbl_d_loc__fk_pr__300424B4] FOREIGN KEY 
	(
		[fk_property_def]
	) REFERENCES [tbl_d_property_def] (
		[seq_num]
	) NOT FOR REPLICATION 
) ON [PRIMARY]
GO


alter table [hm].[tbl_d_location_def] nocheck constraint [FK__tbl_d_loc__fk_pr__300424B4]
GO

----------------------------------------------------------

CREATE TABLE [tbl_d_property_def] (
	[seq_num] [numeric](23, 0) IDENTITY (1, 1) NOT NULL ,
	[Property_Number] [OBJNUMTYPE] NOT NULL ,
	[Property_Name] [NAMETYPE] NOT NULL ,
	[Property_Id] [NAMETYPE] NOT NULL ,
	[Group_Id_1] [NAMETYPE] NOT NULL ,
	[Group_Name_1] [NAMETYPE] NOT NULL ,
	[Group_Id_2] [NAMETYPE] NOT NULL ,
	[Group_Name_2] [NAMETYPE] NOT NULL ,
	[Group_Id_3] [NAMETYPE] NOT NULL ,
	[Group_Name_3] [NAMETYPE] NOT NULL ,
	[Group_Id_4] [NAMETYPE] NOT NULL ,
	[Group_Name_4] [NAMETYPE] NOT NULL ,
	[Group_Id_5] [NAMETYPE] NOT NULL ,
	[Group_Name_5] [NAMETYPE] NOT NULL ,
	CONSTRAINT [PK__tbl_d_property_d__145C0A3F] PRIMARY KEY  CLUSTERED 
	(
		[seq_num]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
) ON [PRIMARY]
GO

Kristen
Test

22859 Posts

Posted - 2007-05-20 : 09:11:01

Try changing these:

(loc.Rvc_Number = COALESCE(@Outlet,Rvc_Number))
(@Outlet IS NULL OR loc.Rvc_Number = @Outlet)

Total_Check >= COALESCE(@sp_Total,Total_Check)
(@sp_Total IS NULL OR Total_Check >= @sp_Total)

Food >= COALESCE(@sp_Food,Food)
(@sp_Food IS NULL OR Food >= @sp_Food)

Bev >= COALESCE(@sp_Bev,Bev)
(@sp_Bev IS NULL OR Bev >= @sp_Bev)

Note that
set rowcount 50
will be imposed on the INNER Sub Select too (in a random manner as there is no Order By!). You could just use
Select TOP 50 * From ( ...
instead, but I reckon that:


WHERE Total_Check >= COALESCE(@sp_Total,Total_Check) AND
     	 Food 	>= COALESCE(@sp_Food,Food) AND
      	Bev 	>= COALESCE(@sp_Bev,Bev)

(including the changes I suggested above) in the OUTER Select could be changed to a HAVING clause, and then you wouldn't need a nested sub-select at all.

You may be getting some parameter-sniffing mucking up your Query Plan in the SProc, particularly with:

if @Outlet = 0 Set @Outlet = NULL

which makes a radical difference to the query plan, and which the parameter sniffer may not spot! (You'd be better off using a working variable instead, e.g.

DECLARE @TempOutlet int
SELECT @TempOutlet = NullIf(@Outlet, 0)

and then use @TempOutlet in the WHERE clause etc. instead of @Outlet

Have you got any indexes on the tables, other than the PKs?

hm.tbl_f_dtl_chk_header.fk_Business_Date
hm.tbl_d_property_def.Property_Number
hm.tbl_d_location_def.Rvc_Number

look to be prime candidates

The PK on tbl_d_property_def (i.e. seq_num) should ahve a FILL FACTOR of 100% because it is an incrementing number. I'll lay a small bet that you are using the Maintenance Wizard to do the "housekeeping" and you have the default option ticked to "Leave 10% space free" which will have altered ALL your indexes to be 90% Fill

Kristen

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-20 : 09:45:33

Without diving into your stored proc, I bet the stored proc is recompiling each time. Do a sql profiler trace, and capture the sp:recompile event. Run the trace, and then the stored proc, and see if that event is getting captured.

Kristen
Test

22859 Posts

Posted - 2007-05-20 : 16:57:41

"I bet the stored proc is recompiling each time"

I don't see anything sinister in it that would cause that (I ain't placing any bets however!)

Kristen

GrandVizier
Starting Member

14 Posts

Posted - 2007-05-21 : 15:57:28

Thanks a lot - there was much improvement from modifying the COALESCE functions

and it didn't even dawn on me that the rowcount was messing with my results - since it needs to be set dynamically I've kept it in there instead of using the TOP x option and am now able to do that with the HAVING

the tables are indexed - but they were indexed before I started with my work so I'm not too sure they're indexed well - because as you guessed the seq_num's were set to 90% fill

the idea that it was recompiling every time made sense but I ran a bunch of scans and didn't find any such issues

again, thanks for the help

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 01:55:06

"since it needs to be set dynamically I've kept it in there instead of using the TOP x option"

No harm with how you have now rearranged it, but given that this is a SQL 2005 Forum, and assuming your are using SQL 2005!, you can now use a @Variable in a TOP statement.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources