Running slow over 30000 record

[siagian]

Set db = CurrentDb
Set qdfReport = db.CreateQueryDef("", _
"PARAMETERS tgl_awal DateTime, tgl_akhir
DateTime; " & _
"SELECT [tblWO].[no_wo], [tblWO].[masuk_tgl],
[tblDataKendaraan].[no_pol], [tblDataPemilik].
[perusahaan], [tblWO].[no_wo] AS wo " & _
"FROM tblDataPemilik RIGHT JOIN (tblDataKendaraan
RIGHT JOIN tblWO ON [tblDataKendaraan].[nokey_cu]=[tblWO].
[nokey_cu]) ON [tblDataPemilik].[IDCU]=[tblDataKendaraan].
[IDCU]" & _
" WHERE ((([tblWO].[masuk_tgl]) Between [tgl_awal]
And [tgl_akhir])) " _
& " ORDER BY [tblWO].[masuk_tgl], [tblWO].[no_wo]")

qdfReport.Parameters!tgl_awal = Me.awal
qdfReport.Parameters!tgl_akhir = Me.akhir

With qdfReport
' Open Recordset from QueryDef.
Set rs = .OpenRecordset(dbOpenSnapshot)
With rs
Set Me.Recordset = rs
End With
End With

Data 30000 record each table

why at first time slow ??

thanks

 

[John Viescas]

Do you have an index on tblWO.masuk_tgl? Do you also have indexes on the
JOIN columns? That could speed up the query significantly.

Also, it would be simpler to do it like this:

Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT [tblWO].[no_wo], [tblWO].[masuk_tgl],
[tblDataKendaraan].[no_pol], [tblDataPemilik].
[perusahaan], [tblWO].[no_wo] AS wo " & _
"FROM tblDataPemilik RIGHT JOIN (tblDataKendaraan
RIGHT JOIN tblWO ON [tblDataKendaraan].[nokey_cu]=[tblWO].
[nokey_cu]) ON [tblDataPemilik].[IDCU]=[tblDataKendaraan].
[IDCU]" & _
" WHERE ((([tblWO].[masuk_tgl]) Between #" & Me.awal & _
"# And #" & Me.akhir & "#)) " _
& " ORDER BY [tblWO].[masuk_tgl], [tblWO].[no_wo]")

Set Me.Recordset = rs

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)