Board index » delphi » Single or multiple queries

Single or multiple queries

I wish to display in a DBGrid all of the records subject to certain
conditions, and at the botton of the grid, show the total calculated with
the sum of the field "amount". I am now doing it with two different queries.
Would it be possible to do it with a single one ?

I like to know since I have a basic knowledge of Delphi and I am still
working with dbase tables and related TQuery. I am starting to feel the need
to have multiple totals and I wonder what the application performance will
be if I need to add query to query.
Thanks for any answer.

 

Re:Single or multiple queries


Quote
Stark wrote:
> I wish to display in a DBGrid all of the records subject to certain
> conditions, and at the botton of the grid, show the total calculated with
> the sum of the field "amount". I am now doing it with two different queries.
> Would it be possible to do it with a single one ?

You need two.

Quote
> I like to know since I have a basic knowledge of Delphi and I am still
> working with dbase tables and related TQuery. I am starting to feel the need
> to have multiple totals and I wonder what the application performance will
> be if I need to add query to query.

Don't worry. Err, well, *do* let SQL performance be a
priority, but a few extra SUM() queries are not going
to damage performance. If you start to notice a slowdown,
then do your SUM queries in a background thread after the
main grid is updated (or upgrade your DBMS ;-)

--
jc

Re:Single or multiple queries


"Stark" <starkwed...@virgilio.it> skrev i melding
news:MlPva.83161$3M4.1949106@news1.tin.it...

Quote
> I wish to display in a DBGrid all of the records subject to certain
> conditions, and at the botton of the grid, show the total calculated with
> the sum of the field "amount". I am now doing it with two different
queries.
> Would it be possible to do it with a single one ?

> I like to know since I have a basic knowledge of Delphi and I am still
> working with dbase tables and related TQuery. I am starting to feel the
need
> to have multiple totals and I wonder what the application performance will
> be if I need to add query to query.
> Thanks for any answer.

This depends on more factors:
- If you have a fast db server, go for the dual query solution.
- If there are only few records, you may retrieve sum by looping through the
dataset after opening the query. There are DBGrid clones that do this.
- If the result sets are large and there is a possibility you won't ever see
more than the top N records, there will be a performance penalty with
forcing all records to be retrieved - go for dual queries then.
- There is a slight possibility you won't get the same sum values by using
dual queries (if some record was posted & committed inbetween the two
calls).

--
Regards,

Bj?rge S?ther
bjorge@haha_itte.no
-------------------------------------
I'll not spend any money on American Software products
until armed forces are out of Iraq.

Re:Single or multiple queries


"Bj?rge S?ther" <bjorge@hahaha_itte.no> skrev i melding
news:Js0wa.11847$b71.227778@news4.e.nsc.no...

Quote
> "Stark" <starkwed...@virgilio.it> skrev i melding
> news:MlPva.83161$3M4.1949106@news1.tin.it...
> > I wish to display in a DBGrid all of the records subject to certain
> > conditions, and at the botton of the grid, show the total calculated
with
> > the sum of the field "amount". I am now doing it with two different
> queries.
> > Would it be possible to do it with a single one ?

Well, I forgot one concideration:
- If query filter (WHERE FIELD1 = ...) requires heavy processing, and there
is no easy way to improve this with an index, it may be faster to loop
through the result set rather than issuing a separate sum query.

--
Regards,

Bj?rge S?ther
bjorge@haha_itte.no
-------------------------------------
I'll not spend any money on American Software products
until armed forces are out of Iraq.

Other Threads