Post reply

optimize cost sort

  • November 16, 2021 at 4:46 pm

    #3953670

    Sans titrehello ,

    do you have any idea for optimize the sort object

    in my table I have only one clustered index in column id

    I created a nonclustered index on the dateInsertion column but I still have an index browse

    GO

    CREATE TABLE [dbo].[BLMachines](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [distinguishedName] [varchar](max) NULL,
    [samAccountName] [varchar](max) NULL,
    [TPMRecoveryInformation] [varchar](max) NULL,
    [PasswordID] [varchar](max) NULL,
    [dateInsertion] [varchar](max) NULL,
    [dateBL] [varchar](max) NULL,
    [RecoveryPassword] [varchar](max) NULL,
     CONSTRAINT [PK_BLMachines] PRIMARY KEY CLUSTERED 
    (
    [index] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    Attachments:
    You must be logged in to view attached files.

  • November 16, 2021 at 6:02 pm

    #3953697

    • Your DDL script is wrong. You have specified your primary key on a column "index" that doesn't exist in your table definition.
    • Why are all your columns except ID varchar(max)???? That doesn't reflect reality, and can't help performance.

      • distinguishedName, samAccountName, TPMRecoveryInformation, & recoveryPassword should be big enough for the longest possible value plus some "cushion".  Nothing has a 2-billion-character name. Highly doubtful anything has an 8000 character name.
      • PasswordID:  Is this an integer? a uniqueidentifer (guid)? Or really a string? Make it the right datatype and size. It's doubtful you have any 2-billion-character PasswordIDs.
      • dateInsertion & dateBL should probably be datetime, datetime2, or date.

    Is ID used in other queries for joins, filtering, or sorts?  Or are one of your date columns more often used? If the latter, you should reconsider using your clustered index on the date column rather than ID (ID could still be a non-clustered primary key)

    You might need to include the columns in your select in the non-clustered index for the query engine to be able to use it effectively.

  • November 16, 2021 at 6:03 pm

    #3953698

    Your data types are wrong.

    In your query, you are ordering by  DateInsertion. Since this is stored as characters, and not a date, your sort is not going to return in date order.

    I suggest creating your table with the proper data types as a first step.

    Secondly, the create table SQL you posted is not correct.  Your creation of the primary key is referring to a column named "index". That does not exist.  "Index" is a reserved word in SQL, and is not a good choice for a name.

    Last, ID is probably not a good choice for your clustered index.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • November 17, 2021 at 1:25 pm

    #3954031

    Without a WHERE clause, no filtering occurs, so it goes to the cluster to pull stuff together. If you want to eliminate the sort, you can create an index on the DateInsertion column, but it must then have an INCLUDE with all the other columns in the query. You'll basically be creating a second clustered index to all intents & purposes. Sacrificing storage for performance can be a valid approach.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • November 17, 2021 at 2:54 pm

    #3954074

    Another way to optimize the sort operation would be to remove it by removing your ORDER BY statement.  If you don't order the data, no sorting would be required.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 18, 2021 at 6:08 am

    This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply