Help with QueryPlan

[Mark2457]

Hi Guys

I'm a total noob and using 64 bit ODBC driver to query 10.2B SP06 (can't apply any other SP's as it's a 3rd party vendor product and that's the latest they certify) from SQL client (SSMS, RazorSQL, etc.).

Queries are really slow and I'm trying to figure out what the issue sis. Read loads of stuff about queryplan, but can't for life of me get it to work.

This is an example query:

SELECT
   Col1
FROM
   PUB.apinvtrl
WHERE
    "voucher-number" = '   30889'
 
  
SELECT SUBSTRING("_Description",1,70) FROM pub."_Sql_Qplan" WHERE "_Pnumber" = (SELECT MAX( "_Pnumber" ) FROM pub."_Sql_Qplan" WHERE "_Ptype" > 0 )

Nothing I run against _Sql_Qplan returns any rows. From what I read, it should return the query plan for the last statement using this connection.

Anyone got any idea what I'm doing wrong? Does something need to be enabled first?

Regards

mark

 

[tamhas]

It has been a ***long*** since I did any with query plan, but one obvious question is whether you have run UPDATE STATISTICS.

 

[Mark2457]

I tried, but get an error (caused by a memory leak according to Progress KB). Fix is in SP 8 and I can;t apply that (which really sucks)

 

[tamhas]

Tell your vendor that it is stupid to not let people upgrade and not letting them apply service packs is mind-numbingly stupid.

 

[TheMadDBA]

Especially with service packs.

If update statistics has not been run that could be the cause... and/or missing indexes.

Did you try this using sqlexp instead of a 3rd party tool? It isn't supposed to matter.

 

[Mark2457]

Tell your vendor that it is stupid to not let people upgrade and not letting them apply service packs is mind-numbingly stupid.

Believe me we have told them that! They said that SP is not certified with their product and won;t support us if we install it and something breaks. Problem is they moved to SQL and we're stuck with Progress for the time being because the company we bought had 400 customizations done to the product so a SQL migration (normally $7K ) is $50K and 6 months. We're probably to going to buy a different product or re-implement that co with new SQL version fro scratch. in meantime I need to get SQL working

 

[Mark2457]

Especially with service packs.

If update statistics has not been run that could be the cause... and/or missing indexes.

Did you try this using sqlexp instead of a 3rd party tool? It isn't supposed to matter.

I finally got it working on a machine with 32-bit drivers and WinSQL as the client. Puzzled why it doesn't work with a 64-bit drivers and Razor SQL client

I need to figure how to run stats on subsets of the DB so we don;t run out of the memory due to the leak

Thanks for suggestions

 

[TheMadDBA]

What is the exact error you are getting?

You can try and update statistics one table at a time. That should probably help out at least some.

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.<Table_Name>;
COMMIT WORK;

 

[Mark2457]

What is the exact error you are getting?

You can try and update statistics one table at a time. That should probably help out at least some.

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB.<Table_Name>;
COMMIT WORK;

Thanks

Do I need the "Commit"? Normally only use with explicit transactions is MS SQL

 

[TheMadDBA]

Pretty sure you need that for the Progress SQL side. Depends on your client type and whether it issues an auto commit silently or not after each statement.

As far as I know the automatic commit after a statement is only a MS SQL Server thing and not for Progress,Oracle or DB2.

I would try a few tables first and then just script the whole thing. There are some examples in the Progress KB about how to do that.

 

[Mark2457]

Pretty sure you need that for the Progress SQL side. Depends on your client type and whether it issues an auto commit silently or not after each statement.

As far as I know the automatic commit after a statement is only a MS SQL Server thing and not for Progress,Oracle or DB2.

I would try a few tables first and then just script the whole thing. There are some examples in the Progress KB about how to do that.

Thanks

 

[Rob Fitzpatrick]

A while back I tried to run a SQL script to update stats on my whole database (about 350 tables, 1000 indexes) and it threw an error. Can't remember now what it was. I changed the ABL program I use to create the script so that it added a "COMMIT WORK;" line after each table. That did the trick for me.

 

[TomBascom]

Yes, you need the COMMIT WORK;

Without it nothing will be remebered and updating the statistics will have no effect.

 

[Rob Fitzpatrick]

I meant that my prior script had one COMMIT WORK at the end, after all the UPDATE STATISTICS commands; it crashed with an error. I revised it to make each table/index/column update a separate transaction (COMMIT WORK after every UPDATE STATISTICS) and it worked.

 

[TomBascom]

I was replying to the general case

But, yes, "commit per table" has also been my practice. For the same reason -- with a big schema something goes BOOM!!! if you do not. But I've been doing one table at a time for so long that I forget what, exactly, goes "boom".

 

[Rob Fitzpatrick]

For some reason I think the "boom" was a lock table overflow, but I could be wrong.