Simple Insert Issue
-
I am trying to do a simple insert in sql analyzer and it just hangs or if it runs from an odbc connection, we get a timeout error:
INSERT INTO Designs
(DESIGN_NUM, END_RINGS, COLOR_TABS, STUDIO_INSTR, FILMS)
VALUES ('CC 25641',1,0,1,0)
If I use a different design number, it works:
INSERT INTO Designs
(DESIGN_NUM, END_RINGS, COLOR_TABS, STUDIO_INSTR, FILMS)
VALUES ('XX 25641',1,0,1,0)
I can then do an update which works:
update Designs set design_num = 'CC 25641' where DESIGN_NUM = 'XX 25641'
Any help is appreciated....Richard
-
Is there an insert trigger that might be causing the issue?
-SQLBill
-
There are only 14,355 rows.
I do not see any triggers; this is a very simple database. There is a relationship on the design and design_detail tables.
Here is the table script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Designs_Detail_Designs]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Designs_Detail] DROP CONSTRAINT FK_Designs_Detail_Designs
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Designs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Designs]
GO
CREATE TABLE [dbo].[Designs] (
[DESIGN_NUM] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TO_NAME] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TO_COMPANY] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TO_ADDRESS1] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TO_ADDRESS2] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TO_CITYSTZIP] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INPUT_DATE] [datetime] NULL ,
[SCREENS] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAT_REPEAT] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WIDTH] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LEGEND] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOB_NUM] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENGRAVER] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_RECD] [datetime] NULL ,
[DEL_DATE] [datetime] NULL ,
[NOTES] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLANT] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHP_DATE] [datetime] NULL ,
[ARTIST] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NUM_COLORS] [float] NULL ,
[COMP_OPER] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ART_TIME] [float] NULL ,
[COMP_TIME] [float] NULL ,
[SCR_ROL] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENG_ PRICE] [float] NULL ,
[LIB_TAPE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILENAME] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STEP] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STEP_HEIGHT] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STEP_WIDTH] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OLD_NUM_REPEATS] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OLD_RESOLUTION] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMP_COMMENTS] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOB_TYPE] [int] NULL ,
[OLD_COMMENTS] [nvarchar] (210) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EST_COST] [float] NULL ,
[PATTERN_NUM] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASER] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WIP] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DISC_NUM] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASER_DISC_NUM] [nvarchar] (90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCR_LENGTH] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCR_SIZE] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WIDTH_ON_SCR] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HEIGHT_ON_SCR] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DROP_ON_SCR] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HRES] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VRES] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REPEATS_CIRCF] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENG_BEGIN] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENG_MODE] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENG_WIDTH] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASER_PWR] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SURFACE_SPEED] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENG_FOCUS] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCR_CIRCF] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILE_TYPE] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OLD_FILENAME] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STRT_PT_X] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STRT_PT_Y] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REPEAT_WIDTH] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REPEAT_HEIGHT] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOR_RESOL] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VERT_RESOL] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DROP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCAN_FOCUS] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JOIN_OPTION] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCAN_WIDTH] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SCAN_HEIGHT] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LEFT_BEGIN] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSTRUCTIONS] [nvarchar] (185) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CAD_NUM] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INKJET_OUT_DT] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INKJET_APPR_DT] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMER] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LEGEND_TO_READ] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INPUT_RESOL] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INSPECT_APPR] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[END_RINGS] [bit] NOT NULL ,
[BACKUP_DISC_NUM] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COLOR_TABS] [bit] NOT NULL ,
[STUDIO_INSTR] [bit] NOT NULL ,
[FILMS] [bit] NOT NULL ,
[NUM_RPTS_HT] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NUM_RPTS_WDTH] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[R_S_DIM_HT] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[R_S_DIM_WDTH] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXPOSE_HT] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXPOSE_WDTH] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RESOL_HT] [float] NULL ,
[RESOL_WDTH] [float] NULL ,
[LSR_HV_HT] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LSR_HV_WDTH] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LICENSOR] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REV_DEL_DATE] [datetime] NULL ,
[ACT_COST] [float] NULL ,
[FILL_IN_TIME] [float] NULL ,
[LINE] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GROUP_NAME] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VARIANCE] [float] NULL ,
[DELIVERY_TIME] [float] NULL ,
[ANGLE_X] [float] NULL ,
[ANGLE_Y] [float] NULL ,
[RES_X] [float] NULL ,
[RES_Y] [float] NULL ,
[LEVEL] [float] NULL ,
[RASTER] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GRAPH1] [float] NULL ,
[GRAPH2] [float] NULL ,
[GRAPH3] [float] NULL ,
[GRAPH4] [float] NULL ,
[GRAPH5] [float] NULL ,
[GRAPH6] [float] NULL ,
[GRAPH7] [float] NULL ,
[GRAPH8] [float] NULL ,
[GRAPH9] [float] NULL ,
[GRAPH10] [float] NULL ,
[GRAPH11] [float] NULL ,
[SEPARATIONS] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILE_RCVD_DATE] [datetime] NULL ,
[PAID_DATE] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Designs] ADD
CONSTRAINT [PK_Designs] PRIMARY KEY NONCLUSTERED
(
[DESIGN_NUM]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
-
CC 25631 OR CC 25639 OR CC 25641 don't work. But, CC 25621 does work..this is weird...
-
How many rows are in your Design_Details table? Is you rFK column in the Design_Details table indexed?
-
The design_detail table has 107,628 rows:
Here is the table script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Designs_Detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Designs_Detail]
GO
CREATE TABLE [dbo].[Designs_Detail] (
[DESIGN_NUM] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PITCH] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CHANNEL] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PCT_COVERAGE] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COLOR_TRAP] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MESH] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POWER] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REG] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRAP_SIZE] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPEED] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASER_RES] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COLOR_NOTES] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECLAIMED] [bit] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Designs_Detail] ADD
CONSTRAINT [PK_Designs_Detail] PRIMARY KEY NONCLUSTERED
(
[DESIGN_NUM],
[POSITION]
 
WITH FILLFACTOR = 90 ON [PRIMARY]GO
ALTER TABLE [dbo].[Designs_Detail] ADD
CONSTRAINT [FK_Designs_Detail_Designs] FOREIGN KEY
(
[DESIGN_NUM]
 
REFERENCES [dbo].[Designs] ([DESIGN_NUM]
 
ON DELETE CASCADE ON UPDATE CASCADEGO
-
did you check to see if the insert is being blocked by another spid?
-
How do you check to see if it is being blocked by another spid?
How would/why another spid block a new row insert?
-
I just redid my original insert statement and now it works:
INSERT INTO Designs
(DESIGN_NUM, END_RINGS, COLOR_TABS, STUDIO_INSTR, FILMS)
VALUES ('CC 25641',1,0,1,0)
So what would cause this? Was this being block by another spid as suggested? I did not see anything in the log files? Thanjs for the help!!
-
It's hard to tell what may have caused it after the fact, but I'd suspect blocking.
The log files don't show blocking, but you can use sp_who2 and look at the "BlkBy" column or "select * from sysprocesses" and look at the "blocked" column to look for blocking.
There could have been a page level lock on the page where the 'CC 25641' row resides, but no lock on the page where the 'XX 25641' row resides due to other updates or open transactions.
-
I agree that blocking looks to be the issue. Just add, you will need to start the insert FIRST to see the BlkBy value.
Also, I would strongly suggest you get someone who knows database design to evaluate your database. The table you posted is VERY denormalized. I understand why the PK is not clustered. A row that large would suffer badly if it was a clustered index.
Also, I see more details in the design table than I do the the design_detail table.
Sorry, I realize this wasn't the question, but this design will lead to other performance issues. In fact, it might be part of your blocking issue.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply