Speed up query

You can much much faster performance by doing a join
and making sure that the joined fields are indexed.

Example:

create index table1_idx on table1(ID);
create index table2_idx on table2(ID);

SELECT t2.*
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.ID = t2.ID

Looking at it you may want to combine Q1 and Q2 into one query using a standard join.  
Since this is a standard join, you will satisfy the recordcount > 0 for each row retrieved.

You will still want to do this in batches since you said the tables are big and you may need sorting to ensure that the retrieved table2 records are grouped together.

Hi WOlfgang and Johan,
The two tables I am quering are different types e.g Q1 is an Oracle table, and Q2 is a MYSQL table, this is the actualy Delphi code I am using
 oracledataset1.SQL.text := 'Select person_id, transperth_authorisation, enrolment_status from UIC_STAFF_STUDENT_V';

  while not oracledataset1.eof do
          begin
          statuschange := false;
          // now compare to MYSQL
          if not adoconnection1.Connected then adoconnection1.Connected := true;
          ADOQuery1.SQL.Text := ('select smartriderpermission , fulltime from patron where patronid = ' + quotedstr(oracledataset1.FieldByName('person_id').AsString));
          ADOquery1.open;
          if ((oracledataset1.FieldByName('transperth_authorisation').AsString  = 'Y') and (adoquery1.FieldByName('smartriderpermission').AsString = '0')) or ((oracledataset1.FieldByName('transperth_authorisation').AsString  = 'N') and (adoquery1.FieldByName('smartriderpermission').AsString = '1')) then statuschange := true;

          if ((oracledataset1.FieldByName('enrolment_status').AsString  = 'FT') and (adoquery1.FieldByName('fulltime').AsString <> '1')) or ((oracledataset1.FieldByName('enrolment_status').AsString  <> 'FT') and (adoquery1.FieldByName('fulltime').AsString = '1')) then statuschange := true;

          if statuschange  then  statuschangecount := statuschangecount + 1;
         if statuschangecount > statuschangelimit then
            sanitycheck := false
         else
            sanitycheck := TRUE;

         label3.Caption := inttostr(strtoint(label3.Caption)+1);
         application.ProcessMessages;
         oracledataset1.Next;


the purpose of this is to see how many status changes there are , compare it to a max limit and then either perform the changes or not perform as a high number indicates an error somewhere else.
If its possible through a join would be great but I would need some direction and assistance

thanks

Dave

It might be better to come up with a procedure to automate copying the data from one database to the other then use a standard join query.

Hi AGain,
Im not actually doing a copy at this stage (that happens latter int he project), this initial part is comparing the status of users in Q1 and Q2 and simply counting the number of changes there would be if we continued and did update new information in Q1 into Q2. Its a saftey check as if there are a lot of status changes something has been stuffed up in another process.
Its just a very slow process reading the first value from Q1, looking it up in Q2 and comparing the status, then moving to the next record in Q1.

Any suggestions?

thanks

Dave

If I may insist, it shouldn't be difficult to copy the mysql table to Oracle using external tables (see http://www.orafaq.com/node/848).  You export the mysql table to csv, configure an oracle external table for it, create a new regular oracle table via "create table new_table as select * from name_of_external_table", add the appropriate indexes to the new table, then use a normal join query.  It's very possible you will take less than an hour to do the above - how long has your existing script been running?  

If not, Then only way to speed this up then is to use less queries.  I believe you're on the right track in your original post - you just have to find the optimum number of Q1 values you want to load first.  Then maybe you can use an IN (value1, value2, value3) clause rather than multiple OR clauses - don't think it will have any performance impact, the SQL will only look cleaner that way.

Hi Johan,
As nice as it would be we cant utilise Oracle for the Q2, in fact we only have read rights for the Oracle DB anyway and its an area they wouldnt let us into. The MYSQL table we have full rights and can do what we like which is why we update it form the  Oracle table.

SO back to the original plan I guess - would using locate, or some other form of lookup be any quicker than a full select statement? probably not!
Say I did my query
Select ID, status from Table where value1 = var1 ,  or value1 = var2, or value1 = var3
How would I match the returns? - or will this be pretty obvious

thanks

Dave

Pseudocode: 

temp_var := '';

open r for select id, status, value1 
from Table
where value1 in (var1, var2, var3, var4)
order by value1;

while r_has_rows_available loop
  Q1 := r.next_row;

  if Q1.value1 != temp_var /*new value1 - this replaces your recordcount > 0 check */

    temp_var := Q1.value1; /*this will enable to detect if next rows are for the same value1 */

    /*do whatever you need to do for new values of value1 here*/
  end;

  /*do whatever you need to do for all rows matched here */

end loop;

Select allOpen in new window

Hi Dave,

A couple of questions.  :)

How many rows are returned from the first query?
How long does the combined query take to execute?

The goal should be to reduce the number of queries that your program makes against the second database.  Even on a fully indexed set of tables, you should get about 100 to 1 improvement by joining the results of the first table to the second query instead of running a query of the second table for every row returned by the first query.

- If the first query returns a relatively few number of rows, you may be able to build them into the second query.
- Can you write all of the rows returned from the first query into a "temporary" table on the MYSQL database?

Kent

Hi Kent,
I have answered below

How many rows are returned from the first query - ABout 40k
How long does the combined query take to execute - each individual query is relativly quick, I have a counter that flashes past pretty quick its just that there are about 40k so the whole process is about 10 minutes

The goal should be to reduce the number of queries that your program makes against the second database.  Even on a fully indexed set of tables, you should get about 100 to 1 improvement by joining the results of the first table to the second query instead of running a query of the second table for every row returned by the first query. - sounds like what I am after!

- If the first query returns a relatively few number of rows, you may be able to build them into the second query - 40k
- Can you write all of the rows returned from the first query into a "temporary" table on the MYSQL database - would this help?


thanks for your input Kent - look forward to your suggestions

thanks

Dave

Hi Johan,
Ill give your code a go in about an hour - cheers
Dave

Thanks to all who contributed