performance differance between 2 Table I/O methods(fetchsingle and select-fetch)

[ricky]

hi, all:
Does anybody have any idea about the performance differance between these two Table I/O methods: 1,FetchSingle(index 1) by index1 to get field A value; 2 first Select (index 1) by index1 ,then Fetch (index 1) get fieldA value. Sounds like the first method run faster than the second one. any thought?

 

[jdeakv]

First one runs faster than the second and also the prefered one.

 

[lefab]

I don't know about speed. A small test will give you the answer for your config.

But be carefull : FetchSingle should be used only when 1 or 0 record match your request. If 2 or more records match the requestion, FetchSingle can return no values.

 

[jolly]

The difference will be absolutely minimal. Those methods just set up basically the same SQL to run on the server and that's where all the run time is. If you are calling this inside a loop then it will be slow as you are multiplying the effort to parse the query, obtain an execution plan etc.

 

[sviardot]

not minimal
Fetch is use to find the first record on the index (you can also modifiy condition to use greater, greather, different, ... ).
Fetch_single give you the first find record, we have to same but not necesseraly if your index is not at the top.
By extension, if you want to test the existence of a record or if there is only one, use the fectch single. If you want to find the first one or build a complex request use the fetch.
Just take a look om sone NER BSFN (especially in the 49's), will help you to see the difference.

 

[ricky]

I just don't know, and after I post the question, I successfully persuaded our developer to change from Select-Fetch to FetchSigle.
before this simple change, the first UBE need 60 mins to finish, the other need 180 mins to finish.
but after the change, the first one need only 30 mins, the second one only took 60 mins............ I do not know if this make sense theoretically..

 

[jolly]

Serge, I don't think that is correct. The original poster specifed a Select followed by Fetch, not just an isolated FetchNext. The FetchNext will return the first record returned by the Select statement, not the first record on the index. In either case what the database sees is simply an SQL select statement followed by a fetch. Even if the programmer uses FetchSingle the database will see Select followed by Fetch. It is easy to verify this by looking at the debug log in each case. The SQL seen by the server is the same.

The only *real* difference between the two methods is that FetchSingle only allows you to select on index keys where Select/Fetch allows you to select on any column. Of course selecting on non keys would be slower than selecting on keys but that was not the original posters question.

As a test I just put two buttons on a form. One does FetchSingle and one does Select followed by FetchNext.

This is the FetchSingle:

RT: >>>Beginning ER:	 Button Clicked	App: 	 Form: WTESTJO1A	[T:524 F:c:\b7\system\jdeuser\jdecgrt\RT_ER.cpp Ln:2986 Lv:LEVEL3]  
Feb 02 14:54:41 ** 460/1316	Entering JDB_OpenTable( Table = F0101) 
Feb 02 14:54:41 ** 460/1316	Entering JDB_ClearSelection 
Feb 02 14:54:41 ** 460/1316	Entering JDB_SetSelection 
Feb 02 14:54:41 ** 460/1316	Exiting JDB_SetSelection With Success 
Feb 02 14:54:41 ** 460/1316	Entering JDB_SetSequencing 
Feb 02 14:54:41 ** 460/1316	Entering JDB_ClearSelection 
Feb 02 14:54:41 ** 460/1316	Entering JDB_BuildKeyBuffer 
Feb 02 14:54:41 ** 460/1316	Entering JDB_FetchKeyed 
Feb 02 14:54:41 ** 460/1316	ODBC:I DBInitRequest(reuse) conn=03CF2F88 hd=04117608 dr=0439E6E0 PRJDE A (OWJOHNO@Business Data - BDV) 
Feb 02 14:54:41 ** 460/1316	SELECT  *  FROM BDVDTA/F0101  WHERE  ( ABAN8 = 1312.000000 )  AND  ( ABMCU BETWEEN '           1' AND '   ZZZZZZZZZ' ) 
Feb 02 14:54:41 ** 460/1316	Entering JDB_ClearSelection 
Feb 02 14:54:41 ** 460/1316	Entering JDB_ClearSequencing 
Feb 02 14:54:41 ** 460/1316	Entering JDB_FreeKeyBuffer 
Feb 02 14:54:41 ** 460/1316	RT: <<<Finished  ER:	 Button Clicked	App: 	 Form: WTESTJO1A	:: [T:524 F:c:\b7\system\jdeuser\jdecgrt\RT_ER.cpp Ln:3010 Lv:LEVEL3]

and this is the Select followed by FetchNext:

RT: >>>Beginning ER:	 Button Clicked	App: 	 Form: WTESTJO1A	[T:49c F:c:\b7\system\jdeuser\jdecgrt\RT_ER.cpp Ln:2986 Lv:LEVEL3]  
Feb 02 14:56:42 ** 916/1180	Entering JDB_OpenTable( Table = F0101) 
Feb 02 14:56:42 ** 916/1180	Entering JDB_ClearSelection 
Feb 02 14:56:42 ** 916/1180	Entering JDB_SetSelection 
Feb 02 14:56:42 ** 916/1180	Exiting JDB_SetSelection With Success 
Feb 02 14:56:42 ** 916/1180	Entering JDB_SetSequencing 
Feb 02 14:56:42 ** 916/1180	Entering JDB_SelectKeyed 
Feb 02 14:56:42 ** 916/1180	ODBC:I DBInitRequest(reuse) conn=03CF2F88 hd=0412E220 dr=0439E908 PRJDE A (OWJOHNO@Business Data - BDV) 
Feb 02 14:56:42 ** 916/1180	SELECT  *  FROM BDVDTA/F0101  WHERE  ( ABAN8 = 1312.000000 )  AND  ( ABMCU BETWEEN '           1' AND '   ZZZZZZZZZ' )  ORDER BY ABAN8 ASC 
Feb 02 14:56:42 ** 916/1180	Entering JDB_Fetch 
Feb 02 14:56:42 ** 916/1180	RT: <<<Finished  ER:	 Button Clicked	App: 	 Form: WTESTJO1A	:: [T:49c F:c:\b7\system\jdeuser\jdecgrt\RT_ER.cpp Ln:3010 Lv:LEVEL3]

 

[jolly]

Hi Ricky,

Was the same selection criteria used in both cases and was the data and indexes in the table the same? What about database load on the system at the time of the tests?

In general, best practice is to use FetchSingle in preference to Select/Fetch as a) the code is smaller and more succinct, and b) it enforces use of index keys in the select.

Regards
JohnO

 

[ricky]

Hi,John,
the reason why I post the question is that we run into poor performance issue, and I take a look at the coding, and found out that programmer never use the fetchsingle, they just use select then fetch to pick up only one from our indexed records(the table has Ms of records). So I think that should be the root cause, but not sure.
as I said in my last post, after the post, we changed to fetchsingle, then the performance is much better. in addition, the other criteria is the same, such as selection,Index, database load.....

and I think in general there is no right or wrong in this post, just preference . but if need to pick up only one indexed record from the huge table,I think the fetchSingle would be the better one. Just my thought.


Best regards