On 9 May 2001, at 20:41, stewartbourke@... wrote:

> This topic has always been of interest to me.
>
> Would anybody be able to offer any advice on the table vs query
> debate when it comes to grids. Many of my customers like to see grids
> summarising information on the main screen of the application. >From
> this grid, they can drill down into the application. The obvious
> component to use here in a local app is a ttable. However on a c/s
> app, I can see why a query makes more sense from a resources point of
> view. However, how does one treat grids in this instance? If you
> have say 2500-3000 records on the database, and you open a query which
> is simply 'select * from tablename' are you going to end up drawing
> the entire table down over the network?

It depends on how you use the data, for example, say you have an
order entry system, and you are creating a file, that shows the
number of items ready to ship, the total volume and weight for each
city you are shipping to. You send the file to your shipper, so he
knows how big a truck to send, and whether there is enough to fill
trailers to several cities, he can simply nose load or seal and
linehaul. Yes this kind of thing can save money if you ship in
volume.

Now you do a table scan, and your application picks the records it
wants, and counts the required information in buckets then creates
the file from the buckets. It's going to be a relatively slow
process, on a slower workstation, over a slower network. It will
also be a pain in the donkey to debug, and what do you do if you
count on shipping to 50 cities, and there are 60 cities being shipped
to?

The alternative is to write a query that eliminates those records you
don't want, you sum the volume and weight numbers, and group it by
city. Now your application simply reads the query and writes the
data into the file directly.

The difference is where you process the data, and what code is used
to do it. The data is local to the server, so access should be fast,
the server is likely a faster machine with more memory and faster
drives, and the elimination and summarization code is highly tuned,
evolving over the life of the database software. You have eliminated
the network as a factor, and negated the speed of the workstation,
and saved yourself a lot of coding and debugging time, and they could
ship to 100 cities for all you care.

You may actually be able to do this, if your careful using a script,
and then run the script using cron, and sendmail to make this whole
process automatic, done by the server, direct to the shippers email.

I> On a more general note, are there any forums or explanations of what
> is best to use and how in terms of c/s programming with particular
> regard to Delphi?
>

Other then the basics, which I have described here, I don't know of
any special forums, other then if your using IBO, then since it is a
C/S tool, you can always as questions here.

You just need to think a little differently, it's no longer what data
is available, but what data do I really need, do you really need to
look at every field of every row? It's extremely rare that the
answer is yes. So don't use SELECT *, instead type in the fields
that you need, because even if you use every field now, you may add
fields in the future that you don't need. Always look for a WHERE,
because that will allow you to lower the row count. Use the JOIN
constructs rather then xx.yy_id = yy.yy_id type constructs. The
database usually can build a better plan that way.

C/S databases also tend to be a lot larger, because they can be, disk
space is cheap, and it really doesn't affect performance ilinearly if
you add more records, for example if you comparre performance with
50,000 records it's not 10 times slower if there are 500,000 records.

So if you have 3,000 orders a month, rather then purging those every
month, you can store 10 years worth of records, because your only
looking at one months worth of data at a time, and you can summarize
10 years worth of data, for things like sales analysis.

HTH

Paul







Paul Schmidt,
Tricat Technologies
Email: paul@...
Website: www.tricattechnologies.com