Solved

Branching a TQuery after executing SQL

Posted on 1998-07-28
7
208 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
Comment Utility
... what about using temp queries in SQL code?
0
 
LVL 2

Author Comment

by:greendot
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Author Comment

by:greendot
Comment Utility
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
Comment Utility
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
Comment Utility
greendot, where are you?
What do you think about my solution?

0
 
LVL 2

Author Comment

by:greendot
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

12 Experts available now in Live!

Get 1:1 Help Now