October 13, 2015 at 2:02 am
Dear experts,
I have a procedure which used user define table @TableVar9.
I found the problem:table tbl_traffic is Disk I/O problem. when procedure execute and execution time is almost 20-30 sec. and when application call this procedure it take too much time.
though I have data almost 700 million on "tbl_OutBox_Charging" and "tbl_OutBox_Charging_response". and it is a charging application and continuous process to run. when we send traffic then we used "tbl_OutBox_Charging" table and when we get response then we call "tbl_OutBox_Charging_response".
we monitor actual traffic in every hour.
I need help to optimize this procedure and is it possible to run traffic query separately which is run accommodatingly now.
---------****************
query..............
---------****************
and we can call separate procedure after this.
please see it..................
CREATE PROCEDURE [dbo].[spWaletMsg] (
@TableVar9 AS dbo.TableVariable9 Readonly )
AS
BEGIN
INSERT INTO [GP_NGW].[dbo].[tbl_OutBox_Charging_response] (
[TRAN_ID],[Request_time],StatusCode,[sReplyTextXml]
,[RESPONSE_TIME],Lastupdate)
SELECT *,GETDATE()FROM @TableVar9
SELECT * INTO #tempFROM @TableVar9
UPDATE [GP_NGW].[dbo].[tbl_OutBox_Charging]
SET [TRANSACTION_OPERATION_STATUS_NGW] = CASE
WHEN TOCRR.sreplytextxml LIKE '%Charging operation failed, the charge was not applied%'
THEN 'BadRequest: InsufficientBalance'
WHEN TOCRR.sreplytextxml LIKE '%Request cannot be served%'
THEN [TRANSACTION_OPERATION_STATUS_NGW]
WHEN TOCRR.sreplytextxml LIKE '%referenceCode must be unique and length%'
THEN 'OK'
ELSE CASE
WHEN Isnull(TOCRR.StatusCode, 'NULL') = 'OK'
THEN 'OK'
WHEN Isnull(TOCRR.StatusCode, 'NULL') = 'BadRequest'
THEN 'BadRequest: Others'
ELSE [TRANSACTION_OPERATION_STATUS_NGW]
END
END
,RESPONSE_TIME = GETDATE()
,EXPIRED=CASE
WHEN Isnull(TOCRR.StatusCode, 'NULL') = 'OK'
THEN 'Y' else 'N' end
FROM dbo.tbl_OutBox_Charging_rizvee9 AS TOCR
INNER JOIN @TableVar9 AS TOCRR ON TOCR.TRAN_ID = TOCRR.TRAN_ID
WHERE (TOCR.TRANSACTION_OPERATION_STATUS_NGW IS NULL)
OR (TOCR.TRANSACTION_OPERATION_STATUS_NGW <> 'OK')
AND ( (TOCRR.StatusCode IN ('OK', 'BadRequest') or TOCRR.sreplytextxml LIKE '%referenceCode must be unique and length%'))
----------------****************************************************
SELECT [date],[Hour],[StatusCode],COUNT(*) AS TotalINTO #statistic
FROM (
SELECT TOP (1000) CAST(getdate() AS DATE) AS [Date]
,DATEPART(HH, getdate()) AS [Hour],[Request_time]
,isnull([StatusCode], 'NULL') AS [StatusCode]FROM #temp
) AS TT
GROUP BY [date],[Hour],[StatusCode]
----------------
UPDATE [GP_NGW].[dbo].[tbl_traffic]
SET [count] = ISNULL([count], 0) + st.Total
,lastupdate = GETDATE()
FROM [GP_NGW].dbo.tbl_traffic AS TC
INNER JOIN #statistic AS ST ON DATEDIFF(dd, TC.DATE , ST.DATE)=0
AND TC.hour = ST.Hour
AND TC.STATUS = ST.StatusCode
------------------
INSERT INTO [GP_NGW].[dbo].[tbl_traffic] (
[Date],[hour],[status],[count],[lastupdate])
SELECT TOP 1000 [date],[Hour],[StatusCode],[Total],GETDATE()
FROM #statistic AS ST
WHERE NOT EXISTS (SELECT * FROM [GP_NGW].[dbo].[tbl_traffic] AS TC
WHERE DATEDIFF(dd, TC.DATE , ST.DATE)=0
AND TC.hour = ST.Hour AND TC.STATUS = ST.StatusCode )
DROP TABLE #statistic
------------------****************************************************
MERGE [GP_NGW].[dbo].[tbl_t] AS T
USING (
SELECT isnull([StatusCode], 'NULL') AS [StatusCode], count(*) AS Total
FROM @TableVar9
GROUP BY StatusCode
) AS S
ON (T.[status] = S.[StatusCode]) AND datediff(dd, t.[Date], getdate()) = 0 AND t.hour = datepart(hour, getdate())
WHEN NOT MATCHED BY TARGET
THEN
INSERT ([Date], [hour], [status], [count], [lastupdate])
VALUES (CAST(getdate() AS DATE), datepart(hour, getdate()), S.[StatusCode], S.total, getdate())
WHEN MATCHED
THEN
UPDATE
SET T.[count] = T.[count] + s.Total, [lastupdate] = getdate();
END
October 13, 2015 at 2:26 am
For proper help, please post table definitions (CREATE TABLE statements), execution plan, the CREATE TYPE statement for your dbo.TableVariable9 and the T-SQL you use to call the stored procedure. As a quick win, you might try moving SELECT * INTO #temp FROM @TableVar9 to the very top of the stored procedure, and not using @TableVar9 at all after that.
John
October 13, 2015 at 2:40 am
Table definitions, index definitions and execution plan please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 13, 2015 at 3:18 am
Table 1
CREATE TABLE [dbo].[tbl_OutBox_Charging](
[TRAN_ID] [uniqueidentifier] NOT NULL,
[OUT_MSG_ID] [bigint] NOT NULL,
[IN_TRAN_ID] [varchar](36) NOT NULL,
[END_USER_ID] [varchar](18) NOT NULL,
[ON_BEHALF_OF] [varchar](15) NOT NULL,
[REFERENCE_CODE] [varchar](10) NOT NULL,
[TRANSACTION_OPERATION_STATUS] [varchar](7) NOT NULL,
[AMOUNT] [varchar](6) NOT NULL,
[CURRENCY] [varchar](6) NOT NULL,
[CLIENT_CORRELATOR] [varchar](10) NOT NULL,
[SERVICE_ID] [varchar](15) NOT NULL,
[PRODUCT_ID] [varchar](5) NOT NULL,
[CHANNEL] [varchar](5) NOT NULL,
[PURCHASE_CATEGORY_CODE] [varchar](13) NOT NULL,
[DESCRIPTION] [varchar](25) NOT NULL,
[TAX_AMOUNT] [varchar](6) NOT NULL,
[REQUEST_TIME] [datetime] NOT NULL,
[TOTAL_AMOUNT_CHARGED] [varchar](10) NOT NULL,
[SERVER_REFERENCE_CODE] [varchar](36) NULL,
[TRANSACTION_OPERATION_STATUS_NGW] [varchar](200) NULL,
[RESPONSE_TIME] [datetime] NULL,
[REPLY_TEXT] [varchar](100) NULL,
[EXPIRED_AT] [datetime] NULL,
[EXPIRED] [varchar](1) NOT NULL,
[TRAN_STATUS] [bigint] NOT NULL,
[postdata] AS (((((((((((((((('{"endUserId":"tel:+'+[END_USER_ID])+'","transactionOperationStatus":"Charged","amount":"')+[AMOUNT])+'","referenceCode":"')+[REFERENCE_CODE])+'","description":"')+left([DESCRIPTION],(25)))+'","currency":"BDT","clientCorrelator":"')+[REFERENCE_CODE])+'","onBehalfOf":"Gakk Media","purchaseCategoryCode":"')+[PURCHASE_CATEGORY_CODE])+'","channel":"WAP","taxAmount":"0","productID":"')+[PRODUCT_ID])+'","serviceID":"')+[SERVICE_ID])+'"}'),
CONSTRAINT [PK_tbl_OutBox_Charging] PRIMARY KEY CLUSTERED
(
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] WITH CHECK ADD CONSTRAINT [CK_tbl_OutBox_Charging_rizvee9] CHECK ((len([END_USER_ID])=(13) AND left([END_USER_ID],(5))='88017' AND isnumeric([END_USER_ID])=(1)))
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] CHECK CONSTRAINT [CK_tbl_OutBox_Charging_rie9]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] WITH CHECK ADD CONSTRAINT [CK_tbl_OutBox_Charging_r] CHECK (([END_USER_ID] IS NOT NULL AND [AMOUNT] IS NOT NULL AND [REFERENCE_CODE] IS NOT NULL AND [DESCRIPTION] IS NOT NULL AND [REFERENCE_CODE] IS NOT NULL AND [PURCHASE_CATEGORY_CODE] IS NOT NULL AND [PRODUCT_ID] IS NOT NULL AND [SERVICE_ID] IS NOT NULL))
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] CHECK CONSTRAINT [CK_tbl_OutBox_Charging_2]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] ADD CONSTRAINT [DF_tbl_OutBox_Charging_TRAN_ID] DEFAULT (newsequentialid()) FOR [TRAN_ID]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] ADD CONSTRAINT [DF_tbl_OutBox_Charging_TRAN_STATUS] DEFAULT ((0)) FOR [TRAN_STATUS]
GO
Table 2
CREATE TABLE [dbo].[tbl_OutBox_Charging_response](
[TRAN_ID] [varchar](36) NULL,
[Request_time] [varchar](36) NULL,
[StatusCode] [varchar](36) NULL,
[sReplyTextXml] [text] NULL,
[RESPONSE_TIME] [varchar](36) NULL,
[Lastupdate] [datetime] NOT NULL,
[ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging_response] ADD CONSTRAINT [DF_tbl_OutBox_Charging_response_Lastupdate] DEFAULT (getdate()) FOR [Lastupdate]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging_response] ADD CONSTRAINT [DF_tbl_OutBox_Charging_response_ID] DEFAULT ((0)) FOR [ID]
GO
/****** Object: UserDefinedTableType [dbo].[TableVariable9] Script Date: 10/13/2015 14:55:58 ******/
CREATE TYPE [dbo].[TableVariable9] AS TABLE(
[TRAN_ID] [varchar](36) NULL,
[Request_time] [varchar](36) NULL,
[StatusCode] [varchar](36) NULL,
[sReplyTextXml] [text] NULL,
[RESPONSE_TIME] [varchar](36) NULL
)
GO
--------------- INDEX
/****** Object: Index [IX_tbl_OutBox_Charging] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_1] ON [dbo].[tbl_OutBox_Charging]
(
[TRAN_STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_2] ON [dbo].[tbl_OutBox_Charging]
(
[EXPIRED] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_99] ON [dbo].[tbl_OutBox_Charging]
(
[TRANSACTION_OPERATION_STATUS_NGW] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_01] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging] ON [dbo].[tbl_OutBox_Charging]
(
[REQUEST_TIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging] ON [dbo].[tbl_OutBox_Charging]
(
[END_USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_EXPIRED_TRAN_STATUS_42E23] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_EXPIRED_TRAN_STATUS_42E23] ON [dbo].[tbl_OutBox_Charging]
(
[EXPIRED] ASC,
[TRAN_STATUS] ASC
)
INCLUDE ( [TRAN_ID],
[TRANSACTION_OPERATION_STATUS_NGW],
[REPLY_TEXT]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_TRAN_STATUS_46BE0] Script Date: 10/13/2015 15:11:44 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_TRAN_STATUS_46BE0] ON [dbo].[tbl_OutBox_Charging]
(
[TRAN_STATUS] ASC
)
INCLUDE ( [TRAN_ID],
[TRANSACTION_OPERATION_STATUS_NGW],
[REPLY_TEXT]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [PK_tbl_OutBox_Charging] Script Date: 10/13/2015 15:11:44 ******/
ALTER TABLE [dbo].[tbl_OutBox_Charging] ADD CONSTRAINT [PK_tbl_OutBox_Charging] PRIMARY KEY CLUSTERED
(
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
--------------
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_response9] Script Date: 10/13/2015 15:16:51 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_response9] ON [dbo].[tbl_OutBox_Charging_rizvee9_response9]
(
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_response] Script Date: 10/13/2015 15:16:51 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging__response_1] ON [dbo].[tbl_OutBox_Charging_response]
(
[ID] ASC,
[Lastupdate] ASC,
[RESPONSE_TIME] ASC,
[StatusCode] ASC,
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
October 13, 2015 at 4:15 am
CREATE TABLE [dbo].[tbl_traffic](
[transactionID] [uniqueidentifier] NULL,
[Date] [date] NOT NULL,
[hour] [varchar](50) NOT NULL,
[status] [varchar](100) NOT NULL,
[count] [int] NULL,
[lastupdate] [datetime] NULL,
CONSTRAINT [PK_tbl_traffic] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[hour] ASC,
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_traffic] ADD CONSTRAINT [DF_tbl_traffic_transactionID] DEFAULT (newsequentialid()) FOR [transactionID]
GO
ALTER TABLE [dbo].[tbl_traffic] ADD CONSTRAINT [DF_tbl_traffic_lastupdate] DEFAULT (getdate()) FOR [lastupdate]
GO
INDEX OF tbl_traffic[/b]
/****** Object: Index [IX_tbl_traffic_BE2E_status] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_BE2E_status] ON [dbo].[tbl_traffic]
(
[status] ASC
)
INCLUDE ( [count],
[lastupdate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_traffic_E0D6_hour] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_E0D6_hour] ON [dbo].[tbl_traffic]
(
[hour] ASC
)
INCLUDE ( [status],
[count],
[lastupdate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_traffic_status_CB30C] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_status_CB30C] ON [dbo].[tbl_traffic]
(
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_traffic_transactionID_72D79] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_transactionID_72D79] ON [dbo].[tbl_traffic]
(
[transactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [Missing_IX_tbl_traffic_status_24B31] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [Missing_IXNC_tbl_traffic_status_24B31] ON [dbo].[tbl_traffic]
(
[status] ASC
)
INCLUDE ( [Date],
[hour]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [PK_tbl_traffic] Script Date: 10/13/2015 16:14:25 ******/
ALTER TABLE [dbo].[tbl_traffic] ADD CONSTRAINT [PK_tbl_traffic] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[hour] ASC,
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
October 13, 2015 at 4:26 am
CREATE TABLE [dbo].[tbl_traffic](
[transactionID] [uniqueidentifier] NULL,
[Date] [date] NOT NULL,
[hour] [varchar](50) NOT NULL,
[status] [varchar](100) NOT NULL,
[count] [int] NULL,
[lastupdate] [datetime] NULL,
CONSTRAINT [PK_tbl_traffic] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[hour] ASC,
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_traffic] ADD CONSTRAINT [DF_tbl_traffic_transactionID] DEFAULT (newsequentialid()) FOR [transactionID]
GO
ALTER TABLE [dbo].[tbl_traffic] ADD CONSTRAINT [DF_tbl_traffic_lastupdate] DEFAULT (getdate()) FOR [lastupdate]
GO
/****** Object: Index [IX_tbl_traffic_BE2E_status] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_BE2E_status] ON [dbo].[tbl_traffic]
(
[status] ASC
)
INCLUDE ( [count],
[lastupdate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_traffic_E0D6_hour] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_E0D6_hour] ON [dbo].[tbl_traffic]
(
[hour] ASC
)
INCLUDE ( [status],
[count],
[lastupdate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_traffic_status_CB30C] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_status_CB30C] ON [dbo].[tbl_traffic]
(
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_traffic_transactionID_72D79] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_traffic_transactionID_72D79] ON [dbo].[tbl_traffic]
(
[transactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [Missing_IX_tbl_traffic_status_24B31] Script Date: 10/13/2015 16:14:25 ******/
CREATE NONCLUSTERED INDEX [Missing_IXNC_tbl_traffic_status_24B31] ON [dbo].[tbl_traffic]
(
[status] ASC
)
INCLUDE ( [Date],
[hour]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [PK_tbl_traffic] Script Date: 10/13/2015 16:14:25 ******/
ALTER TABLE [dbo].[tbl_traffic] ADD CONSTRAINT [PK_tbl_traffic] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[hour] ASC,
[status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
----------------------------------------------------------------
CREATE TABLE [dbo].[tbl_OutBox_Charging](
[TRAN_ID] [uniqueidentifier] NOT NULL,
[OUT_MSG_ID] [bigint] NOT NULL,
[IN_TRAN_ID] [varchar](36) NOT NULL,
[END_USER_ID] [varchar](18) NOT NULL,
[ON_BEHALF_OF] [varchar](15) NOT NULL,
[REFERENCE_CODE] [varchar](10) NOT NULL,
[TRANSACTION_OPERATION_STATUS] [varchar](7) NOT NULL,
[AMOUNT] [varchar](6) NOT NULL,
[CURRENCY] [varchar](6) NOT NULL,
[CLIENT_CORRELATOR] [varchar](10) NOT NULL,
[SERVICE_ID] [varchar](15) NOT NULL,
[PRODUCT_ID] [varchar](5) NOT NULL,
[CHANNEL] [varchar](5) NOT NULL,
[PURCHASE_CATEGORY_CODE] [varchar](13) NOT NULL,
[DESCRIPTION] [varchar](25) NOT NULL,
[TAX_AMOUNT] [varchar](6) NOT NULL,
[REQUEST_TIME] [datetime] NOT NULL,
[TOTAL_AMOUNT_CHARGED] [varchar](10) NOT NULL,
[SERVER_REFERENCE_CODE] [varchar](36) NULL,
[TRANSACTION_OPERATION_STATUS_NGW] [varchar](200) NULL,
[RESPONSE_TIME] [datetime] NULL,
[REPLY_TEXT] [varchar](100) NULL,
[EXPIRED_AT] [datetime] NULL,
[EXPIRED] [varchar](1) NOT NULL,
[TRAN_STATUS] [bigint] NOT NULL,
[postdata] AS (((((((((((((((('{"endUserId":"tel:+'+[END_USER_ID])+'","transactionOperationStatus":"Charged","amount":"')+[AMOUNT])+'","referenceCode":"')+[REFERENCE_CODE])+'","description":"')+left([DESCRIPTION],(25)))+'","currency":"BDT","clientCorrelator":"')+[REFERENCE_CODE])+'","onBehalfOf":"Gakk Media","purchaseCategoryCode":"')+[PURCHASE_CATEGORY_CODE])+'","channel":"WAP","taxAmount":"0","productID":"')+[PRODUCT_ID])+'","serviceID":"')+[SERVICE_ID])+'"}'),
CONSTRAINT [PK_tbl_OutBox_Charging] PRIMARY KEY CLUSTERED
(
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] WITH CHECK ADD CONSTRAINT [CK_tbl_OutBox_Charging_rizvee9] CHECK ((len([END_USER_ID])=(13) AND left([END_USER_ID],(5))='88017' AND isnumeric([END_USER_ID])=(1)))
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] CHECK CONSTRAINT [CK_tbl_OutBox_Charging_rizvee9]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] WITH CHECK ADD CONSTRAINT [CK_tbl_OutBox_Charging_rizvee9_2] CHECK (([END_USER_ID] IS NOT NULL AND [AMOUNT] IS NOT NULL AND [REFERENCE_CODE] IS NOT NULL AND [DESCRIPTION] IS NOT NULL AND [REFERENCE_CODE] IS NOT NULL AND [PURCHASE_CATEGORY_CODE] IS NOT NULL AND [PRODUCT_ID] IS NOT NULL AND [SERVICE_ID] IS NOT NULL))
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] CHECK CONSTRAINT [CK_tbl_OutBox_Charging_rizvee9_2]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] ADD CONSTRAINT [DF_tbl_OutBox_Charging_rizvee9_TRAN_ID] DEFAULT (newsequentialid()) FOR [TRAN_ID]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging] ADD CONSTRAINT [DF_tbl_OutBox_Charging_rizvee9_TRAN_STATUS] DEFAULT ((0)) FOR [TRAN_STATUS]
GO
-------------- INDEX -----------------
/****** Object: Index [IX_tbl_OutBox_Charging] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9] ON [dbo].[tbl_OutBox_Charging]
(
[TRAN_STATUS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_1] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_1] ON [dbo].[tbl_OutBox_Charging]
(
[EXPIRED] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_2] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_2] ON [dbo].[tbl_OutBox_Charging]
(
[TRANSACTION_OPERATION_STATUS_NGW] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_3] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_3] ON [dbo].[tbl_OutBox_Charging]
(
[REQUEST_TIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_5] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_5] ON [dbo].[tbl_OutBox_Charging]
(
[END_USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_EXPIRED_TRAN_STATUS_42E23] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_EXPIRED_TRAN_STATUS_42E23] ON [dbo].[tbl_OutBox_Charging]
(
[EXPIRED] ASC,
[TRAN_STATUS] ASC
)
INCLUDE ( [TRAN_ID],
[TRANSACTION_OPERATION_STATUS_NGW],
[REPLY_TEXT]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_TRAN_STATUS_46BE0] Script Date: 10/13/2015 16:21:23 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_TRAN_STATUS_46BE0] ON [dbo].[tbl_OutBox_Charging]
(
[TRAN_STATUS] ASC
)
INCLUDE ( [TRAN_ID],
[TRANSACTION_OPERATION_STATUS_NGW],
[REPLY_TEXT]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [PK_tbl_OutBox_Charging_rizvee9] Script Date: 10/13/2015 16:21:23 ******/
ALTER TABLE [dbo].[tbl_OutBox_Charging] ADD CONSTRAINT [PK_tbl_OutBox_Charging_rizvee9] PRIMARY KEY CLUSTERED
(
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
-------------------- TABLE --------------------
CREATE TABLE [dbo].[tbl_OutBox_Charging_response](
[TRAN_ID] [varchar](36) NULL,
[Request_time] [varchar](36) NULL,
[StatusCode] [varchar](36) NULL,
[sReplyTextXml] [text] NULL,
[RESPONSE_TIME] [varchar](36) NULL,
[Lastupdate] [datetime] NOT NULL,
[ID] [numeric](18, 0) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging_response] ADD CONSTRAINT [DF_tbl_OutBox_Charging_rizvee9_response9_Lastupdate] DEFAULT (getdate()) FOR [Lastupdate]
GO
ALTER TABLE [dbo].[tbl_OutBox_Charging_response] ADD CONSTRAINT [DF_tbl_OutBox_Charging_rizvee9_response9_ID] DEFAULT ((0)) FOR [ID]
GO
------------- INDEX ------------------
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_response9] Script Date: 10/13/2015 16:24:38 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_response9] ON [dbo].[tbl_OutBox_Charging_response]
(
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [IX_tbl_OutBox_Charging_rizvee9_response9_1] Script Date: 10/13/2015 16:24:38 ******/
CREATE NONCLUSTERED INDEX [IX_tbl_OutBox_Charging_rizvee9_response9_1] ON [dbo].[tbl_OutBox_Charging_response]
(
[ID] ASC,
[Lastupdate] ASC,
[RESPONSE_TIME] ASC,
[StatusCode] ASC,
[TRAN_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
-------------------------------------------
/****** Object: UserDefinedTableType [dbo].[TableVariable9] Script Date: 10/13/2015 16:25:41 ******/
CREATE TYPE [dbo].[TableVariable9] AS TABLE(
[TRAN_ID] [varchar](36) NULL,
[Request_time] [varchar](36) NULL,
[StatusCode] [varchar](36) NULL,
[sReplyTextXml] [text] NULL,
[RESPONSE_TIME] [varchar](36) NULL
)
GO
October 13, 2015 at 4:29 am
Thank you (I can see the posts, they're pending approval due to the spam filter)
Can you also please post the actual execution plan, as a .sqlplan file?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply