topic Re: SQL syntax review in Application Development and Automation Discussions

SQL syntax review

Former Member — Tue, 04 Mar 2014 14:56:45 GMT

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.

Re: SQL syntax review

ThomasZloch — Tue, 04 Mar 2014 15:28:13 GMT

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

Re: SQL syntax review

RaymondGiuseppi — Tue, 04 Mar 2014 15:38:35 GMT

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

Re: SQL syntax review

Former Member — Tue, 04 Mar 2014 16:35:13 GMT

Thank you Thomas & Raymond !!

Re: SQL syntax review

ThangaPrakash — Tue, 04 Mar 2014 19:07:22 GMT

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

Re: SQL syntax review

ThangaPrakash — Tue, 04 Mar 2014 19:16:11 GMT

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

Re: SQL syntax review

ThomasZloch — Tue, 04 Mar 2014 19:36:37 GMT


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

Re: SQL syntax review

ThangaPrakash — Tue, 04 Mar 2014 19:46:15 GMT

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

Re: SQL syntax review

ThomasZloch — Tue, 04 Mar 2014 19:53:35 GMT

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

Re: SQL syntax review

Former Member — Tue, 04 Mar 2014 19:54:22 GMT

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

Re: SQL syntax review

ThangaPrakash — Tue, 04 Mar 2014 19:58:16 GMT

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

Re: SQL syntax review

SuhaSaha — Tue, 04 Mar 2014 20:38:14 GMT


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?

Re: SQL syntax review

ThangaPrakash — Tue, 04 Mar 2014 20:50:57 GMT

Hello Suhas,

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

Regards,

Thanga