July 6, 2011 at 8:53 am
Hi,
I am working on a 17 parameters SSRS report (version - 2005) which processes quite slowly. It takes quite some time (4-5 minutes) before I can actually view the data. Someone suggested to include the 'WITH RECOMPILE' option. I have tried this but there is still no change in the priocessing time of the rerport. Could someone please suggest anything else that can be done ?
Thanks
Paul
July 6, 2011 at 8:58 am
Post the proc code and the execution plans (actual).
We'll also need the indexes available for all tables.
July 6, 2011 at 9:35 am
Ninja's_RGR'us (7/6/2011)
Post the proc code and the execution plans (actual).We'll also need the indexes available for all tables.
Thanks for your reply. Below is the main SP:
-----------------------------------------------------
USE [TMIS]
GO
/****** Object: StoredProcedure [dbo].[usp_QM_Details] Script Date: 07/06/2011 15:22:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_QM_Details]
@Choose_Date [nvarchar](10),
@From_date [varchar](30),
@To_date [varchar](30),
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
--WITH EXECUTE AS CALLER
WITH RECOMPILE
AS
BEGIN
/*
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
*/
if @Choose_Date = 'SAILING'
exec usp_QM_Details_Sailing @From_date,@To_date,
@direction, @fe ,
@scnolike , @CPSHCNNPLike , @Commoditylike,
@Countryofreceipt,
@POR , @POL , @POD , @Countryofdelivery , @PDL , @Trade ,
@Service , @VesselVoyage
if @Choose_Date = 'ONBOARD'
exec usp_QM_Details_Onboard @From_date,@To_date,
@direction, @fe ,
@scnolike , @CPSHCNNPLike , @Commoditylike,
@Countryofreceipt,
@POR , @POL , @POD , @Countryofdelivery , @PDL , @Trade ,
@Service , @VesselVoyage
END
--SET ANSI_WARNINGS ON
-----------------------------------------------------
These 2 are the sub-procedures:
1:
-----------------------------------------------------
USE [TMIS]
GO
/****** Object: StoredProcedure [dbo].[usp_QM_Details_Sailing] Script Date: 07/04/2011 09:53:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_QM_Details_Sailing]
@From_date [varchar](30),
@To_date [varchar](30),
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
--WITH EXECUTE AS CALLER
WITH RECOMPILE
AS
/*
SET NOCOUNT ON
SET ANSI_WARNINGS Off
*/
BEGIN
SELECT BLNO, SAILING_DT, ON_BOARD_SAILING_DT, DIRECTION, [F/E], SCNO, SHIPPER,FORWARDER,
CONSIGNEE, NOTIFY, [CONTROL PARTY], [NAYOSE NAME], COMMODITY, ORIGIN, POR, POL, POD, DEST, PDL, TRADE_CD, SAISAN_SERVICE, VESSEL, VOYAGE, LEG, TEU,
(VESSEL + VOYAGE) AS VSLVOY
FROM tbl_TMIS_DATASET
WHERE
SAILING_DT >= Convert(datetime, Convert(varchar, @From_date)) and
SAILING_DT <= Convert(datetime, Convert(varchar, @To_date)) and
/*
SAILING_DT >= Convert(datetime, @From_date, 101) and
SAILING_DT <= Convert(datetime, @To_date, 101) and
*/
DIRECTION IN (select distinct Item From dbo.Split(@direction,',')) and
[F/E] IN (select distinct Item From dbo.Split(@fe,',')) and
origin IN (select distinct Item From dbo.Split(@Countryofreceipt,',')) and
POR IN (select distinct Item From dbo.Split(@POR,',')) and
POL IN (select distinct Item From dbo.Split(@POL,',')) and
POD IN (select distinct Item From dbo.Split(@POD,',')) and
DEST IN (select distinct Item From dbo.Split(@Countryofdelivery,',')) and
PDL IN (select distinct Item From dbo.Split(@PDL,',')) and
TRADE_CD IN (select distinct Item From dbo.Split(@Trade,',')) and
SAISAN_SERVICE IN (select distinct Item From dbo.Split(@Service,',')) and
(VESSEL + VOYAGE) IN (select distinct Item From dbo.Split(@VesselVoyage,',')) and
(SCNO like '%' + @scnolike + '%' )
AND
([CONTROL PARTY] like '%' + @CPSHCNNPLike + '%'
or SHIPPER like '%' + @CPSHCNNPLike + '%'
or CONSIGNEE like '%' + @CPSHCNNPLike + '%'
or NOTIFY like '%' + @CPSHCNNPLike + '%')
AND Commodity like '%' + @Commoditylike + '%'
END
--SET ANSI_WARNINGS ON
-----------------------------------------------------
2:
-----------------------------------------------------
USE [TMIS]
GO
/****** Object: StoredProcedure [dbo].[usp_QM_Details_Onboard] Script Date: 07/06/2011 15:45:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_QM_Details_Onboard]
@From_date [varchar](30),
@To_date [varchar](30),
@direction [nvarchar](4000),
@fe [nvarchar](4000),
@scnolike [nvarchar](4000),
@CPSHCNNPLike [nvarchar](4000),
@Commoditylike [nvarchar](4000),
@Countryofreceipt [nvarchar](4000),
@POR [nvarchar](4000),
@POL [nvarchar](4000),
@POD [nvarchar](4000),
@Countryofdelivery [nvarchar](4000),
@PDL [nvarchar](4000),
@Trade [nvarchar](4000),
@Service [nvarchar](4000),
@VesselVoyage [nvarchar](4000)
--WITH EXECUTE AS CALLER
WITH RECOMPILE
AS
/*
SET NOCOUNT ON
SET ANSI_WARNINGS Off
*/
BEGIN
SELECT BLNO, SAILING_DT, ON_BOARD_SAILING_DT, DIRECTION, [F/E], SCNO, SHIPPER,FORWARDER,
CONSIGNEE, NOTIFY, [CONTROL PARTY], [NAYOSE NAME], COMMODITY, ORIGIN, POR, POL, POD, DEST, PDL, TRADE_CD, SAISAN_SERVICE, VESSEL, VOYAGE, LEG, TEU, (VESSEL + VOYAGE) AS VSLVOY
FROM tbl_TMIS_DATASET
WHERE
ON_BOARD_SAILING_DT >= Convert(datetime, Convert(varchar, @From_date)) and
ON_BOARD_SAILING_DT <= Convert(datetime, Convert(varchar, @To_date)) and
DIRECTION IN (select distinct Item From dbo.Split(@direction,',')) and
[F/E] IN (select distinct Item From dbo.Split(@fe,',')) and
origin IN (select distinct Item From dbo.Split(@Countryofreceipt,',')) and
POR IN (select distinct Item From dbo.Split(@POR,',')) and
POL IN (select distinct Item From dbo.Split(@POL,',')) and
POD IN (select distinct Item From dbo.Split(@POD,',')) and
DEST IN (select distinct Item From dbo.Split(@Countryofdelivery,',')) and
PDL IN (select distinct Item From dbo.Split(@PDL,',')) and
TRADE_CD IN (select distinct Item From dbo.Split(@Trade,',')) and
SAISAN_SERVICE IN (select distinct Item From dbo.Split(@Service,',')) and
(VESSEL + VOYAGE) IN (select distinct Item From dbo.Split(@VesselVoyage,',')) and
(SCNO like '%' + @scnolike + '%' )
AND
([CONTROL PARTY] like '%' + @CPSHCNNPLike + '%'
or SHIPPER like '%' + @CPSHCNNPLike + '%'
or CONSIGNEE like '%' + @CPSHCNNPLike + '%'
or NOTIFY like '%' + @CPSHCNNPLike + '%')
AND Commodity like '%' + @Commoditylike + '%'
END
--SET ANSI_WARNINGS ON
-----------------------------------------------------
the SP uses only one table and below is the DDL for the same:
-----------------------------------------------------
USE [TMIS]
GO
/****** Object: Table [dbo].[tbl_TMIS_DATASET] Script Date: 07/06/2011 16:36:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_TMIS_DATASET](
[BL_ID] [decimal](18, 0) NOT NULL,
[POL_NAME] [nvarchar](50) NULL,
[POD_NAME] [nvarchar](50) NULL,
[POR_NAME] [nvarchar](50) NULL,
[PDL_NAME] [nvarchar](50) NULL,
[CCYYMM] [nvarchar](6) NULL,
[WEEK_NUM] [nvarchar](6) NULL,
[SAILING_DT] [datetime] NULL,
[ON_BOARD_CCYYMM] [nvarchar](6) NULL,
[ON_BOARD_WEEK_NUM] [nvarchar](6) NULL,
[ON_BOARD_SAILING_DT] [datetime] NULL,
[BL_TYPE_CD] [nchar](1) NULL,
[TRADE_CD] [nvarchar](20) NULL,
[SAISAN_LINE] [nvarchar](40) NULL,
[SAISAN_SERVICE] [nvarchar](20) NULL,
[LINE] [nvarchar](5) NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](10) NULL,
[LEG] [nchar](3) NULL,
[ORIGIN] [nvarchar](20) NULL,
[POR] [nvarchar](5) NULL,
[WB_POR] [nvarchar](5) NULL,
[POL] [nvarchar](5) NULL,
[BL_POL] [nvarchar](5) NULL,
[POD] [nvarchar](5) NULL,
[PDL] [nvarchar](5) NULL,
[REAL_POL] [nvarchar](5) NULL,
[REAL_POD] [nvarchar](5) NULL,
[DEST] [nvarchar](20) NULL,
[CCP_ID] [nvarchar](30) NULL,
[CONTROL PARTY] [nvarchar](255) NULL,
[NAYOSE NAME] [nvarchar](255) NULL,
[SHIPPER] [nvarchar](255) NULL,
[SH GApp ID] [nvarchar](50) NULL,
[NOTIFY] [nvarchar](255) NULL,
[NP GApp ID] [nvarchar](50) NULL,
[CONSIGNEE] [nvarchar](255) NULL,
[CN GApp ID] [nvarchar](50) NULL,
[FORWARDER] [nvarchar](255) NULL,
[FW GApp ID] [nvarchar](50) NULL,
[COMMODITY] [nvarchar](255) NULL,
[PREF] [nvarchar](5) NULL,
[NUM] [nvarchar](20) NULL,
[BLNO] [nvarchar](50) NOT NULL,
[CIF/FOB] [nvarchar](20) NULL,
[A/C TYPE] [nvarchar](20) NULL,
[SALES] [nvarchar](255) NULL,
[SCNO] [nvarchar](50) NULL,
[R/D TYPE] [nvarchar](50) NULL,
[F/L] [nchar](1) NULL,
[F/E] [nchar](1) NULL,
[20DRY] [decimal](18, 3) NULL,
[40DRY] [decimal](18, 3) NULL,
[40DHC] [decimal](18, 3) NULL,
[45DRY] [decimal](18, 3) NULL,
[20REF] [decimal](18, 3) NULL,
[40REF] [decimal](18, 3) NULL,
[HCREF] [decimal](18, 3) NULL,
[20OTP] [decimal](18, 3) NULL,
[40OTP] [decimal](18, 3) NULL,
[20FLR] [decimal](18, 3) NULL,
[40FLR] [decimal](18, 3) NULL,
[20OTH] [decimal](18, 3) NULL,
[40OTH] [decimal](18, 3) NULL,
[TEU] [decimal](18, 3) NULL,
[Ave_GW] [decimal](18, 2) NULL,
[Ave_GW_with_Tare] [decimal](18, 2) NULL,
[OFT] [nchar](1) NULL,
[AMOUNT] [decimal](19, 2) NULL,
[Direction] [nvarchar](3) NULL,
[DB_SOURCE] [nvarchar](10) NULL,
[NCTV_USD] [decimal](19, 2) NULL,
[CCP_GROUP_CD] [nvarchar](50) NULL,
[CONTRACT_PARTY_NAME] [nvarchar](255) NULL,
[SAISAN_MONTH] [nchar](6) NULL,
[SAISAN_DT] [datetime] NULL,
[TATASEN_YEAR] [int] NULL,
[TATASEN_WEEK] [int] NULL,
[ORIGIN_GROUP] [nvarchar](50) NULL,
[DEST_GROUP] [nvarchar](50) NULL,
[Cargo_mix] [nvarchar](255) NULL,
[BL_VERSION_NBR] [decimal](4, 0) NULL,
[REF_TEU] [decimal](18, 3) NULL,
[LAST_UPDATE_DT] [datetime] NULL,
[CREATE_DT] [datetime] NULL,
[IPC_TRUNK_NAME] [nvarchar](10) NULL,
CONSTRAINT [PK_tbl_TMIS_DATASET] PRIMARY KEY NONCLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------
July 6, 2011 at 9:44 am
Ninja's_RGR'us (7/6/2011)
Post the proc code and the execution plans (actual).We'll also need the indexes available for all tables.
Just forgot to tell you this is a cascading report but still the processing time is slow in both the cases-cascading or non-cascading.
July 6, 2011 at 9:46 am
2 things
#1 I told you to dump the split results into temp table.
#2 I asked for the actual execution plan and indexes.
July 7, 2011 at 3:22 am
Ninja's_RGR'us (7/6/2011)
2 things#1 I told you to dump the split results into temp table.
#2 I asked for the actual execution plan and indexes.
Oh sorry about that...there are a total of 7 index associated with the table. Below are the scripts for the same:
1. Cl_tbl_TMIS_DATASET-
-----------------
USE [TMIS]
GO
/****** Object: Index [Cl_tbl_TMIS_DATASET] Script Date: 07/07/2011 09:37:03 ******/
CREATE NONCLUSTERED INDEX [Cl_tbl_TMIS_DATASET] ON [dbo].[tbl_TMIS_DATASET]
(
[CCYYMM] ASC,
[WEEK_NUM] ASC,
[Direction] ASC,
[TRADE_CD] ASC,
[LEG] ASC,
[VOYAGE] ASC,
[VESSEL] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-----------------
2. IX_tbl_TMIS_DATASET:
------------------
USE [TMIS]
GO
/****** Object: Index [IX_tbl_TMIS_DATASET] Script Date: 07/07/2011 09:39:16 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_TMIS_DATASET] ON [dbo].[tbl_TMIS_DATASET]
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
------------------
3. IX_tbl_TMIS_DATASET_1:
---------------------
USE [TMIS]
GO
/****** Object: Index [IX_tbl_TMIS_DATASET_1] Script Date: 07/07/2011 09:40:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_TMIS_DATASET_1] ON [dbo].[tbl_TMIS_DATASET]
(
[CONTROL PARTY] ASC,
[NAYOSE NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
---------------------
4. IX_tbl_TMIS_DATASET_2:
---------------
USE [TMIS]
GO
/****** Object: Index [IX_tbl_TMIS_DATASET_2] Script Date: 07/07/2011 09:41:06 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_TMIS_DATASET_2] ON [dbo].[tbl_TMIS_DATASET]
(
[NUM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
---------------
5. IX_tbl_TMIS_DATASET_3:
-----------------
USE [TMIS]
GO
/****** Object: Index [IX_tbl_TMIS_DATASET_3] Script Date: 07/07/2011 09:41:31 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_TMIS_DATASET_3] ON [dbo].[tbl_TMIS_DATASET]
(
[VESSEL] ASC,
[VOYAGE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-----------------
6. IX_TMIS_DATASET_4:
----------------
USE [TMIS]
GO
/****** Object: Index [IX_TMIS_DATASET_4] Script Date: 07/07/2011 09:42:06 ******/
CREATE NONCLUSTERED INDEX [IX_TMIS_DATASET_4] ON [dbo].[tbl_TMIS_DATASET]
(
[CCP_ID] ASC,
[TEU] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
----------------
7. PK_tbl_TMIS_DATASET:
----------------------
USE [TMIS]
GO
/****** Object: Index [PK_tbl_TMIS_DATASET] Script Date: 07/07/2011 09:42:34 ******/
ALTER TABLE [dbo].[tbl_TMIS_DATASET] ADD CONSTRAINT [PK_tbl_TMIS_DATASET] PRIMARY KEY NONCLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
----------------------
please find the actual execution plans attached for all the 3 SPs used. However I must confess to you I am very new to SQL and would appreciate if you could tell how can the results be dumped into a temp table.
Thanks again.
July 7, 2011 at 5:47 am
That's the only chance you have to tune this.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Once that's done, post the new execution plans and I'll see if I can help further.
P.S. Of the 17 parameters, are the mandatory parameters that are always supplied?
July 7, 2011 at 7:58 am
Ninja's_RGR'us (7/7/2011)
That's the only chance you have to tune this.http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
Once that's done, post the new execution plans and I'll see if I can help further.
P.S. Of the 17 parameters, are the mandatory parameters that are always supplied?
That was informative but I dont think it would help me a lot. I tried to run the report after removing the search text parameters but it still processed very slow. The above link only explains the scenario where the search text parameters exist. In my case, the report still processes slow even when the those dont exist. I think implementing what is mentioned in the above link wont change anything for me.
July 7, 2011 at 8:02 am
I was refferring more to the dynamic sql section. I just wanted you to have an exemple on how to build it.
With 17 optional parameters, the only valid plan is full scan. With dynamic sql you have a shot of getting index seeks once in a while.
July 7, 2011 at 9:35 am
Ninja's_RGR'us (7/7/2011)
I was refferring more to the dynamic sql section. I just wanted you to have an exemple on how to build it.With 17 optional parameters, the only valid plan is full scan. With dynamic sql you have a shot of getting index seeks once in a while.
oh okay...actually of the 17 parameters only 3 are optional and rest 14 are mandatory...the 3 optional parameters are search text (the user can enter full or part value).
So what do you say, is there still some wayout for the report to process faster ? I will give a shot with the dynamic sql bit you mentioned though.
July 7, 2011 at 9:45 am
pwalter83 (7/7/2011)
Ninja's_RGR'us (7/7/2011)
I was refferring more to the dynamic sql section. I just wanted you to have an exemple on how to build it.With 17 optional parameters, the only valid plan is full scan. With dynamic sql you have a shot of getting index seeks once in a while.
oh okay...actually of the 17 parameters only 3 are optional and rest 14 are mandatory...the 3 optional parameters are search text (the user can enter full or part value).
So what do you say, is there still some wayout for the report to process faster ? I will give a shot with the dynamic sql bit you mentioned though.
Am I misreading the indexes over here? You apparently don't have any clustered index on that table.
If that's the case I'd put a clustered index on ON_BOARD_SAILING_DT.
See by how much this helps. If the usage is to get 1-2 days of data you should see a major boost. If that date filter doesn't filter out anything then you'll always be stuck with a table scan unless you can find a usage pattern you can exploit.
July 7, 2011 at 9:57 am
Ninja's_RGR'us (7/7/2011)
pwalter83 (7/7/2011)
Ninja's_RGR'us (7/7/2011)
I was refferring more to the dynamic sql section. I just wanted you to have an exemple on how to build it.With 17 optional parameters, the only valid plan is full scan. With dynamic sql you have a shot of getting index seeks once in a while.
oh okay...actually of the 17 parameters only 3 are optional and rest 14 are mandatory...the 3 optional parameters are search text (the user can enter full or part value).
So what do you say, is there still some wayout for the report to process faster ? I will give a shot with the dynamic sql bit you mentioned though.
Am I misreading the indexes over here? You apparently don't have any clustered index on that table.
If that's the case I'd put a clustered index on ON_BOARD_SAILING_DT.
See by how much this helps. If the usage is to get 1-2 days of data you should see a major boost. If that date filter doesn't filter out anything then you'll always be stuck with a table scan unless you can find a usage pattern you can exploit.
No, you're right, there's no clustered index on the table. Is it supposed to increase performance better than the non-clustered one ?
How would you know which column to put the clustered index on ? This should really help.
Also, I was meaning to ask you, if you noticed there are 7 non-clustered indexes on the table. Could that be the main reason for the report processing slowly ?
July 7, 2011 at 10:08 am
The missing clustered index is the best path for improvement at this point.
The 7 nc index would only slow down the import of the data (assuming it's a massive import).
I know to use the date column for the clustered index based on 1000s of tests.
July 8, 2011 at 1:58 am
Ninja's_RGR'us (7/7/2011)
The missing clustered index is the best path for improvement at this point.The 7 nc index would only slow down the import of the data (assuming it's a massive import).
I know to use the date column for the clustered index based on 1000s of tests.
Thanks Ninja...there is always a massive amount of data in this table at any given point of time. Would you suggest dropping some of the NC indexes ? this table is used for other reports as well and I hope those dont get affected with the changes.
By the way what criteria should be used to decide which NC indexes to remove ?
July 8, 2011 at 7:23 am
pwalter83 (7/8/2011)
Ninja's_RGR'us (7/7/2011)
The missing clustered index is the best path for improvement at this point.The 7 nc index would only slow down the import of the data (assuming it's a massive import).
I know to use the date column for the clustered index based on 1000s of tests.
Thanks Ninja...there is always a massive amount of data in this table at any given point of time. Would you suggest dropping some of the NC indexes ? this table is used for other reports as well and I hope those dont get affected with the changes.
By the way what criteria should be used to decide which NC indexes to remove ?
As I said those index will only affect the INSERTS of data (and deletes / updates depending on how you clean that table).
Reporting tables are best served by having tones of indexing on it.
Really the ONLY thing I'd touch at this point is the clustered index on date. Now the only way to be sure is to run the all reports (as much as you an squeeze in a short test). Log the results in profiler. Then add the clustered index and rerun the exact same reports. Compare to see which one ran the fastest and you'll see what is best for your solution.
I'd also make sure to rebuild all the indexes after any big data import / purge. Make sure fill factor is at 0 (same as 100%). Once the rebuild is done update any stats that were not included in the indexed columns.
You can also log the actual execution plan and stats in profiler. That event is in the performance section tho I'm not 100% sure which one it is from memory. Doing so allows you to compare if you really messed things up. The easiest counter is cpu time and reads. Then in the plan is the estimated cost of the final operator.
B4 doing any test have a run at rebuilding the stats / indexes.
Here's a good article on tuning as well to get you started :
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply