Solved

A huge table in MS SQL

Posted on 2000-05-08
20
247 Views
Last Modified: 2010-04-04
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?  
0
Comment
Question by:skyrider_tieus
  • 9
  • 4
  • 2
  • +3
20 Comments
 
LVL 15

Expert Comment

by:simonet
ID: 2790622
>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
 

Author Comment

by:skyrider_tieus
ID: 2790892
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
 
LVL 7

Expert Comment

by:Motaz
ID: 2791117
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
 

Author Comment

by:skyrider_tieus
ID: 2791376
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
 

Expert Comment

by:Chelly_the_dog
ID: 2792295
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
 
LVL 15

Expert Comment

by:simonet
ID: 2792374
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
 

Author Comment

by:skyrider_tieus
ID: 2793415
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
 
LVL 1

Expert Comment

by:bozo7
ID: 2793645
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
 
LVL 15

Expert Comment

by:simonet
ID: 2794630
>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
 

Author Comment

by:skyrider_tieus
ID: 2794684
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Expert Comment

by:bozo7
ID: 2796232
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
 

Author Comment

by:skyrider_tieus
ID: 2798204
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
 
LVL 15

Accepted Solution

by:
simonet earned 60 total points
ID: 2798513
>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
 
LVL 7

Expert Comment

by:Motaz
ID: 2799480
How you can use TUpdateSQL? please post an example of adding record to a table.

Motaz
0
 

Author Comment

by:skyrider_tieus
ID: 2802782
Adjusted points from 25 to 75
0
 

Author Comment

by:skyrider_tieus
ID: 2802783
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
 
LVL 3

Expert Comment

by:darinw
ID: 2877838
Community Support has reduced points from 75 to 60
0
 
LVL 3

Expert Comment

by:darinw
ID: 2877839
Reducing points to 60 for a split per your request.

darinw
Customer Service

0
 

Author Comment

by:skyrider_tieus
ID: 2881900
Comment accepted as answer
0
 

Author Comment

by:skyrider_tieus
ID: 2881901
Alex has a good insight in the BDE mechanism, which is essential in coding efficient C/S program.  
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now