June 20, 2011 at 11:27 pm
Hey dudes,
I had an interview yesterday , And in that meeting , they asked me :"How can we get better performance in our queries? for example we can use set nocount."
I had no Idea what it was, and didn't know what can we do for better performance...:(
Know I want to learn. Could you please help me findout some more?
Best Regards,
Ashkan
June 20, 2011 at 11:39 pm
You know there is nice book out there SQL Server 2008 Query Performance Tuning Distilled by Apress.
you can read it ,we have to see why query is behaving bad,then we can give perfect answer.
June 21, 2011 at 12:01 am
Set Nocount
when you set on this,SQL Server will not count the rows ,it will increase the performance because overhead of counting must be disabled and another overhead is print
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 21, 2011 at 12:14 am
@Gaurang-Patel : thanks, but You know, As I said he wants me something like that, I need some help if it is possible
Best Regards,
Ashkan
June 21, 2011 at 12:20 am
@Syed Jahanzaib Bin hassan: Thanks a lot, It really helps me to understand what they really mean....
Could you please describe more ,and also if you know more functions like this...
Best Regards,
Ashkan
June 21, 2011 at 12:28 am
@ashkan siroos
i suggest you to read grant fritchey's perfomance books. It will clear your doubts.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 21, 2011 at 1:00 am
Syed Jahanzaib Bin hassan (6/21/2011)
Set Nocountwhen you set on this,SQL Server will not count the rows ,it will increase the performance because overhead of counting must be disabled and another overhead is print
The only thing that SET NOCOUNT affects is the printing of the X Row(s) affected message.
SQL still knows how many rows the operation affected. Easily proven, use SELECT @@RowCount straight after and the row count is still correct, even if SET NOCOUNT ON was run first.
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
June 21, 2011 at 1:20 am
performance tuning is a vast topic and you need to explain that to the interviewer, if he is looking for a specific answer he should know better. Performance tuning is dicated by the business needs as well as the actual functionality provided in that version of sql.
Queries can perform poorly because of bad coding practice or because the server configuration is not right.
How you would approach them differ and the first step is to identify the root cause.
Tuning memory , network packet size, placement of files on disk , using RAID etc are some server side tuning options
Adding indexes , normalizing or denormalizing as required, using table variable , using joins , avoiding cursors and loops where possible are option on how to tune the query.
Another thing to keep in mind is that there could be a number of minor tweaks which can improve performance such as suing no count etc but the real improvements come from right coding practice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply