March 20, 2014 at 2:57 am
Dear Experts,
just i see a database and a table 'tbl_OutBox_MT' where there is now primary key and have index (non unique, non cluster). and it store almost 3000000 data per everyday. and wipe out data from their and archive all data to other location and broadcast this table 'tbl_OutBox_MT' by mobile operator everyday from morning to evening. but when it perform broadcast it to mobile operator it takes huge time. because this table gather data from different sources (tables) by using complex query and INSER INTO statement and insert into this table.
I need to perform first, my observation is there is no primary key.
please see the table structure below...
please show me the way how i tune up my database for first query execution.
one more question, when i run any complex query into this table it takes huge time and sometimes shows transaction deadlock error.
CREATE TABLE [dbo].[tbl_OutBox_MT](
[TRAN_ID] [varchar](36) NOT NULL,
[OUT_MSG_ID] [int] IDENTITY(1,1) NOT NULL,
[OUT_MSG_ID_TELCO] AS (CONVERT([bigint],((((CONVERT([varchar](4),datepart(year,[PROCESS_TIME]),(0))+case len(CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)+CONVERT([varchar](2),datepart(month,[PROCESS_TIME]),(0)))+case len(CONVERT([varchar](2),datepart(day,[PROCESS_TIME]),(0))) when (1) then '0' else '' end)+CONVERT([varchar](2),datepart(day,[PROCESS_TIME]),(0)))+CONVERT([varchar](11),[OUT_MSG_ID],(0)),(0))),
[SERVICE_ID] [int] NULL,
[REPLY_TYPE] [int] NULL,
[IN_TRAN_ID] [varchar](36) NOT NULL,
[MOBILE_NO] [varchar](15) NOT NULL,
[IN_MSG_ID] [varchar](30) NOT NULL,
[REPLY_TEXT] [varchar](1000) NOT NULL,
[REPLY_TEXT_XML] [varchar](2000) NULL,
[MSG_TYPE] [int] NOT NULL,
[SEND_PORT] [varchar](9) NOT NULL,
[CHARGE_ID] [int] NOT NULL,
[PROCESS_TIME] [datetime] NOT NULL,
[STATUS] [int] NOT NULL,
[RETRY] [int] NULL,
[SUBMIT_ID] [varchar](255) NULL,
[SEND_TIME] [datetime] NULL,
[ACTUAL_SENDTIME] [datetime] NULL,
[SMSCount] [int] NULL,
[ErrorCode] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] WITH CHECK ADD CONSTRAINT [CK_tbl_OutBox_MT] CHECK ((len([REPLY_TEXT])>(10)))
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] CHECK CONSTRAINT [CK_tbl_OutBox_MT]
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] WITH CHECK ADD CONSTRAINT [CK_tbl_OutBox_MT_1] CHECK ((len([mobile_no])=(13) AND left([mobile_no],(5))='88018' AND isnumeric([mobile_no])=(1)))
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] CHECK CONSTRAINT [CK_tbl_OutBox_MT_1]
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] ADD CONSTRAINT [DF_tbl_OutBox_MT_RECORD_NO] DEFAULT (newid()) FOR [TRAN_ID]
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] ADD CONSTRAINT [DF_tbl_OutBox_MT_TIME_STAMP] DEFAULT (getdate()) FOR [PROCESS_TIME]
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] ADD CONSTRAINT [DF_tbl_OutBox_MT_STATUS] DEFAULT ((0)) FOR [STATUS]
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] ADD CONSTRAINT [DF_tbl_OutBox_MT_RETRY] DEFAULT ((0)) FOR [RETRY]
GO
ALTER TABLE [dbo].[tbl_OutBox_MT] ADD CONSTRAINT [DF_tbl_OutBox_MT_SMSCount] DEFAULT ((1)) FOR [SMSCount]
GO
------------------
March 20, 2014 at 4:11 am
Without seeing the queries and the execution plans, there's no way to suggest any indexes just based on table structure. I have no idea which column or columns are used for filtering, where the slow downs are occurring or anything. It's just a table.
Deadlocks are fundamentally a performance problem, so it's very likely that you have tuning opportunities.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply