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

<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>

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

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.

[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.

[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 ... ???