Solved

Branching a TQuery after executing SQL

Posted on 1998-07-28
7
210 Views
Last Modified: 2010-04-04
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
0
Comment
Question by:greendot
7 Comments
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358847
... what about using temp queries in SQL code?
0
 
LVL 2

Author Comment

by:greendot
ID: 1358848
Oh mm...
(I'm somewhat of an upper-novice with SQL)
Could you explain a little more about temp queries?

thx,
j

0
 
LVL 1

Expert Comment

by:andrey070798
ID: 1358849
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:greendot
ID: 1358850
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
 
LVL 3

Accepted Solution

by:
vladika earned 100 total points
ID: 1358851
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
 
LVL 3

Expert Comment

by:vladika
ID: 1358852
greendot, where are you?
What do you think about my solution?

0
 
LVL 2

Author Comment

by:greendot
ID: 1358853
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now