Query vs. Indexing?

[Jim Chapman]

Hello Steve,

Tell us how to duplicate your issues. Also, the largest table in your sample has 35 records. Do you have any way of populating your example with a significant number of records you spoke about so we can do meaningful tests?

Thanks,
Jim

[spain246]

Try this Jim

Hey Jim,
Here is an updated one. It has a little over 200,000 records. I have set the startup form to load. Here's how to recreate the problem:

1. Open Database
2. Enter start date of 02/02/2002
3. Enter end date of 02/02/2003 (or any date after)
4. The Radio button should be on all ANIs
5. Click the "CDR Acct Summary Rpt" button.

First it loads over 180,000 records and takes forever (10+ minutes) to display a simple report. All of my xbasic script is on the form buttons.

Like I said before, it only takes Access a few seconds to run this same report vs. the 10+ minutes on Alpha 5.....and Access is awful!

Let me know what you think.
Steve Hall

[spain246]

new file

Here's the file. Try this out.

[spain246]

Oops!

My file didn't dave the absolute file paths for my data. Try this one.

[CALocklin]

While Finian's working on his tome, here's a quick summary of what I've done in the past. It sounds like it could be time consuming to build and to run but, while it takes a little time to build (but not really all that much) it usually runs REALLY fast. I've used this technique a number of times.

- Make sure there is an index on the appropriate field in your "parent" table. (The "parent" is in quotes because we are not working in a set here. We are only working with what would be the parent table of the set.)
- Create a temp table that contains all the necessary fields from the "parent" table. (Or just include all fields if you prefer. The time difference is negligible.) Either create the temp table each time or zap the data in an existing temp table. (Creating a new temp table will overwrite any existing table with the same name and creating a new table is just as fast as zapping existing data - maybe faster.)
- Using the simple index, either (a) run a query if LQO will work with the simple index on just the one table or (b) use a .fetch_find() to locate the first appropriate record.
- use a WHILE loop or an Append operation to copy the data from the parent table to the temporary table.
- Have a set just like the original set that uses the temp table as the parent rather than the "real" parent table. Attach the report to this set. By running the report from this "temp table" set, it will only have those few records that were copied in the WHILE loop and the report will run very fast.

Note: Even if your simple index results in 100 extra records in the "parent" table instead of the 3 you referenced earlier, running a second query on 100 records will be fast even without LQO - certainly faster than querying a million records.

The above is just one simple example. There are many possible variations on this theme. Once, back in A5v1, I had a report that was taking over 15 minutes to run and LQO wasn't even in anyone's imagination yet. My solution involved at least 3 temp tables but the result was a report that ran in something under a minute even in those days.

In some cases I have used a true "temp" table that is only used as an intermediate table which is manipulated further before appending the data to the "final temp table". (I don't recall why I did this and it may even have been done in the v1 app and not needed in v5-7 but I wanted to put the concept out there for your consideration.)

[Jim Chapman]

Hello Steve,

I took a quick look at your set up. It took 3 minutes on my computer to run the report via your buttons and scripts. IMHO there are a couple of issues, but I think to get the speed you need, you'll need to reverse the set. You also have a ton of calculations going on. I didn't have the time to look through all of these, but if there are any lookup or totalling functions being called this can really slow things down as Alpha would have to do a lot of table querying for each one.

But, primarily you are querying the one-to-many child and Alpha has no way of limiting the parent records so it is stepping through each parent querying the child records, stepping to the next parent, querying the child records, etc. At least this is what I think is going on. What you need to do is reverse the set. I haven't run into a situation where I couldn't output the same type of reports with a reversed set, but you may have to redo some of your calculated fields.

I went into your database and reversed the set, copied the existing report over to the new set, then renamed the old report so there wouldn't be a name conflict. I didn't do anything to the report itself. Don't have a clue whether your calculations are still working properly. The report was set to order on the break field, ANI, so all I did was pass the filter on the 'offhookdate'. The report now runs in under 3 seconds.

I did add an index on the 'offhookdate' field and one for 'ani' as well.

(Ooops, my changes made the zip file larger than is allowed, so I deleted the other reports to slim it down)

Good luck,

Jim

[spain246]

Query Code Error

Jim, Cal, or anybody,
Ok, i've created my query and it creates a new table with my queried records in it. For some reason though I keep getting an error of that states "Error running copy operation, not an executable file." Any thoughts as to why this won't work? I'll attach my script. If I can get this and another couple of options to work, i'll pot what I did here for everybody.

Thanks
Steve Hall

[Stan Mathews]

Jim, Cal, or anybody,
Ok, i've created my query and it creates a new table with my queried records in it. For some reason though I keep getting an error of that states "Error running copy operation, not an executable file." Any thoughts as to why this won't work? I'll attach my script. If I can get this and another couple of options to work, i'll pot what I did here for everybody.

Thanks
Steve Hall

Put an end after

a_tbl.copy()

a_tbl.copy()
END

otherwise execution continues through the error handler.

[spain246]

Thanks Stan! The only other thing i'm having a problem with is after I put "END" into the code, it seems like the code is stopping and not continuing on to the print preview.


Any thoughts anybody?

Steve Hall

[Stan Mathews]

Jim, Cal, or anybody,
Ok, i've created my query and it creates a new table with my queried records in it. For some reason though I keep getting an error of that states "Error running copy operation, not an executable file." Any thoughts as to why this won't work? I'll attach my script. If I can get this and another couple of options to work, i'll pot what I did here for everybody.

Thanks
Steve Hall

My bad, I didn't scroll down far enough.

Try it this way.

It is also possible that there is some other error triggering the error handler but since the error routine remains in effect after the copy operation completes successfully it still reports that the copy was the problem.

You might try changing the error section to

ON ERROR GOTO 0 'reset error handler if copy is successful
GOTO CONTINUE2012200510442621
ERROR2012200510442621:
ON ERROR GOTO 0 'reset error handler if copy is unsuccessful
ui_msg_box("Error","Error running Copy Operation"+crlf()+error_text_get())
END
CONTINUE2012200510442621:
a_tbl.close()

[spain246]

Stan,
I'll try that. I'm gonna also try maybe some action scripting. I'll just set up two actions to run my report. (Although, I shouldn't hafta do that). I'll try it in the morning when I get back to the office.

Steve Hall

[CALocklin]

It's always better form to put your error routines at the end of the script and use a RESUME NEXT/0/<Label> or END command after the error routine. This (a) avoids the complexity of skipping over the error routine if no error occurs and (b) puts all error routines in one place.

[Jim Chapman]

Steve,

Did the example I post of reversing the set not work for you?

Jim

[spain246]

Jim,
It did speed it up (basically cut the time in half) but it still wasn't as fast as I needed it. I have found a way though that's working very well. As soon as I finish fixing a cuple of things, I'll post what I came up with.

Steve Hall

[Finian Lennon]

Here is the "tome" as Cal so aptly described it. Talk about closing the barn door after the horse has bolted! Oh well, I found some time over the holiday to write this up. Hopefully it will prove useful to some.

In fact, the method described here differs in only minor details from that described by Cal in his message. The main difference is that it explicitly addresses a multi-user printing situation.

While the circumstances that originally prompted me to do this occur only rarely, the techniques used have become essential to other aspects of printing in our application.

In fact, I would go so far as to state that some variation of this theme (moving transaction data into temporary tables) is required for printing in any busy multi-user Alpha system. This is especially true for system intensive processes like generation of financial statements.

I don't have time to go back to the original document now but:

(1) To state the obvious .. your application needs to have a built-in method to run the equivalent of a database compact. Although the number of active records in the temporary tables does not grow significantly, the number of deleted records can cause the temporary tables to become HUGE.

(2) While experienced Alpha users will know or infer some of the benefits of temporary tables, I think the following comments are in order. Indexing plays a huge role in speeding up any application. It is also true that too many (especially complex or filtered) indexes are antithetical to the goal of fast, accurate data entry if they are applied to your key transaction tables. By doing your key data processing in temporary tables, you gain the ability to apply very complex indexes to help speed processing without the concerns you would have were those indexes in place on a heavily used table.