January 26, 2011 at 9:38 am
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
January 26, 2011 at 9:43 am
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
January 26, 2011 at 12:46 pm
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
January 27, 2011 at 7:30 am
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
January 27, 2011 at 7:57 am
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
January 27, 2011 at 2:52 pm
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