Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010

    Unanswered: Probs with the performance of the proc

    For below mentioned scenario can any one please advice if this is a right approach or still there is a cleaner way to tune the query
    The scenario is as follows:

    I have a query in one of the existing proc which is of kind
    insert into #table
    select distinct <70 columns>
    from <join on 7 tables which have data in millions>

    create a nonclustered index on the above mentioned #table

    I am planning to convert this query into

    set table count 7
    select * into #table
    <70 columns>
    from <join on 7 tables which have data in millions>

    create clustered index on #table with ignore_dup_row--to get distinct rows

    Thanking in advance for time


  2. #2
    Join Date
    Jun 2010
    Changing is insert into to select * into is the rite step. Make sure all the 7 tables have index in them and indexes are being used by the sybase optimizer. You can do this this by set showplan on.

    Also, I would suggest you to break down your query in 2 pieces. Create 1 temp table using 3/4 tables and then create final table by joining temp tables previously created and remaining of 7 tables. You can create clustered index on the intermediate temp table too.

  3. #3
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    If trying to speed things up, why in the world would you insert to a temp table??? Work should be done on the query to alter the problem that is slowing things down so much. As the prior poster already suggested look at the plan and see how the tables are being accessed. If still continuing to have issues, post the SQL and we may be able to offer some suggestions.

Posting Permissions

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