A huge table in MS SQL

>In a two-tier client/server system

Ok, first point: in such a project, you should NEVER be using a TTable component, because that doesn't make it C/S, but a desktop app accessing data somewhere else. TTable brings all the workload of processing the info to the client machine. And yes, it does fetch all the records, unless you've instructed BDE to fetch at most "n" records.

There are no limits to the # of records, but if that number is very high, it's a design flaw. Good design practices on C/S should keep data flow as small as possible, so the application is smooth, and neither the clients or the network are overloaded with data that will not be used.

My suggestion: use TQuery or ADOExpress (if you're using Delphi 5) w/ TADOQuery and resort to stored procedures, transactions and views as much as possible. This is true C/S computing.

Yours,

Alex

Dear Alex:

Your comments are right.  However, Borland (or Inprise) upgrades their BDE engine in each Delphi version.  Is it possible in Delphi 5 that this problem can be avoided by the programming technique.  I don't want to scroll the whole table content.  I just want to use TTable component to append some new records.    

You can use StoredProcedure to append records such as:

CREATE PROCEDURE [ewsdTrunkGroupAdd]
      (@Time       [datetime],
       @Dest       [varchar](30),
         @Exchange [varchar] (30))

AS INSERT INTO [Traffic].[dbo].[ewsdTrunkGroup]
       ( [Time],
       [Dest],
       [Exchange],)
 
VALUES
      ( @Time,
       @Dest,
       @Exchange)

and you can call this procedure such as:

ADOStoredProc1.Parameters.ParamByName('@Time').Value:= Now;
ADOStoredProc1.Parameters.ParamByName('@Dest').Value:= 'Khartoum'...

ADOStoredProc1.ExecProc;

I want to know some thing, if I use TTable to append data, did the entire table downloaded from server then appended?

Motaz

Motaz

This is exactly what I want to know!

Does the entire table download from server while using TTable to append records?  We have to take a look at how BDE implements this feature.

As far is I know it does not !
The BDE fetches only the number of records it needs to display in a Control attached to the TTable.
This is what TQuery does, but I think TTable does not behave different.

Waiting for other comments !

Nevertheless you should always use TQuery.
If you only want to append some records, use a SQL like : 'Select * from myTable where 1=2'
This will get the Table-info from the Server without loading any data, but you can insert into this Dataset within a DBGrid, save with the post-Methode and so on.

Motaz,

If the purpose is to append records only, there's no need to create a stored proc just for that. He can use a TUpdateSQL component that does the insertions.

Only if the insertion mechanism has to have some different behaviour, then the stored procedure can be justified, but even for that there can be triggers.

Alex

Guys:

How about filter in TTable? BDE would formulate the actions of db component to SQL statement and execute it.  If TTable formulates the SQL with filter as the WHERE Clause and passes it to MS SQL, then there won't be any large volume data over the network.  But if it downloads the data and then does sorting, then it is a bad hack.

The only problem with using a TTable is that it makes updating fields progrmatically more difficult. I agree I don't use a TTAble unless i absolutely have to.
A TTable is definetly easier progrmatically for the user to change on field and then click Post. Unless someone has a really easy way to do this, I have not found one yet. It involves displaying the info for the user to edit and then getting what they entered as a change and then createing an Update statement to make the change. A TTable is easier.

I have heard elsewhere that the BDE generates a SQL Statement if a TTAble is filtered. Is this true? I have used SQL Monitor to watch this is the BDE deceiveing us?

Ross

>BDE would formulate the actions of db component to SQL

Think of how much overhead that is to the application. You're moving something that should be processed by the server to the client.

Tables have surely made everyone's life easier, but they compromise application performance. They are not the panacea a RAD tool needs, and should be used with caution. IMHO, TTables are forbidden on a true C/S project.

I instruct my programmers to use it as least as possible, and always prefer SQL-based solutions.

Alex

Moving data between Heteorogeneous databases sometimes has to be a client to server transaction.  TTable might be acceptable at the expense of a tolerable overhead.

I agree with Alex.
My only complaint is the effort involved with updating record changes with SQL. I have not found an easy way to do that yet, for me or the user.

Ross

Here I'm using the TTable component to move data from dBase to MSSQL. In TTable, a filter with a false condition would never pass anything back, and new records are appended.  I am not sure of the impact of this practice to the client app and the MSSQL.

How you can use TUpdateSQL? please post an example of adding record to a table.

Motaz

Adjusted points from 25 to 75

Alex

TQuery + TUpdateSQL :
Data had been appended to MSSQL, but my prog hanged up after transaction commit!  Is the following code correct?
TQuery.open;
TQuery.append;
...
TQuery.post;

TDatabase.CachedUpdates([TQuery]);

or I missed something?
By the way, TQuery.open would download the unnecessary data to client, is there a way to avoid it?

Community Support has reduced points from 75 to 60

Reducing points to 60 for a split per your request.

darinw
Customer Service

Comment accepted as answer

Alex has a good insight in the BDE mechanism, which is essential in coding efficient C/S program.