Branching a TQuery after executing SQL

A little background info here.  I'm accessing a FoxPro table / database from a CD-ROM (ick!).  There are lots of tables, but one huge *mutha* of a table that takes up most of the CD.  Now, imagine doing a SQL query on that...  sorted, even...  now you have entered my little room in hell.

Okay, I'm going thru various testing ideas here to help speed this thing up.  There comes a point after the first SQL execution where I need to "fine tune" the data, in a branching kind of way.  I need a subset of the original query, while keeping the original query.  

Can this be done without executing a new query?

I know about the Filter property on a query, but that doesn't allow me to branch off...

I wish there was something like a query.assign where it could just transfer over the guts and then I could apply the filters.

Any hints?

thanks,
-gd
LVL 2
greendotAsked:
Who is Participating?
 
vladikaConnect With a Mentor Commented:
I wrote small component CloneQuery which can clone existing dataset.
Set MasterQuery property, then open MasterQuery
After MasterQuery opened you can open CloneQuery.
Then you can apply your filter to CloneQuery.
You can improve my component.

unit CloneQuery;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  BDE, Db, DBTables;

type
  TCloneQuery = class(TQuery)
  private
    FMasterQuery: TQuery;
    procedure SetMasterQuery(Value: TQuery);
  protected
    function CreateHandle: HDBICur; override;
    procedure Notification(AComponent: TComponent;
      Operation: TOperation); override;
  published
    property MasterQuery: TQuery read FMasterQuery write SetMasterQuery;
  end;

procedure Register;

implementation

procedure TCloneQuery.Notification(AComponent: TComponent;
  Operation: TOperation);
begin
  inherited Notification(AComponent, Operation);
  if (Operation = opRemove) and (FMasterQuery <> nil) and
    (AComponent = FMasterQuery) then FMasterQuery := nil;
end;

procedure TCloneQuery.SetMasterQuery(Value: TQuery);
begin
  if Value = Self then Exit;
  FMasterQuery := Value;
  if Value <> nil then Value.FreeNotification(Self);
end;

function TCloneQuery.CreateHandle: HDBICur;
begin
  if (FMasterQuery <> nil) and (FMasterQuery.Active) then
    Check(DbiCloneCursor(FMasterQuery.Handle, False, False, Result))
  else Result := nil;
end;

procedure Register;
begin
  RegisterComponents('ExpertExchange', [TCloneQuery]);
end;

end.

0
 
ZifNabCommented:
... what about using temp queries in SQL code?
0
 
greendotAuthor Commented:
Oh mm...
(I'm somewhat of an upper-novice with SQL)
Could you explain a little more about temp queries?

thx,
j

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
andrey070798Commented:
Hi, gd!

I have question.
  You HAVE this Database on the CD and your problem to speed it up for you
     OR
  you CREATING Database (you'll store it in a CD) and all you want to speed up work in the future?
If you're CREATING it I can help a little bit.
But if you already have data structure and it's on the CD I think there is difficult to change it.
May be I'm wrong.
Andrey.
0
 
greendotAuthor Commented:
The tables are already out on the CD.  We can't change them.
(That's the next version.. yay)

I'm just trying to find ways to speed things up.  There is one point where I have to print a report and do a query on the huge (100-600 meg) file.  I can measure this in hour/query. ick.

So I was wanting to branch so I could apply the filters on those seperately, without the entire query being run again.

My thinking is I do the query to gather in the fields and define the sort order, then split it off and filter off the little "subqueries".

-gd
0
 
vladikaCommented:
greendot, where are you?
What do you think about my solution?

0
 
greendotAuthor Commented:
Oops,
sorry vladika, but I haven't had a chance to get at it yet.
I made the component, and put it in the component tray..
but haven't tested.  :\

I should be able to get at it either this weekend or on Monday.
But off hand, it looks like this is what I want.  :)

-gd
0
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.

All Courses

From novice to tech pro — start learning today.