SQL syntax review

[Former Member]

Hi All,

Could you please check whether the attached select statement  syntax is appropriate.

I have notification ID in the where clause so should I add the  VIQMEL table in the FROM clause or is this fine - I am getting the values correctly.

[ThomasZloch]

You are getting the desired result, VIQMEL is already included (two lines below the commented out line, otherwise there would be a syntax error), so what is the issue?

Nice join, by the way

Thomas

[RaymondGiuseppi]

The join will even be "nicer" if you "explode" VIQMEL which is a database view with three tables QMIH, QMEL and ILOA, into its three components.

Regards,

Raymond

[Former Member]

Thank you Thomas & Raymond !!

[ThangaPrakash]

Hello John,

It is very good that you used INNER JOINS instead of FOR ALL ENTRIES.

Appreciate your work. Maintain the same.

INNER JOIN looks fine and make sure that you used the fields in the order as it is appearing in the table, I mean the same index as in the tables.

Regards,

Thanga

[ThomasZloch]

Thanga Prakash wrote:

... and make sure that you used the fields in the order as it is appearing in the table, I mean the same index as in the tables.

I assume you mean the ON- and WHERE-conditions. Please note that this is also a myth.

The order of the fields does not matter, presence or absence of fields does matter (a lot).

If you have an index with fields A, B and C in that order, you might as well write ... WHERE C = ... AND B = ... AND A = ...

Compare yourself with an example program if in doubt.

Thomas

[ThangaPrakash]

Hello Thomas,

Thanks for the explanation, will check it. Also can you please give me some points on how to increase the performance of INNER JOINS.

Regards,

Thanga

[ThomasZloch]

The most important thing:

Make sure all ON- and WHERE-conditions make effective use of the primary or a secondary index of the involved tables.

"Effective" means having EQ-conditions for as many fields of that index as possible, top down without gaps.

Thomas

[ThangaPrakash]

Hello Thomas,

Even I meant to say the same in my last line of the statement,   to use Index. Might be i have not used correct sentence to convey it.

Regards,

Thanga

[SuhaSaha]

make sure that you used the fields in the order as it is appearing in the table

What did you want to convey here? The same order as they appear in the index, is it?

[ThangaPrakash]

Hello Suhas,

I mean to say try to use the index available in those tables.

Regards,

Thanga

[ThangaPrakash]

Hello John,

Also check the performance of the query in ST05 (SQL performance race) and SE30 (Runtime analysis transaction).

Refer to the below link for the same.

Regards,

Thanga

[Former Member]

Hi John,

Looks fine for me.You can run a live analysis of the program via SAT(new version of SE30) which will give your run time analysis of code.You can validate the select/loop statements specifically with excellent performance tuning.

Please refer how to use SAT.

.

Regards,

Kannan