Query taking too long to run

[arobbo]

Hi guys i'm running a query which isn't too complicated but is taking a lot longer than I would expect to run, its suming the value of sales orders (there are over 200,000 in the db) invoiced in the current month


I'm not sure if i were to order the where clause of my query differently it would increase the performance

Select Sum(totlineamt) MonthlyTotal , Sum(totcost) TotalCost, Sum(vendrebamt) Rebate From pub.oeeh
 where cono = 1 AND
 invoicedt >= TO_Date('" & month(date) & "/01/" & year(date) & "') AND invoicedt <= TO_Date('" & month(date) & "/" & day(date) & "/" & year(date) & "') 
 AND slsrepin ='" & rsemployee("employee_sales_initials") & "' 
 AND UCASE (transtype) <>'RM'

any advice on how I could increase the speed of this would be greatly appreciated , its taking 15 seconds to complete per employee , I want to produce a webpage with 15 employees monthly sales on and thats taking mins to complete.

many thanks

Andy

 

[TomBascom]

Is this running in the 4GL engine or the SQL-92 engine?

If it is the 4GL engine then you are making the mistake of trying to use SQL-89. Nothing but pain and misery is to be found down that road. Abandon it now. (Judging from the syntax shown this is not the 4GL engine.)

If it is the SQL-92 engine then the first question whenever performance is an issue is "Have you run UPDATE STATISTICS?".

The SQL-92 engine uses a cost based query optimizer. If you have never updated statistics then the optimizer has no data to work with and will basically do everything as table scans. It will be really, really slow.

 

[tamhas]

And, if it is the 4GL engine ... since this is not the SQL forum ... have you explored what indexes are available and what is being used. It is quite possible that you are reading every record for every employee. If there is no good index to use in reading just the orders for one employee, consider using a temp-table, reading all the orders once, and accumulating the total for each employee in the temp-table.

 

[arobbo]

Hi guys,

thanks for your quick reply it is the SQL-92 engine we are running.

I will try to run an update statistics for the order tables involved in my query on the weekend and see if this improves performance.

I was thinking about a temporary table option to run this nightly and then my users could see their total invoived sales the following day.

However I would like to have the functionality of being able to see what orders will invoice on the current day so that they can track their month to date progress in real time.

Kind regards

Andy

 

[Marian EDU]

That looks like a web-based reporting solution you have there... is that using PHP, ASP in conjunction with ODBC connection?

You might want to consider using a java reporting engine instead and use a JDBC driver that use the ABL engine of Progress database and even allows you to get the report data from business logic instead of having to use direct raw data-access that might lead to complex queries...

What you get by that...
- no sql engine needed, so... no update statistics
- use plain ABL query syntax (for each) or stick to SQL if you like that most
- have business logic 'stored procedures' that prepare the report data using plain ABL (it supports multiple result sets - pro dataset)

it works with any java reporting engine out there (it's standard JDBC): jasper report, birt, pentaho... all of those have web-based report viewer components and you get to design your reports in a graphical designer

check it out if that might sound like an option to you... http://www.ganimede.ro/cms/products/abl-jdbc/