Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    41

    Unanswered: Need to Optimize the SQl Query

    Hi Everyb,
    I need to optimize the below query.the following query returns only 632 out 4460000 rows.

    Datatypes:

    Region varchar
    Status int
    Server varchar
    Directory varchar
    PercentUsed decimal
    AvailableSpace int
    scantime datetime



    select Max(status),server,max(scantime)
    from Tbl_LotusServerDiskSpaceReport b
    where
    scantime=(select max(scantime)
    from Tbl_LotusServerDiskSpaceReport a
    where
    a.server=b.server
    and
    a.percentused is not null
    and
    a.AvailableSpace is not null)
    and b.percentused is not null
    and b.AvailableSpace
    is not null
    group by server order by server


    Thanks in Advance ,
    Arun

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    First, the max on scantime in line 1 is not required, because you are only getting one scantime value per server, thanks to the subquery. So your query becomes:
    Code:
    select Max(status),server,scantime
    from Tbl_LotusServerDiskSpaceReport b
    where scantime=
    (select max(scantime)
     from Tbl_LotusServerDiskSpaceReport a
     where a.server=b.server
     and a.percentused is not null
     and a.AvailableSpace is not null)
    and b.percentused is not null
    and b.AvailableSpace is not null
    group by server,scantime
    order by server
    Now, if (server, scantime) form a unque key for the table then you don't need the max and group by at all:
    Code:
    select status,server,scantime
    from Tbl_LotusServerDiskSpaceReport b
    where scantime=
    (select max(scantime)
     from Tbl_LotusServerDiskSpaceReport a
     where a.server=b.server
     and a.percentused is not null
     and a.AvailableSpace is not null)
    and b.percentused is not null
    and b.AvailableSpace is not null
    order by server
    I assume there is an index on the server column at least?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •