optimize cost sort
-
hello ,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- This topic was modified 3 years ago by abdalah.mehdoini.
- This topic was modified 3 years ago by abdalah.mehdoini.
-
- 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.
-
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/ -
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 RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
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. -
This was removed by the editor as SPAM
hello ,Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply