Why does a SQL query take so long to detect invalid col name

[Mark2457]

I'm using OpenEdge 10.2A with 64 bit ODBC driver and linked server in SQL Server 2008 R2

I run a SQL query that spans 5 tables (probabaly a few thousand rows in each and all inner joins). Query completes in 15 secs (need to improve, but it will do for now).

If I mis-spell ONE column name, it takes 2min 40secs before I get a message saying .

Anyone got any idea why?

Also, is there something I can use in Progress (total noob) to see the execution plan that was generated so I can tune the queries?

Thanks

Mark

 

[Rob Fitzpatrick]

You haven't indicated which service pack of 10.2A you have installed. That may be important in this case.

Help with getting help: download and install the Offline KB Application. In the Search box, type your query. For example, "SQL query plan". It will give you a list of KB articles you can open in the application or in your browser, with interesting titles like:

Hints to improve the performance of SQL queries?
What to look for in SQL-92 Query Plan for performance issue?
Inefficient optimization of inner JOINs
SQL-92 query takes a very long time to complete using OE10.2A02
...and a lot more.

The third article above shows the statement for enabling query plans:

SET PRO_SERVER LOG ON with (statement,query_plan);

As always with OE SQL performance questions, ensure you have run UPDATE STATISTICS to provide good metadata for the SQL-92 query optimizer.

Also, you would likely benefit from an upgrade. I know 10.2B included some OE SQL performance enhancements. Info from Progress Exchange Online 2010:

Session abstract:

Learn how a leaner Progress OpenEdge 10.2B SQL resolves complex SQL queries as much as an order of magnitude faster. See queries meet your high-performance expectations—without adding calories. Regimens for addressing potential performance barriers are also discussed, and complex query performance is illustrated in a real-world customer environment and backed by proven lab results. Don’t miss the SQL query processing weigh-in on our very own “biggest loser” scale!

Video:
http://download.psdn.com/media/exchange_online_2010/1020ChrisWolf.wmv

Hope this helps.

 

[Rob Fitzpatrick]

Other info that may be helpful (conference slide decks):

From PUG Challenge Americas 2012:
Removing Performance Bottlenecks in Your SQL Connections - Data Connectivity Best Practices - Steve Pittman

From Progress Exchange 2013:
What's New In Progress OpenEdge SQL - Steve Pittman, Chandra Sekhar

 

[Mark2457]

Turns out I'm on 10.B

10.2B
Service Pack : 06
Temp. Fix : 00
Build : 1613

 

[Mark2457]

So I'm trying to update statistics on the table gams1.pub.ACOptions

In Microsoft SQL I'm used to the syntax DBName.SchemaName.TableName this Progress article http://knowledgebase.progress.com/articles/Article/20992 says runt he following:

1. Connect via ODBC or JDBC to the database.
2. Run the statement: update statistics for owner.tablename and then execute the command.

I'm connected as sysprogress using RazorSQL (no SQL Explorer on my progress server for some reason and no idea where to get or how to install) and ODBC on Windows

No matter what I try, I get:

ERROR: [DataDirect][ODBC Progress OpenEdge Wire Protocol
driver][OPENEDGE]Table/View/Synonym not found (7519) Error Code:
-20005

Is the owner name the same as schema name in other SQL variants? For example is PUB the owner? If not, how do I find the owner?

This query runs fine (so I know the connection is good):

select top 1 * from GAMS1.pub.ACOptions

I tried:

update statistic for GAMS1.pub.ACOptions
update statistic for pub.ACOptions
update statistic for ACOptions

All failed

Can anyone tell me where I'm going wrong?

Thanks

Mark

 

[Rob Fitzpatrick]

ABL-created tables are owned by the PUB schema. Table names have to be qualified with the schema name. So your syntax can be, for example:

update table statistics and index statistics and all column statistics for PUB.ACOptions;

Note that "statistics" ends with an "s".

There are several different syntax options. For more, read the SQL Reference manual, OpenEdge SQL Statements, UPDATE STATISTICS.

I don't know how sqlexp could be missing. Are you trying to run it on the database server? What syntax are you using?

 

[Mark2457]

Note that "statistics" ends with an "s".

Don't I feel like an idiot now

I don't know how sqlexp could be missing. Are you trying to run it on the database server? What syntax are you using?

I'm on server. Was just looking in the OpenEdge programs group in start menu as there seems to be a lot of utils there.

What is the command syntax?

 

[Rob Fitzpatrick]

You won't see a Windows shortcut for sqlexp; it is a command-line utility. You will see a shortcut for "proenv". It is a command prompt with the appropriate environment variables set so you can run OpenEdge binaries and batch files.

Run proenv and then run sqlexp. For example:
sqlexp -db dbname -H host-name -S SQL-broker-port-no -user SQL-user-name -password SQL-user's-password

For more info:
SQL Development manual | JDBC Client | Connecting to an OpenEdge database with a JDBC driver | Connecting using SQL Explorer