Re: Select Statement: Join vs Inner Select

From: SriSamp (ssampath_at_sct.co.in)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 16:11:21 +0530

The JOIN will definitely perform much better. In (2), the SELECT statement
will be executed for each row, which is huge overhead. Also, if you need
multiple columns from the same table, you will have to write repeated
SELECTs using the same join condition, which can severely hamper
performance. Using JOINs however, you can join the tables based on the keys
and then extract as many columns as you want. The execution plan also will
be much simpler and optimal.

Also, if you are executing this query from your VB app each time, SQL Server
will have to prepare an execution plan for each call. Rather, encapsulate
this as a stored procedure and call the stored procedure from your app. This
way, the plan is optimized and stored once and you also increase maintenance
of your code, since you can change your procedure code without affecting
your client. 

-- 
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Jason" <jasonserv@hotmail.com> wrote in message
news:uzh5gswFEHA.3064@tk2msftngp13.phx.gbl...
> Says I have 2 tables: tbTran & tbUser.
> Says the table has these fields:
> tbTran (SeqNo, UserID, AuthUserID, ...)
> tbUser(UserID, UserName, ...)
>
> Now, i need to list our the details of the transactions including the
> username. I have 2 options here:
> 1. Using Join table: "Select tbTran.SeqNo, tbTran.UserID,
tbUser1.UserName,
> tbTran.AuthUserID, tbUser2.UserName As AuthUserName From ((tbTran Left
Join
> tbUser tbUser1 On tbUser1.UserID=tbTran.UserID) Left Join tbUser tbUser2
On
> tbUser2.UserID=tbTran.AuthUserID)"
>
> 2. Using Inner Select: "Select SeqNo, UserID, (Select UserName From tbUser
> Where UserID = tbTran.UserID) As UserName, AuthUserID, (Select UserName
From
> tbUser Where UserID = tbTran.AuthUserID) As AuthUserName"
>
> This is only a simple illustration of the problem, some times the join
table
> can be as many as 10 tables. What I concerned about is the performance of
> these 2 statements, which one would give better performance when running
> from within a VB app connecting to a SQL Server? Please give me some
advice,
> thanks.
>
> Regards,
> Jason
>
>

Relevant Pages

  • Q: Select Statement: Join vs Inner Select
    ... tbTran & tbUser. ... tbUser(UserID, UserName, ...) ... "Select SeqNo, UserID, (Select UserName From tbUser ... Where UserID = tbTran.UserID) As UserName, AuthUserID, (Select UserName From ...
    (microsoft.public.sqlserver.programming)
  • Re: Using Password in Application
    ... course of execution. ... without user intervention I will have to store a username and password inside ...
    (microsoft.public.dotnet.languages.csharp)
  • Looking for help with a calendar logic
    ... laptop. ... I'd like a query that, upon execution (I would call it from ... UserID ) ...
    (microsoft.public.sqlserver)
  • Re: Execution time executing stored procedure
    ... I'd check for blocking and also compare the execution plan between the quick and the slow executions. ... > I am about to deploy a huge web-application, but ran into a strange problem. ... Then I watched the query that was executing in the Profiler. ... This is only happening when I write correct username and password. ...
    (microsoft.public.sqlserver.programming)
  • Re: Thread was being aborted
    ... "The Response.End method ends the page execution and shifts the execution to ... the Application_EndRequest event in the application's event pipeline. ... The username and password are ...
    (microsoft.public.dotnet.framework.aspnet)
Loading