A huge table in MS SQL

In a two-tier client/server system, when a TTable component opens a huge table, does it actually fetch all records or just part of them?  Is there a way to limit the records being passed over the network?  
skyrider_tieusAsked:
Who is Participating?
 
simonetConnect With a Mentor Commented:
>Here I'm using the TTable component to move data from dBase to MSSQL.

Use TTable for dBase and TQuery + TUpdateSQL on the SQL Server side. This will give you top performance.

Alex
0
 
simonetCommented:
>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
0
 
skyrider_tieusAuthor Commented:
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.    
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MotazCommented:
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
0
 
skyrider_tieusAuthor Commented:
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.
0
 
Chelly_the_dogCommented:
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.

0
 
simonetCommented:
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
0
 
skyrider_tieusAuthor Commented:
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.
0
 
bozo7Commented:
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
0
 
simonetCommented:
>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
0
 
skyrider_tieusAuthor Commented:
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.
0
 
bozo7Commented:
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
0
 
skyrider_tieusAuthor Commented:
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.
0
 
MotazCommented:
How you can use TUpdateSQL? please post an example of adding record to a table.

Motaz
0
 
skyrider_tieusAuthor Commented:
Adjusted points from 25 to 75
0
 
skyrider_tieusAuthor Commented:
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?


0
 
darinwCommented:
Community Support has reduced points from 75 to 60
0
 
darinwCommented:
Reducing points to 60 for a split per your request.

darinw
Customer Service

0
 
skyrider_tieusAuthor Commented:
Comment accepted as answer
0
 
skyrider_tieusAuthor Commented:
Alex has a good insight in the BDE mechanism, which is essential in coding efficient C/S program.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.