Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2008
    Posts
    14

    Unanswered: Drop/Recreate Tables in Sybase

    Hi,

    I am new to SYBASE, my application team reported that whenever they try to tun batch processing (update table) it usually took 2.5hrs to complete. But after they perform the follwoing steps the time duration dramatically dropped to 2-3mins.

    1) Original table is kpkgr_lmts0t
    2) Select * into kpkgro_lmts0 from kpkgr_lmts0t
    3) Drop table kpkgr_lmts0t
    4) Select * into kpkgr_lmts0t from kpkgro_lmts0
    5) Drop table kpkgro_lmts0
    6) Run in-house job Pre Batch

    I would highly appreciate any explanation/reasoning behind this phenomenon.

    Thanks a lot,
    Mike

  2. #2
    Join Date
    Mar 2007
    Posts
    72
    mike,
    wat about indexes, does the table not have any indexes. obviously after step 3 there is a step to recreate the table schema.

    when the new table is created and populated with rows from another table, the statistics become up-to-date and also that the pages are available in cache, so the physical i/o is reduced.

    what is the size of the table and ur cache, do a sp_showplan of the query that is doing the update and you will see the steps taken by the optimiser.

    hope this helps

    suda

  3. #3
    Join Date
    Jul 2008
    Posts
    14
    Hi Suda,

    Thanks a lot for your reply...

    Would it be a good practice to always perform the series of steps I have mentioned here in updating the involved table/s?

    Is there any negative impact if we will put this steps in place, like vulnerability to data corruption, etc...

    Thanks a lot,
    Mike

  4. #4
    Join Date
    Mar 2007
    Posts
    72

    drop & re-create tables

    dropping & re-creating objects is not the ideal solution. if you want to do select * into then the database option "select into bulk copy" needs to be turned on, this makes your database backups invalid. also, what is the time taken to create a temp table and then load all the data from the temp table to the newly created table, you haven't answered my question of the size of the table(s) involved and what kind of updates are being done and wat type of indexes are present, all these are very important in this scenario.

    ideally you should be running regular update statistics on your tables, indexes (weekly) so that the stats are up-to-date and your queries perform better. wat cache size do u have, is the database bound to any specific cache if so wat size is that.

    suda

  5. #5
    Join Date
    Jul 2008
    Posts
    14
    Hi Suda,

    The table size is 187MB, no index was used for this table.

    I asked the application team to provide me the details of their activity.

    1. The table that we are focusing is Kustom..KPKGR_LMTS0t.
    2. The Kustom database at UAT don't have any index.
    3. The table before and after drop same (no indexing)
    4. The command that we use to re-create table :
    SELECT * INTO Kustom..TEMP_KPKGR_LMTS0t FROM Kustom..KPKGR_LMTS0t
    DROP Kustom..KPKGR_LMTS0t
    SELECT * INTO Kustom..KPKGR_LMTS0t FROM Kustom..TEMP_KPKGR_LMTS0t
    DROP Kustom..TEMP_KPKGR_LMTS0t
    No more additional command.


    I do some testing and i found:
    1. based on log, before and after re-create table make the time very different
    BEFORE
    07-15 00:07:32[----]Calling kpeod_p4000j
    07-15 00:07:32[----]At kpeod_p4000p
    07-15 02:23:48[----]Result from kpeod_p4000p:2000Extraction files extracted/updated.
    07-15 02:23:48[----]End of run kpeod_p4000j

    AFTER
    07-15 02:47:35[----]Calling kpeod_p4000j
    07-15 02:47:35[----]At kpeod_p4000p
    07-15 02:52:38[----]Result from kpeod_p4000p:2000Extraction files extracted/updated.
    07-15 02:52:38[----]End of run kpeod_p4000j

    2. About the catch, i restart all the application and database and also do the same testing and the time still good.
    AFTER restart App and DB
    07-15 04:45:50[----]Calling kpeod_p4000j
    07-15 04:45:50[----]At kpeod_p4000p
    07-15 04:52:25[----]Result from kpeod_p4000p:2000Extraction files extracted/updated.
    07-15 04:52:25[----]End of run kpeod_p4000j

    I have already asked application team to send me the UPDATE script they are using.

    Thanks and regards,
    Mike

  6. #6
    Join Date
    Jul 2008
    Posts
    14
    Hi Suda,

    here are the update scripts used for the table...

    ---------------------------------------------------
    -- 1.4 Update Settlement utilisations using the max
    ---------------------------------------------------
    UPDATE KPKGR_LMTS0t
    SET LmtUtilAmt = SLS.MaxAmount
    FROM KPKGR_LMTS0t SLM,
    #TMP_SLS SLS
    WHERE SLM.EODDate = @CurDate
    AND SLM.EODSTS = @EODSTS
    AND SLM.LimitType = 'D'
    AND SLM.RiskDate = NULL
    AND SLM.LimitShortName = SLS.ShortName
    DROP TABLE #TMP_SLS

    -- ************************************************** ******************
    SELECT 'Updated Sett Limts with max util', GETDATE() --

    --------------------------------------------------
    -- 2. Derivew Level Limit Code from LimitShortName
    -----------------------------------------------
    -- a. list of limit with _LLL format
    UPDATE KPKGR_LMTS0t
    SET LimitCode = (CASE WHEN patindex('%[_]FX%', LimitShortName) > 0 THEN 'FX'
    WHEN patindex('%[_]CMI%', LimitShortName) > 0 THEN 'CMI'
    WHEN patindex('%[_]CMK%', LimitShortName) > 0 THEN 'CMK'
    WHEN patindex('%[_]DER%', LimitShortName) > 0 THEN 'DER'
    WHEN patindex('%[_]PLA%', LimitShortName) > 0 THEN 'PLA'
    WHEN patindex('%[_]STS%', LimitShortName) > 0 THEN 'STS' END)
    FROM KPKGR_LMTS0t
    WHERE EODDate = @CurDate
    AND EODSTS = @EODSTS

    -- b. get from fixed position if more than 14
    UPDATE KPKGR_LMTS0t
    SET LimitCode = substring(LimitShortName,12,3)
    FROM KPKGR_LMTS0t
    WHERE EODDate = @CurDate
    AND EODSTS = @EODSTS
    AND CHAR_LENGTH(RTRIM(LimitShortName)) >= 14
    AND LimitCode = NULL

    -- c. special for FX
    UPDATE KPKGR_LMTS0t
    SET LimitCode = 'FX'
    WHERE EODDate = @CurDate
    AND EODSTS = @EODSTS
    AND CHAR_LENGTH(RTRIM(LimitShortName)) = 13
    AND substring(LimitShortName,12,2) = 'FX'
    AND LimitCode = NULL

    --- d. take end with FX as limit
    UPDATE KPKGR_LMTS0t
    SET LimitCode = 'FX'
    WHERE EODDate = @CurDate
    AND EODSTS = @EODSTS
    AND patindex('%FX', LimitShortName) > 0
    AND LimitCode = NULL

    --- e. take end with LLL as limit
    UPDATE KPKGR_LMTS0t
    SET LimitCode = substring(LimitShortName,(CHAR_LENGTH(RTRIM(LimitS hortName)) -3),3)
    WHERE EODDate = @CurDate
    AND EODSTS = @EODSTS
    AND CHAR_LENGTH(RTRIM(LimitShortName)) >=6
    AND LimitCode = NULL

    Thanks a lot,
    Mike

  7. #7
    Join Date
    Mar 2007
    Posts
    72
    Hey,
    sorry for the delayed reply, was away for a while.
    Any DML activity on a table performs better if the query uses indexes. so the first step for you would be to create one (clustered) index on the table, with EODATE & EODSTS, LimitShortName, LimitCode as the index columns, how are you maintaining the referential / data integrity of the table, no priamry key, foregin etc.

    when you have the index and every time the bulk job does a lot of updates, do a update index statistics on this table, which will make the stats uptodate after the bulk modifcation to the rows.

    once u have done this, then, try to run your application (queries) with showplan turned on and see what the plan looks like.

  8. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Since there are no indexes it scans the whole table in both cases
    My guess is that there were a lot of changes to the table and it became fragmented.
    (e.g. scanning the table read thousands of pages instead hundreds)
    Similar processes that will also fix this is
    Recreating a clustered index or doing a reorg rebuild

    All the updates include
    WHERE EODDate = @CurDate
    AND EODSTS = @EODSTS

    I suggest you check what difference an index make
    create index ix1 on KPKGR_LMTS0t (EODDate, EODSTS)

    For the 1st update you might also need an index for LimitShortName
    And depending on the size and content of #TMP_SLS
    It might also benefit from an index.

Posting Permissions

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