Re: disable index while doing insert into

On May 17, 4:02 pm, Klaus Riemer <k...@xxxxxxxxxxxxxxxxxxx> wrote:
sai schrieb:> Hi All,

I have a query that does an "insert into... select from", both the
source and target table have multiple indexes, it is taking too long
to execute this query but when i've deleted the indexes on target
table, insert took very little time.

Is there a way to disable indexes before executing a query and enable
them back after executing query ?

This is a price you have to pay on any database not just Sybase.
Once you insert in a database with indexes then the index key fields
must be adjusted as well. B-trees eventually rebalanced and so on.

I assume you have a clustered index on your table. This is particular an
issue when you do mass inserts and the index will maintain a sort
order to this index. Then the effect you describe might become more
visible compared to other kind of index.
(Lots of I/O - operations)

If you could disable an index during a set of insert opeartions or mass
insert such as bulk copy (bcp) then your index is either incorrect or
the housekeeper must do it. So or so you would have either slow insert,
bad query performance and so on.

If your business process permits this procedure
I suggest to drop before insert and recreate indexes after insert /
bulkcopy. This is probably faster than slow insert e.g. on bulk insert.

Again as mentioned before: Beware of clustered indexes when doing
inserts with not corresponding sort - order. The server will then resort
the table on each insert operation. Occasional page split .... That is a
lot of work when inserting...

You have a lot of indexes - do you do reporting and analytics on your
database ? In this case you should really consider Sybase IQ - That one
is designed for this kind of job!

--Klaus Riemer, Greven - DE
--klausATroehlergenriemer!REMOVESPAMPROTECTORBEFOREMAIL!.de
--

Thanks a lot for your suggestions,
i was thinking about using update statistics after enabling indexes
( if at all there is a way to disable/enable indexes..)..but i guess
it turns out to be one and the same..forgive my ignorance

.

Relevant Pages

  • Re: Newbie question about formula based values
    ... if the query hangs or if Access crashes or is someone restarts ... If you want to execute the code from your web site, you can do that as well. ... If your web site can execute SQL code against an Access database, ... In Windows scheduler, create a new scheduled task based on Microsoft ...
    (comp.databases.ms-access)
  • TIP #308: Twylites concerns
    ... If, as you say, the simple change of adding a place for [$db execute] ... likely the result of a query against a system catalog. ... > especially for a pure-Tcl implementation for a specific database. ... 'execute' functionality that you discussed above address this concern? ...
    (comp.lang.tcl)
  • Re: .Execute vs. .RunSQL - Speed vs. Size
    ... >to know which is faster - Dim a database and then use the db.execute method ... Execute a sequence of action queries. ... Note that Execute requires you to resolve any query ... can gain orders of magnitude better improvement by making ...
    (microsoft.public.access.formscoding)
  • Re: Execute queries in back-end database via ADO !?!
    ... > I found that by defining an ADO connection to my back end database, ... execute a SQL query stored in my back end database. ... As long as your data is stored in an MDB file all query processing is ... What program would you expect to execute the query on the back end? ...
    (microsoft.public.access.queries)
  • Re: Concurrent/Parallel Execution
    ... run in parallel on the host os, then query the staging table that shows ... the select would run the parallel process on the target or host. ... parallelism to use all the resources of the central database server to ...
    (comp.databases.oracle.server)