How can CREATE INDEX be made more effecient

Question

How can CREATE INDEX be made more effecient

Viewing 4 posts - 16 through 18 (of 18 total)

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

Bhuvnesh (8/20/2010)

Stefan_G (8/20/2010)
I have made some tests and for some of my large tables creating a second identical index is 3 times faster than creating the original index.
i will take this but any reason for it ? how new one will work better then recreating existing one ?

Did you read the thread?

The problem is that the OP has a tool that he needs to run. He can not modify the tool in any way.

The tool tries to create an index and times out.

The suggested solution is to create an identical index with the same definition but a different name before running the tool.

(He can't use the same index name because then the tool will get an error while trying to create an already existing index)

/SG

YSLGuru SSC-Insane Points: 21661 More actions · Answer 2

Stefan_G (8/20/2010)
GilaMonster (8/19/2010)
YSLGuru (8/19/2010)
I take it you don't believe that an existing index with teh same structure but by a different name would help speed up the create index statement?
It quite likely would, as SQL could then scan that index instead of the cluster to get the data it needs. It's a waste of space, it'll slow down the purge process, but it should speed up the creation of a new index. By how much is another matter...
I believe that there is a very good chance that this will speed up the index creation very much.
Not only will creating the second index only need to scan the existing index, it will also not have to sort the data.
I have made some tests and for some of my large tables creating a second identical index is 3 times faster than creating the original index.
/SG

Thanks Stephan for the input. I'd say this approach definately qualifies as a creative work-a-round to the problem.

Kindest Regards,

Just say No to Facebook!

YSLGuru SSC-Insane Points: 21661 More actions · Answer 3

UPDATE

Well this test of creating a duplicate index by a differnet name has appeared to of worked. The purge tool ran to completion without errors and the Index it creates was built a lot faster then it was taking before, when I had not yet setup the duplicate dummy index proposed in thsi thread.

CONCLUSION: You can reduce the time it takes for some automated process to build an Indxex by creating in advance your own index that is indetical in every way except for the name.

Thanks to all who took time to rpely.

Kindest Regards,

Just say No to Facebook!

Stefan_G SSCertifiable Points: 6609 More actions · Answer 4

YSLGuru (8/20/2010)
UPDATE
Well this test of creating a duplicate index by a differnet name has appeared to of worked. The purge tool ran to completion without errors and the Index it creates was built a lot faster then it was taking before, when I had not yet setup the duplicate dummy index proposed in thsi thread.

Glad that it worked.

After all it was your own idea from the start. We just confirmed that it might work.