SQL Server Performance Forum – Threads Archive

sql server slow performance

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? Thank You for your time. Best Regard,
Bambang Adi

The whole performance of a query is dependant on the columns used in WHERE clause and ensure those columns has necessary indexes. You may check the execution plan for this query and post the results here.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

How fast depends on hardware and indexes. You don’t have single search condition in your where clause. Why do you need to return 20,000 rows? I guess your intention is not to display all of them. For index configuration where clause is the most important however sometimes you can improve performance if you take into considaration columns from the select list too. As satya suggested post the execution plan, so we can advice.

hi Bambang Adi,
I faced the same problem back.
Try to do calculations in ur VB appln. and index columns included in where clause and dont use order by clause.
if u r not using whole rowset the minimize the rowset by TOP operator.

Dear Sir,
I don’t know how I can post the result of execution plan, I’m have been try but still I can’t do it. Thank you and I hope you can help me Best Regard,
Bambang Adi

SET SHOWPLAN_TEXT ON before the Query This will give you a text version. Then copy and paste results.

Dear Sir, Thank for Simon. this is my result : |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[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<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Z].[KodeSatuan])) |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Tanggal] ASC)) | |–Hash Match Root(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Z].[KodeBarang])=([X].[Kode]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([X].[Kode]=[Z].[KodeBarang] AND [W].[KodeKemasan]=[Z].[KodeSatuan]) AND [Z].[KodeGudang]=.[Lain2])) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[StokGudang] AS [Z])) | |–Hash Match Team(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[X].[Kode])=([W].[KodeBarang]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[W].[KodeBarang]=[X].[Kode])) | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Barang].[PK_Barang] AS [X])) | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Nota])=([W].[Nota]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Nota]=[W].[Nota])) | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[Kode])=([A].[Kasir]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Kasir]=.[Kode])) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Pegawai].[PK_Pegawai] AS )) | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Retail].[PK_Retail] AS [A])) | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[DetailRetail] AS [W])) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Satuan].[PK_Satuan] AS [Y]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Y].[Kode]=[Z].[KodeSatuan]) ORDERED FORWARD) Best Regard, Bambang Adi

I see two TableScans which I believe you don’t have proper indexs on them, and looks like the Nested loop will consume 90% of CPU. If you cannot restrict the rows in where clause, I have a suggestion you could intelligently use derived tables, some times it boost performance. srinivas

I dont understand what the mean of derived table. how can I get it and how to use. Thank you.

There are 2 table scans and 3 clustered index scans, which is the same thing as a table scans.
There table scans and equivalents because there is no search argument in the query, hence the query is asking for all rows. my 2 questions.
1. how much is CPU time versus network transfer time. run SET STATISTICS TIME ON, the run this query on the SQL Server system, how long does the query take? 2. run the query with SET STATISTICS PROFILE ON, the second row set is the plan with row counts, cost estimates etc, in addition to the op info both,
paste that in to here so i can see the row count estimates

Dear Sir, 1. this is the result of SET STATISTICS TIME ON : SQL Server parse and compile time: CPU time = 359 ms, elapsed time = 452 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (195179 row(s) affected) 2. and this is the result of SET STATISTICS PROFILE ON SQL Server parse and compile time: CPU time = 372 ms, elapsed time = 372 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (195179 row(s) affected) SQL Server Execution Times: CPU time = 2875 ms, elapsed time = 27956 ms. (14 row(s) affected) SQL Server Execution Times: CPU time = 2875 ms, elapsed time = 27978 ms. SQL Server Execution Times: CPU time = 2875 ms, elapsed time = 27979 ms. ==================================================================== 1951791SELECT 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 * W210NULLNULLNULLNULL213820.31NULLNULLNULL115.79597NULLNULLSELECT0NULL 1951791 |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1006]=[W].[Jumlah]*[W].[Harga], [Expr1007]=[W].[Jumlah]*[W].[Disc], [Expr1008]=[W].[Jumlah]*[W].[Harga]-[W].[Jumlah]*[W].[Disc]))221Compute ScalarCompute ScalarDEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1006]=[W].[Jumlah]*[W].[Harga], [Expr1007]=[W].[Jumlah]*[W].[Disc], [Expr1008]=[W].[Jumlah]*[W].[Harga]-[W].[Jumlah]*[W].[Disc])[Expr1006]=[W].[Jumlah]*[W].[Harga], [Expr1007]=[W].[Jumlah]*[W].[Disc], [Expr1008]=[W].[Jumlah]*[W].[Harga]-[W].[Jumlah]*[W].[Disc]213820.310.02.1382032E-2298115.79597[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang], [X].[Nama], [Y].[Nama], [Z].[Jumlah], [Expr1006], [Expr1007], [Expr1008]NULLPLAN_ROW01.0 1951791 |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Z].[KodeSatuan]))232Nested LoopsInner JoinOUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Z].[KodeSatuan])NULL213820.310.00.89376891322115.77459[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang], [X].[Nama], [Y].[Nama], [Z].[Jumlah]NULLPLAN_ROW01.0 1951791 |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Tanggal] ASC))243SortSortORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Tanggal] ASC)NULL213820.3180.2702716.109357822897.853676[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang], [X].[Nama], [Z].[KodeSatuan], [Z].[Jumlah]NULLPLAN_ROW01.0 1951791 | |–Hash Match Root(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Z].[KodeBarang])=([X].[Kode]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([X].[Kode]=[Z].[KodeBarang] AND [W].[KodeKemasan]=[Z].[KodeSatuan]) AND [Z].[KodeGudang]=.[Lain2]))254Hash Match RootInner JoinHASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Z].[KodeBarang])=([X].[Kode]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />([X].[Kode]=[Z].[KodeBarang] AND [W].[KodeKemasan]=[Z].[KodeSatuan]) AND [Z].[KodeGudang]=.[Lain2])NULL213820.310.01.584880726211.474048[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang], [X].[Nama], [Z].[KodeSatuan], [Z].[Jumlah]NULLPLAN_ROW01.0 307731 | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[StokGudang] AS [Z]))265Table ScanTable ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[StokGudang] AS [Z])[Z].[KodeGudang], [Z].[KodeSatuan], [Z].[KodeBarang], [Z].[Jumlah]30773.00.220541460.0339288640.25447026[Z].[KodeGudang], [Z].[KodeSatuan], [Z].[KodeBarang], [Z].[Jumlah]NULLPLAN_ROW01.0 1951691 | |–Hash Match Team(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[X].[Kode])=([W].[KodeBarang]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[W].[KodeBarang]=[X].[Kode]))275Hash Match TeamInner JoinHASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[X].[Kode])=([W].[KodeBarang]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[W].[KodeBarang]=[X].[Kode])NULL84070.8360.01.23050762309.6346865[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang], [X].[Kode], [X].[Nama]NULLPLAN_ROW01.0 101771 | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Barang].[PK_Barang] AS [X]))287Clustered Index ScanClustered Index ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Barang].[PK_Barang] AS [X])[X].[Kode], [X].[Nama]10177.09.9900357E-25.6365998E-32550.21107392[X].[Kode], [X].[Nama]NULLPLAN_ROW01.0 1954651 | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Nota])=([W].[Nota]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Nota]=[W].[Nota]))297Hash MatchInner JoinHASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Nota])=([W].[Nota]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Nota]=[W].[Nota])NULL91245.3980.04.22490071948.1931019[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang]NULLPLAN_ROW01.0 579531 | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[Kode])=([A].[Kasir]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Kasir]=.[Kode]))2109Hash MatchInner JoinHASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />.[Kode])=([A].[Kasir]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[A].[Kasir]=.[Kode])NULL26831.6740.00.531997861271.4931439[A].[Kasir], [A].[Tanggal], [A].[Nota], .[Lain2], .[Nama]NULLPLAN_ROW01.0 281 | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Pegawai].[PK_Pegawai] AS ))21110Clustered Index ScanClustered Index ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Pegawai].[PK_Pegawai] AS ).[Kode], .[Lain2], .[Nama]28.03.7578501E-20.00010932483.7687801E-2.[Kode], .[Lain2], .[Nama]NULLPLAN_ROW01.0 739581 | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Retail].[PK_Retail] AS [A]))21210Clustered Index ScanClustered Index ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Retail].[PK_Retail] AS [A])[A].[Kasir], [A].[Tanggal], [A].[Nota]73958.00.842022968.1432298E-21340.92345524[A].[Kasir], [A].[Tanggal], [A].[Nota]NULLPLAN_ROW01.0 2515061 | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[DetailRetail] AS [W]))2149Table ScanTable ScanOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[DetailRetail] AS [W])[W].[Nota], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang]251506.02.19831920.2767351932.4750543[W].[Nota], [W].[Disc], [W].[Harga], [W].[Jumlah], [W].[KodeKemasan], [W].[KodeBarang]NULLPLAN_ROW01.0 195179195179 |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Satuan].[PK_Satuan] AS [Y]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Y].[Kode]=[Z].[KodeSatuan]) ORDERED FORWARD)2183Clustered Index SeekClustered Index SeekOBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Supermarket].[dbo].[Satuan].[PK_Satuan] AS [Y]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Y].[Kode]=[Z].[KodeSatuan]) ORDERED FORWARD[Y].[Nama]1.06.3284999E-37.9603E-510217.027145[Y].[Nama]NULLPLAN_ROW0213820.31 trully I don’t really understand what you mean. so if this is wrong, please help me to understand more. Thank You

i need to know how much time it takes to execute the raw query, and how much to send it over the network. i need you to run the query from a Query Analyser session on the database server itself,
and compare it to a run from a QA session on the client. also, try removing 1) the order by, 2) the join to KodeSatuan
this is just a test

this query is returning 195179 rows, avg size 115 bytes, or over 20M,
if your network and client app is not fast, i am inclined to think that consume a good portion of the 29sec,
notice your cpu time is only 2.8s + 0.37sec for the compile
sorting 200k rows takes time also

]]>