Index hint - Increasing performance on large query

[rdekker]

I have a large query joining eleven tables together with a resulset of 15000 records. By default (all records) the query has 6 predicates in the where clause. It will cost the databaseserver about ten seconds to cough up the recordset. When I add another predicate in the where clause the number of records decrease. But the execution time of the query gets longer strongly depending on how many results are returned, for example if 125 rows are returned it takes 3 seconds longer. The more records are returned the worse it gets.... Sometimes over 2 minutes.... It looks like it uses another query plan, which in this case is obviously a bad plan.

How can I influence this behaviour? I tried:

FROM ***.pub.polis P WITH (INDEX (nr_tussenp)) LEFT JOIN .....

The index nr_tussenp is the best index of the main table (polis) in all cases (in my opinion) so it should always use this one.

I tried to view the query plan by executing:

con.Execute("SET PRO_SERVER LOG ON WITH (QUERY_PLAN)")

Then execute my query.

And then opening a recordset like:

Set rsRec = con.Execute("SELECT SUBSTR(description,1,78) FROM pub.""_SQL_QPLAN"" WHERE _ptype > 0")
Do Until rsRec.eof
Response.Write rsRec(0) & "<br>"
rsRec.MoveNext
Loop
rsRec.Close
Set rsRec = Nothing

But the query returns an error I've never seen before so that's not very useful either.

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Encountered internal error in SQL ENGINE at 166 in /vobs_sql/sql/src/public/rss\rss_env.hxx. Contact Progress Technical Support

Anyone who can help me tuning this query?

 

[Casper]

To find out the query plan use run the query with noexecute so no results are returned and right after that run:

SELECT SUBSTRING("_Description",1,70)
FROM pub."_Sql_Qplan"
WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" )
FROM pub."_Sql_Qplan"
WHERE "_Ptype" > 0 );

This will give you the query plan. Further questions: What Progress version are you running and do you run update statistics periodically?

Regards,

Casper

 

[rdekker]

I now have the query plan. I don't see anything special except for that one join was using a full table scan due to one missing join field to perform an index scan. I added the extra field and it's now using an index scan. Strangely enough when I perform the query on the total resultset it takes about twice as long as it took with the full table scan. The index slice of that particular table is about 0,1% and it has a total of 15000 rows. But when I add the extra search field (for the main table) the performance has increased (but only when a large resultset is returned). I don't get it...

Is there a way to give a hint to not use an index?

For example something like:
INNER JOIN ***.pub.tab_prod TP ON P.kd_verz = TP.kd_verz AND P.kd_prod = TP.kd_prod USE-NO-INDEX

where USE-NO-INDEX must be the hint not to use an index.

Is that possible on a join?

I found the index-hint WITH (INDEX (nameofindex)) and I used that one the main table. But on a joined table and use no index...I have no idea.


UPDATE STATISTICS: I tried doing that but the ODBC connection is only allowed in readonly isolation level. Error: Statement not allowed in readonly isolation level

I can't change that. Via the Webspeed webtools I can also run SQL queries and I don't know if I can try to run this command from the Scripting Lab. But I don't know what the command should look like. It doesn't understand something like "UPDATE STATISTICS"

 

[RealHeavyDude]

AFAIK you should be enable to set the isolation level on the connection level. IIRC with a JDBC connection you can do that when specifying the connection URL ...

HTH, RealHeavyDude.

 

[rdekker]

We don't own one of the databases. I connect to three databases. One of these databases belongs to a sofwarecompany and we only have read access to it on the sql connection. The 4GL connection is also a write-connection. (maybe our DBA doesn't allow us to write via SQL I don't know).

 

[RealHeavyDude]

To run UPDATE STATISTICS on Progress database which provides access to SQL clients is - IMHO - a DBA task which should be performed on a regular basis. You should check whether "your" DBA is able to do it. If he is granted the necessary access rights then you should delegate this task to him. If he is lacking the necessary privileges and the other software company is not willing to give them to him then they must do it. IMHO it does not make sense to run a Progress database which provides SQL access with nobody ever updating the statistics. AFAIK this is also true for other database vendors.

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Running UPDATE STATISTICS on SQL enabled databases is mandatory in the same sense that running backups or enabling after-imaging is. If your DBA cannot or will not do it then they are not doing their job. If your software vendor won't let you do it then they are not supporting you and you should withhold maintenance payments until they see the light.