Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Unhappy Unanswered: join returns no result.

    Hi,

    I have PeopleSoft running on ASE 11.9.2 and today a failure occured on adding some budget information to existing project.

    I found the SQL statement which results the problem:

    It does an insert to a project table based on a join with another table, the value if compared manually exists in both tables. But when doing a join it returns no result. We allready had a similar problem and it was solved by:
    bcp out -> rebuilding the table -> bcp in -> rebulding indexes.

    I gues it will work this time too (just had to leave the office very early today) but I wonder why this problem occurs at all and how to prevent it. Did anybody experience similar problems with joins? And is it about corrupt index. Because the same query work fine on our test server with the same data as production.

    Any suggestions?

    Thanks a lot,

    SL

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: join returns no result.

    How often do you run DBCCs?
    Are there any errors in the error log?

  3. #3
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Re: join returns no result.

    Hi,

    The thing is that I've just started at this department two weeks ago, and I suppose that DBCC commands were never run here.

    The issue I wrote about was a bad Index on one of the tables. Rebuilding indexes solved the problem. But what is recommended to prevent this from happenning again? I was thinking of a stored procedure that drops all the indexes and recreates them once in a while (weekly or monthly).

    Is there any better approach?

    Thanks a lot,

    SL
    kdb is the solution of all problems.
    Just try it, www.kx.com

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: join returns no result.

    That's not a bad approach. Certainly dropping and recreating indexes will help with regard to performance, espcially if there is a large amount of data manipulation being performed.

    If you're using All Pages locking dropping and recreating the clustered index on each table will reduce fragmentation and it rebuilds the other indexes on the table at the same time. It's also worth running update statistics (or rather update all statistics) to regenerate the optimiser statistics for the tables. This is less of an impact than dropping and recreating the clustered index. If you're not using All Pages Locking, use reorg rebuild. This can be given an amount of time to run in and it can pick up from where it previously finished.

    It's probably worth creating a non clustered index on each table. This allows you to have an alternative access path to each row in the table, should the links between the pages become corrupt. You can perform select * from table and force the non clustered index.

    A better solution is to implement dbcc checkstorage. While this requires a little bit of fore thought and planning running this frequently will provide you will a nice warm security feeling knowing that you are checking the consistency of you database. If you act on any errors found by dbcc checkstorage you should be able to save yourself hassel of recovering the database should things go really badly (and believe me they do...)

    If you can't put the work in to planning and setting up dbcc checkstorage use dbcc checkdb and dbcc checkalloc together, these take much longer and make a bigger impact with concurrent activity.

    Most of details for this stuff can be found in the System Administration Guide and the Performance and Tuning guide. If you don't have them to hand check out http://www.sybase.com/support/manuals for online versions.

    Richard.

  5. #5
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    Finally

    Hi Richard,

    thanks a lot on helping me to analyze that situaton and giving good advise on the solution of the problem. I think I will post some things later under Sybase 11.9.x and PeopleSoft. I've heard that many people suffer...

    Best Regards,

    Slava
    kdb is the solution of all problems.
    Just try it, www.kx.com

  6. #6
    Join Date
    Mar 2003
    Location
    Vienna
    Posts
    24

    But as I said

    www.kx.com

    See yourself, that thing is sexy...

    SL
    kdb is the solution of all problems.
    Just try it, www.kx.com

Posting Permissions

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