Trims teman2 utk tanggapannya. Saya pake Acer Altos G520 (Xeon 3 GH +
RAM 1 GB), kynya cukup bagus utk server. Trus tabelnya jg dah sy index.
Ceritanya, saya mo menampilkan EMC/kadar air dlm kayu yg dihitung dari
suhu udara + suhu titik embun. Tabel yg saya donload dr NCDC (BMG-nya
amerika) berisi No stasiun pengamat, tanggal, suhu udara dan suhu
titik embun. Saya punya data th 2003-2006 dlm bentuk txt yg di-zip.
tiap tahun 1 file (besarnya +- 80 MB per file). Ukuran sebesar itu
karena memua data harian di +- 7000 stasiun pengamatan di seluruh
dunia.
Karena tiap tahun pasti ada data yg lebih baru, maka saya membebaskan
user saya utk memilih tahun berapa yg akan dipakai sbg dasar
penghitungan (bisa 2003, 2006, rata2 2003-2006 dsb).
Data tiap tahun saya letakkan di tabel yg berbeda (2003, 2004 dst)
Yg saya lakukan adalah mengambil data yg ingin digunakan, kemudian
saya letakkan di tabel tersendiri (ALLDATA). ALLDATA bisa merupakan
copy dari salah satu tabel (2003 dll) atau rata2 atau maksimum atau
minimum dari beberapa tabel.
Ada 2 tabel lain CountryList (field yg dipake CTR_CODE dan CTR_NAME) &
Station List (field :STN_NO, STN_NAME, CTR CODE).
tapi tabel2 tsb ga terintegrasi (ada value CTR_CODE di StationList yg
tdk terdapat di CountryList dsb), sehingga ALLDATA tadi harus saya
delete sebagian datanya. Ini stored procedurenya, mudah2an ga pusing
melihatnya
CREATE PROCEDURE SP_DataSourceSet
@Model Int, @Tahun1 Int, @Tahun2 Int
AS
SET NOCOUNT ON
DECLARE @Tabel1 Varchar(1000)
DECLARE @Tabel2 Varchar(1000)
DECLARE @SQLStr Varchar(4000)
DECLARE @TempTahun Varchar(100)
CREATE TABLE #Temp0 (STN_NO Int, YEARMODA SmallDateTime, [TEMP] Float,
DEWP Float )
CREATE TABLE #Temp1 (STN_NO Int, [DATE] Int, [TEMP] Float, DEWP Float )
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'ALLDATA')
DROP TABLE ALLDATA
IF @Model = 1
BEGIN
SET @Tabel1 = CONVERT(Varchar, @Tahun1)
SET @SQLStr = 'SELECT * INTO [ALLDATA] FROM ['+ @Tabel1 + ']'
EXECUTE (@SQLStr)
DELETE FROM ALLDATA
WHERE [TEMP] > 500 OR DEWP > 500
END
ELSE IF @Model = 2
BEGIN
SET @Tabel1 = CONVERT(Varchar, @Tahun1)
SET @Tabel2 = CONVERT(Varchar, @Tahun2)
SET @TempTahun = @Tabel1
WHILE @TempTahun <> @Tabel2
BEGIN
SET @SQLStr = 'INSERT INTO #Temp0
SELECT * FROM [' + @TempTahun + ']'
EXECUTE(@SQLStr)
SET @TempTahun = CONVERT(Varchar(100), (CONVERT(Int,
@TempTahun) + 1))
END
SET @SQLStr = 'INSERT INTO #Temp0
SELECT * FROM [' + @Tabel2 +']'
EXECUTE(@SQLStr)
DELETE FROM #Temp0
WHERE [TEMP] > 500 OR DEWP > 500
INSERT INTO #Temp1
SELECT STN_NO, DATEPART(dy, YEARMODA) AS [DATE], AVG([TEMP])
AS [TEMP], AVG(DEWP) AS DEWP
FROM #Temp0
GROUP BY STN_NO, DATEPART(dy, YEARMODA)
ORDER BY STN_NO, DATEPART(dy, YEARMODA)
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'ALLDATA')
DROP TABLE ALLDATA
SELECT STN_NO, DATEADD(dd, [DATE]-1, 0) AS YEARMODA, [TEMP], DEWP
INTO ALLDATA
FROM #Temp1
END
ELSE IF @Model = 3
BEGIN
SET @Tabel1 = CONVERT(Varchar, @Tahun1)
SET @Tabel2 = CONVERT(Varchar, @Tahun2)
SET @TempTahun = @Tabel1
WHILE @TempTahun <> @Tabel2
BEGIN
SET @SQLStr = 'INSERT INTO #Temp0
SELECT * FROM [' + @TempTahun + ']'
EXECUTE(@SQLStr)
SET @TempTahun = CONVERT(Varchar(100), (CONVERT(Int,
@TempTahun) + 1))
END
SET @SQLStr = 'INSERT INTO #Temp0
SELECT * FROM [' + @Tabel2 +']'
EXECUTE(@SQLStr)
DELETE FROM #Temp0
WHERE [TEMP] > 500 OR DEWP > 500
INSERT INTO #Temp1
SELECT STN_NO, DATEPART(dy, YEARMODA) AS [DATE], MAX([TEMP])
AS [TEMP], MAX(DEWP) AS DEWP
FROM #Temp0
GROUP BY STN_NO, DATEPART(dy, YEARMODA)
ORDER BY STN_NO, DATEPART(dy, YEARMODA)
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'ALLDATA')
DROP TABLE ALLDATA
SELECT STN_NO, DATEADD(dd, [DATE]-1, 0) AS YEARMODA, [TEMP], DEWP
INTO ALLDATA
FROM #Temp1
END
ELSE IF @Model = 4
BEGIN
SET @Tabel1 = CONVERT(Varchar, @Tahun1)
SET @Tabel2 = CONVERT(Varchar, @Tahun2)
SET @TempTahun = @Tabel1
WHILE @TempTahun <> @Tabel2
BEGIN
SET @SQLStr = 'INSERT INTO #Temp0
SELECT * FROM [' + @TempTahun + ']'
EXECUTE(@SQLStr)
SET @TempTahun = CONVERT(Varchar(100), (CONVERT(Int,
@TempTahun) + 1))
END
SET @SQLStr = 'INSERT INTO #Temp0
SELECT * FROM [' + @Tabel2 +']'
EXECUTE(@SQLStr)
DELETE FROM #Temp0
WHERE [TEMP] > 500 OR DEWP > 500
INSERT INTO #Temp1
SELECT STN_NO, DATEPART(dy, YEARMODA) AS [DATE], MIN([TEMP])
AS [TEMP], MIN(DEWP) AS DEWP
FROM #Temp0
GROUP BY STN_NO, DATEPART(dy, YEARMODA)
ORDER BY STN_NO, DATEPART(dy, YEARMODA)
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'ALLDATA')
DROP TABLE ALLDATA
SELECT STN_NO, DATEADD(dd, [DATE]-1, 0) AS YEARMODA, [TEMP], DEWP
INTO ALLDATA
FROM #Temp1
END
DELETE FROM ALLDATA
WHERE STN_NO NOT IN (SELECT USAF FROM NewStationList)
DELETE FROM ALLDATA
WHERE STN_NO IN (SELECT STN_NO FROM ALLDATA
GROUP BY STN_NO
HAVING (COUNT(STN_NO) < 300))
SET @SQLStr = 'DELETE FROM ALLDATA
WHERE STN_NO IN (SELECT DISTINCT [' + @Tabel1 +'].STN_NO
FROM [' + @Tabel1 + '] INNER JOIN NewStationList ON [' + @Tabel1
+ '].STN_NO = NewStationList.USAF
WHERE (NewStationList.[STATION NAME] LIKE N' + '''%buoy%''))'
EXECUTE (@SQLStr)
SET @SQLStr = 'DELETE FROM ALLDATA
WHERE STN_NO IN (SELECT DISTINCT [' + @Tabel1 +'].STN_NO
FROM [' + @Tabel1 + '] INNER JOIN NewStationList ON [' + @Tabel1
+ '].STN_NO = NewStationList.USAF
WHERE (NewStationList.[STATION NAME] LIKE N' + '''%platform%''))'
EXECUTE (@SQLStr)
UPDATE DataSource SET Model = @Model, Tahun1 = @Tahun1, Tahun2 =
@Tahun2
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'STATION')
DROP TABLE STATION
SELECT * INTO STATION
FROM NewStationList
WHERE USAF IN (SELECT DISTINCT STN_NO FROM ALLDATA)
SELECT USAF INTO #Temp3
FROM STATION
GROUP BY USAF
HAVING COUNT(USAF) > 1
--maksudnya utk membuat tabel #Temp4
SELECT TOP 1 * INTO #Temp4
FROM STATION
DELETE FROM #Temp4
DECLARE @NomorStasiun Int
DECLARE Stasiun CURSOR
FOR
SELECT USAF FROM #Temp3
OPEN Stasiun
FETCH NEXT FROM Stasiun INTO @NomorStasiun
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Temp4
SELECT TOP 1 * FROM STATION
WHERE USAF = @NomorStasiun
DELETE FROM STATION
WHERE USAF = @NomorStasiun
INSERT INTO STATION
SELECT * FROM #Temp4
DELETE FROM #Temp4
FETCH NEXT FROM Stasiun INTO @NomorStasiun
END
CLOSE Stasiun
DEALLOCATE Stasiun
IF EXISTS (SELECT Name FROM SysObjects WHERE Name = 'COUNTRY')
DROP TABLE COUNTRY
SELECT * INTO COUNTRY
FROM NewCountryList
WHERE CTR_CODE IN (SELECT DISTINCT CTRY1 FROM STATION)
GO
Saran temen2 utk membuat ConnectionTimeOut jadi 0 jg sudah saya ikuti
tapi hasilnya masih Time Out Expired
Pls temen2 tolongin saya.
salam,
cahyo
> Yg penting bukan banyaknya data, tapi :
>
> 1. Spesifikasi komputernya apaan, server beneran atau PC Desktop
> biasa?
> 2. tabel2 nya memiliki Index gak
>
> kalo data segitu banyak sih harus pake server yang beneran tuh
misalnya
> yang proccsesor nya Dual Core atau Xeon gituh
>
>
>
> Regards,
>
> Ardhy
