topic Re: SQL optimization in Application Development and Automation Discussions

SQL optimization

Former Member — Tue, 22 Aug 2006 04:45:07 GMT

Hi All,

How can you make the following statements faster? How do you optimize this?

Note: All fields. values and tables are for example purposes only. I am asking for a general answer.

DATA: it_test type hashed table of tabletype with unique key field1.

select distinct field1 from table1

into corresponding fields of table it_test

where field2 = 'value'

and field3 like 'value%'

and field4 = 'value'.

Thanks,

Kenny

Message was edited by: Kenny Martinez

Re: SQL optimization

Former Member — Tue, 22 Aug 2006 04:46:37 GMT

arrenge field in internal table as same way u fetch from dbtab...(remove corresponding field)

select distinct field1 from table1

into of table it_test

where field2 = 'value'

and field3 like 'value%'

and field4 = 'value'.

Re: SQL optimization

Former Member — Tue, 22 Aug 2006 04:53:48 GMT

Try these:

1. Create a secondary index with key fields field2, field3, field4

2. Ensure the fields in where condition are in the same order as they occur in the database table.

3. you may name write the query as

select distinct field1 from table1

into table it_field1

where field2 = 'value'

and field3 like 'value%'

and field4 = 'value'.

Note: the internal table now contains only one field. i.e., field1.

Re: SQL optimization

Former Member — Tue, 22 Aug 2006 04:56:18 GMT

Hi Guys,

Sorry I forgot to mention that the tabletype of the it_test is table1.

thanks.

Kenny

Re: SQL optimization

Former Member — Tue, 22 Aug 2006 05:24:42 GMT

good

go through this link

http://techsupport.osisoft.com/support_bulletin.aspx?bulletin=ddd7b5d2-c7a6-49dc-bd72-96fabbefab51

thanks

mrutyun

Re: SQL optimization

Former Member — Tue, 22 Aug 2006 05:29:37 GMT

hey use

deleted adjecent duplicates insted of select disticnt ..

hope this helps ..

thanking you

rohit gupta