Post reply

BULK INSERT HELP

  • September 27, 2011 at 9:35 am

    #390485

    I see following query running on Database which is kicked off by an application.... It is taking 15 to 30 seconds to bulk insert 5000 rows at a time rows which is very very slow.

    I did check memory,CPU,Network and Harddisk and everything seems perfectly fine.

    FYI - there are no indexes the bulk insert table. I have no idea what is going on..

    Here is the query which it runs..

    I see process ID which runs select query and another processid is does bulk insert and both from same application.. I dont know how these processes ID'd are internally linked

    Here is the select query with processid 90

    eclare @P1 int

    set @P1=3

    exec sp_prepare @P1 output, NULL, N'

    SELECT

    a.*

    FROM "dbo"."tablex" CLM WITH (NOLOCK)

    inner join tabley WITH (NOLOCK)

    on CLM.FromDate = FDTS.skey_Date

    inner join D_Dates TDTS WITH (NOLOCK)

    on CLM.ToDate = TDTS.skey_Date

    inner join D_Dates PDTS WITH (NOLOCK)

    on CLM.DatePaid = PDTS.skey_Date

    group by

    FDTS.skey_CalendarMonth,

    TDTS.skey_CalendarMonth,

    PDTS.skey_CalendarMonth,

    CLM.ClaimID,

    "RS_tc",

    CLM."sd",

    CLM.Age,

    CLM.Prockey,

    CLM.Approved,

    CLM.test, 1

    select @P1

    here is the bulk insert from same application with different process id 91

    insert bulk Mytable("col1" bigint,"col2" bigint,"col3" bigint,"col4" bigint,"col5" int,"col6" int,"col7" varchar(61) COLLATE SQL_Latin1_General_CP1_CI_AS,"test1" int,"col19" int,"col90" int,"test1" int)

    When I kill "select" process ID it is automatically killing bulk insert process ID.

    If I run the same select from backend it gave the results very quckly... The issue is bulk insert is very slow...and I dont know how to fix it.

    Can someone help me where to start looking to find it..

  • September 27, 2011 at 12:31 pm

    #1387408

    Gilamonster - Any advice on what to look ?

  • September 29, 2011 at 9:52 am

    #1388474

    OK to try and help you need to be a little clearer.

    Is the process inserting the results of the select query into a table?

    Is this all within the same database and SQL Instance?

    Usually the term BULK INSERT refers to copying data from an external souce for example a text file into a table using something like BCP \ SSIS or the SQL Function BULK INSERT.

    MCITP SQL 2005, MCSA SQL 2012

Viewing 3 posts - 1 through 2 (of 2 total)

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