select without a key field

[Former Member]

experts.

we need to fetch only one record from the ztable and ztable has 6 key fields and 5 non key fields. one non key field is the active status indicator. At any time only one record will be active . I mean if i have same record 5 times but in that 5 records only one will be active.

In my select query i am using first 4 key fields and rest 2 fields creation data and time i am not using and i am using active indicator field.

Now i am using select single statement.

Do i need to write this in select upto 1 rows because I am not using all key fields or can

i still use select single because i am using active field in where clause.

please tell me which one is best and why?

[Former Member]

experts.

we need to fetch only one record from the ztable and ztable has 6 key fields and 5 non key fields. one non key field is the active status indicator. At any time only one record will be active . I mean if i have same record 5 times but in that 5 records only one will be active.

In my select query i am using first 4 key fields and rest 2 fields creation data and time i am not using and i am using active indicator field.

Now i am using select single statement.

Do i need to write this in select upto 1 rows because I am not using all key fields or can

i still use select single because i am using active field in where clause.

please tell me which one is best and why?

[Former Member]

Hi,

If you use select single then you should provide all the key fields.

and if you are using select up to 1 row then you can give part of the key fields and it will fetch exact data what you want.

any way in current scenario both the satement will work because you have used active indicator fields

because above mentioned things are just for performace.

Regarads,

Raj Gupta

[Former Member]

Hi,

at a time only 1 record is active then use select single only it's very useful at the run time and memeory usage.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause .

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause or lack of, applies any aggregate, ordering or grouping functions to them and then returns the first record of the resultant result set.

Salil...

[Former Member]

any other thoughts please

[faisalatsap]

Hi,

SELECT SINGLE will be best i think too.

Best Regards,

Faisal

[Former Member]

Hi,

If you analyse deeply i think SELECT UPTO 1 ROWS is more efficient than SELECT SINGLE. Particularly when dealing with a table containing large number of records.

The basic difference between SELECT SINGLE and SELECT UPTO 1 ROWS is

1) SELECT SINGLE will fetch all the records which satisfies the where conditions in to the memory and then supply only the first record of the selected records and this often misleads programmers that it selects only a single record directly. This can lead to performance degradation when accessing large number of tables.

2) SELECT UPTO 1 ROWS will directly fetch only the first record which satisfies the where condition.

Thus i would suggest you to use SELECT UPTO 1 ROWS particularly when dealing with tables containing millions of records. For smaller tables there wouldnt be much of a performance issue between the two

Regards,

Vik

Edited by: vikred on Aug 20, 2009 10:16 PM

[Former Member]

>

> Hi,
>    at a time only 1 record is active then use select single only it's very useful at the run time and memeory usage. 
> 
> The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause .
> 
> The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause or lack of, applies any aggregate, ordering or grouping functions to them and then returns the first record of the resultant result set. 
> 
> Salil...

This is incorrect. Exactly opposite of what happens.

Check this []

Regards

[Former Member]

best method to test.

goto se30->tips and tricks->sql query

put both codes in two boxes. click measurement,

then check the runtime and use.

[Former Member]

Hi,

On using the select up to 1 rows or select single the opearation is going to be same. The thing that you need to keep in mind for consideration is the performance based on the where clause. Make sure of the volume of the data. If the volume is less then any approach is fair enough to the system.

If in case the volume is large then create an index for the table so that the fectch is performed better.

Rgds,

Swetha.