April 2, 2012 at 4:34 am
HI All,
I am a having a procedure, which displays lacs of records, this is we are using it for reporting.
I have fine tuned the procedure. it is taking min 2 min time. how can i speedUp the queries inside the procedure. I can't show the code.. it is confidential...I had table hints, and joining hints...
will anybody Please give me some suggestions how to speed up the queries...
Thanks in Advance
April 2, 2012 at 4:46 am
First step. Take out the join hints and table hints. The chance that you know better than the optimiser is slim.
If you can't post the code, then I can only give vague suggestions:
Make sure the predicates are SARGable
Make sure you have indexes that support the queries
Make sure the query is as simple as possible and that there are no implicit conversions.
Edit: Remove scalar UDFs
and buy Grant's SQL Server performance tuning distilled book
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 2, 2012 at 5:00 am
I'm totally with Gail on this, remove the hints as a first step, then look at the execution plan to understand where things are going wrong.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2012 at 5:07 am
GilaMonster (4/2/2012)
First step. Take out the join hints and table hints. The chance that you know better than the optimiser is slim.If you can't post the code, then I can only give vague suggestions:
Make sure the predicates are SARGable
Make sure you have indexes that support the queries
Make sure the query is as simple as possible and that there are no implicit conversions.
and buy Grant's SQL Server performance tuning distilled book
I just Removed the Table hints, now it is taking same 1 min 30 sec to display...
Will you please explain me the further steps to Proceed..
Thanks
April 2, 2012 at 5:11 am
We can't see your query and we can't see your execution plan. With no knowledge, it's almost impossible to tell you what to do. As Gail said, look for problems such as functions on columns in the JOIN critieria or the WHERE clause, as well as other common simple problems. I suggested you look at the execution plan to understand how the query is being run. Other than that, I can't tell you much without a lot more information (including seeing the query and the execution plan). You may want to pick up a copy of my books, listed below in my signature. They're all about tuning queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2012 at 5:17 am
SqlSpider... (4/2/2012)
Will you please explain me the further steps to Proceed..
I did. In the post you quoted.
p.s. I've never seen or heard of code that confidential. Data, sure. Code is code and there's really nothing new under the sun.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply