Board index » delphi » ADO Query REALLY SLOW over slow connection

ADO Query REALLY SLOW over slow connection

The following tests were performed over a VPN to my SQL server using a slow
connection.

Note, that when the connection is fast - 10mbit or greater, there is not a
noticeable difference.  So, this is not a server optimization issue.

In each these tests, a query was opened, then attached to a datasource/grid
and the grid had 10 records on the screen.  The query is a "select fields
from view order by field".  I tried simplifying the query to just 'select *
from basetable', but it makes no difference....

Using ADO 2.7 - OLEDB Provider for SQL Server

ADO - Client Cursor, Static
Open Query - 9.75 seconds, attach to Grid - immediate
Open Query - 10.64 seconds, attach to Grid - immediate
Open Query - 9.00 seconds, attach to Grid - immediate

ADO - Server Cursor, Keyset
Open Query - 3.39 seconds, attach to Grid - total time 16.33
Open Query - 3.51 seconds, attach to Grid - total time 17.10
Open Query - 3.53 seconds, attach to Grid - total time 18.56

ADO - Server Cursor, Static
Open Query - 2.49 seconds, attach to Grid - total time 12.58
Open Query - 2.61 seconds, attach to Grid - total time 12.84
Open Query - 2.92 seconds, attach to Grid - total time 13.89

BDE
Open Query - 0.73 seconds, attach to Grid - immediate
Open Query - 0.60 seconds, attach to Grid - immediate
Open Query - 0.62 seconds, attach to Grid - immediate

Any ideas?   Remember, when running against a server over a normal
connection the difference is unnoticable.  I would be glad to do any tests
anyone can come up with.   This is a show stopper here.  I will have to
stick with the BDE...ughhh....

Wayne

 

Re:ADO Query REALLY SLOW over slow connection


ADO is COM-based and uses RPC (if I'm not mistaken). RPC needs more
bandwidth to function properly. There's nothing you can do except to ensure
that your WINS (and DNS probably) are up and provide clients with fast
name-to-IP resolving. IMHO, that is.

rb

Quote
"Wayne Brantley" <Wa...@aprompt.com> wrote in message

news:3c4e3985$1_1@dnews...
Quote
> Note, that when the connection is fast - 10mbit or greater, there is not a
> noticeable difference.  So, this is not a server optimization issue.

Re:ADO Query REALLY SLOW over slow connection


Did you read that if I set maxrecords to 2, it is really fast.   That means
that DNS/WINS is resolving and all is fine on that front.    Also, I opened
up SQL Query Analyzer, did a select from the same table and it pulls the
ENTIRE Recordset down in 4 seconds and today the ADO query takes 5 secs to
open.  So, like I said it is pulling down the entire recordset for some
reason.

Quote
"rb" <ra...@killspam-videotron.ca> wrote in message

news:3c4e8c4c$1_2@dnews...
Quote

> ADO is COM-based and uses RPC (if I'm not mistaken). RPC needs more
> bandwidth to function properly. There's nothing you can do except to
ensure
> that your WINS (and DNS probably) are up and provide clients with fast
> name-to-IP resolving. IMHO, that is.

> rb

> "Wayne Brantley" <Wa...@aprompt.com> wrote in message
> news:3c4e3985$1_1@dnews...
> > Note, that when the connection is fast - 10mbit or greater, there is not
a
> > noticeable difference.  So, this is not a server optimization issue.

Re:ADO Query REALLY SLOW over slow connection


Quote
"Wayne Brantley" <Wa...@aprompt.com> wrote in message

news:3c4ed3af$1_2@dnews...
Quote
> Did you read that if I set maxrecords to 2, it is really fast.   That

means

No. You didn't say that.

Quote
> that DNS/WINS is resolving and all is fine on that front.    Also, I
opened
> up SQL Query Analyzer, did a select from the same table and it pulls the
> ENTIRE Recordset down in 4 seconds and today the ADO query takes 5 secs to
> open.  So, like I said it is pulling down the entire recordset for some
> reason.

What do you mean by "entire recordset". How else would you do it? In
instalments? Don't think it can work that way with ADO. Maybe the fact that
it's COM-based has something to with it.

rb

Re:ADO Query REALLY SLOW over slow connection


HEHE!   I did not say it, did I...woops...

Anyway, I downloaded this OLEDB direct component set from oledbdirect.com
and tried it.   They are even faster than the BDE, I was getting times like
.1 seconds, instead of the BDE .2 and instead of ado of FIVE seconds.  So,
it has something to do with the ADO layer.

And yes, that is exactly right, it should bring it down in pieces.  When I
open the dataset, it should bring down nothing.  When I access records, it
should bring them down as I access them.  JUST like the BDE does and just
like the OLEDB stuff I mention above does.

One point though, the oledbdirect components are far from polished.......

Quote
"rb" <ra...@killspam-videotron.ca> wrote in message news:3c4f7322_1@dnews...

> "Wayne Brantley" <Wa...@aprompt.com> wrote in message
> news:3c4ed3af$1_2@dnews...
> > Did you read that if I set maxrecords to 2, it is really fast.   That
> means

> No. You didn't say that.

> > that DNS/WINS is resolving and all is fine on that front.    Also, I
> opened
> > up SQL Query Analyzer, did a select from the same table and it pulls the
> > ENTIRE Recordset down in 4 seconds and today the ADO query takes 5 secs
to
> > open.  So, like I said it is pulling down the entire recordset for some
> > reason.

> What do you mean by "entire recordset". How else would you do it? In
> instalments? Don't think it can work that way with ADO. Maybe the fact
that
> it's COM-based has something to with it.

> rb

Re:ADO Query REALLY SLOW over slow connection


Quote
"Wayne Brantley" <Wa...@aprompt.com> wrote in message

news:3c50713b$1_1@dnews...

Quote
> HEHE!   I did not say it, did I...woops...

HEHEHE here too. It happens, don't worry.

Quote
> And yes, that is exactly right, it should bring it down in pieces.  When I

I wonder how that process works. Really. Think about it - you have to create
a monstreous query to emulate web-search/results of 10 records a pop, and
some meesly driver can do it just like that. It's not fair!!! or it doesn't
really work like that.

Quote
> open the dataset, it should bring down nothing.  When I access records, it
> should bring them down as I access them.  JUST like the BDE does and just
> like the OLEDB stuff I mention above does.

> One point though, the oledbdirect components are far from polished.......

Have you checked CacheSize property. According to what I read in help, it
does exactly what you want. Bring as many records at the time and bring more
as pointer moves. The only odd thing is that its default is 1 and you say
you get everything. Either way, check it out and please let me know how did
it go.

rb

Other Threads