Paradox versus Sql Server 2000 3

[Karen99]

I have an application that is running on D5 and Paradox. And it works fine. Now I am converting it that it must also run on Sql Server. It is not to difficult to do it, except everything are so much slower on Sql Server. Is Sql Server so much slower than Paradox, or am I doing something wrong. I run my connections through ODBC and the BDE. Have anybody worked with both, and have any sugesstions how to optimize my code for Sql Server eg. rather use query components that table components ect.

Please help !!

 

[edwinjs]

Well, You should avoid using table components while using SQL Server instead use TQuery with "where" clause to restrict data and you logic will change substancially while converting it to Client / Server. Unless you do that you will have perfomance degradation.

One more thing !! why is ODBC required when ur using BDE ?
I hope ur using BDE MSSQL Native drivers which is faster.

 

[Karen99]

No, I am not using MSSQL driver with the BDE. Thanx for the tip. Must I just use the standard settings when I setup the MSSQL driver ? Or are the any special considerations I have to make ?

Any other tips ?

 

[Karen99]

When I setup the MSSQL connection and open it, the following text appears at Type : MSSQL: Unknown Version: Mapping error: Bad template: SQL Server [^0-9]*([0-9]+)\.

But it still opens..... ????

 

[Karen99]

I did try my application with the MSSQL connection, but it is slower than with my ODBC connection. Why is that ? Can it be that I don't have the correct/latest MSSQL driver ? If that is the case, where can I find that ?

 

[svanels]

It is tempting to use the same components and techniques for paradox as for Client-Server (SQL-Server, Interbase) systems but what works well for access/paradox will be extremely inefficient in the client-server world.

Working with tables will generate a lot of data traffic on your network, even if the RDBMS and your application are on the same computer.

Imagine you need the highest salary in a employee table,
steps:
empTable.Open
empTable.First
MaxSalary :=0
while not etc...
empTable.next

With a query you would use: select Max(Salary) from employee

Using a table component takes about 10 times longer then using a query in this example on a small table, even if the Database server (SQL-Server 2000) is on the same computer as the application. This regardless it is ODBC, ADO, BDE, MDAC or something else to make the connection.
Imagine when you have thousands of records.

Regards







Steven van Els
SAvanEls@cq-link.sr

 

[edwinjs]

Steven is 100% Right. Unless you change the logic to suit Client / Server, performance will suffer and some times the application hangs. I had this problem (5 years back) of changing paradox to SQL Server. More over BDE is no more supported by Borland either ADO / DBExpress is the way to go. DBExpress is suppose to be faster.

Or you can do is post the code that is taking more time and may be we can suggest few things to improve the performance.

 

[Karen99]

Ok, here follows some code :

// Write feature points for each Network
for ItemNr := 0 to (NetworkMapList.Count - 1) do
begin
with qrySql3 do
begin
close;
databasename := ClientDatabaseName;
sql.clear;
sql.add('select * '+
'from MapFeatureLand '+
' where NetworkNr = ' + IntToStr(FNetworkNr) +
' and FeatureType between ' +
IntToStr(CGisFeatureStand) +
' and ' + IntToStr(CGisFeatureStandSG));
Open;
First;

while not Eof do
begin

Inc(FeatureNr);
//this is a form that appears to show the progress
with FormProgress do
begin
if (FeatureNr mod NrFeaturesDiv100) = 0 then
begin
RecordGauge.Progress := Round(100 * FeatureNr /
FStandFeatureCount);
RecordGauge.Update;
Application.ProcessMessages;
end;
end;

//I now this part takes a lot of time, I am updating
//MapFeatureLand here. So it is the same record that
//I am standing on with my query that I am updating.
//I did put this code in a query, but it actually
//takes longer than using a table component.
LandFeatureTable.indexname := '';
LandFeatureTable.findkey([fieldbyname
('featurename').Value]);
LandFeatureTable.Edit;
LandFeatureTableFeatureNr.Value := FeatureNr;
LandFeatureTablePosFeaturePts.Value := PosFeaturePts;

LandFeatureTable.Post;

FeatureTypeSm := fieldbyname('FeatureType').Value;
NrFeatureParts := fieldbyname
('NrFeatureParts').Value;
NrFeaturePoints := fieldbyname('NrFeaturePts').Value;
PosFeaturePts := (PosFeaturePts + 4 *
SizeOf(Smallint)) +
((NrFeatureParts - 1) *
SizeOf(Integer)) +
(NrFeaturePoints *
SizeOf(TPoint));

//this is a file stream that gets populated
StandFeatureStream.Write(FeatureTypeSm,
SizeOf(Smallint));
StandFeatureStream.Write(FeatureColourNr,
SizeOf(Smallint));
StandFeatureStream.Write(NrFeatureParts,
SizeOf(Smallint));
StandFeatureStream.Write(NrFeaturePoints,
SizeOf(Smallint));

if NrFeatureParts > 1 then
LandFeatureTableFeatureParts.SaveToStream
(StandFeatureStream);

if NrFeaturePoints > 1 then
LandFeatureTableFeaturePoints.SaveToStream
(StandFeatureStream);
Next;
end;
close;
end;
end;
end; // for ItemNr


MapFeatureLand have 376527 records. So I now it is a lot. This code above was first in a table component when it was used for Paradox, I already changed it to use a query component. I now we are talking about a lot of records here, so I am now expecting it to run in a flash, I just want to figure out why it is so much faster on Paradox than on Sql Server. If I am doing something wrong or is Sql Server just slower than Paradox ? I tested with ODBC and BDE (MSSQL) and ADO. I think ADO is faster, but stil slower than Paradox where BDE was used.

And edwinjs can you please tell me where can I get info about dbexpress. I have never worked with that. So I have no idea what it is.

Thanks for al your help !!!!!

 

[svanels]

I dont like the part

" LandFeatureTable.indexname := '';
LandFeatureTable.findkey([fieldbyname
('featurename').Value]);
LandFeatureTable.Edit;
LandFeatureTableFeatureNr.Value := FeatureNr;
LandFeatureTablePosFeaturePts.Value := PosFeaturePts;"

Findkey is time consuming.

Hold in mind that for a Client-Server environment, the data processing is done on the server. Updating a table with sql would be something like:

insert into TableA(
RNUM , E110_THRD , etc..)

select e.RNUM,
TDH_E110 , REM_TDH_E110, etc..
from RSAMPLE e, x_unitsteam
where e.R_date = dat_


Avoid iterations as .next, .edit, .post, while not EOF on client server systems.
If you need some values, write a stored procedure on the SQL-Server to get it.

Don't use visual components like gauges to much, especially in loops, because windows need to check and update screens also. It is easy to get locked up in an endless loop that has nothing do with the data processing itself.

The BDE runs like an SQL engine and is optimized for paradox, and other file bases (desk-top) databases. It can be interpreted like the Borland Alternative for ODBC.

The Client-Server systems got their own SQL engine embedded in the Database, thus for making optimal use you will need to inquire them with SQL statements.

There are a lot of database components that make calls directly to the API of the different systems (ADO, IBX etc..), and don't need the use of the BDE. I suspect that when using the BDE with the native client-server drivers, the calls are directly routed to the database API, if you do not use .next etc.

I personally like the BDE, because it gives me more portability. I can write a program using plain BDE components (in the right way) and connect it to any client-server database. If you use ADO, IBX or other you have to certainly rewrite some code, with the BDE it would be only 2 seconds fiddling around in the BDE administrator.

Regards






Steven van Els
SAvanEls@cq-link.sr

 

[edwinjs]

http://bdn.borland.com/article/images/29106/

migrating_bde_applications_to_dbexpress.pdf

 

[svanels]

Why using ODBC and BDE ?

Answer: the BDE need the drivers, which can be the native delphi drivers, or odbc drivers registered on the computer.

When installing the BDE it scans automatically for installed drivers and connections.

The components that do not make use of the BDE must have the programming logic build in, and have their specific way of dealing with the API of the Database.



Steven van Els
SAvanEls@cq-link.sr