Insert takes long

  • Hello,

    I have a simple INSERT in a stored procedure.

    I do this from a .NET Client, which calls the procedure.

    It inserts about 10 columnn values with ordinary data types (varchar(50), int) and a value of type image. (tif, ~130 KB)

    There is no WHERE clause, just INSERT.

    There are some indexes on the table.

    I listened to

    The first time I do the insert, it takes 30 seconds to finish the stored procedure.

    The second time I do the same insert, it takes 1 second.

    Why this difference?

    Is there a cache or something like this?

    Where can I search for the bottleneck?

    Thank You

    Joachim

    Edited with vim

  • Are there any triggers on the table? Check contraints? Foreign Keys? All of these can have an effect. But my first thought was triggers.

    CEWII

  • Take a look at the execution plan. On a guess, it's doing scans based on some foreign keys, loading that data into cache in the first query, just reading from the cache in the second. But, that's a guess.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • OK following your hints I looked at some properties:

    Table Triggers:None

    (Check) Constraints:3 x "Default 0"; 1 "Column = 'NN' OR Column ='BST'"" (Simple, I think)

    Foreign Key Constraints: None; just 5 other Tables around which refer to the Primary Key. The FK-Columns are indexed.

    The Execution Plan shows no Scans. It shows the by far most cost on the "Clustered Index Insert", and a little cost on "Clustered Index Update".

    The Clustered Index (PK) of the table is of type integer, identity.

    Is is part of a few other, nonclustered indexes.

    When I look at the "properties", the "Output List" says "Versorgungsart" which is the name of another "ordinary" Column. I don't know what this means or what the sense is.

    I wonder why the cost of maintainig the Clustered Index Insert ist the only which matters. Additional there is 2% clustered index seek, all other costs are 0.

    Is it meaningful to look for problems in the index structure?

    index_name index_description index_keys

    Belege_KT___Covering_Index_OPTIMIERT_20090527_TOHnonclustered located on BILDER Datum_Erledigt(-), Kreditor_KEY, Versorgungsart, EVUMandantenVertragsnr

    Belege_KT___Laufende_nr_OPTIMIERT_20090527_TOH nonclustered located on BILDER Laufende_Nr

    ids_Belege_KT_AutoBeleg nonclustered located on BILDER Kreditor_KEY, Belegarten_KT_KEY, Sammelbelege_KEY, User_Gesperrt, Beleg_KT_Status, Datum_Gesperrt, GUID

    idx_belege_kt_3_columns nonclustered located on BILDER Sammelbelege_KEY, Belege_KT_KEY, Versorgungsart

    IDX_Belege_KT_dtVon_dtBis_Belegart_Status nonclustered located on BILDER Datum_Von, Datum_Bis, Belegarten_KT_KEY, Beleg_KT_Status

    IDX_Belege_KT_User_Gesperrt_Datum_Gesperrt_GUID nonclustered located on PRIMARY User_Gesperrt, GUID, Datum_Gesperrt

    idx_belege_kt_versorgungsart nonclustered located on BILDER Versorgungsart, Kreditor_KEY, Belegarten_KT_KEY

    idx_belege_kt_versorgungsart__kreditor_key nonclustered located on BILDER Versorgungsart, Kreditor_KEY

    idx_noncl_belege_kt_sammelbelege_key nonclustered located on BILDER Sammelbelege_KEY, Belege_KT_KEY, Versorgungsart, Datum_Von, Datum_Bis, Beleg_KT_Status, Zahlbetrag, EVUKundenVertragsnr, EVUMandantenVertragsnr, Import_Dateiname

    IX_Belege_KT_Beleg_KT_Status nonclustered located on BILDER Beleg_KT_Status

    IX_Belege_KT_Belegarten_KT_KEY nonclustered located on BILDER Belegarten_KT_KEY

    PK_Belege_KT clustered, unique, primary key located on PRIMARYBelege_KT_KEY

    Scanstation nonclustered located on BILDER Scanstation

    - Joachim

    Edited with vim

  • Schuttberg (1/27/2011)


    OK following your hints I looked at some properties:

    Table Triggers:None

    (Check) Constraints:3 x "Default 0"; 1 "Column = 'NN' OR Column ='BST'"" (Simple, I think)

    Foreign Key Constraints: None; just 5 other Tables around which refer to the Primary Key. The FK-Columns are indexed.

    The Execution Plan shows no Scans. It shows the by far most cost on the "Clustered Index Insert", and a little cost on "Clustered Index Update".

    The Clustered Index (PK) of the table is of type integer, identity.

    Is is part of a few other, nonclustered indexes.

    When I look at the "properties", the "Output List" says "Versorgungsart" which is the name of another "ordinary" Column. I don't know what this means or what the sense is.

    I wonder why the cost of maintainig the Clustered Index Insert ist the only which matters. Additional there is 2% clustered index seek, all other costs are 0.

    Is it meaningful to look for problems in the index structure?

    index_name index_description index_keys

    Belege_KT___Covering_Index_OPTIMIERT_20090527_TOHnonclustered located on BILDER Datum_Erledigt(-), Kreditor_KEY, Versorgungsart, EVUMandantenVertragsnr

    Belege_KT___Laufende_nr_OPTIMIERT_20090527_TOH nonclustered located on BILDER Laufende_Nr

    ids_Belege_KT_AutoBeleg nonclustered located on BILDER Kreditor_KEY, Belegarten_KT_KEY, Sammelbelege_KEY, User_Gesperrt, Beleg_KT_Status, Datum_Gesperrt, GUID

    idx_belege_kt_3_columns nonclustered located on BILDER Sammelbelege_KEY, Belege_KT_KEY, Versorgungsart

    IDX_Belege_KT_dtVon_dtBis_Belegart_Status nonclustered located on BILDER Datum_Von, Datum_Bis, Belegarten_KT_KEY, Beleg_KT_Status

    IDX_Belege_KT_User_Gesperrt_Datum_Gesperrt_GUID nonclustered located on PRIMARY User_Gesperrt, GUID, Datum_Gesperrt

    idx_belege_kt_versorgungsart nonclustered located on BILDER Versorgungsart, Kreditor_KEY, Belegarten_KT_KEY

    idx_belege_kt_versorgungsart__kreditor_key nonclustered located on BILDER Versorgungsart, Kreditor_KEY

    idx_noncl_belege_kt_sammelbelege_key nonclustered located on BILDER Sammelbelege_KEY, Belege_KT_KEY, Versorgungsart, Datum_Von, Datum_Bis, Beleg_KT_Status, Zahlbetrag, EVUKundenVertragsnr, EVUMandantenVertragsnr, Import_Dateiname

    IX_Belege_KT_Beleg_KT_Status nonclustered located on BILDER Beleg_KT_Status

    IX_Belege_KT_Belegarten_KT_KEY nonclustered located on BILDER Belegarten_KT_KEY

    PK_Belege_KT clustered, unique, primary key located on PRIMARYBelege_KT_KEY

    Scanstation nonclustered located on BILDER Scanstation

    - Joachim

    Ah, the slowdown may just be all the maintenance required on those indexes. By the way, you have a duplicate index. idx_belege_kt_versorgungsart and idx_belege_kt_versorgungsart__kreditor_key have the same leading edge. You don't need the index that only has the first two columns.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Any other process may be blocking your insert. Check for server blocks if your insert takes longtime. This could be one of the reasons.

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

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