Results 1 to 5 of 5

Thread: T-SQL question

  1. #1
    Join Date
    Oct 2004
    Posts
    28

    Unanswered: T-SQL question

    Running into performance issues on datamart updates for 3 out of 71 tables. I'm getting some PAGEIOLATCH_SH showing up and nothing seems to be happening.

    Here is the gist of the process. Any help/advice would be greatly appreciated.

    3rd party tool used to bring new records into db where a tinyint flag is 0. Afterwards a stored proc is kicked off that refreshes that flag to 1.
    Then the following proc is kicked off that modifies some columns to ensure accurate data. ( see below)

    Background info
    1. PK = CallId + CallModifiedAt
    2. A call with remodifiedat = '1/1/3000' is the newest version of the call (thus it has not been remodified) This can change.
    3. EvalFlag. A tinyint field that can either be 0 or 1.
    a. 0 = record can still be modified
    b. 1 = more recent record exists. Never need to touch these.

    4. RefreshFlag. A tinyiny field that can either be 0 or 1
    a. 0 = record has NOT been refreshed/ or newest that still needs evaluation
    b. 1 = record has been refreshed

    5. Indices
    a. PK clustered
    b. have tried numerous non-clustered to no performance gain.

    6. Purpose of proc

    Looks at all records that have 1/1/3000 (most current) and turns off refreshflag. Then selects all current records into temp table that need evaluation (evalflag=0) Update the table by joining with #temp. Set Eval Flag = 1 on all records that have more current versions. Refresh everything.

    Stats.
    About 8million rows with less than 1 million unique CallId's. So the majority of the table is not even being touched (at least I think, because of evalflag)
    Takes about 45 minutes to run.

    What do you guys think? Index issue? tempdb issue? crappy design issue? Any advice to get me in the right direction would be GREATLY appreciated.


    create proc Call_Test


    update Call_Fact
    set remodifiedat = getutcdate(),
    refreshflag = 0
    where remodifiedat = '1/1/3000' and RefreshFlag = 1



    select distinct CallId,max(CallModifiedat) as modifiedat,evalflag
    into #temp
    from Call_Fact
    where evalflag=0
    group by CallId,evalflag
    order by CallId


    update c
    set c.RemodifiedAt = '1/1/3000'
    from Call_Fact c
    join #temp t
    on c.CallId = t.CallId
    and c.CallModifiedAt = t.ModifiedAt


    update Call_Fact
    set EvalFlag = 1
    where EvalFlag = 0
    and RemodifiedAt <> '1/1/3000'


    update Call_Fact
    set RefreshedAt = getutcdate(),
    RefreshFlag = 1
    where RefreshFlag =0

    GO

  2. #2
    Join Date
    May 2002
    Posts
    299
    this could be the killer:

    select distinct CallId,max(CallModifiedat) as modifiedat,evalflag
    into #temp
    from Call_Fact
    where evalflag=0
    group by CallId,evalflag
    order by CallId

    distinct requires an internal worktable (in tempdb) for sorting and you're bulkcopy to tempdb. If your tempdb doesn't have enough space, it has to grow - everything will suspend until the growth finishes. Pre-grow the db prior to this query would help a bit.

    update c
    set c.RemodifiedAt = '1/1/3000'
    from Call_Fact c
    join #temp t
    on c.CallId = t.CallId
    and c.CallModifiedAt = t.ModifiedAt

    create an index on #temp.CallID should help here. what you expect for a join of the a large table (8mil) and a non-indexed #temp.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Oct 2004
    Posts
    28
    What about a table variable? That would (I think) let me not worry about tempdb as we anticipate these tables growing (DataMart situation)

  4. #4
    Join Date
    May 2002
    Posts
    299
    What about table variable. It's actually not the solution here.

    1. You cannot create index on @tb (other than defining the pk at the @tb declaration).
    2. you have to do insert into @tb - 100% logged.
    3. for such large dataset, tempdb is the final destination for @tb anyway.

    If someone has told you that @tb resides only in RAM, it's really unfounded.

    http://support.microsoft.com/kb/305977
    --
    -oj
    http://www.rac4sql.net

  5. #5
    Join Date
    Oct 2004
    Posts
    28
    Been goofing around in test environment that has about 1/3 of the data. What really gets me is that running the 5 different updates individually takes about 1/2 the time as running as stored proc. Any ideas?

    What about putting begin tran and commit around the parts?

    I'm adding ddl of temp table w/ pk defined before the insert and removing the order by clause. Will let you know what happens.

    Thanks

Posting Permissions

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