September 19, 2013 at 4:56 pm
Hi,
I had a query that I was running to get the top n records (eg, top 1,000). I changed the query so that it does row_number() and then gives back rows numbered between i and j (eg, rn between 1,001 and 2,000).
My query was using an index that worked very fast (0 - 3 secs). When I made the change, it decided not to use that index anymore and now it is very slow (5 - 120 secs). I tried using >1000 and <2000 instead of between, and a few other small things that did not work.
Putting in an index hint made the query run at full speed again - but this is the first time I've ever done that and as far as I understand I should undo it and make it the last. How can I make this choose my index without giving it a hint??
I have so many tables and indexes involved, I am not sure what to show exactly...
This is my query:
ALTER PROCEDURE [dbo].[sp_SFP099_NonLab_Drilldown]
-- Add the parameters for the stored procedure here
--@myCurr varchar(10) = NULL -- 'Mixed$' or 'Can$'
@L3 varchar(50) = NULL,@L4 varchar(50) = NULL,@L5 varchar(50) = NULL,@L6 varchar(50) = NULL
,@L7 varchar(50) = NULL,@L8 varchar(50) = NULL,@L9 varchar(50) = NULL,@L10 varchar(50) = NULL,@CC varchar(50) = NULL
,@myFunc varchar(25) = NULL -- 'Engineering' or 'Mechanical' or etc. If passed blank, not used
,@myPer varchar(2) = NULL, @myYTD_1_0 bit = 0 -- the month number (eg, '8'); 1 means YTD, 0 CM only
,@numRecs bigint = 1, @expL2 varchar(4) = NULL, @primLev varchar(2) = NULL
,@desc1 varchar(100) = NULL, @desc2 varchar(100) = NULL
,@myUser varchar(50) = NULL -- info about the person who called the procedure
,@1stRec bigint = 1, @myLY_1_or_0 bit = 0 --1 means last year, 0 means current year
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @startTime as datetime
set @startTime = getdate()
--compose dynamic SQL
declare @myAcctKey as varchar(8)
set @myAcctKey = right('00' + @primLev,2) + case when @expL2 is null then '' else right('0000' + @expL2,4) end
declare @myOrg as varchar(50) = 'All' -- 'All' gives the whole company
declare @myLev as varchar(20) = 'All'
if @cc is not null begin set @myOrg = @cc; set @myLev = 'CC'; end
else if @L10 is not null begin set @myOrg = @L10; set @myLev = '[Level 10]'; end
else if @L9 is not null begin set @myOrg = @L9; set @myLev = '[Level 9]'; end
else if @L8 is not null begin set @myOrg = @L8; set @myLev = '[Level 8]'; end
else if @L7 is not null begin set @myOrg = @L7; set @myLev = '[Level 7]'; end
else if @L6 is not null begin set @myOrg = @L6; set @myLev = '[Level 6]'; end
else if @L5 is not null begin set @myOrg = @L5; set @myLev = '[Level 5]'; end
else if @L4 is not null begin set @myOrg = @L4; set @myLev = '[Level 4]'; end
else if @L3 is not null begin set @myOrg = @L3; set @myLev = '[Level 3]'; end;
declare @totRows as bigint
set @totRows = 0
declare @mySQL as nvarchar(max)
set @mySQL =
'SELECT @_myOrg as [myOrg]
--------THIS PART USES MY INDEX BY DEFAULT-------------
;with myRows as
(
SELECT RowNum, ROW_NUMBER() OVER (ORDER BY RowNum ASC) AS rn
FROM '
+ case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end
+ ' a INNER JOIN dbo._SFP100_Org_No_Ampers e on a.[cost ctr] = e.[org id]
WHERE '
+ case when len(@myAcctKey) = 2 then 'left([AcctKey],2) = @_myAcctKey' else '[AcctKey] = @_myAcctKey' end --filter primary & maybe subL2 accts
+ case when @myLev = 'All' then '' else ' and ' + @myLev + ' = @_myOrg ' end --don't necessarily filter CC org
+ ' and [month] ' + case when @myYTD_1_0 = 1 then '<' else '' end + '= @_myPer ' --YTD or just CM 1 or 0
+ case when @myFunc is null then '' else ' and [functional reporting] = @_myFunc ' end
+')
SELECT @_totRows = Count([RowNum]) from myRows
------THIS PART USED THE INDEX UNTIL I CHANGED FROM TOP @_numRecs to BETWEEN @_1stRec and (@_1stRec + @_numRecs - 1), NOW ONLY USES INDEX WITH HINT----
SELECT @_totRows as [totRows] ' +
';with myRows as
(
SELECT RowNum, ROW_NUMBER() OVER (ORDER BY RowNum ASC) AS rn
FROM '
+ case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end
+ ' a '
+ ' WITH (INDEX(IX_CostCtr_AcctKey_Mo_RowNum)) '
+ ' INNER JOIN dbo._SFP100_Org_No_Ampers e on a.[cost ctr] = e.[org id]
WHERE '
+ case when len(@myAcctKey) = 2 then 'left([AcctKey],2) = @_myAcctKey' else '[AcctKey] = @_myAcctKey' end --filter primary & maybe subL2 accts
+ case when @myLev = 'All' then '' else ' and ' + @myLev + ' = @_myOrg ' end --don't necessarily filter CC org
+ ' and [month] ' + case when @myYTD_1_0 = 1 then '<' else '' end + '= @_myPer ' --YTD or just CM 1 or 0
+ case when @myFunc is null then '' else ' and [functional reporting] = @_myFunc ' end
+')
, myTopRowNums as
(
SELECT [RowNum] from myRows where rn between @_1stRec and (@_1stRec + @_numRecs - 1)
)
SELECT
a.[CC]
, a.[Account]
, a.[Vendor]
, a.[Doc Header Text] as [Doc]
, a.[User Text] as [UserText]
, a.[Description]
, a.[Year]
, a.[Month]
, a.[Quantity]
, a.[Unit]
, a.[Dollar Amount] as [DollarAmount]
FROM '
+ case when @myLY_1_or_0 = 0 then ' dbo.SFP099_NonLab_Read ' else ' dbo._SFP099_NonLabLY ' end
+ ' a INNER JOIN myTopRowNums b on a.[RowNum] = b.[RowNum]
ORDER BY a.[RowNum]'
PRINT @mySQL
--execute dynamic SQL (using sp_executesql instead of exec(@sql) means not vulnerable to sql injection)
EXEC sp_executesql @mySQL,
N'@_myOrg varchar(50), @_myFunc varchar(25), @_myPer varchar(2),@_myAcctKey varchar(8)
,@_numRecs bigint, @_expL2 varchar(4), @_primLev varchar(2), @_1stRec bigint, @_totRows bigint output',
@_myOrg = @myOrg, @_myFunc = @myFunc, @_myPer = @myPer, @_myAcctKey = @myAcctKey
,@_numRecs = @numRecs, @_expL2 = @expL2, @_primLev = @primLev, @_1stRec = @1stRec, @_totRows = @totRows output
END
The index that was being used is a non-clustered index defined like this (my NonLab_Read synonym points at either an A or B table):
CREATE NONCLUSTERED INDEX [IX_CostCtr_AcctKey_Mo_RowNum] ON [dbo].[_SFP099_NonLabB]
(
[Cost Ctr] ASC,
[AcctKey] ASC,
[Month] ASC,
[RowNum] ASC
)
When I don't give the hint, it now wants to use a clustered index, even though the only thing it gets from there is the cost center #:
CREATE CLUSTERED INDEX [CX_Cost_Ctr_Cost_Elem] ON [dbo].[_SFP099_NonLabB]
(
[Cost Ctr] ASC,
[Cost Elem] ASC
)
I'm at a loss here... Can anyone see based on this what might make my preferred index be used without the hint? Are there other things I should post to help the experts see the answer? Should I just go ahead and use the hint?
Thanks for the help.
Tai
September 19, 2013 at 6:27 pm
I forgot to mention... I also have an index on RowNum that includes all the fields I am selecting; with or without the index hint my query is 1. getting the RowNums and 2. using the RowNum index to get all the other fields. Please let me know if other info would be helpful.
Thanks for any advice!
Tai
September 23, 2013 at 7:49 am
When you run the stored proc in SQL Server Management Studio with menu option
Query -> Include Actual Execution Plan
Does the plan show a recommended index to add?
What parts of the query has the most cost?
Sounds like the index is in the wrong place.
Cheers 🙂
September 23, 2013 at 12:13 pm
Jason,
Thanks for the response. No, there are no recommended indexes shown when I display the execution plan.
As I said, I also have an index on RowNum that includes all the fields I am selecting;with or without the index hint my query is 1. getting the RowNums and 2. using the RowNum index to get all the other fields.
The part of the query that takes the most time is 1. getting the RowNums... however, the whole query takes on the order of 40x as long without the index hint as with it. I'm no expert, but it seems to me that the index is working and is just not being chosen. So I don't understand what you mean when you say the index is in the wrong place...
Thanks.
Tai
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply