Solved

A huge table in MS SQL

Posted on 2000-05-08
20
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Magic Software info 18 144
Delphi 2 77
Delphi: sending SMS on android platform 1 68
DBCtrlGrid, Delphi, Scroll 8 59
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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