Time out - 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

General SQL Server Forums

New to SQL Server Programming

Time out

Author

Topic

victorsarathy
Starting Member

8 Posts

Posted - 2008-08-08 : 03:10:09

Hi friends,
The table from SQL server have 1000 records.
The data fetch operation will take 2 mins . I dont know the reason for delay. Have any opinions pls drop here. I am awaiting.
Thanks SQL masters

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-08 : 03:12:30

we're really gonna need more detail than that to offer any help. table structure / indexing / hardware config / what query etc....

Em

victorsarathy
Starting Member

8 Posts

Posted - 2008-08-08 : 03:22:56

There are four operations are done.
1. Insert record from ParentTable to X table -> nearly 1000 records
2. Insert record from ChildTable to X table -> nearly 3000 records
3. Data read from X Table,calculate and validation(from server application) and insert into Y table
4. Data read from Y Table.

Please note the following times taken for the above 4 operations.
start Time End Time
1. 2:54:20.090 - 2:54:20.450
2. 2:54:20.467 - 2:54:53.810
3. 2:54:56 - 2:56:06.483 -> Delay
4. 2:56:28.543 - 2:56:34 -> Delay.
So mainly data fetch is very delay .
So whats the possibility to improve speed.
Pls..

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-08 : 03:24:51

what is the query?

Em

victorsarathy
Starting Member

8 Posts

Posted - 2008-08-08 : 03:40:03

Four operations (simple)
1. INSERT INTO X(A,B) SELECT X,Y FROM TABLE_SOURCE1
2. INSERT INTO X(A,B) SELECT X,Y FROM TABLE_SOURCE2
3.
3-1. SELECT X
3-2. CALCULATION
3-3. INSERT Y (A,B) SELECT A,B FROM X
4. SELECT Y

Four operations ( Real)
1.
insert into HACHUDAT (HANO, KRNO, BUBN, SRKN, KAKU, KFSU, HASU, NOKI, NKKG,TANK, HACC, JOCD, NKGS, TOCD, HADY, ZUBN, HNAM, ZAIS, HINJ, HASC, BUNC, CHBN, DFLG, OCHB, CHDY, KFLG, SFLG, YSU1, YSU2,
YSU3,JYDY) SELECT '' AS HANO, 0 AS KRNO, JYUCHMST.BUBN, 0 AS SRKN, JYUCHMST.KAKU, (SYOHNMST.GZMH + SYOHNMST.MNMH) - (JYUCHMST.SUUR - (JYUCHMST.GZHA + JYUCHMST.MNHA)) AS KFSU, 0 AS HASU, JYUCHMST.NOKI AS
NOKI, JYUCHMST.NOKI AS NKKG, JYUCHMST.TANK, '0' AS HACC, '' AS JOCD,0 AS NKGS, SYOHNMST.TEHC, '2008/08/08', SYOHNMST.ZUBN, SYOHNMST.HNAM, SYOHNMST.ZAIS, '0' AS HINJ, JYUCHMST.JUSC AS HASC, 0 AS BUNC, ''
AS CHBN, '' AS DFLG, JYUCHMST.CHBN AS OCHB, JYUCHMST.CHDY, JYUCHMST.BNKN AS KFLG, '0' AS SFLG, JYUCHMST.YSU1, JYUCHMST.YSU2, JYUCHMST.YSU3, JYUCHMST.JYDY FROM JYUCHMST INNER JOIN SYOHNMST ON JYUCHMST.BUBN
= SYOHNMST.BUBN WHERE (JYUCHMST.JOCD NOT IN ('2', '9', '4')) AND (JYUCHMST.DFLG NOT IN ('1')) AND (JYUCHMST.JUSC = '0') AND (JYUCHMST.JYDY = '2008/08/07') AND (JYUCHMST.BNKN = 0) AND (NOT EXISTS
(SELECT OCHB, BUBN, CHDY FROM HACHUDAT WHERE (OCHB = JYUCHMST.CHBN) AND (BUBN = JYUCHMST.BUBN ) AND (CHDY = JYUCHMST.CHDY) AND JYUCHMST.JOCD NOT IN ('3'))) order by JYUCHMST.NOKI desc, JYUCHMST.BOSU desc,
JYUCHMST.CHDY

2.
insert into HACHUDAT (HANO, KRNO, BUBN, SRKN, KAKU, KFSU, HASU, NOKI, NKKG,TANK, HACC, JOCD, NKGS, TOCD, HADY, ZUBN, HNAM, ZAIS, HINJ, HASC, BUNC, CHBN, DFLG, OCHB, CHDY, KFLG, SFLG,YSU1, YSU2, YSU3,JYDY
) SELECT '' AS HANO, 0 AS KRNO, KTJYUMST.KBUN, 0 AS SRKN, KTJYUMST.KAKU, (SYOHNMST.GZMH + SYOHNMST.MNMH) - ((JYUCHMST.SUUR * KTJYUMST.SKSU) -(KTJYUMST.GZHA + KTJYUMST.MNHA)) AS KFSU, 0 AS HASU,
JYUCHMST.NOKI AS NOKI, JYUCHMST.NOKI AS NKKG, KTJYUMST.TANK, CASE WHEN KTJYUMST.SFLG = 1 THEN 'S' ELSE 'A' END AS HACC, '' AS JOCD,0 AS NKGS, SYOHNMST.TEHC, '2008/08/08', SYOHNMST.ZUBN, SYOHNMST.HNAM,
SYOHNMST.ZAIS, '0' AS HINJ, JYUCHMST.JUSC AS HASC, 0 AS BUNC, '' AS CHBN, '' AS DFLG, KTJYUMST.THCB AS OCHB, KTJYUMST.CHDY, JYUCHMST.BNKN AS KFLG, KTJYUMST.SFLG AS SFLG, JYUCHMST.YSU1, JYUCHMST.YSU2,
JYUCHMST.YSU3, KTJYUMST.JYDY FROM KTJYUMST INNER JOIN SYOHNMST ON KTJYUMST.KBUN = SYOHNMST.BUBN INNER JOIN JYUCHMST ON KTJYUMST.CHBN = JYUCHMST.CHBN AND KTJYUMST.CHDY = JYUCHMST.CHDY AND KTJYUMST.BUBN
= JYUCHMST.BUBN where (KTJYUMST.JYDY = '2008/08/07') AND (KTJYUMST.JOCD NOT IN ('2', '9')) and (KTJYUMST.DFLG <> '1') AND (KTJYUMST.SFLG = 0) AND JYUCHMST.BNKN=1 AND (NOT EXISTS (SELECT OCHB, BUBN,
CHDY FROM HACHUDAT WHERE (OCHB = KTJYUMST.THCB) AND (BUBN = KTJYUMST.KBUN ) AND (CHDY = KTJYUMST.CHDY) AND KTJYUMST.JOCD NOT IN ('3'))) order by JYUCHMST.NOKI desc, KTJYUMST.CHDY

3. With in loop ( for example there are 4000 records created by above querry, We access by distinct BUBN count loop ( ex.1750) )
select HACHUDAT.HANO, HACHUDAT.BUBN, HACHUDAT.SRKN, HACHUDAT.KAKU, HACHUDAT.KFSU, HACHUDAT.NOKI, HACHUDAT.TANK, HACHUDAT.HACC, HACHUDAT.NKGS, HACHUDAT.JOCD, HACHUDAT.TOCD, HACHUDAT.HADY, HACHUDAT.ZUBN,
HACHUDAT.HNAM, HACHUDAT.ZAIS, HACHUDAT.HINJ, HACHUDAT.HASC, HACHUDAT.BUNC, HACHUDAT.YSU1, HACHUDAT.YSU2, HACHUDAT.YSU3, HACHUDAT.DFLG, HACHUDAT.OCHB, HACHUDAT.CHDY, HACHUDAT.NKKG, HACHUDAT.HASU,
HACHUDAT.JYDY,SYOHNMST.TEHC,HACHUDAT.SFLG from HACHUDAT INNER JOIN SYOHNMST ON HACHUDAT.BUBN = SYOHNMST.BUBN where (HACHUDAT.HANO = '') and (HACHUDAT.BUBN = '00X99-PY3-A000')and (HACHUDAT.HASC = '0') AND
(HACHUDAT.JYDY = '2008/08/07') AND (HACHUDAT.SFLG = 0) order by HACHUDAT.NOKI asc

4. SELECT ALL RECORD FINALLY
select H.BUBN, H.HANO, H.NOKI, S.ZUBN, S.HNAM, S.ZAIS, S.STAN, H.TOCD, H.HASU, H.NKGS, H.HADY, H.KFSU, H.NKKG, S.HTSU, S.HKIN, H.KRNO, H.YSU1, H.YSU2, H.YSU3, H.YSU4, S.HTAN, H.HASC ,S.GZMH, S.GZHA,
S.MNMH, S.MNHA , H.OCHB, H.CHDY,H.HACC,H.SFLG from HASOUDAT as H left outer join SYOHNMST as S on (H.BUBN = S.BUBN) where (KRNO = 0 and HASC IN (0, 1) and H.BUBN LIKE '94608-10000%' and (JOCD NOT IN ('2',
'9')) and (DFLG NOT IN ('1')) and NOT HANO LIKE 'SK%') and (JYDY = '2008/08/07') AND (HANO = '') ORDER BY H.BUBN

Thanks for your kindness help

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-08 : 03:50:05

so it's your 'loop' / cursor that's taking all the time. not really surprising is it? have you tried to work on a set based solution?

Em

victorsarathy
Starting Member

8 Posts

Posted - 2008-08-08 : 03:53:53

Dear Em,
I have no idea for further step . So please give me some idea. What is set based solution. And basically data read is very loop even without loop. Just I tried to retrieve 2000 record then it will take nearly 1 mins. Why?. While in data read the data is not thrown any exception.
So.
Thanks Em,

quote:
Originally posted by elancaster

so it's your 'loop' / cursor that's taking all the time. not really surprising is it? have you tried to work on a set based solution?

Em

victorsarathy
Starting Member

8 Posts

Posted - 2008-08-08 : 03:55:51

Dear Em,
I have no idea for further step . So please give me some idea. What is set based solution. And basically data read is very slow even without loop. Just I tried to retrieve 2000 record then it will take nearly 1 mins. Why?. While in data read the data is not thrown any exception.
So.
Thanks Em,

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-08 : 03:57:05

i don't understand this bit...

quote:
Originally posted by victorsarathy

Dear Em,
I have no idea for further step . So please give me some idea. What is set based solution. And basically data read is very loop even without loop. Just I tried to retrieve 2000 record then it will take nearly 1 mins. Why?. While in data read the data is not thrown any exception.
So.
Thanks Em,

victorsarathy
Starting Member

8 Posts

Posted - 2008-08-08 : 04:04:49

The data read is very slow . Here the sample code. Just i try to get table records. It has nearly 2000 records. it will take 1 mints to get all.
this->sqlop1->SqlCmdText = 'SELECT A from Table1';
this->sqlop1->SqlCmd->CommandTimeout = 0;
SqlDataReader^ datareader = nullptr;
datareader = this->sqlop1->ExecuteFetch();
if(datareader == nullptr)
return nullptr;
SumOrderInfoData^ element =nullptr;
while(this->sqlop1->FetchNext() != nullptr) {
try{
element->PartsNo = datareader->GetString(0);
}
catch(...){
element->PartsNo = "";
}
}

quote:
Originally posted by elancaster

i don't understand this bit...

quote:
Originally posted by victorsarathy

Dear Em,
I have no idea for further step . So please give me some idea. What is set based solution. And basically data read is very loop even without loop. Just I tried to retrieve 2000 record then it will take nearly 1 mins. Why?. While in data read the data is not thrown any exception.
So.
Thanks Em,

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources