Oracle vs MS SQL Server Query Execution Speed

Hi,
I recently setup MS SQL Server and Oracle 9i on the same machine - and the world still has not come to an end ;-) - and both databases are started and operational. Both contain a single table, TRN, containing 6,348,752 rows. The SQL Server table was imported from the Oracle table and the tables have the same logical structure and the same number of indexes.
That's the background. Now the problem: I have a query that does monthly summaries. The Oracle query executes on average 13 times faster than the SQL Server Query. I have tried a number of suggestions on how to improve SQL performance but they me very little difference (tried STATISTICS, using the CAST function instead of the CONVERT function, tried the DATEPART function and using and ORDER BY on the same "fields" as used by the GROUP BY clause)
So over to you. I am open to any suggestions.
Here are the queries and table structures:
Oracle Query:
SELECT
METER_ID,
TO_CHAR(TS,'YYYY-MM') YEAR_MONTH,
SUM(UNITS) UNITS,
SUM(AMT) AMT,
COUNT(*) CNT
FROM
VPS.TRN
WHERE
TRN_TYPE_ID =3D 1 GROUP BY
METER_ID,
TO_CHAR(TS,'YYYY-MM')
Oracle Table:
CREATE TABLE VPS.TRN
(STN_ID NUMBER(15),
TRN_ID NUMBER(15) NOT NULL,
TRN_TYPE_ID NUMBER(15) NOT NULL,
PAY_TYPE_ID NUMBER(15),
BATCH_ITEM_ID NUMBER(15) NOT NULL,
AGR_ID NUMBER(15),
METER_ID NUMBER(15),
RES_ID NUMBER(15),
AMT NUMBER(19,4) NOT NULL,
TS DATE DEFAULT SYSDATE,
RNO VARCHAR2(20),
TKN VARCHAR2(30),
UNITS NUMBER(15,4),
TRF_ID NUMBER(15),
DEBT_ID NUMBER(15),
FCITEM_ID NUMBER(15),
TAXITEM_ID NUMBER(15),
GNO VARCHAR2(20),
REV_ID NUMBER(15) DEFAULT 0,
REP_ID NUMBER(15) DEFAULT 0,
COST NUMBER(19,4),
UERR NUMBER(7,3),
CMT VARCHAR2(255),
PAY_ID NUMBER(15),
UTIL_ID NUMBER(15) NOT NULL,
RSEQ NUMBER(15),
GSEQ NUMBER(15),
TSEQ NUMBER(15),
TNO VARCHAR2(20),
RDP_ID NUMBER(15),
CERR NUMBER(19,4),
DB_ID NUMBER(4),
REFERENCE_NO VARCHAR2(30),
SERVICE_TYPE_ID NUMBER(15),
OPERATOR_ID NUMBER(15),
TID DATE,
MSNO VARCHAR2(50),
SG_ID NUMBER(15),
CUST_ID_METHOD NUMBER(15),
ENCRYPTIONTYPE_TKTYPE_ID NUMBER(15),
VEND_REASON_ID NUMBER(15),
TARIFF_INDEX NUMBER(10),
NAME VARCHAR2(60),
ADDRESS VARCHAR2(255),
VOTE_ID NUMBER(15),
INVOICENO VARCHAR2(50),
RDP_UNITS NUMBER(15,4),
RDP_UNITS_ID NUMBER(15),
TRN_RECON_NO NUMBER(15),
MSG_ID NUMBER(15))
TABLESPACE VPS_DATA
/
CREATE INDEX VPS.TRN_PAY_TYPE_IDX ON VPS.TRN
(
PAY_TYPE_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_PAY_ASSOC ON VPS.TRN
(
PAY_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_AGR_ASSOC ON VPS.TRN
(
AGR_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_BATCHITEM_ASSOC ON VPS.TRN
(
BATCH_ITEM_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_STN_TSEQ_IDX ON VPS.TRN
(
STN_ID ASC,
TSEQ ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_DEBT_ASSOC ON VPS.TRN
(
DEBT_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_RECON_IDX ON VPS.TRN
(
TRN_RECON_NO ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_TS_IDX ON VPS.TRN
(
TS ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_OPERATOR_IDX ON VPS.TRN
(
OPERATOR_ID ASC
)
/
CREATE INDEX VPS.TRN_UTIL_IDX ON VPS.TRN
(
UTIL_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_DB_IDX ON VPS.TRN
(
DB_ID ASC,
TS ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_RDP_TS_IDX ON VPS.TRN
(
RDP_ID ASC,
TS ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_STN_GSEQ_IDX ON VPS.TRN
(
STN_ID ASC,
GSEQ ASC
)
/
CREATE INDEX VPS.TRN_TRNTYPE_ASSOC ON VPS.TRN
(
TRN_TYPE_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_REV_IDX ON VPS.TRN
(
REV_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_STN_RSEQ_IDX ON VPS.TRN
(
STN_ID ASC,
RSEQ ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_RDP_ASSOC ON VPS.TRN
(
RDP_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_GNO_IDX ON VPS.TRN
(
GNO ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.FK_TRN_VOTE ON VPS.TRN
(
VOTE_ID ASC
)
TABLESPACE VPS_INDEX
/
CREATE INDEX VPS.TRN_METER_ASSOC ON VPS.TRN
(
METER_ID ASC
) TABLESPACE VPS_INDEX
/
ALTER TABLE VPS.TRN
ADD CONSTRAINT PK_TRN PRIMARY KEY (TRN_ID)
USING INDEX TABLESPACE VPS_INDEX
/
MS SQL Server Query:
SELECT
METER_ID,
CONVERT( varchar(7), TS, 120) YEAR_MONTH,
SUM(UNITS) UNITS,
SUM(AMT) AMT,
COUNT(*) CNT
FROM
VPS.TRN
WHERE
TRN_TYPE_ID =3D 1
GROUP BY
METER_ID,
CONVERT( varchar(7), TS, 120)
MS SQL Server Table:
CREATE TABLE [VPS].[TRN] (
[STN_ID] [numeric](15, 0) NULL ,
[TRN_ID] [numeric](15, 0) NOT NULL ,
[TRN_TYPE_ID] [numeric](15, 0) NOT NULL ,
[PAY_TYPE_ID] [numeric](15, 0) NULL ,
[BATCH_ITEM_ID] [numeric](15, 0) NOT NULL ,
[AGR_ID] [numeric](15, 0) NULL ,
[METER_ID] [numeric](15, 0) NULL ,
[RES_ID] [numeric](15, 0) NULL ,
[AMT] [numeric](19, 4) NOT NULL ,
[TS] [datetime] NULL ,
[RNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TKN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNITS] [numeric](15, 4) NULL ,
[TRF_ID] [numeric](15, 0) NULL ,
[DEBT_ID] [numeric](15, 0) NULL ,
[FCITEM_ID] [numeric](15, 0) NULL ,
[TAXITEM_ID] [numeric](15, 0) NULL ,
[GNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REV_ID] [numeric](15, 0) NULL ,
[REP_ID] [numeric](15, 0) NULL ,
[COST] [numeric](19, 4) NULL ,
[UERR] [numeric](7, 3) NULL ,
[CMT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAY_ID] [numeric](15, 0) NULL ,
[UTIL_ID] [numeric](15, 0) NOT NULL ,
[RSEQ] [numeric](15, 0) NULL ,
[GSEQ] [numeric](15, 0) NULL ,
[TSEQ] [numeric](15, 0) NULL ,
[TNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RDP_ID] [numeric](15, 0) NULL ,
[CERR] [numeric](19, 4) NULL ,
[DB_ID] [numeric](4, 0) NULL ,
[REFERENCE_NO] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SERVICE_TYPE_ID] [numeric](15, 0) NULL ,
[OPERATOR_ID] [numeric](15, 0) NULL ,
[TID] [datetime] NULL ,
[MSNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SG_ID] [numeric](15, 0) NULL ,
[CUST_ID_METHOD] [numeric](15, 0) NULL ,
[ENCRYPTIONTYPE_TKTYPE_ID] [numeric](15, 0) NULL ,
[VEND_REASON_ID] [numeric](15, 0) NULL ,
[TARIFF_INDEX] [numeric](10, 0) NULL ,
[NAME] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VOTE_ID] [numeric](15, 0) NULL ,
[INVOICENO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RDP_UNITS] [numeric](15, 4) NULL ,
[RDP_UNITS_ID] [numeric](15, 0) NULL ,
[TRN_RECON_NO] [numeric](15, 0) NULL ,
[MSG_ID] [numeric](15, 0) NULL ) ON [VPS_DATA]
GO
ALTER TABLE [VPS].[TRN] WITH NOCHECK ADD CONSTRAINT [PK_TRN] PRIMARY KEY CLUSTERED (
[TRN_ID]
) ON [VPS_DATA] GO
ALTER TABLE [VPS].[TRN] ADD CONSTRAINT [DF__TRN__TS__34C8D9D1] DEFAULT (getdate
()) FOR [TS],
CONSTRAINT [DF__TRN__REV_ID__35BCFE0A] DEFAULT (0) FOR [REV_ID],
CONSTRAINT [DF__TRN__REP_ID__36B12243] DEFAULT (0) FOR [REP_ID]
GO
CREATE INDEX [TRN_PAY_TYPE_IDX] ON [VPS].[TRN]
([PAY_TYPE_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_PAY_ASSOC] ON [VPS].[TRN]([PAY_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_AGR_ASSOC] ON [VPS].[TRN]([AGR_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_BATCHITEM_ASSOC] ON [VPS].[TRN]
([BATCH_ITEM_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_STN_TSEQ_IDX] ON [VPS].[TRN]([STN_ID], [TSEQ]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_DEBT_ASSOC] ON [VPS].[TRN]([DEBT_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_RECON_IDX] ON [VPS].[TRN]
([TRN_RECON_NO]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_TS_IDX] ON [VPS].[TRN]([TS]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_OPERATOR_IDX] ON [VPS].[TRN]
([OPERATOR_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_UTIL_IDX] ON [VPS].[TRN]([UTIL_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_DB_IDX] ON [VPS].[TRN]([DB_ID], [TS]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_RDP_TS_IDX] ON [VPS].[TRN]([RDP_ID], [TS]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_STN_GSEQ_IDX] ON [VPS].[TRN]([STN_ID], [GSEQ]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_TRNTYPE_ASSOC] ON [VPS].[TRN]
([TRN_TYPE_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_REV_IDX] ON [VPS].[TRN]([REV_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_STN_RSEQ_IDX] ON [VPS].[TRN]([STN_ID], [RSEQ]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_RDP_ASSOC] ON [VPS].[TRN]([RDP_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_GNO_IDX] ON [VPS].[TRN]([GNO]) ON [VPS_DATA]
GO
CREATE INDEX [FK_TRN_VOTE] ON [VPS].[TRN]([VOTE_ID]) ON [VPS_DATA]
GO
CREATE INDEX [TRN_METER_ASSOC] ON [VPS].[TRN]
([METER_ID]) ON [VPS_DATA]
GO
Software on Server:
Windows 2000 Server 5.00.2195 Service Pack 3
SQL Server Enterprise Edition 8.00.760 (SP3)
Oracle9i Enterprise Edition Release 9.2.0.3.0
Software on Client:
Windows 2000 Professional 5.00.2195 Service Pack 3
Vincent van der Vlis
Application Frameworks - Creating a framework of value
Microsoft Certified Partner
2nd Floor Waterside Place
Tygervalley Waterfront
South Gate
Carl Cronj=E9 Drive
Bellville
South Africa
7550
Phone Office: +27 21 914 9666 or 914 9775
Phone Mobile: +27 82 789 7049
Fax: +27 21 914 9826
Email: vincent@.appframeworks.com1) if SQL Server is using a parallel execution plan, turn it off
2) if this is a Xeon/Xeon MP system, disable Hyper-
Threading
3) make sure only one of SQL Server or Oracle is running during the test, or ensure that each can only use the same amount of memory
4) Are you executing this code on the client system from Query Analyzer or your own application?
If your own app, try seeing if there is a difference from Query Analyzer.
I am going to guess that TRN_TYPE_ID =3D 1 involves a large number of rows, such that the SQL Server execution plan for your query has a table scan on TRN at the far right (in Display Estimated Execution Plan)
Depending on the distribution of METER_ID and TS date value, you could have a Stream Aggregate or Hash Match,
The Hash Match is prefered for large row count on the group by clause.
If this is a very large Hash Match operation (>10-30K rows in group by clause) SQL Server will spool the results to the tempdb, hence sequential transfer performance of the tempdb is very important
Still, why don't you try the following on SQL Server,
it involves a computed column, sorry I don't know what the Oracle syntax for this is
ALTER TABLE [VPS].[TRN] ADD TS7 AS CONVERT( varchar(7), TS, 120)
CREATE INDEX [TRN_COVERED_IDX] ON [VPS].[TRN]( [TRN_TYPE_ID], [METER_ID], [TS7], UNITS, AMT) ON [VPS_DATA]
GO
SELECT METER_ID, TS7 YEAR_MONTH, SUM(UNITS) UNITS, SUM
(AMT) AMT, COUNT(*) CNT FROM VPS.TRN WHERE TRN_TYPE_ID =3D 1
GROUP BY METER_ID, TS7
OPTION (MAXDOP 1)
-joe chang
>--Original Message--
>Hi,
>I recently setup MS SQL Server and Oracle 9i on the same >machine - and the world still has not come to an end ;-
) - >and both databases are started and operational. Both >contain a single table, TRN, containing 6,348,752 rows. >The SQL Server table was imported from the Oracle table >and the tables have the same logical structure and the >same number of indexes.
>That's the background. Now the problem: I have a query >that does monthly summaries. The Oracle query executes on >average 13 times faster than the SQL Server Query. I have >tried a number of suggestions on how to improve SQL >performance but they me very little difference (tried >STATISTICS, using the CAST function instead of the CONVERT >function, tried the DATEPART function and using and ORDER >BY on the same "fields" as used by the GROUP BY clause)
>So over to you. I am open to any suggestions.
>Here are the queries and table structures:
>Oracle Query:
>SELECT
> METER_ID,
> TO_CHAR(TS,'YYYY-MM') YEAR_MONTH,
> SUM(UNITS) UNITS,
> SUM(AMT) AMT,
> COUNT(*) CNT
>FROM
> VPS.TRN
>WHERE
> TRN_TYPE_ID =3D 1 >GROUP BY
> METER_ID,
> TO_CHAR(TS,'YYYY-MM')
>Oracle Table:
>CREATE TABLE VPS.TRN
> (STN_ID NUMBER(15),
> TRN_ID NUMBER(15) NOT NULL,
> TRN_TYPE_ID NUMBER(15) NOT NULL,
> PAY_TYPE_ID NUMBER(15),
> BATCH_ITEM_ID NUMBER(15) NOT NULL,
> AGR_ID NUMBER(15),
> METER_ID NUMBER(15),
> RES_ID NUMBER(15),
> AMT NUMBER(19,4) NOT NULL,
> TS DATE DEFAULT SYSDATE,
> RNO VARCHAR2(20),
> TKN VARCHAR2(30),
> UNITS NUMBER(15,4),
> TRF_ID NUMBER(15),
> DEBT_ID NUMBER(15),
> FCITEM_ID NUMBER(15),
> TAXITEM_ID NUMBER(15),
> GNO VARCHAR2(20),
> REV_ID NUMBER(15) DEFAULT 0,
> REP_ID NUMBER(15) DEFAULT 0,
> COST NUMBER(19,4),
> UERR NUMBER(7,3),
> CMT VARCHAR2(255),
> PAY_ID NUMBER(15),
> UTIL_ID NUMBER(15) NOT NULL,
> RSEQ NUMBER(15),
> GSEQ NUMBER(15),
> TSEQ NUMBER(15),
> TNO VARCHAR2(20),
> RDP_ID NUMBER(15),
> CERR NUMBER(19,4),
> DB_ID NUMBER(4),
> REFERENCE_NO VARCHAR2(30),
> SERVICE_TYPE_ID NUMBER(15),
> OPERATOR_ID NUMBER(15),
> TID DATE,
> MSNO VARCHAR2(50),
> SG_ID NUMBER(15),
> CUST_ID_METHOD NUMBER(15),
> ENCRYPTIONTYPE_TKTYPE_ID NUMBER(15),
> VEND_REASON_ID NUMBER(15),
> TARIFF_INDEX NUMBER(10),
> NAME VARCHAR2(60),
> ADDRESS VARCHAR2(255),
> VOTE_ID NUMBER(15),
> INVOICENO VARCHAR2(50),
> RDP_UNITS NUMBER(15,4),
> RDP_UNITS_ID NUMBER(15),
> TRN_RECON_NO NUMBER(15),
> MSG_ID NUMBER(15))
>TABLESPACE VPS_DATA
>/
>CREATE INDEX VPS.TRN_PAY_TYPE_IDX ON VPS.TRN
> (
> PAY_TYPE_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_PAY_ASSOC ON VPS.TRN
> (
> PAY_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_AGR_ASSOC ON VPS.TRN
> (
> AGR_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_BATCHITEM_ASSOC ON VPS.TRN
> (
> BATCH_ITEM_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_STN_TSEQ_IDX ON VPS.TRN
> (
> STN_ID ASC,
> TSEQ ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_DEBT_ASSOC ON VPS.TRN
> (
> DEBT_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_RECON_IDX ON VPS.TRN
> (
> TRN_RECON_NO ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_TS_IDX ON VPS.TRN
> (
> TS ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_OPERATOR_IDX ON VPS.TRN
> (
> OPERATOR_ID ASC
> )
>/
>CREATE INDEX VPS.TRN_UTIL_IDX ON VPS.TRN
> (
> UTIL_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_DB_IDX ON VPS.TRN
> (
> DB_ID ASC,
> TS ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_RDP_TS_IDX ON VPS.TRN
> (
> RDP_ID ASC,
> TS ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_STN_GSEQ_IDX ON VPS.TRN
> (
> STN_ID ASC,
> GSEQ ASC
> )
>/
>CREATE INDEX VPS.TRN_TRNTYPE_ASSOC ON VPS.TRN
> (
> TRN_TYPE_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_REV_IDX ON VPS.TRN
> (
> REV_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_STN_RSEQ_IDX ON VPS.TRN
> (
> STN_ID ASC,
> RSEQ ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_RDP_ASSOC ON VPS.TRN
> (
> RDP_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_GNO_IDX ON VPS.TRN
> (
> GNO ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.FK_TRN_VOTE ON VPS.TRN
> (
> VOTE_ID ASC
> )
>TABLESPACE VPS_INDEX
>/
>CREATE INDEX VPS.TRN_METER_ASSOC ON VPS.TRN
> (
> METER_ID ASC
> ) >TABLESPACE VPS_INDEX
>/
>ALTER TABLE VPS.TRN
>ADD CONSTRAINT PK_TRN PRIMARY KEY (TRN_ID)
>USING INDEX TABLESPACE VPS_INDEX
>/
>
>MS SQL Server Query:
>SELECT
> METER_ID,
> CONVERT( varchar(7), TS, 120) YEAR_MONTH,
> SUM(UNITS) UNITS,
> SUM(AMT) AMT,
> COUNT(*) CNT
>FROM
> VPS.TRN
>WHERE
> TRN_TYPE_ID =3D 1
>GROUP BY
> METER_ID,
> CONVERT( varchar(7), TS, 120)
>MS SQL Server Table:
>CREATE TABLE [VPS].[TRN] (
> [STN_ID] [numeric](15, 0) NULL ,
> [TRN_ID] [numeric](15, 0) NOT NULL ,
> [TRN_TYPE_ID] [numeric](15, 0) NOT NULL ,
> [PAY_TYPE_ID] [numeric](15, 0) NULL ,
> [BATCH_ITEM_ID] [numeric](15, 0) NOT NULL ,
> [AGR_ID] [numeric](15, 0) NULL ,
> [METER_ID] [numeric](15, 0) NULL ,
> [RES_ID] [numeric](15, 0) NULL ,
> [AMT] [numeric](19, 4) NOT NULL ,
> [TS] [datetime] NULL ,
> [RNO] [varchar] (20) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [TKN] [varchar] (30) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNITS] [numeric](15, 4) NULL ,
> [TRF_ID] [numeric](15, 0) NULL ,
> [DEBT_ID] [numeric](15, 0) NULL ,
> [FCITEM_ID] [numeric](15, 0) NULL ,
> [TAXITEM_ID] [numeric](15, 0) NULL ,
> [GNO] [varchar] (20) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [REV_ID] [numeric](15, 0) NULL ,
> [REP_ID] [numeric](15, 0) NULL ,
> [COST] [numeric](19, 4) NULL ,
> [UERR] [numeric](7, 3) NULL ,
> [CMT] [varchar] (255) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [PAY_ID] [numeric](15, 0) NULL ,
> [UTIL_ID] [numeric](15, 0) NOT NULL ,
> [RSEQ] [numeric](15, 0) NULL ,
> [GSEQ] [numeric](15, 0) NULL ,
> [TSEQ] [numeric](15, 0) NULL ,
> [TNO] [varchar] (20) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [RDP_ID] [numeric](15, 0) NULL ,
> [CERR] [numeric](19, 4) NULL ,
> [DB_ID] [numeric](4, 0) NULL ,
> [REFERENCE_NO] [varchar] (30) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [SERVICE_TYPE_ID] [numeric](15, 0) NULL ,
> [OPERATOR_ID] [numeric](15, 0) NULL ,
> [TID] [datetime] NULL ,
> [MSNO] [varchar] (50) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [SG_ID] [numeric](15, 0) NULL ,
> [CUST_ID_METHOD] [numeric](15, 0) NULL ,
> [ENCRYPTIONTYPE_TKTYPE_ID] [numeric](15, 0) NULL ,
> [VEND_REASON_ID] [numeric](15, 0) NULL ,
> [TARIFF_INDEX] [numeric](10, 0) NULL ,
> [NAME] [varchar] (60) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS] [varchar] (255) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [VOTE_ID] [numeric](15, 0) NULL ,
> [INVOICENO] [varchar] (50) COLLATE >SQL_Latin1_General_CP1_CI_AS NULL ,
> [RDP_UNITS] [numeric](15, 4) NULL ,
> [RDP_UNITS_ID] [numeric](15, 0) NULL ,
> [TRN_RECON_NO] [numeric](15, 0) NULL ,
> [MSG_ID] [numeric](15, 0) NULL >) ON [VPS_DATA]
>GO
>ALTER TABLE [VPS].[TRN] WITH NOCHECK ADD > CONSTRAINT [PK_TRN] PRIMARY KEY CLUSTERED > (
> [TRN_ID]
> ) ON [VPS_DATA] >GO
>ALTER TABLE [VPS].[TRN] ADD > CONSTRAINT [DF__TRN__TS__34C8D9D1] DEFAULT (getdate
>()) FOR [TS],
> CONSTRAINT [DF__TRN__REV_ID__35BCFE0A] DEFAULT (0) >FOR [REV_ID],
> CONSTRAINT [DF__TRN__REP_ID__36B12243] DEFAULT (0) >FOR [REP_ID]
>GO
> CREATE INDEX [TRN_PAY_TYPE_IDX] ON [VPS].[TRN]
>([PAY_TYPE_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_PAY_ASSOC] ON [VPS].[TRN]([PAY_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_AGR_ASSOC] ON [VPS].[TRN]([AGR_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_BATCHITEM_ASSOC] ON [VPS].[TRN]
>([BATCH_ITEM_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_STN_TSEQ_IDX] ON [VPS].[TRN]
([STN_ID], >[TSEQ]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_DEBT_ASSOC] ON [VPS].[TRN]
([DEBT_ID]) >ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_RECON_IDX] ON [VPS].[TRN]
>([TRN_RECON_NO]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_TS_IDX] ON [VPS].[TRN]([TS]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_OPERATOR_IDX] ON [VPS].[TRN]
>([OPERATOR_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_UTIL_IDX] ON [VPS].[TRN]([UTIL_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_DB_IDX] ON [VPS].[TRN]([DB_ID], [TS]) >ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_RDP_TS_IDX] ON [VPS].[TRN]([RDP_ID], >[TS]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_STN_GSEQ_IDX] ON [VPS].[TRN]
([STN_ID], >[GSEQ]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_TRNTYPE_ASSOC] ON [VPS].[TRN]
>([TRN_TYPE_ID]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_REV_IDX] ON [VPS].[TRN]([REV_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_STN_RSEQ_IDX] ON [VPS].[TRN]
([STN_ID], >[RSEQ]) ON [VPS_DATA]
>GO
> CREATE INDEX [TRN_RDP_ASSOC] ON [VPS].[TRN]([RDP_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_GNO_IDX] ON [VPS].[TRN]([GNO]) ON >[VPS_DATA]
>GO
> CREATE INDEX [FK_TRN_VOTE] ON [VPS].[TRN]([VOTE_ID]) ON >[VPS_DATA]
>GO
> CREATE INDEX [TRN_METER_ASSOC] ON [VPS].[TRN]
>([METER_ID]) ON [VPS_DATA]
>GO
>
>Software on Server:
> Windows 2000 Server 5.00.2195 Service Pack 3
> SQL Server Enterprise Edition 8.00.760 (SP3)
> Oracle9i Enterprise Edition Release 9.2.0.3.0
>Software on Client:
> Windows 2000 Professional 5.00.2195 Service Pack 3
>Vincent van der Vlis
>Application Frameworks - Creating a framework of value
>Microsoft Certified Partner
>2nd Floor Waterside Place
>Tygervalley Waterfront
>South Gate
>Carl Cronj=E9 Drive
>Bellville
>South Africa
>7550
>Phone Office: +27 21 914 9666 or 914 9775
>Phone Mobile: +27 82 789 7049
>Fax: +27 21 914 9826
>Email: vincent@.appframeworks.com
>.
>|||If the world only revolves around this particularly query, the it would
help to make the primary key non-clustered, and add a clustered index on
(TRN_TYPE_ID,METER_ID,TS).
Other suggestions:
1. Make sure the statistics are up to date. If not (or when in doubt)
run UPDATE STATISTICS.
2. As Andrew partly mentioned, it is more space efficient to use:
- int instead of numeric(15,0) (assuming the values will not exceed 2
billion),
- smallint instead of numeric(4,0),
- money instead of numeric(19,4)
- smalldatetime instead of datetime (assuming no precision beyond
minute is needed, and the date does not exceed 2079)
This is especially true for the columns in the proposed clustered index.
3. If you are using different disks for your different table spaces in
Oracle, then for good comparison you should create different filegroups
in SQL-Server (each group on its own disk), and create the indexes in a
different filegroup that the table.
Hope this helps,
Gert-Jan
Vincent wrote:
> Hi,
> I recently setup MS SQL Server and Oracle 9i on the same
> machine - and the world still has not come to an end ;-) -
> and both databases are started and operational. Both
> contain a single table, TRN, containing 6,348,752 rows.
> The SQL Server table was imported from the Oracle table
> and the tables have the same logical structure and the
> same number of indexes.
> That's the background. Now the problem: I have a query
> that does monthly summaries. The Oracle query executes on
> average 13 times faster than the SQL Server Query. I have
> tried a number of suggestions on how to improve SQL
> performance but they me very little difference (tried
> STATISTICS, using the CAST function instead of the CONVERT
> function, tried the DATEPART function and using and ORDER
> BY on the same "fields" as used by the GROUP BY clause)
> So over to you. I am open to any suggestions.
<snip>