Indexing....

Is it possible to schedule indexing?
I am loading the data from flat file to table every day night from a
scheduled job.
After loading the data I would like to index the table so that search
becomes very fast.
I am using SQL2K.
Thanks,
Joan AlexJoan Alex wrote:
> Is it possible to schedule indexing?
> I am loading the data from flat file to table every day night from a
> scheduled job.
> After loading the data I would like to index the table so that search
> becomes very fast.
> I am using SQL2K.
> Thanks,
> Joan Alex
If you are already scheduling the job to import the data, why can't you
add the indexing to the end of that batch? Or as a new step in the job?
You can easily add a new step to the job that runs after the successful
completion of the first "import" step.
David Gugick
Imceda Software
www.imceda.com|||What you are saying is
1. Call the CREATE index STATEMENT after loading the data.
2. Do I have to drop the index before creating the index with the same name.
I can check this in the documenation. Just asked.
Thanks,
Joan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Joan Alex wrote:
> If you are already scheduling the job to import the data, why can't you
> add the indexing to the end of that batch? Or as a new step in the job?
> You can easily add a new step to the job that runs after the successful
> completion of the first "import" step.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Joan Alex wrote:[vbcol=seagreen]
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the
> same name. I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
You can use the WITH DROP EXISTING clause in the CREATE INDEX statement
to force SQL Server to drop it for you. It also has other benefits. Just
run it on the clustered index on the table and you should be set.
David Gugick
Imceda Software
www.imceda.com|||How many rows are you importing and are there rows already in the table?
You may be better off dropping the indexes before you import and then
creating them again after the import.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
> What you are saying is
> 1. Call the CREATE index STATEMENT after loading the data.
> 2. Do I have to drop the index before creating the index with the same
> name.
> I can check this in the documenation. Just asked.
> Thanks,
> Joan
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uUzT9pr7EHA.1408@.TK2MSFTNGP10.phx.gbl...
>|||Everyday nightly scheduled job truncates the table and loads the data. The
data will be around 2 milllion records every day.
Do you suggest drop the index first, truncate the table, load the data from
the flatfile and build the index.
Thanks,
Joan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
> How many rows are you importing and are there rows already in the table?
> You may be better off dropping the indexes before you import and then
> creating them again after the import.
> --
> Andrew J. Kelly SQL MVP
>
> "Joan Alex" <JAlex45@.hotmail.com> wrote in message
> news:ON2IAAs7EHA.1260@.TK2MSFTNGP12.phx.gbl...
>|||It can depend but I would Truncate it first in any case. Then you should
test a few ways to see which gives the best performance in regards to
importing with the indexes intact or removed. The Clustered index can
usually be the most significant factor but you usually can get faster
overall times by dropping them, importing and then creating. If the
Clustered index is on a monotonically increasing column such as Identity or
a Datetime that will be imported in chronological order you might want to
leave the clustered index intact and drop the non-clustered ones. If it's
on a column that has a fairly random value you might be best to remove it
before importing. Always create the clustered index first before creating
any nonclustered ones. You should also see if you can do a "minimally
logged bulk load" for even faster performance. See "minimally logged bulk
copy" in BooksOnLine for details on what you need to accomplish that.
Andrew J. Kelly SQL MVP
"Joan Alex" <JAlex45@.hotmail.com> wrote in message
news:%23fB4S%23s7EHA.2516@.TK2MSFTNGP09.phx.gbl...
> Everyday nightly scheduled job truncates the table and loads the data. The
> data will be around 2 milllion records every day.
> Do you suggest drop the index first, truncate the table, load the data
> from
> the flatfile and build the index.
> Thanks,
> Joan
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eqJDO4s7EHA.1392@.tk2msftngp13.phx.gbl...
>