SQL Server Performance

Which query is faster/better? (Screen shots included).

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Pure.Krome, Nov 26, 2007.

  1. Pure.Krome New Member

    <P mce_keep="true">Hi folks,<BR>&nbsp;&nbsp; I'm trying to go through my stored procs finding stuff that's not very fast/performant. Below is one example of some sql which i'm not sure is very good. All my data retrieval is handled via stored procs. To try something different, i've tried to create some dynamic sql inside a stored procedure to reduce joins and stuff (if applicable) ... though i'm not sure how to look at the query analzyer results.</P><P mce_keep="true">&nbsp;So - i'll start out with the stored procedure, then move onto dynamic sql inside a stored procedure. </P><P mce_keep="true">Please take note of the ISNULL(..) usage in the first statement, which i've tried to replace with dynamic sql.</P><P mce_keep="true">EDIT: Some of the screen shots are getting visually truncated (to the right). To see the full image, right click on the image and grab the image url :)</P><P mce_keep="true">---------</P><P mce_keep="true">&nbsp;<IMG src="http://img57.imageshack.us/img57/5839/sp11ub3.png" mce_src="http://img57.imageshack.us/img57/5839/sp11ub3.png"></P><P mce_keep="true">&nbsp;<IMG src="http://img77.imageshack.us/img77/3340/sp12yi3.png" mce_src="http://img77.imageshack.us/img77/3340/sp12yi3.png">&nbsp;<IMG src="http://img77.imageshack.us/img77/627/sp13un6.png" mce_src="http://img77.imageshack.us/img77/627/sp13un6.png"></P><P mce_keep="true">&nbsp;</P><P mce_keep="true">Now for the second attempt, this time with the fixed T-SQL changed to now be a dynamic sql statement with the WHERE clause be determined if we have an ID's or not. In this example, the IdSecondaryCity is&nbsp;ZERO (due to&nbsp;some ISNULL(..) code)&nbsp;... so it's not included in the WHERE clause. In essence, the attempted difference between this dynamic sql code and the fixed stored proc code (above) is that any Id's that are zero are NOT included, while above they are 'cancelled out' .. but still exist in the query plan.</P><P mce_keep="true"><IMG src="http://img77.imageshack.us/img77/5854/sp21hb7.png" mce_src="http://img77.imageshack.us/img77/5854/sp21hb7.png"></P><P><IMG src="http://img77.imageshack.us/img77/7273/sp22vn5.png" mce_src="http://img77.imageshack.us/img77/7273/sp22vn5.png"></P><P><IMG src="http://img77.imageshack.us/img77/9136/sp23hf9.png" mce_src="http://img77.imageshack.us/img77/9136/sp23hf9.png"></P><P><IMG src="http://img80.imageshack.us/img80/225/sp24vm1.png" mce_src="http://img80.imageshack.us/img80/225/sp24vm1.png"></P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P>Thanks heaps folks - looking forward to some help :) I can provide more screenies and reasons for why i did certain code if required.</P><P>Cheers! :)</P><P>-PK-</P><A href="http://img77.imageshack.us/img77/5854/sp21hb7.png"></A>
  2. ranjitjain New Member

    Hi,
    I think you can't paste pictures here, as I can't see any images, only imageholder is visible.
    Anyways, you have said that you are using dynamic SQL which should be avoided. Go through this very good article on the same.
    http://www.sommarskog.se/dynamic_sql.html
  3. Adriaan New Member

    Instead of using dynamic SQL, you can also replace
    ISNULL(column,0)=@param
    with this:
    ((column IS NULL AND @param=0) OR column = @param)
    If you have fixed query statements that use alternative techniques, then put them together in a batch script and ask for the execution plan. This will give you the relative cost for each statement in the batch, and the one statement with the lowest percentage should be the best performing one. Note that this is only a rule-of-the-thumb, so you will have to test thoroughly to see which version actually performs best.
  4. Pure.Krome New Member

    [quote user="Adriaan"]
    Instead of using dynamic SQL, you can also replace
    ISNULL(column,0)=@param
    with this:
    ((column IS NULL AND @param=0) OR column = @param)
    [/quote]
    Hi Adriaan, I'm assuming you're referring to the first image which has the fixed SQL? I'll try going your suggestion in the morning.
  5. Pure.Krome New Member

    [quote user="ranjitjain"]I think you can't paste pictures here, as I can't see any images, only imageholder is visible.[/quote]
    er .. the images work for me both at work and at home ... ???

Share This Page