Slow queries

[Elroacho]

Hi,

I need to access three tables to produce a report in VB but its dog slow. Firstly I get a list of customers from one table then I need to look through two more tables to pick out certain data from any record with a matching Customer ID.

Basically my program runs a Select * for the three tables ordered by customer ID and placed into recordsets. Next I step through the customer table, take the ID and then scan each of the other tables to find the records related to that customer. It works fine just, like I said, really slow.

I’ve tried running queries to scan the two data tables from the database using the ID in a where clause but that make no difference. I thought if I got all the tables in RAM it would be quicker by using a loop then once I found the first record belonging to that customer I could take each subsequent record until the ID changed, as all the queries are ordered that way. It made a little difference but not as much as I’d hoped.

Does anyone know of any optimization or other method I could use to speed it up. I don't think its VBs fault as all it's really doing is sending the SQL and writing the output to a file.

Cheers in advance,
Kevin

 

[Elroacho]

I should also add I am using Interbase.

 

[alvechurchdata]

It sounds as though you're reading the three tables in three separate operations. Have you tried using SQL to join them?

Are the tables indexed on the foreign keys?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Elroacho]

The problem is there are a different number of data records for each customer. The output should look like this

&0,"REPORT","2","0"
&1,"ID","SURNAME","FORENAME","ADDRESS_1"
&2,"PROD ID","TEXT","VALUE1"
$1,"1","BECKLES","DAVID","75 FIR TREE APPROACH"
$2,"19990830","Sample product 1","90"
$2,"20030202","Sample product 2","95"
$2,"20031216","Sample product 3","101"
$1,"3","WRIGHT","DONNA","88 POTTERTON LANE"
$2,"19841205","Sample product 3","101"
$2,"20030914","Sample product 1","90"

 

[Elroacho]

Please, there must be someone out there how can help me.

 

[alvechurchdata]

I step through the customer table, take the ID and then scan each of the other tables to find the records related to that customer.

You're having to scan both the other recordsets once for every record in the customer recordset. You need to rearrange your code so that you scan each recordset once.

Put all three recordsets into customer order.
Get the first customer
Get the records for that customer from recordset A
Get the records for that customer from recordset B
Get the second customer
Get the records for that customer from recordset A
Get the records for that customer from recordset B
etc

I suspect that you're starting from the top of recordset A and B each time. Change your search algorithm so that you don't traverse the entries for the first customer in A when you look for the second customer. If you can make the search carry on from where it left off each time then you'll only have to go through tables A and B once.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[willir]

Elroacho

Well, is this going to be an ongoing process where you grab data from Interbase and create your reports in Access? It sound like it.

But if you are not, and it depends on how big the database, but I would seriously look at migrating the data over.

It looks like you have multiple types of records -- reminds me of the old coding days....
&n - titles / headers and subheaders
$n - data header and detail

...dog slow

I can understand why...
- ODBC (?) connection to grab the data
- process line by line, record by record

You will most likely not be using an index. I am not sure how Interbase access it's data, but I suspect the ODBC driver does not how to use the index.

So, rather than painfully loop and check your data, I would bring the data over with one read, and then process the data within Access where you have more control over indexing and such.

I suspect some of the data is fairly static, so after the first run, you may have less data to bring over on subsequent times.

For example, you bring over the customer data. After the first time, you only need to bring over customer data where the info has changed, and new customers.

By moving the processing to within the native format, either Access or Foxpro or whatever, you should see a big boost in speed. By performing a serial read one-time-only for the report, you should avoid having to access the data over what looks like to be a very ineffecient port.

Richard

 

[paulrw1]

Sounds like a very good place for a join on each table. And (not knowing Interbase) a good place to create a view on your 3 tables. Then process your data out of your view.

 

[Elroacho]

Cheers for all your help.

I managed to get the run time down to 20 seconds for over 8 thousand customers by having all the tables ordered by customer id then once I find the first record for that customer, I step down and take each consecutive record till the id changes. I then leave my record pointer there and grab the next ID to scan the data tables with.

Since fixing this I have been on a C# course and discovered a much simpler way. Just need to try and implement it. Using datasets and setting up the relationships between the three tables I can use a command to get all the child records for a given primary key (i.e. customer id).

YNWA,
Kevin.