Faster Searching & Sorting 4

[EricDraven]

Hi all,

I have a large DBase containing a list of invoices which is linked to a customers table via a unique Custoomer Number. On my screen there is a DBGrid showing the customers info and a listview in report style to show the corresponding invoices which are displayed when the user selects a customer. Filters are too slow so I am populating the listview using an index as follows.

var
  Customer : Integer;

Customer := Table1.FieldByName('CUSTOMER').AsInteger;
Table2.IndexName := 'CustomerNo';
Table2.First;
Table2.FindKey([Customer]);
while (Table2.FieldByName('CUSTNO').AsInteger = Customer) and (not Table2.EOF) do
begin

  {Add items to Listview here}

  Form1.Table2.Next;    
end;

[\code]

The problem is that a lot of larger customers have quite a few invoices and my users would like to be able to change the criteria used in sorting them in the listview (by invoice date, type, price etc).

I have tried changing the listview to dbgrid with a query as its datasource but unfortunately the sql code took a few seconds to sort all the relevant data. Im hopelesly stumped as to how to quickly sort the data on screen so any ideas would be greatly appreciated! [machinegun]  [bugeyed]

Arte Et Labore [rockband]

 

[StevenK]

It sounds as if a TQuery would be a simple answer here.
You could run a direct query to only pick up the required customer's invoices (through a parameter)
Furthermore you could also set an appropriate 'ORDER BY' clause as part of the dynamic SQL statement built up.
I hope that this helps.
Steve

 

[svanels]

I agree with Steve, a Tquery is handier for retrieving large sets of data.
Check out Search capability in a Tquery using common vcl components
faq102-3597

It has some hints to modify SQL statements at runtime with parametrized queries.

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[EricDraven]

Unfortunately, as I mentioned in my first post, I have tried this method and it takes a good few seconds to retrieve the data, whereas, using the code above returns over 300 results in approx half a second.

Speed is my primary concern as about 95% of users will be using this application over a network and on not particularly amazing machines!

Isn't there a quick way to reorganize the data in a listview? So far my efforts have turned up nothing!

Arte Et Labore

 

[svanels]

Eric to which method are you refering? The method described in the FAQ retrieves the data instantaneous.

Maybe you could give some more details how you have used the Tquery.

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[StevenK]

In addition to using a TQuery component (I'm sure this is a better and faster option than the existing TTable component) - I would ensure that there are no chained components (TTables / TQuerys) that have this first dataset as a mastersource - as this would slow down proceedings.
As part of your SQL statement ensure only the required fields are included in your SELECT statement (much better to define only those fields you need than have a TTable component drag all field information from server to client).
Also ensure that there are no visual components associated with the TQuery / TTable being user - as when this is stepped through this would slow down the proceedings - if you do need to have this result set on show then make use of 'DisableControls' and then 'EnableControls' against the TQuery / TTable accordingly.
Backing up what svanels says - I can't see why a TQuery would be inappropriate in this instance.
Maybe you could enlighten us ?
Steve

 

[EricDraven]

I have setup the system to use the Query again and I must have done something incorrectly before because it is running quicker now than I remember, so many thanks for pointing me in that direction again the pair of you!

I still have two problems though of which I think only one can be remedied here.

1.) The query is to display the results in a DBGrid. Two of the fields are monetary figures (Price & Paid). I need to display a temporary field/column in the DBGrid showing the amount due (Price - Paid). Is this possible or will I have to add the field to the table?

2). Secondly (and I think this will be solvable), I need to display the totals for each of these fields underneath the grid, i.e. Total Price, Total Paid and Total Due. I would think this could be accomplished with a simple Sum statement but for the life of me, I can't figure out wherabouts to put it in the query. Is there an expression component similar to the one included with QuickReports (QRExpr) which would do this?

Arte Et Labore

 

[StevenK]

1). You can add a new field through means of the SQL statement something like :
SELECT Price, Paid, (Price - Paid) OutstandingAmount
FROM Table1
This should result in a result set with fields Price, Paid and 'OutstandingAmount' (reflecting the value you're after).
2). I'd run this as a separate TQuery SUM'ming the values as required - I can't think that it's achievable within the scope of one single query - split it into two to keep speed performance - a simple SUM statement should do the job.
Hope that we've helped a little ....
Steve

 

[svanels]

Eric is this a kind of master-detail query? You can add a calculated field to the dataset, that will perform the amount Due. Probably this will be in the detail query.
For the total amount, I would use a second detail query with a SUM statement.

The master query would show the customer data
Detail1 would be order data, with calculated field
Detail2 would be the sum of the orders

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[EricDraven]

Ok I'm almost there, I now have my totals and the extra column, I just have two teeny tiny problems left and with my limited SQL knowledge, I dont know if they are solvable.

1). Can I reverse the order in which my invoices are being displayed in the DBGrid? (Using Order By in SQL)

2). I have a field called DATE in my table which is a reserved field name in SQL. If I use the Select * command I can display this field, but I am selecting each individual field in my SQL code so that I can use the run time column as in StevenK's code. Is there any way I can display this field still keeping the extra column without changing the field name as I have several users already using the system?
(Ideally I would like to Order By on this field but it is not essential).

Many, many thanks

Eric

Arte Et Labore

 

[towerbase]

You can specify descending order in SQL using something like

SELECT field1, field2 FROM table1 ORDER BY field2 DESC

You can use reserved words as field names in TQuery SQL providing you surround the field name in quotes. For example

SELECT 'date' FROM table1

Andrew

 

[jwenting]

If the users are going to use it on slow machines over a network you WANT to perform as much as possible on the server.
This means efficient queries and stored procedures and as little clientside sorting and filtering as possible (and better bring a fast server...).

 

[EricDraven]

Im so very nearly there!

Towerbase The descending ordering works but surrounding the reserved word with quote marks doesn't appear to work, instead it drops the field name "DATE" into the DBGrid on each line. I searched this forum and the SQL forum but only found a reference to placing square brackets around the word i.e [DATE]. Needless to say, this doesn't work either.

Once this date field displays, I'll be an extremely happy

Arte Et Labore

 

[EricDraven]

Ah, I see said the blind man! After much searching of the google groups, much cursing and so, I have found the solution to the last problem.

The SQL code read like this :-

SELECT INVNO,DATE,PRICE,PAID,(PRICE - PAID) DUE FROM INVOICES
WHERE CUSTNO = //Given Customer No
ORDER BY DATE;

Which was returning the obvious error about the reserved word DATE.

To remedy this, the code should read like this :-

SELECT INVNO,INVOICES."DATE" ,PRICE,PAID,(PRICE - PAID) DUE FROM INVOICES
WHERE CUSTNO = //Given Customer No
ORDER BY INVNO."DATE";

Many thanks to everyone who helped me out on this one. Its baked my noodle for quite some time! Ye shall now be rewarded with stars!


Arte Et Labore

 

[lespaul]

Stars for you Eric (for finding the answer on your own and solving your problem AND for letting everybody know what you did)! I learned that little bit of information yesterday too! In our really un-well designed database, somebody named a field BOOKING#, it's not a reserved word, but SQL didn't like the # at the end of it!


Leslie