Post reply

How i fast my large data INSERT statement

  • July 24, 2008 at 3:01 am

    #188571

    Hi all,

    is there any option or HINT for insert statament using that i can increse my insert statement speed.

    also what is the function of,

    DBCC DROPCLEANBUFFERS

    DBCC FREEPROCCACHE

    and when we may use these options.

    Best Regards
    Faheem latif
    Senior Database Architect
    Genie Technologies (Pvt.) Ltd.

  • July 24, 2008 at 3:18 am

    #847589

    Hi,

    BOL is best option to find the answer of your questions.

    DBCC DROPCLEANBUFFERS Removes all clean buffers from the buffer pool.

    Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

    Cheers,
    Hari
    Tips & Tricks for SQL BI Developers

  • July 24, 2008 at 3:30 am

    #847597

    faheemlatif (7/24/2008)

    Hi all,

    is there any option or HINT for insert statament using that i can increse my insert statement speed.

    SQL doesn't have a RUNFASTER hint, if that's what you're asking. I can't really gve suggestions on speeding up an insert without knowing more about what is being inserted and what the destination table looks like

    DBCC DROPCLEANBUFFERS

    DBCC FREEPROCCACHE

    and when we may use these options.

    In general, never on a production system. They're useful for testing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 24, 2008 at 3:31 am

    #847599

    DBCC FREEPROCCACHE Removes all elements from the procedure cache.

    Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache.

    Cheers,
    Hari
    Tips & Tricks for SQL BI Developers

  • July 24, 2008 at 3:44 am

    #847604

    Hari.Sharma (7/24/2008)

    Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache.

    As well as every other procedure and query. Generally will result in heavy CPU usage on a production system until the proc cache is repopulated with the common queries/procedures.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 24, 2008 at 4:20 am

    #847613

    Thanks all

    Best Regards
    Faheem latif
    Senior Database Architect
    Genie Technologies (Pvt.) Ltd.

Viewing 6 posts - 1 through 5 (of 5 total)

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