Query To Slow For Load

Question

Post reply

Query To Slow For Load

saysansay

Valued Member

Points: 50
More actions
January 25, 2008 at 3:09 am

#182295

hai Dear....I newbie here..i hope some trouble with Query..Load data to Slow 😀 Maybe at this forum have a solution
DECLARE @TAwal smalldatetime
DECLARE @TAkhir smalldatetime
DECLARE @KodeRekening varchar(13)
SET @TAwal = '2008-01-01'
SET @TAkhir = '2008-01-31' SET @KodeRekening = '1.111.112.001' SELECT LAB.NoBukti, LAB.NoTransaksi, LAB.Tanggal, LAB.Keterangan, SUM(LAB.Debet) AS Debet,SUM(LAB.Kredit) AS Kredit, SUM(LAB.Saldo) AS Saldo
FROM
(SELECT '-' AS NoBukti, '-' AS NoTransaksi, @TAwal-1 AS Tanggal, 'SALDO AWAL' AS Keterangan,0 AS Debet, 0 AS Kredit, 0 AS Saldo
UNION
SELECT '-' AS NoBukti, '-' AS NoTransaksi, @TAwal-1 AS Tanggal, 'SALDO AWAL' AS Keterangan,0 AS Debet, 0 AS Kredit,
CASE TR.Saldo
WHEN 'D' THEN
SUM(TDJ.Debet - TDJ.Kredit)
WHEN 'K' THEN
SUM(TDJ.Kredit - TDJ.Debet)
END AS Saldo
FROM (TJurnal AS TJ INNER JOIN TDetailJurnal AS TDJ ON
TJ.NoBukti=TDJ.NoBukti AND TJ.IDLokasi=TDJ.IDLokasi)
INNER JOIN TRekening AS TR ON TDJ.KodeRekening = TR.KodeRekening
WHERE
TDJ.KodeRekening = @KodeRekening
AND TJ.Tanggal = @TAwal
AND TJ.Tanggal <= @TAkhir) AS LAB
GROUP BY LAB.NoBukti, LAB.NoTransaksi, LAB.Tanggal, LAB.Keterangan
ORDER BY LAB.Tanggal, LAB.NoBukti
Stuctur Of Table
TJurnal
______________
nobukti varchar(30),PrimaryKey
IdLokasi Int,Foreignkey
NoTransaksi varchar(30),
Tanggal Datetime,
Keterangan Memo,
StEdit bit,
Awal bit,
StatusEdit bit,
Tgl Datetime
TdetailJurnal
_______________
nobukti varchar(30),PrimaryKey
IdLokasi Int,ForeignKey
NoUrut int,
KodeRekening varchar(30),
Dabet Float,
Kredit float,
Awal bit,
Tgl Datetime
Trekening
------------------
KodeRekening Varchar(30),PrimaryKey
..................
..................
..................
Load Data To slow 😀 Why ? !!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

Well frankly i hv'nt understood ur query so iam not going to say anything on it. Some pointers though...

Are there many rows that need to be accessed . What is the condition of the indexes?

Is ur query using proper indexes . check the execution plan for this. Are you accessing the database from a remote machine ?

"Keep Trying"

saysansay Valued Member Points: 50 More actions · Answer 2

I was Create A Index,but not give effect pepormance Database!!

Maybe my Index not good so can u Try to teach me how to index 3 tabel!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Can you post the indexes that are on the table please? Also the execution plan would really help.

Run the query in management studio with the execution plan option switched on. Save the exec plan as a .sqlplan file, zip it and attach to your post.

How many rows are in the tables? How many rows are returned by the query?

Is there any chance that the second select in the union will have a 0 for Saldo? If not, try changing the union to union all. union implies distinct, so requires a sort which can be expensive and slow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saysansay Valued Member Points: 50 More actions · Answer 4

This a SQLPlan of Jurnal Query,But I don't include Data,i hope anybody give a solution 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Ok, so you have no indexes other than the primary keys (from the code you posted). Is that correct?

I'm just guessing without knowing the rows involved, but perhaps indexes on TJurnal.Tanggal and TDetailJurnal.KodeRekening (with Debet and Kredit as Include columns) would help.

Change this declaration (DECLARE @KodeRekening varchar(13) ) to nvarchar to match the column type. Data types of variables should always match the columns that they are compared to.

Any chance of seeing the execution plan for the query? It would really help.

Asking again -

How many rows are in the tables? How many rows are returned by the query?

Is there any chance that the second select in the union will have a 0 for Saldo?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saysansay Valued Member Points: 50 More actions · Answer 6

saysansay

Valued Member

Points: 50

January 27, 2008 at 6:59 pm

#772249

Ok...Thank's mr.gilamonster for u solution 😀