What is the proper way to use...

  • What is the proper way to use WITH(NOLOCK)... i have been using it like this

    a.Name

    from Applicant a With(Nolock)

    Innner join Employer e With(Nolock) On A.ID = E.ID

     

     


    Moe C

  • You have the right syntax. Why do you need to use that?

  • We have a large database and this thing grows like more then 100,000 rows a day... an i get to use Reporting Services to report off views ... so i need all the performance tips i can get to build these views, stored procedures, and UDF.... so far i have been using WITH(Nolock) and set nocount on everywhere i can... oh and takin the GO off at the end of Stored procedures... any other tips anyone would recommend... thanks


    Moe C

  • Set nocount is more of a network boost (small but not insignificant). The nolock will speed up the reads but that can include uncomited data in the selects, can that be a problem? How's removing the go at the end of the code gonna speed up the process?

  • I read somewhere that you really don't need the GO at the end of Stored procedures and views and such because the Stored procedure or view or whatever already knows to execute so you don't really need to tell the Stored procedure to execute twice... if you get what i am sayin....


    Moe C

  • I don't get it actually. The go statement is the batch separator in QA. It just tells where the statement ends, it doesn't actually make it execute over and over again.

  • I can't remember the excact words of the article but its just stated something to the effect of not telling SQL to do the same thing twice cause it already knows to do it...i don't know if there is any truth to what it said... or how much of a preformace gain there is... but the article could be wrong...  


    Moe C

  • Run the test yourself... start profiler, run a simple select statement without go, then again with go... it'll run only once each.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply