Professional Web Applications Themes

Importing lots of data - Microsoft SQL / MS SQL Server

It is a good idea to drop and re-create the indexes to improve the performance -- HTH, SriSamp Please reply to the whole group only! "GriffithsJ" <jonathan.griffithsipuk.com> wrote in message news:OfQzxRwPDHA.2052TK2MSFTNGP11.phx.gbl... > Hi > > I wish to delete/import a lot of data into a database and want it to run as > quickly as possible (using DTS here). > > The triggers don't need to run, so to improve performance I should obviously > disable the triggers. > > Is there anything else that I should be considering here in terms of the > database schema? What about indexes ...

  1. #1

    Default Re: Importing lots of data

    It is a good idea to drop and re-create the indexes to improve the
    performance
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "GriffithsJ" <jonathan.griffithsipuk.com> wrote in message
    news:OfQzxRwPDHA.2052TK2MSFTNGP11.phx.gbl...
    > Hi
    >
    > I wish to delete/import a lot of data into a database and want it to run
    as
    > quickly as possible (using DTS here).
    >
    > The triggers don't need to run, so to improve performance I should
    obviously
    > disable the triggers.
    >
    > Is there anything else that I should be considering here in terms of the
    > database schema? What about indexes - I would have thought that it would
    be
    > more efficient to disable the indexes, import the data and then rebuild
    the
    > index rather than keeping the index on all thie time. Is this assumption
    > correct? If so, is there a way to disable all indexes and re-enable them
    as
    > one can with triggers rather than dropping them and rebuilding them (I ask
    > because I don't really want to hard-code the index definitions into the
    DTS
    > package - the database schema might change from time to time and I don't
    > want to have to keep updating the package...).
    >
    > Thanks in advance
    >
    > Griff
    >
    >

    SriSamp Guest

  2. #2

    Default Re: Importing lots of data

    Hi SriSamp

    Thanks for that. However, is there a way of simply disabling the currently
    existing indexes and re-enabling them afterwards? I ask this because the
    indexes that may be there when I design the DTS package may not be the same
    ones that exist when the DTS package is run (the database schema may
    change).

    Alternatively, is there a way to identify at run time the indexes that
    exist, store all the information necessary to re-create these indexes, drop
    these indexes, import the data and them rebuild the indexes from the stored
    information?

    Any code examples would be much appreciated.

    Thanks

    Griff


    GriffithsJ Guest

  3. #3

    Default Re: Importing lots of data

    Whilst SriSamp is correct in the main the benefits to be had by removing
    indices was reduced with the move from SQL Server 7 to 2000. It may be
    beneficial to keep them in if you need to select from the destination whilst
    doing a load. Try it and see.

    You can script out the indicies with a drop and recreate statement by using
    SQLDMO and SQLServerAgent.

    --


    Allan Mitchell (Microsoft SQL Server MVP)
    MCSE,MCDBA
    [url]www.SQLDTS.com[/url]
    I support PASS - the definitive, global community
    for SQL Server professionals - [url]http://www.sqlpass.org[/url]

    "GriffithsJ" <jonathan.griffithsipuk.com> wrote in message
    news:Ou6KezwPDHA.304tk2msftngp13.phx.gbl...
    > Hi SriSamp
    >
    > Thanks for that. However, is there a way of simply disabling the
    currently
    > existing indexes and re-enabling them afterwards? I ask this because the
    > indexes that may be there when I design the DTS package may not be the
    same
    > ones that exist when the DTS package is run (the database schema may
    > change).
    >
    > Alternatively, is there a way to identify at run time the indexes that
    > exist, store all the information necessary to re-create these indexes,
    drop
    > these indexes, import the data and them rebuild the indexes from the
    stored
    > information?
    >
    > Any code examples would be much appreciated.
    >
    > Thanks
    >
    > Griff
    >
    >

    Allan Mitchell Guest

  4. #4

    Default Re: Importing lots of data

    If you want to get the list of indexes defined on a table, you can use a
    script like the following:
    DECLARE tableName VARCHAR(50)
    DECLARE sqlString VARCHAR(8000)
    BEGIN
    CREATE TABLE #tableIndexes
    (
    index_name VARCHAR(200),
    index_description VARCHAR(1000),
    index_keys VARCHAR(1000)
    )

    SET tableName = 'authors'
    SET sqlString = 'INSERT INTO #tableIndexes EXEC sp_helpindex ' +
    tableName
    EXEC (sqlString)

    SELECT * FROM #tableIndexes
    DROP TABLE #tableIndexes
    END
    The above script will get all the indexes into the temporary table called
    #tableInexes. You can then iterate through this table and form the command
    for dropping the index and execute the same. For creating an index back, you
    need to play-around the same set of tables or probably "sysindexes". I do
    not have any script for this, but someone in the group might post it.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "GriffithsJ" <jonathan.griffithsipuk.com> wrote in message
    news:Ou6KezwPDHA.304tk2msftngp13.phx.gbl...
    > Hi SriSamp
    >
    > Thanks for that. However, is there a way of simply disabling the
    currently
    > existing indexes and re-enabling them afterwards? I ask this because the
    > indexes that may be there when I design the DTS package may not be the
    same
    > ones that exist when the DTS package is run (the database schema may
    > change).
    >
    > Alternatively, is there a way to identify at run time the indexes that
    > exist, store all the information necessary to re-create these indexes,
    drop
    > these indexes, import the data and them rebuild the indexes from the
    stored
    > information?
    >
    > Any code examples would be much appreciated.
    >
    > Thanks
    >
    > Griff
    >
    >

    SriSamp Guest

  5. #5

    Default Re: Importing lots of data

    This has been reported as bug. Check out:
    [url]http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q293/1/77.ASP&NoWebContent=1[/url]
    --
    HTH,
    SriSamp
    Please reply to the whole group only!

    "GriffithsJ" <jonathan.griffithsipuk.com> wrote in message
    news:OOueOLxPDHA.2636TK2MSFTNGP10.phx.gbl...
    > Interestingly, I ran the code and found a "hypothetical" index that
    doesn't
    > exist on the schema...
    >
    > hind_264648286_2A_7A_10A
    > nonclustered, hypothetical, auto create located on PRIMARY
    > fieldA, field B, fieldC
    >
    > So what's this when it's at home?
    >
    >

    SriSamp Guest

Similar Threads

  1. Importing data from one to another
    By DataPacks.com in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 26th, 01:14 PM
  2. Lots of pictures, lots of problems
    By The Eclectic Electric in forum Macromedia Flash
    Replies: 3
    Last Post: February 26th, 09:00 PM
  3. Importing FDF Data
    By Volker_Kleinschmidt@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 5
    Last Post: May 7th, 03:08 PM
  4. Importing Excel data
    By chris in forum FileMaker
    Replies: 5
    Last Post: August 19th, 01:34 PM
  5. Triggers and importing data once again
    By Przemo in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 6th, 04:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139