MS SQL Server 6.5, Queries and Performance. 
Author Message
 MS SQL Server 6.5, Queries and Performance.

I setting up a new system here with MS SQL Server 6.5 and delphi Professional (not C/S). I hadn't used Delphi until a couple of
days ago and generally I've been impressed with what I have found. However, I have a question which I'd like to ask.

I have a lot of data tables built around non updateable queries. Because of this I have to use cached updates and then specify the
INSERT, UPDATE AND DELETE SQL code. This is fine, but one of my problems is that I can't easily see changes made to the data by
other people unless I force a refresh of the TQuery item.

When I do this, I am told that I can't do a refresh because my data doesn't have a unique index (which it does). The only way I
have found of updating the data is to close the query and then open it again which works but takes a long time to complete.

For those of us who have to use cached updates, what is the best way of handling this sort  of thing?

Many thanks for any help offered.

Ian Cottee



Wed, 18 Jun 1902 08:00:00 GMT  
 MS SQL Server 6.5, Queries and Performance.


Quote:

>I setting up a new system here with MS SQL Server 6.5 and Delphi Professional (not C/S). I hadn't used Delphi until a couple of
>days ago and generally I've been impressed with what I have found. However, I have a question which I'd like to ask.

>I have a lot of data tables built around non updateable queries. Because of this I have to use cached updates and then specify the
>INSERT, UPDATE AND DELETE SQL code. This is fine, but one of my problems is that I can't easily see changes made to the data by
>other people unless I force a refresh of the TQuery item.

>When I do this, I am told that I can't do a refresh because my data doesn't have a unique index (which it does). The only way I
>have found of updating the data is to close the query and then open it again which works but takes a long time to complete.

>For those of us who have to use cached updates, what is the best way of handling this sort  of thing?

>Many thanks for any help offered.

>Ian Cottee

Ian,

I hope that I am not about to teach the sucking of eggs here, but it
seems that the crux of your issues are that you're expecting destop
database type features from a SQL Server relational database ... it
ain't gonna happen.

A completely different approach is required when developing Client
Server applications.

Live links into tables are not maintained when using Client Server
DB's. Any view of the data that you have is a snapshot taken when the
query was executed. To see any changes to the DB, therefore, the
select query will have to be re-executed.

If your select statement is taking a long time to return, I would
suggest that it should be carefully analysed : are all of the fields
being returned actually necessary? Are all the records being returned
required?

To speed up select queries, try embedding the SQL in a stored
procedure and calling the SP from Delphi. This is especially true of
situations requring information from several tables. Also ensure that
you're using joined queries rather than recursive or sequential reads
of individual tables.

Updating the data in the DB is another matter entirely. You'll have to
engineer your system so that for Insert, Update and Delete operations,
each table requiring alteration is updated individually, each group of
related updates being wrapped in a transaction.

Also be ready for any updates you perform to fail! In multi user
systems, unless engineered to do so, you can never be sure that a
record you want to alter has not already been changed by another user,
or even if it's been deleted since you last queried it.

As you can see, Client Server systems are more complex to develop than
traditional desktop DB applications, primarily because of the
multi-user, and connectionless state issues outlined above. They are,
however, much better at operating within demanding multi-user
situations.

Drop me a line if you want to discuss this further.

Regards,


C.E.O.
Paradigm Industries Ltd.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. UK query - Delphi3 - NT4-Memory leak on SQL Query to SQL/server 6.5

2. MS-SQL server 6.5 vs Interbase server ???

3. Hang up when using D1 + ODBC + MS SQL Server 6.5

4. Creating a database with MS SQL Server 6.5

5. Image Datatype on a MS-SQL 6.5 Server

6. Delphi 1 Desktop/MS SQL Server 6/6.5

7. No Tables in MS Sql Server 6.5

8. MS SQL Server 6.5 + Delphi 3.0 = ???

9. Transactions in MS-SQL Server 6.5.

10. Delphi 3.0 and MS SQL Server 6.5

11. Delphi 3 and MS SQL Server 6.5 - Bug??

12. Stored Procedure MS Sql Server 6.5 and Delphi 2.0

 

 
Powered by phpBB® Forum Software