SQL execution takes 30 minutes against 18 million

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL execution takes 30 minutes against 18 million

Author

Topic

daibaocun
Starting Member

9 Posts

Posted - 2011-02-01 : 11:44:00

The SQL statement is as below
select
SUM(Simpanan."AMAUN") as "amaun",
Simpanan."YEAR" as "year"
from
dbo.DP_SIMPANAN Simpanan
where
Simpanan."YEAR" >= 2008
group by
Simpanan."YEAR"

The table defintion is as below
CREATE TABLE [dbo].[DP_SIMPANAN] (
[KOD_TRANS] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KOD_KTRGN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CR_BYR] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JNS_TRANS] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GL_TUNAI_FLG] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIMESTAMP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NO_AKAUN] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEGERI] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PUSATKOS] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AMAUN] [decimal](18, 2) NULL ,
[ID_TELLER] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[YEAR] [int] NULL ,
[MONTH] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]GO

The id is primary key.

There are about 18 million rows in the table. The range of value of "YEAR" column is from 2008 to 2010. So I do not think that creation of index on YEAR column make diffence.

How to improve the performance?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-02-01 : 11:51:27

Just an idea...
where Simpanan.id > (select max(id) from dbo.DP_SIMPANAN where Year=2007)

No, you're never too old to Yak'n'Roll if you're too young to die.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-01 : 15:17:36

If it is really critical to have better performance on that query, you could create an covering index with YEAR as the first column and AMAUN as the second column. If this was SQL 2005 or 2008, you could create an index on YEAR and include column AMAUN.

Of course, that will used some space and will create some additional overhead for INSERTs, UPDATEs, or DELETEs.

Is the primary key on the ID column a cluster index? If not, you should make it a clustered index.

Also, if all rows have a value for YEAR of 2008 or greater, you could try removing the WHERE clause, because you are selecting all rows.

Remove:

where Simpanan."YEAR" >= 2008

CODO ERGO SUM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-02-02 : 06:12:18

"How to improve the performance?"

consider partitioning the table based on "year".

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-02 : 09:56:45

quote:
Originally posted by AndrewMurphy

"How to improve the performance?"

consider partitioning the table based on "year".

This is a SQL 2000 forum, and that is only available in SQL 2005 or 2008 Enterprise Edition.

CODO ERGO SUM

daibaocun
Starting Member

9 Posts

Posted - 2011-02-15 : 05:22:07

Did as Michael Valentine Jones suggestion,
after create below index, it takes 9 seconds to execute the SQL.

thanks Michael.

CREATE
INDEX [index_year] ON [dbo].[DP_SIMPANAN] ([YEAR], [AMAUN])
WITH
DROP_EXISTING
ON [PRIMARY]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-15 : 06:04:12

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by AndrewMurphy

"How to improve the performance?"

consider partitioning the table based on "year".

This is a SQL 2000 forum, and that is only available in SQL 2005 or 2008 Enterprise Edition.

CODO ERGO SUM

You can still create partitioned views, cant you? For this you'd get the same benefit if the underlying tables each held a year as long as your view had the right check constraint?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-15 : 10:45:05

quote:
Originally posted by daibaocun

Did as Michael Valentine Jones suggestion,
after create below index, it takes 9 seconds to execute the SQL.

thanks Michael.

CREATE
INDEX [index_year] ON [dbo].[DP_SIMPANAN] ([YEAR], [AMAUN])
WITH
DROP_EXISTING
ON [PRIMARY]

Is that faster than it was before?

CODO ERGO SUM

daibaocun
Starting Member

9 Posts

Posted - 2011-02-15 : 21:55:39

before it took about 30 minutes, now it took 9 seconds after creation of the index.
now it was faster than it was before.
is there a chance that execute the SQL in 2/3 seconds.

I am afraid the customer still complain that 9 seconds still is too long.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-02-15 : 23:57:42

quote:
Originally posted by daibaocun

before it took about 30 minutes, now it took 9 seconds after creation of the index.
now it was faster than it was before.
is there a chance that execute the SQL in 2/3 seconds.

I am afraid the customer still complain that 9 seconds still is too long.

That server must be really slow or overloaded if it took 30 minutes before for that query.

Maybe it's time to look at new hardware, faster processors, more memory, faster disks, partitioned tables with SQL Server 2008 Enterprise Edition and so on.

Or just wait 9 seconds for the query to run, and be happy with the 200 to 1 improvement over what you had before.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources