Re: New to ADO with Delphi

That is an excellent insight. All my quick lookup queries or tables could be
server side, but the items where I need to show data or possibly perform
updates I could run client-side in a query control.

Thanks for the advice.

Jared Sherman

"Vassiliev V. V." <support@xxxxxxxxxxxxxxx> wrote in message
news:4645f473@xxxxxxxxxxxxxxxxxxxxxxxxx
There is another reason to use server-side cursor: when you analyze a
large query - i.e. you make a query, traverse it without show in UI, make
some calculation and close it as soon as possible. Client-side cursor
anyway internally opens query with server-side cursor, traverses it and
caches values, so if all you need is to quickly traverse query results and
do not want large caches, server-side cursor will be fine and it will be
more effective than client-side cursor. However if you want to hold query
results (to display them in UI or for other reason) client-side cursor
should be used.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Brian Hollister" <bhollisterATfuturaintlDOTcom> ñîîáùèë/ñîîáùèëà â
íîâîñòÿõ ñëåäóþùåå: news:4645e9d6$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Jared,

The first thing that sticks out to me is that using server-side cursors,
if
you leave the set open you may possibly create deadlock situations. I
don't
have a ton of experience with this but i have doen it for a little while
now, > 3 years. I feel that the only time you should be using server-side
cursors is when you are writing an application that runs when noone else
is
there interfacing with the db. Server-side cursors held open will create
and
hold locks on tables. While these locks are there noone else will be able
to
change anything. I prefer the optimistic approach to this and as advised
before pull only what i need and deal with failures writing back on a as
needed basis.

Hth,

Brian Hollister

--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com
"Vassiliev V. V." <support@xxxxxxxxxxxxxxx> wrote in message
news:4640b07e@xxxxxxxxxxxxxxxxxxxxxxxxx
If you use these queries in UI and hold them open quite longtime (not
just
open, traverse/make some calculations and close), all memory you save on
client will be allocated on server.

You should limit number of returned rows in your application and not try
to
handle all table rows form one query.

//------------------------------------------
Regards,
Vassiliev V. V.
http://www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
http://www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)


"Jared Sherman" <Jared.Sherman@xxxxxxxxxx> ñîîáùèë/ñîîáùèëà â íîâîñòÿõ
ñëåäóþùåå: news:46409d46$1@xxxxxxxxxxxxxxxxxxxxxxxxx
Whoa, that really helps. Thanks a ton. I tried a few different ways.
Dataset is a 1.5 Gig SQL Server database.
I opened 8 tables for the following tests. The values showen are how
much
memory was showen being used in Task Manager by my application.

Tables in Client Mode: 845,868 KB
Tables in Server Mode: 10,320 KB
Queries in Client Mode: 288,044 KB
Quereis in Server Mode: 9,008 KB

The Queries in Server mode are by far the best setup, but I think I'm
going to do a mix of tables and queries only in server mode.

Do you guys see any downsides to this approach?

Thanks again for your help

Jared Sherman

"Brian Hollister" <bhollisterATfuturaintlDOTcom> wrote in message
news:463babf0@xxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

It's my belief that the first thing you should eliminate is any
TADOTables
that you can. These are loading every record in the table. Try to
stick
to
queries that contain where clauses so that your selectivity can be as
high
as possible. This will result in a big performance boost.

Here's a couple of links that may be helpful:

http://delphi.about.com/od/database/a/adodelphi.htm


http://homepages.borland.com/ccalvert/TechPapers/Delphi/ADOBasics/ADOBasics.html

http://dn.codegear.com/delphi/database/sqlservers

hth,

brian

--
Got a big event coming up? Let us
help coordinate your event. For more
visit www.kissemgoodbye.com
"Jared Sherman" <Jared.Sherman@xxxxxxxxxx> wrote in message
news:463ba00b$1@xxxxxxxxxxxxxxxxxxxxxxxxx
I'm currently using Delphi 6. I'm in the process of converting over
to
Delphi 2006, but for now this project that I've been asked to help
maintain,
must remain in Delphi 6. It is connecting to a SQL Server 2000
database
that
is about 1 GB in size. The program was written with a variety of
TADOTables
and TADOQueries. The startup routine of the application opens a
series
of
TADOTables. Because of the size of the database this application can
grow
to
be over 500,000 KB in the task manager. When I run the application
it
has
been known to breach Microsofts 2 GB limit for a process and it will
crash.
What I'm trying to do is find the right way to optimize this code
for
the
short time. Most the TADOTables are set with the CursorLocation =
clUseClient. They also have the TableDirect setting to False. I've
heard
that changing to CursorLocation=clUseServer and TableDirect=True
would
help,
but evertime I attempt that I get errors like: "Table cannot be
found"
or
something like that.

Can someone give a noob some tips on how to better use these
TADOTables
and
TADOQueries so that this application will stop being such a beast?

Thanks in advance for your help.

Jared Sherman














.

Relevant Pages

  • Re: 0x800a0cb3 when Update record
    ... the SQL statement are... ... The only cursor type allowed with client-side ... let SQL Server take care of your transactions for you: ...
    (microsoft.public.data.ado)
  • Re: VB connection to SQL server
    ... > the client machine begins to lose its relevance and accuracy as soon as it ... > aware that the data is probably out of date, a client sided cursor might ... > design minimises the possibility that records will have changed in the ... >> The Database server is in the office, and people use the Vb program from ...
    (microsoft.public.vb.database)
  • Re: Huge memory comsumption of ADODB Connection object
    ... unless the cursor is returning data to the client* I don't see ... I suppose this could cause some extra memory ... Is there a similar behavior in Sybase? ... Server was originally based on that rdbms, so there very well may be. ...
    (microsoft.public.data.ado)
  • Re: VB connection to SQL server
    ... "Chris Barber" wrote in message ... > recordsets (client side cursor) to allow you to persist the recordsets to ... aware that the data is probably out of date, a client sided cursor might fit ... That is because the SELECT statement is executed on the server and the data ...
    (microsoft.public.vb.database)
  • Re: ADO server side cursor sloooow
    ... Using the ODBC driver my tests show that both client and server side ... forward only server side cursor is faster than a client side cursor as ... requiring over 1000 trips over the network. ...
    (comp.databases.btrieve)
Loading