Re: SQL Query Performance

From: Wayne Snyder (wsnyder_at_computeredservices.com)
Date: 05/19/04 

Date: Wed, 19 May 2004 07:53:16 -0400

What you should do is
set statistics io on
set statistics time on
Execute the query and see which is faster.
Also look at the execution plan in Query Analyzer to ensure proper indexes
are being used.... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"agustina_s" <agustina_s.16hul4@mail.mcse.ms> wrote in message
news:agustina_s.16hul4@mail.mcse.ms...
>
> Hi..
>
> Is there any specific place where I can find which SQL Query is more
> efficient?.
>
> Is INNER JOIN is faster or Select ... Where ID in (SELECT ...) is
> faster?
>
> I have two tables:
> 1.FLEET (The number of rows is not so much)
> Attributes :
> Company_Id  (PK)
> Fleet_Id    (PK)
> Fleet_Name
> Fleet_Description
>
> 2.USER_PRIVILEGE (The number of rows can reach up to 3 times the number
> of row in fleet table)
> Attributes :
> Company_Id  (PK)
> Fleet_Id    (PK)
> User_Id     (PK)
> Privilege_Id(PK)
> Comment
> Category
>
> I want to select Fleet_Id and Fleet_Name from fleet table
> Where the current user has privilege.
>
> I have two possible select statement :
>
> 1.Option 1
> SELECT     Fleet_Name, Fleet_Id
> FROM       FLEET
> WHERE      (Company_Id = 2) AND (Fleet_Id IN
> (SELECT   fleet_id
> FROM     user_privilege
> WHERE    user_id = 11 AND company_id = 2 AND privilege_id =
> 1))
> ORDER BY Fleet_Name
>
> 2.Option 2
>
> SELECT     F.Fleet_Name, F.Fleet_Id
> FROM       USER_PRIVILEGE U INNER JOIN
> FLEET F ON U.Fleet_Id = F.Fleet_Id
> WHERE     (F.Company_Id = 2) AND (U.Privilege_Id = 1) AND (U.User_Id =
> 11)
> ORDER BY F.Fleet_Name
>
> Actually which one is faster. Is SQL Statement with INNER JOIN (Option
> 2) can be executed faster than the one with double Select
> Statement(Option 1)?
>
> Any siggestion is welcomed.
> Thank you very much.
>
>
>
> --
> agustina_s
> ------------------------------------------------------------------------
> Posted via http://www.mcse.ms
> ------------------------------------------------------------------------
> View this thread: http://www.mcse.ms/message689772.html
>

Relevant Pages

  • Re: SQL 2000 manual start job sort order
    ... As you might know, without ORDER BY, SQL Server is free to optimize and execute a query in any way ... then the optimizer has to ...
    (microsoft.public.sqlserver.server)
  • Re: Closing a connection BEFORe command execution has completed
    ... closing the connection with Connection.Close does not close the connection--it simply releases it back to the pool so the engine will continue to fill the cache--but it's not that large so the operation might simply hang resources on the server until the connection is reused. ... The problem is, since all of the Execute methods are synchronous, there is no way to interrupt the query or action command process. ... Because of the way TDS and SQL Server responds to cancel requests your operation might not be immediately "stoppable"--SQL Server might have some cleanup to do before releasing control and resources. ...
    (microsoft.public.dotnet.framework.adonet)
  • SQL Server 2005 with iSeries DB2 Linked Server problem
    ... I have the linked serverset up and and I can execute the ... following query from the SQL Server Management Studio and it works ... I can even create a view of the query in my Master ... database and it works from the SQL Server. ...
    (microsoft.public.sqlserver.connect)
  • Re: Could not find stored procedure
    ... To add on to Dejan Sarka post, Simply execute the below query in Query ... > loin is mapped to a specific db user dbo, so SQL Server searches dbo.name ...
    (microsoft.public.sqlserver.server)
  • Re: CONTAINS performance
    ... That said, and with the query plan, I can start to give you more ... relational join in the context of the free-text optimization, ... SQL Server tables. ...
    (microsoft.public.sqlserver.fulltext)