Speed up query

I am running a QUery (Q1) then performing a lookup for each item in the returned value of the query in another table (Q2) e.g
While not Q1.eof do
Begin
  Q2.SQL.Text := Select * from table2 where ID = Q1,fieldbyname('ID).asstring
  If Q2.recordcount > 0 then (I actually compare a few values at this stage ) count := count + 1;
  Q1.next
End

As the tables are large tables its a slow process so I was wondering the best way to speed it up - maybe locate or lookup is quicker or could I design a query that reads several values at the same time by running the initial Query something like
STR1 := Q2.fieldbyname('ID').asstring
Q1.next
STR2 := Q2.fieldbyname('ID').asstring
Q1.next

then
 Q2.SQL.Text := Select * from table2 where (ID = STR!) or (ID = STR2)

or is there a better way to speed up this query

thanks in advance

Dave
monitorwaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wolfgang_93Commented:
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





0
johanntagleCommented:
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.

0
monitorwaAuthor Commented:
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



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johanntagleCommented:
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.
0
monitorwaAuthor Commented:
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
0
johanntagleCommented:
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.
0
monitorwaAuthor Commented:
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
0
johanntagleCommented:

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;

Open in new window

0
Kent OlsenData Warehouse Architect / DBACommented:

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
0
monitorwaAuthor Commented:
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
0
monitorwaAuthor Commented:
Hi Johan,
Ill give your code a go in about an hour - cheers
Dave
0
johanntagleCommented:
Hi Dave:

- Can you write all of the rows returned from the first query into a "temporary" table on the MYSQL database - would this help?

This would help a lot as this will enable you to do the standard join Wolfgang and I first pointed out.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
monitorwaAuthor Commented:
Thanks to all who contributed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.