Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

How SQL Server Chooses the Type of Join Expand / Collapse
Author
Message
Posted Wednesday, February 7, 2007 8:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 12:03 PM
Points: 28, Visits: 38
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/mdillon/2863.asp
Post #343317
Posted Sunday, May 6, 2007 10:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 8, 2007 8:18 PM
Points: 2, Visits: 1

Thanks for the article, Mike!

Just curious: Why have not you used WITH RECOMPILE option? I believe one has more flexibility with this option if specified in a procedure call statement. Alternatively a procedure can be created with this option.

And the join hints can tell SQL to use a particular join every time, just in case

Post #363566
Posted Sunday, May 6, 2007 11:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 3, 2008 3:55 PM
Points: 8, Visits: 3

You're on the money with your detective work.

However, I agree with Alex that you should have considered the WITH RECOMPILE option with your create procedure statement.  That's probably in line with your intent.

I'm curious, did you call the sp_recompile on your stored procedure from within the stored procedure itself?

-Larry

Post #363569
Posted Monday, May 7, 2007 12:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2008 9:43 PM
Points: 1, Visits: 5

Great!! Recently one of the stored procedure was behaving some what in the similar way as mentioned, I did use sp_recompile which made it much faster, but didnt knew the reason behind..!!

The Great article for explaning the behaviour of sp..!!

Post #363576
Posted Monday, May 7, 2007 3:55 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, January 14, 2013 4:24 AM
Points: 81, Visits: 188

Thank you for posting this useful article, Mike. I also had the same experience in the past with the same cause (sp_recompile).

Regards, Vincent

Post #363604
Posted Monday, May 7, 2007 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 19, 2008 7:47 PM
Points: 1, Visits: 3

I am a novice and don't know much about the SQL server engine but Microsoft documentation claims that "Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so."

I found this claim at http://msdn2.microsoft.com/en-us/library/aa238892(SQL.80).aspx

Not sure what is it. Could some expert explain?

 

Post #363606
Posted Monday, May 7, 2007 5:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 12,908, Visits: 32,005
valuable article; whenever I found a situation similar to this, I always blamed out of date statistics, and updated statistics and recompiled procedures; nice detective work, I have a better understanding now.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #363614
Posted Monday, May 7, 2007 6:33 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 2, 2009 6:30 AM
Points: 66, Visits: 28
This is good info, but I don't see how it matches the article title. Would anyone mind "join"-ing the dots for me?


Redneckin',

Andrew Stanton
Post #363622
Posted Monday, May 7, 2007 6:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 26, 2010 3:37 AM
Points: 8, Visits: 54

Nice. I agree with others, you should use "with recompile" statement . And more, if you are using MS SQL 2k5, you may use "with recompile" on a statement rather on the batch. 

And some comments about the network level in .NET.  If you would be having problem with that layer, I guess you would see a waiting status for your spid with something like 'NETWORKIO'.

Best Regards,

Igor.

 

Post #363623
Posted Monday, May 7, 2007 7:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 10:52 AM
Points: 8, Visits: 35

A little off topic.  I have seen similar results (slow .NET access but fast in Query Analyzer) that seemed to be solved with "SET ARITHABORT ON".  Maybe by doing that I am forcing a recompile and it is the same problem.  Of course you need to be careful that the actual procedure doesn't have the potential for an actual error that ARITHABORT will falsely handle.

Hope that helps someone else.  I would welcome any other feedback on a relationship between how SQL Server and .NET are actually handling these differently at a code level.  Maybe there is some other solution that is more appropriate.

Enjoy,

Brian

Post #363629
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse