Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Improve query performance

    Hi All

    I need help on improving a query which perform badly in our Production system.
    The query requires an Temp table
    And i'm posting the creating Temp table code for your ease
    I'm looking for improvements of the query performance [re-write in a better way] as the
    first solution.

    --Temp Table Creating Script
    create table #temp_userdivs (
    div_id int not null
    );
    --drop table #temp_userdivs

    CREATE INDEX [#temp_userdivs_x1] ON [#temp_userdivs] (div_id) WITH (DATA_COMPRESSION = PAGE);

    declare @geoperm bit;
    set @geoperm = 0;
    select @geoperm = isnull(intval, 0) from tt_orgpref where org_id = 2112 and preftype = 95

    if(@geoperm = 0)
    begin
    -- Insert for ALL divisions
    insert into #temp_userdivs
    select 0

    insert into #temp_userdivs
    select
    tt_orgdiv.orgdiv_id
    from tt_orgdiv
    inner join tl_orgdiv_user on tl_orgdiv_user.orgdiv_id = tt_orgdiv.orgdiv_id
    where tl_orgdiv_user.user_id = 89263 and tt_orgdiv.orgdivstat = 1
    group by tt_orgdiv.orgdiv_id

    end


    -- Query Perform badly

    select top 1000 tt_book.book_id, null
    from tt_book
    where 1 = 1
    and tt_book.createtime >= 'Jun 9 2013 10:00PM'
    and tt_book.createtime <= 'Jun 14 2013 9:59PM'
    and tt_book.type = 1
    and tt_book.carrierorg_id = 13197
    and tt_book.status in ( 2,3,4,6,7)
    and exists ( select 1
    from tt_bookparty
    inner join #temp_userdivs on #temp_userdivs.div_id = tt_bookparty.orgdiv_id
    where tt_bookparty.book_id = tt_book.book_id
    and tt_bookparty.createtime >= 'Mar 11 2013 10:00PM'
    and tt_bookparty.org_id = 2112
    and tt_bookparty.bookpartytype in (1, 4, 6, 11, 17) )
    and exists (select 1
    from tt_bookservice
    where tt_bookservice.book_id = tt_book.book_id
    and ( tt_bookservice.destcity_id = 2000100 or isnull(tt_bookservice.destcity_id, 0) = 0
    and tt_bookservice.entryportcity_id = 2000100 )
    and tt_bookservice.entryportcity_id = 2000398
    and ( tt_bookservice.origcountry_id = 218 or isnull(tt_bookservice.origcountry_id, 0) = 0
    and tt_bookservice.exitportcountry_id = 218 ) )
    order by tt_book.modtime desc

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what does the access path say about the query? Have you reviewed that? Also, why bother with this temp table? should be able to just inner join to that SQL within your EXISTS subselect. Even with that 0 division id that you are slipping in there, that could be done with a union.
    Dave

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    Hey Dave I didnt understand your explanation
    But here is the SQL plan I'm attaching as a .sqlplan file and the xml too [If you are interested in reading the XML]
    XML.txt
    query.zip

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your mixing of AND and OR is quite confusing.
    I'd be surprised if you are getting the correct, desired results.
    It would appear that you only get a resultset when @geoperm = 0?

    What is the query attempting to achieve?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    mixing of AND and OR is quite confusing
    I think this may be because of doing the check in an odd way, but I do agree there may be a logic error here in the middle. AI, you would probably be better off writing this plainly, like and then fixing the line in BOLD:
    Code:
    and (   tt_bookservice.destcity_id = 2000100
        or ( tt_bookservice.destcity_id is null
        and tt_bookservice.entryportcity_id = 2000100 )
    and tt_bookservice.entryportcity_id = 2000398 
        and ( tt_bookservice.origcountry_id = 218
           or ( tt_bookservice.origcountry_id is null
           and tt_bookservice.exitportcountry_id = 218 ) )
    As for what I meant about getting rid of the temp table. Replace:
    from tt_bookparty
    inner join #temp_userdivs on #temp_userdivs.div_id = tt_bookparty.orgdiv_id
    With something like:
    Code:
    from tt_bookparty 
     inner join table(select
     tt_orgdiv.orgdiv_id
     from tt_orgdiv
     inner join tl_orgdiv_user on tl_orgdiv_user.orgdiv_id = tt_orgdiv.orgdiv_id
     where tl_orgdiv_user.user_id = 89263 and tt_orgdiv.orgdivstat = 1
     group by tt_orgdiv.orgdiv_id) as #temp_userdivs
    
        on #temp_userdivs.div_id = tt_bookparty.orgdiv_id
    Dave

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, I did not read/look at your attachments. As I said, you should be reviewing the plan and determining if you are doing a tablespace scan, could you use an index, etc....
    Dave

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    did you look at your "Missing Index" hint in the query plan output?

    Code:
    /*
    Missing Index Details from query.sqlplan
    The Query Processor estimates that implementing the following index could improve the query cost by 97.5917%.
    */
    
    /*
    USE [qalive1b]
    GO
    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
    ON [dbo].[tt_book] ([carrierorg_id],[type],[status],[createtime])
    INCLUDE ([book_id],[modtime])
    GO
    */
    
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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