Question Regarding TMSTable and TMSSQL

[lmcmilli]

All,

First Let me say that I am fairly new to SQL programming so I must put a disclaimer that there are probably a 1000 ways to do this better.

Synopsis:
I have built a Table that has about 100000 rows in it. When the table was small, I used TMSTable to access the SQL Table and the response time was pretty good. When the table grew above 50000 rows the response time became pretty poor and the amount of memory my application began to use became greater than 200Mb on the PC executing the application. I know that it returns all the rows and that is why it is using so much memory, but I have tried many combination of the FetchAll, FetchRows and CursorType but they all have pretty poor response time.


Now the only thing that I am trying to achieve is:
1) see if the record already exists
2) if it exists update it with new data
3) if it does not exist create the row.

With the slow response time here is the sample code:


if table.locate('idnum',trimright(d.idnum),[]) = TRUE then
begin
table.Edit;
table.field1 := newinfo;
table.post;
end
else
begin
table.insert;
table.field1 := newinfo;
table.post;
end;

now to try an combat the slowness I tried changing to TMSSQL which seems to work really well, but it dies everytime I process more than 180 rows at a time. It doesn't matter what the data is, 1-179 records works fine, 180 it gives an error that states execution stopped, some string or binary data may be trunctated.

here is a sample of the SQL:

IF EXISTS (SELECT * FROM table WHERE idnum = :Old_idnum)
BEGIN
UPDATE table
SET
idnum = :idum
field1 = :field1
WHERE
idnum = :Old_idnum
END
ELSE
BEGIN
INSERT INTO demographics
(idnum,
field1)
VALUES
(:idnum,
:field1)
END


sampel delphi code to go along with it.
tmssql.parambyname('idnum').asstring := d.idnum;
tmssql.parambyname('field1').asstring := d.field1;
tmssql.execute;


Other pertinate info
Delphi 6 Enterprise
SDAC 3.0 (I tried with the 2.4X version as well)
Microsoft SQL Server 2000 on 2000 Server.
Server has 4 processors and 4 GB of memory
Workstation is Pentium 2.4Ghz with 2 Gb of memory running XP Pro.
Workstation is connected to Server via Gigabit Ethernet

Any ideas or help will be appreciated. The most import thing here is processing speed. I will be processing as many as 50000 records a day.

Thanks
Lee

[Paul]

We couldn't reproduce the problem. Please send us complete sample to demonstrate it and include script
to create server objects to SDAC support address.

[Ikar]

A solution with using TMSSQL is close to optimal. But you can increase a performance if to place this query in StoredProc.


> but it dies everytime I process more than 180 rows at a time.

Most probably the problem is in using FetchAll in any of DataSets. Please see Sdac help, topic TCustomDADataSet.FetchAll for details.

[Spaggie]

This is the methodology of using SDAC that I'm gravitating towards but it seems to be the wrong path to be reading/amending/writing data in this way.

What's the best was of using SDAC to read/amend/write data ?

Or to get data from fields into and out of variables for use in the program.

I've always used gotokey/findfirst/findfield but it seems to go against the ideals of SDAC/SQL to do it this way ?

any ideas ?

[Ikar]

A necessity of such methods may appear only on working with big volumes of data. For easier cases you can just use usual Edit..Post.


> I've always used gotokey/findfirst/findfield but it seems to go against the
> ideals of SDAC/SQL to do it this way ?

You can use Locate/LocateEx instead these methods. But in some cases you can take a great impact on performance if to pass this procedure to the server.

[Spaggie]

Yeah I just feel I'm missing something having to use gotokey/findfield when I maybe could use something more SQLy

I dunno it just feels like I'm losing a level of abstraction using Locate/LocateEx rather than a snazzy SQL statement, it's just I cant find a crossover fromt he SQL to program variables.

Spag