Query vs. Indexing?

[spain246]

Hey guys,
There is probably a very simple solution to this, but every one of my "print preview" reports is taking forever to load. My reports are base on sets. I run my xbasic script and then Alpha runs through every single child record instead of filtering with my variables. For example, if I have 1,000,000 records and I only need 3 records on my report, it scrolls through all 1,000,000 records at the bottom of my screen (I guess it's filtering), and then shopw my final data. The problem i'm having is this can take 5-10 minutes where if I do the same thing in access, it takes 10 seconds.

I'll attach my xbasic script to see if anyone wants to take a stab at this. I would assume that Alpha should just filter out the un-needed data based on my key, and that should be pretty fast.

Steve Hall
Intellicall, Inc.

[Stan Mathews]

Lightning Query Optimization

Hey guys,
There is probably a very simple solution to this, but every one of my "print preview" reports is taking forever to load. My reports are base on sets. I run my xbasic script and then Alpha runs through every single child record instead of filtering with my variables. For example, if I have 1,000,000 records and I only need 3 records on my report, it scrolls through all 1,000,000 records at the bottom of my screen (I guess it's filtering), and then shopw my final data. The problem i'm having is this can take 5-10 minutes where if I do the same thing in access, it takes 10 seconds.

I'll attach my xbasic script to see if anyone wants to take a stab at this. I would assume that Alpha should just filter out the un-needed data based on my key, and that should be pretty fast.

Steve Hall
Intellicall, Inc.

You can use the between() function to speed things up as it is conducive to LQO.

"(Cdr->Offhookdate>= Var->begindate ) .and. (Cdr->Offhookdate<=Var->enddate )"

would become

"between(Cdr->Offhookdate,{"+ dtoc(Var->begindate)+"},{"+dtoc(Var->enddate)+"}"

seeing as the variables are date type.

If this helps I'd be glad to help construct the other filter statements.

Slow afternoon, here's the rest...

"between(Cdr->Offhookdate,{"+ dtoc(Var->begindate)+"},{"+dtoc(Var->enddate)+"} .and. (Phone->ani="+Var->ani+" )"
if var->ani is numeric
"between(Cdr->Offhookdate,{"+ dtoc(Var->begindate)+"},{"+dtoc(Var->enddate)+"} .and. (Phone->ani='"+Var->ani+"' )"
if var->ani is character

assuming the Var->serial, Var->account, and Var->service are character types.

"between(Cdr->Offhookdate,{"+ dtoc(Var->begindate)+"},{"+dtoc(Var->enddate)+"} .and. (Phone->serial='"+Var->serial+"')"

"(Phone->sysdesc2='"+Var->account+"' ) .and.between(Cdr->Offhookdate,{"+ dtoc(Var->begindate)+"},{"+dtoc(Var->enddate)+"}"

"(Phone->servzone='"+Var->service+"' ) .and.between(Cdr->Offhookdate,{"+ dtoc(Var->begindate)+"},{"+dtoc(Var->enddate)+"}"

[spain246]

Stan,
It's still scanning every record in my child table, then it's creating it's own table through the print preview option. The DTOC won't work for some reason as well.

The same thing happens when I create a query operation. Instead of just automatically filtering the data between the dates, it scans every record. Is there anyway it can just quickly autofilter with my variables based on the date range?

It seems to go pretty quick when I open the table or set individually and filter that way.

Steve Hall

[Tom Cone Jr]

Steve,

You may be unfamiliar with Lightning Query Optimization. There's a useful section on this in the Chapter of the User Guide dealing with Searching and Sorting Records.

If you are using search criteria targeting the child table, you might benefit from creating a new set, inverting the present arrangement and using 1:1 link.
The filter on the primary table in the set sholuld perform faster.

-- tom

[Finian Lennon]

About four years ago, I ran into this situation. No amount of script and/or report tweaking solved the problem. While it was only affecting a couple of clients among many, it was a major deal. My solution (using temporary tables) may seem like a lot of work, but in the context of the time spent chasing unsuccessful fixes, it was nothing. We now use this method for all of our major printing. I've described the method elsewhere and, if you're interested I can go into detail.

F.

[Tom Cone Jr]

Finian, yes, I'm interested in the details of the method you mention. Thanks.

-- tom

[spain246]

I'm definately interested as well.

Steve Hall

[Jim Chapman]

...............It's still scanning every record in my child table............

Hello Steve,

Have you built an index on your child table field: Offhookdate? This should be a simple index just listing the field as the index expression. This should allow LQO at work.

I have occassionally ran into situations where it seemed no matter what, Alpha insisted on running a new query even when an existing index existed where LQO could be used. Going way back to v3 I ran into a situation where three different settings would cause this, but for the life of me, I don't remember what the issues where. Seems like it had something to do will the current filter on the form, and something in the report settings. I did keep notes on this issue.......... if I can find them now :-(

Good luck,
Jim

[Jim Chapman]

Any other filter or order on this report? Also it doesn't look like you replace_parameters() will do anything as your variables in the original string are not encapsulated within brackes []

Jim

[spain246]

Jim,
I have removed all the fliters on this report. I originally had it filtering out some things.

Steve

[spain246]

Tom,
I just followed the parameters for the Lightning Query Optimization (LQO) and was just curious as to how fast this should work. I just want to check a few things.

1. The only place that a filter should be setup is actually in the query? (Operation)

2. The data type of my index must match the datatype of the field I am searching. What if it is a date? Does it have to be a character field?

3. What would make a good order expression?

I tried also rebuilding the set inversely, and had mixed results.

I put a filter on the report since I couldn't put it in my set. When I just checked a single day, it took 51 secs vs. 2:51. That was much better.

When I do multiple dates (ex:01/01/05 - 03/31/05) It goes back to scanning all of the records again and took 10 minutes.

Any thoughts? (I would love to see how Finnian is fixing this problem)

[Finian Lennon]

A comprehensive description of how to set this up will take me a little while to do. It will be about a week before I can get to it. Sorry.

Finian

[Jim Chapman]

Hello Steve,

Are you calling the print routine from a form? If so is the form based on the same set or table/s that are involved in the report? Do you have a filter on the form?

I'd try starting with a clean boot of Alpha. Make sure you don't have any filter or order on the report. Also make sure that the report properties does not have "Base Report on currently selected Records". "None" should be selected here. Run a script that calls the report with a simple filter, possibly just a given date for your field. Don't open any form or instance of the table before you try running the report.

Could you post your tables and set here so we could see what you are doing?

Jim

[spain246]

Database Copy

Jim,
All of my queries are built into buttons on my forms. The one thing that i've noticed is that when I open a set and run a query, it seems to check every single record. If you have several million records, it takes forever! I've tried inverting files and several other things. I can do the same thing in access and it takes 3 seconds as opposed to Alpha's 5 minutes.

I've attached a copy of some of my database if anyone wants to take a stab at this.

Steve Hall

[spain246]

Database

Here's the zip file.

Steve