SQL Server Slow Performance - SQL Server Forums

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 Server Slow Performance

Author

Topic

sulung
Starting Member

3 Posts

Posted - 2005-03-04 : 08:25:35

Dear Sir,
I have problem with my sql server. I have a query like this

SELECT A.Nota, A.Tanggal, A.Kasir, B.Nama, B.Lain2 AS Gudang, W.KodeBarang,
X.Nama AS NamaBarang, W.KodeKemasan, Y.Nama AS NamaSatuan, W.Jumlah,
W.Harga, W.Disc, (W.Jumlah * W.Harga) AS JumlahTagihan,
(W.Jumlah * W.Disc) AS Potongan,
((W.Jumlah * W.Harga) - (W.Jumlah * W.Disc)) AS Bayar,
Z.Jumlah AS SisaStok
FROM Retail AS A, Pegawai AS B, DetailRetail AS W,
Barang AS X, Satuan AS Y, StokGudang Z
WHERE B.Kode = A.Kasir AND W.Nota = A.Nota AND X.Kode = W.KodeBarang AND
Y.Kode = W.KodeKemasan AND Z.KodeBarang = W.KodeBarang AND
Z.KodeSatuan = W.KodeKemasan AND Z.KodeGudang = B.Lain2
ORDER BY A.Tanggal

I have made index for each table and it's take 18 second to select about 20,000 record. I'm using SQL Query Analyser. I made my application with VB 6.0 and crystal report as my report. My problem is 18 second it's too long for my application. Do you have solution how I can select this record more faster. In your opinion How long SQL Server take 20,000 record in 6 table, like in my query? for your consideration this is my execution plan :

|--Compute Scalar(DEFINE:([Expr1006]=[W].[Jumlah]*[W].[Harga], [Expr1007]=[W].[Jumlah]*[W].[Disc], [Expr1008]=[W].[Jumlah]*[W].[Harga]-[W].[Jumlah]*[W].[Disc]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Z].[KodeSatuan]))
|--Sort(ORDER BY:([A].[Tanggal] ASC))
| |--Hash Match Root(Inner Join, HASH:([Z].[KodeBarang])=([X].[Kode]), RESIDUAL:(([X].[Kode]=[Z].[KodeBarang] AND [W].[KodeKemasan]=[Z].[KodeSatuan]) AND [Z].[KodeGudang]=[B].[Lain2]))
| |--Table Scan(OBJECT:([Supermarket].[dbo].[StokGudang] AS [Z]))
| |--Hash Match Team(Inner Join, HASH:([X].[Kode])=([W].[KodeBarang]), RESIDUAL:([W].[KodeBarang]=[X].[Kode]))
| |--Clustered Index Scan(OBJECT:([Supermarket].[dbo].[Barang].[PK_Barang] AS [X]))
| |--Hash Match(Inner Join, HASH:([A].[Nota])=([W].[Nota]), RESIDUAL:([A].[Nota]=[W].[Nota]))
| |--Hash Match(Inner Join, HASH:([B].[Kode])=([A].[Kasir]), RESIDUAL:([A].[Kasir]=[B].[Kode]))
| | |--Clustered Index Scan(OBJECT:([Supermarket].[dbo].[Pegawai].[PK_Pegawai] AS [B]))
| | |--Clustered Index Scan(OBJECT:([Supermarket].[dbo].[Retail].[PK_Retail] AS [A]))
| |--Table Scan(OBJECT:([Supermarket].[dbo].[DetailRetail] AS [W]))
|--Clustered Index Seek(OBJECT:([Supermarket].[dbo].[Satuan].[PK_Satuan] AS [Y]), SEEK:([Y].[Kode]=[Z].[KodeSatuan]) ORDERED FORWARD)

Thank You for your time.

Best Regard,
Bambang Adi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-04 : 11:43:06

I don't see anything wrong with your sql statment. Although you should really start using the ANSI-95 join syntax (that won't help the performance of this statment, though):


FROM	Retail AS A
JOIN	Pegawai AS B
		B.Kode = A.Kasir
JOIN	DetailRetail AS W,
		ON W.Nota = A.Nota
JOIN	Barang AS X
		ON X.Kode = W.KodeBarang
JOIN	,Satuan AS Y
		ON Y.Kode = W.KodeKemasan
JOIN	,StokGudang Z 
		ON Z.KodeBarang = W.KodeBarang 
		AND Z.KodeSatuan = W.KodeKemasan 
		AND Z.KodeGudang = B.Lain2 
ORDER BY A.Tanggal

I see by your execution plan that you have a few table scans happening as well as several Clustered Index Scans happening. It's possible that you could improve the way your tables are indexed. And depending on the indexes and record counts on these table you might be able to improve performance by changing the statement. It's impossible to tell without the DDL for these objects. If you want to you can script out the tables (including indexes) and post that with the approximate record counts of the tables and we could help you more.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-04 : 11:53:14

The first thing you might want to do is run Update Statistics for these tables just in case they are out of date. If the exec plan (and performance time) don't change, then go ahead and post the DDL.

Be One with the Optimizer
TG

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-04 : 13:38:46

Well..you've got scans all over the place.

I would say it's not properly indexed.

Post the DDL of the tables and include the indexes

Brett

8-)

sulung
Starting Member

3 Posts

Posted - 2005-03-05 : 02:28:09

Dear All,
thank for your concert. I don't know how to post dll of the table and index. which table I must post, all? thank

Best Regard,
Bambang

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-05 : 08:20:23

Yes, all tables involved with your statement.

To Post DDL:
In Enterprise Manager
Multi-Select all tables involved in your statement
Action menu - select "All Tasks" | "Generat SQL Scripts"
Formatting tab - only check "Generate the create..."
Options tab - check all "Table scripting options"
Go back to General Tab
Click "Preview" button
copy and past code to here.

Be One with the Optimizer
TG

sulung
Starting Member

3 Posts

Posted - 2005-03-05 : 11:05:47

Dear All,

this is my dll

CREATE TABLE [dbo].[Barang] (
[Kode] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nama] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Warna] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ukuran] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Keterangan] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DetailRetail] (
[Nota] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KodeBarang] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KodeKemasan] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Harga] [float] NULL ,
[Jumlah] [float] NULL ,
[Disc] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Pegawai] (
[Kode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nama] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sandi] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Jabatan] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tgl_Masuk] [datetime] NULL ,
[Tingkat] [smallint] NULL ,
[Gaji] [float] NULL ,
[Tunjangan] [float] NULL ,
[Bonus] [float] NULL ,
[Kesehatan] [float] NULL ,
[UangMakan] [float] NULL ,
[Transport] [float] NULL ,
[Insentif] [float] NULL ,
[Potongan] [float] NULL ,
[Harian] [float] NULL ,
[Lain2] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Retail] (
[Nota] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Tanggal] [datetime] NULL ,
[Kasir] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Disc] [float] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Satuan] (
[Kode] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Nama] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Nilai] [float] NULL ,
[Link] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[StokGudang] (
[KodeBarang] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KodeSatuan] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KodeGudang] [smallint] NULL ,
[Jumlah] [float] NULL ,
[Barcode] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Barang] WITH NOCHECK ADD
CONSTRAINT [PK_Barang] PRIMARY KEY CLUSTERED
(
[Kode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Pegawai] WITH NOCHECK ADD
CONSTRAINT [PK_Pegawai] PRIMARY KEY CLUSTERED
(
[Kode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Retail] WITH NOCHECK ADD
CONSTRAINT [PK_Retail] PRIMARY KEY CLUSTERED
(
[Nota]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Satuan] WITH NOCHECK ADD
CONSTRAINT [PK_Satuan] PRIMARY KEY CLUSTERED
(
[Kode]
) ON [PRIMARY]
GO

thank you.
Bambang Adi

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-05 : 12:34:43

Bambang,

If you don't have testing environment to work in, I would suggest setting one up to make copies of these tables before you start making changes to them. When you find changes that work best, then you can apply them to the real tables.

I've made some suggestions/guidelines below. But I STRONGLY recommend that your read up on some topics in Books on Line (Contents tab) starting with "Designing an Index".

First thing I see is that the following tables don't have Primary Keys:
StokGudang
DetailRetail
If there is no "natural key" (column or combination of columns that should be unique) for these tables then you should create an identity column or uniqueidentifier that could serve as a Primary Key.

Second thing is that, generally speaking, all columns involved in JOINs, WHERE criteria, and ORDER BY clauses should be indexed. So go through your queries that use these tables to find these columns that are being used and create indexes for them. Start with what you think are the most curcial keep retrying your queries. Keep monitoring the execution times and execution plans. typically, you'd like to see the "table scans" eliminated or minimized.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources