Results 1 to 4 of 4

Thread: Consistency

  1. #1
    Join Date
    Feb 2002
    Posts
    16

    Unanswered: Consistency

    Hi guys
    I was wondering if you could help me.

    I wrote a perl script that creates a view (the view contains a sql SELECT statement) that executes against a sybase database. Once the view is created, the perl script calls the unix bcp statement to bulk copy out the data related to that view into a file.

    When I executed this script 2 weeks ago, the process took 24 hours to bulk copy out the data from the sybase database.

    I have just come back from a 2 week vacation and executed the same script and after 48 hours, the process has not completed.
    I have asked my DBA guys whether or not something has changed in the environment during the last 2 weeks but no was the answer.

    Would you guys have any advice as to what I can check to determine the nature of the delay in the Sybase engine ?

    I would be most grateful.
    Thanks in advance
    Tony

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    It is hard to say. Have statistics been updated? What was the row count of the tables involved before and now? What is the query plan of the view?
    Thanks,

    Matt

  3. #3
    Join Date
    Feb 2002
    Posts
    16
    Originally posted by MattR
    It is hard to say. Have statistics been updated? What was the row count of the tables involved before and now? What is the query plan of the view?
    Hi Matt,
    Thanks for the email.
    The row count of the tables remains the same as before as the database is isolated to my team.
    The query plan is as follows :
    STEP 1

    The type of query is SELECT.
    FROM TABLE
    REQ_AC
    CA
    Nested iteration.
    Index : req_ac_idx
    Forward scan.
    Positioning by key.
    Index contains all needed columns. Base table will not be read.
    Keys are:
    AC ASC
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.

    FROM TABLE
    SERV_PRE
    SERV
    Nested iteration.
    Index : svcs_pre_idx01
    Forward scan.
    Positioning by key.
    Keys are:
    AC ASC
    Using I/O Size 2 Kbytes for index leaf pages.
    With LRU Buffer Replacement Strategy for index leaf pages.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    FROM TABLE
    SUBS
    SUB
    Nested iteration.
    Using Clustered Index.
    Index : IDX_00
    Forward scan.
    Positioning by key.
    Keys are:
    AC ASC
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.

    Does the showplan look unusual in any way ?
    Any advice would be greatly appreciated.
    Many thanks
    Tony

  4. #4
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Inconsistency

    Tho

    1 There is not enough specific info in your post (and showplan fragment) to isolate the issue. Pls provide:
    - full original SQL
    - full showplan (of the create view)
    2 Your Inconsistency is unsurprising. The server serves all active uses, not just one connection, so you really need to understand:
    - the loads on the server during the two durations
    - any server config changes between the two durations
    (not just the table/db in question)
    - the rowcounts in ALL affected tables
    3 Do update stats on all affected tables and indices related to the View.
    4 Generally Views are slow performers (uses tempdb which is a shared resource and could be hammered by other users). There are several ways to enhance the SQL code; avoid tempd (mostly if not completely). Here is ONE method:
    1. create tempdb..result_table
    2. select ... into tempdb..result_table
    3. [IFF the output data is not in the required sequence]
      create index ... clustered on tempdb..result_table
    4. update stats all
    5. bcp tempdb..result_table
    6. truncate table tempdb..result_table

    [If you do not expect to have space for (3), do it before (2)]
    5 If you are using tempdb or Views, tempdb needs decent spread and tuning. What have you done to tune tempdb ?
    6 There are the usual errors to look out for, like datatype mismatches.
    7 I would not live with a 24- or 48-hour bcp, even for billions of rows. Do you know how to stripe bcp ? Where is your output file going ? Is it ufs, is it fragmented, is there space ?

    For quick response, email derek@softwaregems.com.au
    Last edited by DerekA; 09-17-02 at 09:14.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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