asked on Could you explain this sequence adopted to increase performance at an update script by using MSSQLServer?
Hi Experts
Could you explain this sequence adopted to increase performance at an update script by using MSSQLServer?
At about 144.000 lines like this:
How to obtain and correctly to interpret this:
Sequence of actions adopted:
Created clustered index by cZiCode, but not UNIQUE, as there are duplicates.
Non-clustered index created by [lIdStreet], [cIdNeighborhood], [cZiCode], as recommended by the Execution Plan.
Thanks in advance.
Could you explain this sequence adopted to increase performance at an update script by using MSSQLServer?
At about 144.000 lines like this:
UPDATE dbo.XX1_StreetZipCodeExt set cNameStreet = 'Rua Padre Eustáquio',cIdNeighborhood = 4013,lIdStreet = 3047 where cZiCode ='35500521' and cIdNeighborhood =4013 and lIdStreet =3047
How to obtain and correctly to interpret this:
Sequence of actions adopted:
Created clustered index by cZiCode, but not UNIQUE, as there are duplicates.
Non-clustered index created by [lIdStreet], [cIdNeighborhood], [cZiCode], as recommended by the Execution Plan.
Thanks in advance.
Microsoft SQL Server
Last Comment
Eduardo Fuerte
ASKER
Hi
There was at about 144.000 (not a single update) lines like this, so they decided to create these indexes.
My doubts are about the steps and to explain the decision to create these index based on the plans, also how to obtain these plans.
There was at about 144.000 (not a single update) lines like this, so they decided to create these indexes.
My doubts are about the steps and to explain the decision to create these index based on the plans, also how to obtain these plans.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Cluster the table on the three columns, like so:
( cZiCode , IdStreet, cIdNeighborhood )
You will not need a nonclus index for this query once that's done.
( cZiCode , IdStreet, cIdNeighborhood )
You will not need a nonclus index for this query once that's done.
ASKER
Thank you for the help!
I'm not clear precisely what you are asking. Do you want to know why the index was suggested? Why/how the index affected the update's execution plan?