Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PSGusano
Starting Member
1 Post |
 Posted - 2006-02-10 : 19:54:15
|
| Hi,I have a PeopleSoft Financials implementation in which saving security info (permission lists for those who are familiar with PeopleSoft) takes approx. 20 minutes. I tracked the problem down to each INSERT or DELETE operation that is executed on one of the security tables. Each INSERT takes about 16 seconds and the same happens for DELETEs (I tried inserting ONE row and deleting that same row in Query Analyzer obtaining the same results). I tried the "Show execution plan" option and got some operations with high costs:Three sort operations (12%, 9% and 4% cost)One collapse (4% cost)Two Merge Join/Left Outer Join (5% and 2% cost)One Hash Match/Aggregate (30% cost)I've tried INSERT's and DELETE's in other tables and the only actual activity involved is "Table Insert" or "Table Delete". Why would this table have so many activities involved in these queries? I'm only trying to insert or delete ONE ROW. As you can probably tell, I'm not very experienced in SQL Server. Can anybody please help? Thx in advance. |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-02-10 : 22:44:49
|
| Check to see if the table has triggers set on it. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-02-11 : 01:33:38
|
| Hi PSGusano, Welcome to SQL Team!I'd go with triggers too. If that isn't the case:Maybe its inserting into a VIEW which has an INSTEAD OF trigger.Failing that do you do regular "best practice" maintenance on the database? If not it could also be one of these:Some hefty Foreign Keys lacking indexes to assist the query.Or STATISTICS have not been updated recentlyOr the indexes need recreating, or defragging.Or, if the task is in a Stored Procedure, the SProc needs recompiling (stop/starting SQL would sort that one out for all SProcs)Or the MDF and LDF files are badly fragmentedKristen |
|
|
|
|
|
|
INSERT and DELETE (for one row) takes 16 seconds