Hello,

I'm running into an issue where some of our procedures are being printed before the procedure finishes. Which results in a report that is maybe 10 lines long, being printed on 4 separate sheets of paper. I have tried alot with the printers themselves, from older drivers to spooling settings.

I'm not to great at the programming side of things, so i'm not sure if it is something with the way the procedure is written.

Let me know if you need additional info.

Any suggestions or advice would be greatly appreciated.

Hi Joe.

DFD was created for dot matrix printers etc where the printer was connected directly to the computer and just printed each character as it was sent and then a CHR(13) - Carriage return and CHR(10) LineFeed at the end of the line emulating a type writer to a tee.

in L4D this get re-routed through Windows but as there is no EOF (end of file/job) sent at the end of a print there is no way for WIndows to know when the print job is finished so the common way to do this is to have a timeout which means that if there hasn't been received any output for a certain amount of time the print job is "judged" to be finished and the printer driver print it.

There is three ways to deal with this in L4D.

1. Use RAW mode on the printer, you can use this even without dedicating a printer driver to a port see:http://www.dataease.com/DG3_BlogList/?ParentID=0000000476&field1=0000000476

2. Increase the timeout in L4D. This is strictly not recommended as the timeout is global so all print jobs will be delayed with the increased timeout.

3. Look at the DQL in question and re-program it to become more efficient. This is the recommended approach as no DQL need to be so slow that the print job times out on a modern computer. Most DQL's was made quickly when there was little data in the application when use of indexes and "clever" selection statements doesn't really matter. When the amount of records go up this become more and more critical

The main problems is normally no index on the first selection statement. 

DataEase ONLY use index on the first criteria so make sure they are built up wisely.

For Test with
YesField=Yes and CustomerNr=097832 ;

Is wrong as it will waste the index on a field that give little or no spread.

For Test with 
CustomerNr=097832 and YesField=Yes will give a much better spread and hence a much faster DQL.

We had a customer that had a big DQL that took 6 hours that they ran every day!

It was a long with statement but the first one was
For BigData with CreditOrder=Yes and NewOrder=Yes and OrderDate between current date-14 to current date ;
1. Reduced the hit to 2.9 million with index 
2. Had to rescan 2.9 and reduced it to 50.000 without index (sequential)
3. Had to rescan 50.000 and reduced it to 1000. without index (sequential)

The table had 3 million orders in it where 2.9 million was CreditOrders

All three fields were indexed.

Of the 3 million orders about 1000 where newer than CurrentDate-14..

So we simply flipped the With statement.

For BigData with OrderDate between current date-14 to current date and CreditOrder=yes and NewOrder=yes  ;

1 Reduced the hit to 1100 with index.

2. Had to rescan 1100 and reduced it to 1006 
3. Had to rescan 1006 and remove 6 cancelled orders and returned 1000.

This optimzation resulted in the DQL in L4D went down from 6 hours to 23 seconds.

Thank you for the response, I kind of get what you are trying to say, but like i said im a little new at this.

This is what one of our procedures looks like now...

for MEMBERS WITH

RX_CANCEL > 12/31/2019

AND ANY COMPANY MASTER RENEW_CODE = "0"

AND ANY COMPANY MASTER START DATE < 01/01/2020

and any company master invoice = BOTH

AND ANY RX RATE TABLE CS_CHECK NOT = BLANK;

list records

MED_DIVISI IN GROUPS;

DIVISION NAME IN ORDER;

ANY COMPANY MASTER INVOICE.

Looks simple enough. 

This should give a very good spread so my question is then, is RX_CANCEL indexed? and how many records is in Members?

It is indexed, and there are 173,828 records in members

Then this is the culprit:

AND ANY COMPANY MASTER RENEW_CODE = "0"

AND ANY COMPANY MASTER START DATE < 01/01/2020

and any company master invoice = BOTH

AND ANY RX RATE TABLE CS_CHECK NOT = BLANK;

With statement is just a boolean statement that need to be true. In DataEase you can create any with statement even one that feed back into itself.

As long as it is true at the time of execution it will work.

Here you make your list dependent on a status in two different tables so then again the speed is dictated on how these relationships are executed and how efficient they are.

If there is an unnamed relationship between master and company that is what ANY COmpany means here (i.e. the relationship and not the table).

So what is the relationship between Members and COmpany etc?