Semi Join in SQL server

It’s an interesting fact that we never tend to use semi joins in SQL server, and instead we tend to use the more easy (or rather lazy) way of using an ‘IN’, which obviously takes huge amount of time (again depends !). I was writing a query to search from multiple tables (having millions of records) and don’t have much option of change, and it was taking lot of time (again depends!) to fetch the result set. That’s when I remembered about semi-join, replaced all lazy ‘IN’s with semi joins and I got alteast 40% performance improvement.

Now how to use semi joins

Semi Join

DELETE @AvalableOrgIdList FROM @AvalableOrgIdList AVL

                                    WHERE EXISTS (SELECT  OrgId FROM @TmpSrchResult RSLT WHERE RSLT.OrgId = AVL.OrgId)

Anti Semi join

DELETE @AvalableOrgIdList FROM @AvalableOrgIdList AVL

                                    WHERE NOT EXISTS (SELECT  OrgId FROM @TmpSrchResult RSLT WHERE RSLT.OrgId = AVL.OrgId)

Want more explanation and more in depth into SQL joins etc , check this blog.

http://blogs.msdn.com/craigfr/Default.aspx

-Rujith

After long days of being lazy, I finally managed to bring my blog up and running... This has been kind of dreem project for me, which was running for past few years now... :(, yes I am bit ashamed of myself. And I know this is not the way to put your first blog on ur new blog site, but thats the TRUTH...

Watch out for more stuffs in the space soon.......

-Rujith

Starting .msc files from .net application.

When writing code, lot of times we have come across situation where we want to start event viewer or any other application which uses management console . The code below shows how to start event viewer from .net application. This can be used to start services.msc or any other similar files.

Process traceFileViewerProcess = new Process(); 

string executable = Environment.ExpandEnvironmentVariables(@"%SystemRoot%\system32\eventvwr.msc"); 

traceFileViewerProcess.StartInfo.FileName = executable; 
 

traceFileViewerProcess.StartInfo.Arguments = "/s"; 

traceFileViewerProcess.Start();