Duplicating ClientDataSets on a slow connection

I am using SQLConnection, SQLTable, DataSetProvider, ClientDataSet, DataSource method of displaying contents of a MySQL database on forms.  

In some cases the same table needs to be viewed simultaneously (but displaying different Rows) using different DataSource's.  The only way this seems to work for me is to use the same SQLConnection, but duplicate the SQLTable, DataSetProvider, ClientDataSet, DataSource components for each of the "Views".

The MySQL database is at a remote location - when activating these components it can take ten minutes to login due to the size of the tables that need to be made available for viewing.  Once loaded speed of scrolling and searching is perfectly fine.

Is there anything I can do to speed up the loading process?
LVL 31
Who is Participating?
moorhouselondonConnect With a Mentor Author Commented:
At the moment I have

SQLConn1 <- SQLTable1 <- SQLProv1 <- CDS1 <- DS1
SQLConn1 <- SQLTable2 <- SQLProv2 <- CDS2 <- DS2
(where SQLTable1 and SQLTable2 point to the same SQLConn1)

Am I right in thinking that using Jensen's technique SQLTable2, SQLProv2 are redundant.  CDS2 is plonked onto a datamodule, then defined without any parameters, then programmatically I would say

CDS2.CloneCursor(CDS1, True);

to duplicate the definition of CDS1?

So my Datamodule will contain the following components:-

SQLConn1 <- SQLTable1 <- SQLProv1 <- CDS1 <- DS1
CDS2 <- DS2

Is this correct?
aikimarkConnect With a Mentor Commented:
Can you populate the TClientDataset with a SQLquery instead of a SQLtable?  That might limit the rows being sent from the server.
moorhouselondonAuthor Commented:
I see that a DataSource can connect into a SQLQuery, which in turn connects directly to the SQLConnection, but this then prompts this question:  

If I have a DbGrid populated in this way (showing a subset of the table), and I want to Insert a record into the underlying Table, how will I be able to ascertain what the last record in the table is (say it's an Invoice table) and I want to generate the next Invoice No?  

Presumably I have to do this using the full SQLTable, Provider, ClientDataSet route, then refresh the Query to display that record in the Grid?

Excuse my ignorance on this, I picked up lots of bad habits coding using the BDE, and having moved away from the BDE am now trying to clean up my act.
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

* if working disconnected from the main database, you could use GUIDs as a key field.

* add the record to the server and requery

* use a two column identity, with one column being a unique client name
moorhouselondonAuthor Commented:
I have just chanced upon this article (whilst looking for something else - as is often the case).  Haven't had chance to wade through it yet... bear with me for a couple of days whilst I do.  In the meantime, any thoughts on Jensen's approach would be appreciated.

moorhouselondonAuthor Commented:
Looks like it works, but crashes when I shut-down, presumably because I need to somehow free up the Clone operation.  Tomorrow will look at that...
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.