August 2, 2012 at 8:41 am
Hello,
I was running a procedure to retrieve data which usually took 14-20 seconds but in last two days it started taking more than a minute which is not acceptable timing. I have checked all the indexes and network and nothing has changed to my knowledge. Is there anything else as far as the database wise that I need to look into such as rebuild indexes and so forth. I would appreciate any recommendations.
Thanks
Aj
August 2, 2012 at 8:46 am
Could you post the execution plan for the procedure?
Have you checked index fragmentation and statistics to ensure they are defragmented and updated.
If stuck please follow this link http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 2, 2012 at 8:50 am
without the details my friend anthony is asking for, i'd WAG and say to update statistics on the underlying tables the procedure uses.
out of date statistics are associated witht eh symptom you describe: performance of an existing proc slowly degrades.
parameter sniffing could also be an issue...more details might be needed, as anthony suggested.
UPDATE STATISTICS dbo.[Table1] WITH FULLSCAN ;
UPDATE STATISTICS dbo.[Table2] WITH FULLSCAN ;
Lowell
August 2, 2012 at 9:35 am
Even after I did reorganized index and updated statistics on the table that is heavily used by the view , I don't see any performance improvement. I am attaching my execution plan.
Please let me know if you see anything else that I could do.
Thanks
August 2, 2012 at 9:36 am
Ooops sorry, forgot to attach the file....
August 2, 2012 at 9:44 am
I am also attaching the index fragmentation percentage on the db
August 2, 2012 at 9:59 am
well there you go!
i see a couple of things;
the plan shows two cursors doing stuff to at least 580 rows;
those cursors can be eliminated and replaced witha set based operation to do the same work in a single step.
want to post the procedure body for some peer review?
Lowell
August 2, 2012 at 10:11 am
Attached the proc for your review; please let me know how I can replace the cursors to set based operations.
Thnks
August 2, 2012 at 10:26 am
aww,at 1000+ lines of code, without the underlying base tables and linked server(EDWGEARS),
that one is a little bigger scope than i can help with as a volunteer;
it would require substantial testing, i think.
the key, however is to remove the openquery stuff, query the linked server with direct commands, as a set based operation, instead of all those cursors.
you might get rid of the temp tables and replace them with teh direct CTE's as well;
this would be beyond the scope of how i volunteer here, sorry.
Lowell
August 2, 2012 at 10:30 am
Thanks for trying at least Lowell
August 7, 2012 at 9:38 am
Is is possible to rewrite this cursor to CTE to gain some performance benefit:
BEGIN
DECLARE yr_cursor CURSOR
FOR
SELECT YEAR, RouteNum, RampInfo, BeginMeasure, EndMeasure, OriginalRoute, Description, CountyDesc, Incidents from #RptParms
OPEN yr_cursor;
FETCH NEXT FROM yr_cursor INTO @Year, @RouteNum, @RampInfo, @BeginMeasure, @EndMeasure, @OriginalRoute, @Description, @CountyDesc, @Incidents;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @sql_str_fred = N'SELECT route_number, beg_measure AS MILELOG, AADT_TOTAL AS VMT, end_measure, RCLINK,YEAR
FROM VW_FRED_AADT_HIST
WHERE route_number = '''+ @RouteNum + '''
and YEAR = '''+ @Year + '''
and FIPS_AND_COUNTY Like ' + '''%' + @CountyDesc + '''
and beg_measure BETWEEN '+ cast(@BeginMeasure as varchar(8)) + ' and ' + cast(@EndMeasure as varchar(8)) + ''
SELECT @sql_str_fred = N' SELECT * from OPENQUERY(EDWGEARS, ''' + REPLACE(@sql_str_fred, '''', '''''') + ''')'
INSERT #freddata (ROUTE_NBR ,
MILELOG ,
VMT ,
END_MEASURE ,
RCLINK ,
YEAR )
EXEC sp_ExecuteSQL @sql_str_fred
FETCH NEXT FROM yr_cursor INTO @Year, @RouteNum, @RampInfo, @BeginMeasure, @EndMeasure, @OriginalRoute, @Description, @CountyDesc, @Incidents;
END
CLOSE yr_cursor
DEALLOCATE yr_cursor
END;
August 7, 2012 at 11:59 am
that needs so much work, on so many levels;
there's multiple cursors, cursor within a cursor, wow.
how many total rows are in the EDWGEARS linked server table VW_FRED_AADT_HIST?
is EDWGEARS is a SQL server?
Lowell
August 7, 2012 at 1:02 pm
Hey I just realized that in my execution plan XML Reader XPath filter is showing 99% cost which value I am passing the procs as the years as an in parameter as following :
(SELECT CAST(Node.query('text()') AS varchar(100)) as YEAR
FROM @XMLSelectedYears.nodes('/Years/*/YYYY') tempxml (Node)) a
How can I create primary index on the table which is passed parameter; I am pretty new in this.
Thanks so much
August 8, 2012 at 7:12 am
Hi Lowell,
This VW_FRED_AADT_HIST is in oracle connecting thru linkserver edwgears; this view has about
146,000 rows
Thanks
August 8, 2012 at 8:00 am
well like i said, the key is a redesign.
first, i agree on getting rid of the xml for the list of years; it might be misleading, but 99 percent or whatever to parse that xml is too much.
much better to use DelimitedSplit8K and a comma delimited list of years.
here's a very basic example of just one slice i would fix...
first, you HAVE to eliminate the cursors. looping thru #table for each row in the open query can be replaced by an inner join between the two; but i'd be a little worried about the number of rows.
i'm sure you know the typical values that get passed in;
how many rows would this query return for the rows between the two dates?
SELECT
route_number,
beg_measure AS MILELOG,
AADT_TOTAL AS VMT,
end_measure,
RCLINK,
YEAR
INTO #freddata
FROM EDWGEARS...VW_FRED_AADT_HIST
WHERE beg_measure BETWEEN cast(@BeginMeasure as varchar(8))
and cast(@EndMeasure as varchar(8))
the idea is the entire cursor could be replaced with something like this:
SELECT
route_number,
beg_measure AS MILELOG,
AADT_TOTAL AS VMT,
end_measure,
RCLINK,
YEAR
INTO #freddata
FROM EDWGEARS...VW_FRED_AADT_HIST
WHERE beg_measure BETWEEN cast(@BeginMeasure as varchar(8))
and cast(@EndMeasure as varchar(8))
--now simply filter that temp table based on the other criteria.
SELECT T1.*
FROM #freddata T1
INNER JOIN #RptParms T2
ON T1.route_number = T2.RouteNum
AND T1.YEAR = T2.YEAR
T1.FIPS_AND_COUNTY = T2.CountyDesc
i would change this to be a master proc that calls child procs based on the parameters...
something like
if @ReportSource = 'TAB' AND @ReportMode = 'C1'
EXECUTE SubReport_TABC1(@Parameters)
if @ReportSource = 'TAB' AND @ReportMode IN('A1','A2','A3','S1')
EXECUTE SubReport_TABDefault
if @ReportSource = 'GIS'
EXECUTE SubReport_GIS(Parameters)
I'd really look at grabbing a consultant to build some parallel proc relacements ; i'm sure you have other reports that have the same performance issues; it's mostly due to not handling everything as if it were set based, the way SQL works;
Oracle is more procedural/cursor.
Lowell
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply