We have many task performed using T-SQL SQL Server stored procedures.
These SQL Server Stored Procedures are converted from Oracle.
One of the Task which involves around 50 plus SQL Server stored procedures(T-SQL) is around 10 times slower in SQL Server compared to Oracle.
In SQL Server this takes 3 minutes and 30 Seconds and in Oracle this takes around 24 seconds.
For e.g., Each INSERT takes 19 milliseconds in SQL Server whereas in Oracle it is less than 1 millisecond.
There are few temp tables used in these SQL Server procedures related to this task. These Temp Tables (# tables) are used in SQL Server DB for Oracle Global Variables (mainly for Oracle Global collections).
This task reads around 14000 records and creates 56000 records .
Both Oracle and SQL Server databases are in same server.
The server is Intel Xeon Silver 4112 two Virtual processors (2.59 GH)
And RAM = 48 GB .
With SQL Server 2014. (Also tried SQL server 2016)
Following is a simple INSERT , inserting 1000 records to a test table. This takes more than 150 milliseconds in SQL Server and in Oracle this takes around 30 milliseconds.
CREATE TABLE [dbo].[TESTPERF01](
[CCN] varchar NOT NULL,
[SVC_CUR_ERROR_ROLLUP_ID] [numeric](6, 0) NULL,
[SVC_ENG_ERROR_ROLLUP_ID] [numeric](6, 0) NULL,
[SVC_SIM_ERROR_ROLLUP_ID] [numeric](6, 0) NULL,
[SVC_ITEM_AGC] varchar NULL,
[SVC_ITEM_VAT] varchar NULL,
[SVC_ITEM_STAX] varchar NULL,
[SVC_ITEM_UTAX] varchar NULL,
[SVC_ITEM_ETAX] varchar NULL
)
CREATE PROCEDURE dbo.TEST_INSERT03
@P_PROCESS_ID VARCHAR(8) = ' '
AS
BEGIN
SET NOCOUNT ON
DECLARE @SEQ VARCHAR(6)
,@I INT = 1
,@LV_END_DATE DATETIME
,@LV_START_DATE DATETIME
,@UMSG_TEXT VARCHAR(1000)
SET @LV_START_DATE = SYSDATETIME()
begin transaction TRAN_1;
IF @P_PROCESS_ID = ' '
BEGIN
SET @P_PROCESS_ID = Format( SYSDATETIME(),'HHmmss','en-us')
END
DELETE FROM TESTPERF01
SET @I = 1
WHILE @I <= 1000
BEGIN
SET @SEQ = CAST(@I AS VARCHAR(6))
INSERT INTO TESTPERF01 (
[CCN]
,[SVC_CUR_ERROR_ROLLUP_ID]
,[SVC_ENG_ERROR_ROLLUP_ID]
,[SVC_SIM_ERROR_ROLLUP_ID]
,[SVC_ITEM_AGC]
,[SVC_ITEM_VAT]
,[SVC_ITEM_STAX]
,[SVC_ITEM_UTAX]
,[SVC_ITEM_ETAX]
)
VALUES (
@SEQ
,123
,123
,123
,'AGC'
,'VT1'
,'1'
,'1'
,'1'
);
SET @I = @I + 1
END
commit transaction TRAN_1;
SET @LV_END_DATE = SYSDATETIME()
PRINT CAST( DATEDIFF(MILLISECOND, @LV_START_DATE, @LV_END_DATE) AS VARCHAR(30))
PRINT CAST( DATEDIFF(nanosecond, @LV_START_DATE, @LV_END_DATE) AS VARCHAR(30))
END
In our procedures , the BEGIN TRAN is issued at the beginning of the transaction. And the COMMIT TRAN is issued at the end of the transactions. (And every INSERT is not embedded with BEGIN TRAN and COMMIT TRAN)
I have reviewed the query execution plan, as far as possible set based approach is used,
Could you please advice if there is any SQL Server Set-ups or any other possible issues .