Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Unanswered: how to optimize this slow query?

    tblElements.ID is a foreign key to tblOwner.eID
    @PrevRec = dynamic number of previous records
    @owner = owner ID

    SELECT TOP 2 tblOwner.eID FROM tblOwner
    LEFT JOIN tblElements ON
    tblElements.ID = tblOwner.eID
    WHERE tblOwner.own_ID = @Owner AND tblElements.ID NOT IN
    (SELECT TOP + @PrevRec + tblOwner.eID FROM tblOwner
    LEFT JOIN tblElements ON
    tblElements.ID = tblOwner.eID WHERE tblOwner.own_ID = @Owner) ORDER BY tblOwner.eID ASC

    This query is used to display one record per page on a ASP paging script, I select top 2 because, I want to be able to know if I have another page or record to go to and deside if I need to display the "Next" button.

    Also It is suppose to select records only assigned to it's ower.
    It works fairly quick when viewing first few hundred records, then it takes a very long time.

    I have indexed ID and eID as clustered indexes also tblOwner could have multiple instances eID.

    Is there an easier way of writing this out.

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Re: how to optimize this slow query?

    Change the NOT IN to a LEFT OUTER JOIN where the right joining column is null

    this should be a bit quicker ??

    something like

    select a.*
    from table1
    left outer join table2
    on a.id = b.id
    where b.id is null

    is the same as

    select * from table1 where id not in
    (select id from table2)

    get a non clustered index on own_id

    HTH

  3. #3
    Join Date
    Nov 2003
    Posts
    46
    Thanks for the tip, unfortunatley your solution won't work for like I need, besides that I found a much faster way, which involves executing 2 separate queries to get the result.

    Thanks again for your soulution, I did use it in a different part of the project.

    --Lito

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking

    nice one mate

    glad I could help.

    Somethimes it easier to break your problem down into multiple queries to get the same answer and can ba a lot faster as well

    kinder on the server resource as well if you do short queries to get smaller recordsets rather than trying to write one massive query which just grinds SQL Server to halt.

    Cheers

Posting Permissions

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