Order of where conditions

[sventevit]

Does the order of where conditions influence the execution of the query? Does it depend on indexes on the table (like in SQL Server)?

Can you point me to some documentation?

Thanks

 

[tamhas]

There is almost certainly some version to version variation here ... although no specifics are documented to my knowledge. Neither is there any documentation on current state. I will say two things:

1. Yes, there are times when it sure seemed to me that order mattered, but not in an obvious, simple way because this code is all being processed by an optimizer so the exact significance can easily depend on how recently you have done update statistics and the specifics of the query. I.e., most of the time it won't matter because the optimizer will do the right thing, but on some versions and some queries it could matter.

2. My experience, pretty much across the board, is that one is ahead of the game doing most of the WHERE work in the FROM section. I.e., specify the JOINs in the FROM, including any restrictions, and do so in a logical way based on your knowledge of the data. I.e., if you know that there are only a few records in one table which are easily selected by index, start there so that the minimum dataset is required. A non-trivial side benefit of specifying JOINs instead of WHEREs is that the data relationships are much more clearly documented for the next sucker that has to work on the code.

 

[mosfin]

since no one knows how Progress algoritm picks the correct index according to 'where' conditions, you can always 'help' by explicitly selecting the preferred index with 'USE-INDEX' clause

 

[Casper]

Since this is sql92 I dont think use-index will work

You can give an index hint to the optimizer by using with index ("index-name"). If the index is in the list of indexes which can be used it will be used.
SQL is using, unlike Progress, a cost based optimizer. to see what effects changes on the query have on index usage, check the query plan:

after executing a query run in the same session:

select "_Pnumber", substring("_Description",1,80) 
from pub."_Sql_Qplan" 
where "_Pnumber" = (select max( "_Pnumber" ) 
from pub."_Sql_Qplan" 
where "_Ptype" > 0 );

This will give you the query plan.

Regards,

Casper.

 

[tamhas]

Evaluating the query plan is always a sound diagnostic move if you are having a performance problem or odd behavior. Specifying indexes is no more good routine practice in SQL than it is in ABL.

 

[atopher1]

I've often struggled with this issue...when the SQL is executed from a Progress editor session it is analysed correctly and it uses the correct indexes. But when the same query is executed from Excel for example, it picks a different index and ends up trawling through thousands of records. Can anyone shed any light...I usually just blame MS query without fully understanding what the SQL was doing.

For example

  [COLOR=#943634][FONT=&quot]SELECT [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.knote, [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     orderdoc.kdoctype, [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     orderdoc.contact[/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]FROM [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     PUB.order [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]INNER JOIN [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     PUB.orderdoc [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]ON [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kco = orderdoc.kco AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kdoctype = orderdoc.korddoctype AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.knote = orderdoc.kordnote[/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]WHERE [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kco = 1 AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kdaccount = 'S1234' AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.orderstatus = 1 AND[/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     orderdoc.kdoctype = ‘GO’[/FONT][/COLOR]

trawls through thousands of records......B[FONT=&quot]ut change the orderdoc.kdoctype (A key field) to orderdoc.contact (A non key field) and it runs really quickly …
[/FONT]

  [COLOR=#943634][FONT=&quot]SELECT [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.knote, [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     orderdoc.kdoctype, [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     orderdoc.contact[/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]FROM [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     PUB.order [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]INNER JOIN [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     PUB.orderdoc [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]ON [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kco = orderdoc.kco AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kdoctype = orderdoc.korddoctype AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.knote = orderdoc.kordnote[/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]WHERE [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kco = 1 AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.kdaccount = 'S1234' AND [/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     order.orderstatus = 1  AND[/FONT][/COLOR]
  [COLOR=#943634][FONT=&quot]     orderdoc.contact like 'Martin%'[/FONT][/COLOR]

 

[tamhas]

It is pointless to compare SQL in the Progress editor with that from Excel. The former is SQL-89 executed by the AVM and the later is SQL-92 executed by an entirely separate process. If the SQL-89 does what you expect and SQL-92 doesn't, it is a pure fluke ... in fact, more likely a bug in SQL-89.

Among other things, SQL-89 is going to use ABL index resolution at compile time and SQL-92 using run-time optimization. Run UPDATE STATISTICS recently? If not, you are asking SQL to work blind.

 

[atopher1]

It is pointless to compare SQL in the Progress editor with that from Excel. The former is SQL-89 executed by the AVM and the later is SQL-92 executed by an entirely separate process. If the SQL-89 does what you expect and SQL-92 doesn't, it is a pure fluke ... in fact, more likely a bug in SQL-89.

Among other things, SQL-89 is going to use ABL index resolution at compile time and SQL-92 using run-time optimization. Run UPDATE STATISTICS recently? If not, you are asking SQL to work blind.

** Goes off and googles UPDATE STATISTICS.

Never run it, so that's the next task then + RTFM.

 

[tamhas]

Might help!

 

[atopher1]

Trying to figure out where to run UPDATE STATISTICS from......can you point me in the right direction?

Can't get it to compile in the ABL or run it from MS Query......does it need to run from some other front-end?

Thanks

 

[atopher1]

Got it working through MS Query...just about, got to get the table names correct.....nearly there. Getting the following error:
Table/View/Synonym not found (7519)

 

[tamhas]

Usually run through SQL Explorer (sqlexp) that comes with Progress.

 

[atopher1]

Well it took me a while to figure it out and put all the pieces together....but it was worth it in the end:

From Solaris root prompt:

root@server # sqlexp -db DBName -H HostName -S ServiceName
Connecting user "root" to URL "jdbc:datadirect:openedge://HostName:-
1;databaseName=DBName;serviceName=ServiceName"... (8920)
SQLExplorer>update table statistics and index statistics
1> and all column statistics for PUB.mytable;
Update count = 0.
SQLExplorer>commit;
SQLExplorer>

A simple query that took hours/didn't finish now completes within 10 seconds. What a difference some statistics makes!

Thanks

 

[Casper]

Yeah welcome to the world of the cost based optimiser

 

[tamhas]

Nice when it is that easy, isn't it?