topic abap database view not returning entries in Application Development and Automation Discussions

abap database view not returning entries

Former Member — Fri, 14 Feb 2014 11:11:25 GMT

Hi Experts,

I created a dbview with inner join for 4 base tables based on certain conditions.

Now the dbview in se11, if I try to retrieve the entries without giving

any condition, I am getting the entire records pretty fast enough ( hardly few kbs, 97000 records).

And the real issue occurs when I give an extra condtion for one of the field as equal to space, the data retrieval takes alot of time.

However for the same field if I give ne space, the data retrieval is fast.

Can anyone tell me if anything is wrong in using space as condition in the dbview.

i.e.

select data from dbview where field eq space. -> takes ample time

where as select data from dbview where field ne space. -> super fast.

select data from dbview. ->also super fast.

Any hints will be much appreciated.

Thanks in advance.

Wangmo

Re: abap database view not returning entries

Former Member — Fri, 14 Feb 2014 18:33:39 GMT

Hello Tenzin,

Is the field that you are using to set as space and not equal space, part of an index in one of the tables?

Have you tried using the SQL trace to see what the database is creating as the SQL statement?

Best regards,

Kim

Re: abap database view not returning entries

Former Member — Sun, 16 Feb 2014 17:22:34 GMT

Check TCODE ST05 and use "Explain SQL". Here you can check how the DB will try to execute the query: which index(es) and field(s) will be used. There you will see the answer for sure.

Cheers,

Andy

Re: abap database view not returning entries

Former Member — Tue, 18 Feb 2014 04:54:42 GMT

Hi Kim,

Thanks for the suggestion. I already have index for the column. I tried the SQL trace, couldn't find anything useful.

The strange thing is If I give the condition <> space, it works perfectly fine. But when I give = space,

the performance goes for a toss.. I couldn't figure out exactly why?

Thanks

Wangmo

Re: abap database view not returning entries

Former Member — Tue, 18 Feb 2014 04:56:42 GMT

Hi Andy,

I tried st05, explain sql, couldn't find anything meaningful to fix the issue.

However I know which fields and indexes are being used.

I can't figure out where exactly the time takes for that particular condition.

Thanks

Wangmo

Re: abap database view not returning entries

Former Member — Tue, 18 Feb 2014 13:35:43 GMT

Hi Tenzin,

I asked about the index because, the use of a negative operator on a field in the index, "cannot be used for a search over a database index". From BC490 ABAP Performance Tuning

In other words, when you use '<>' you are causing the database optimizer to disregard the index and it is performing a full table scan.

When you use '=', the index is used by the database optimizer, making the search faster.

Best regards,

Kim