ADO Performence 
Author Message
 ADO Performence

System : ADO + SQL SERVER 2000 + MyApplication (installed on the server, no
network)
Table : Customer (12 varchar (50) + 1 image + 1 text + 1 uniqueidentifier +
5 int = 20 Fields )
            Record count = 0 (because we don't care)

Result to open table (tested with stored procedure, view, table)
Total time = 200 ms = ADO open Customer table selecting 20 fields (200ms)
and execute query by sql server (0 ms profiler)
Total time = 010 ms = ADO open Customer table selecting 3 fields (010ms) and
execute query by sql server (0 ms profiler)

Does anyone know how it works? I need to select 20 fields but faster !!!!



Sun, 18 Jul 2004 23:04:46 GMT
 ADO Performence

Hi,

Do you think it is slow? I do not think so. What are you
trying to achieve? 0 ms? It is impossable. SQL Server
spends some time to retrieve data and provider spends some
time to transfer that data to client, even if it is on
same PC wher database server is.

Val

Quote:
>-----Original Message-----
>System : ADO + SQL SERVER 2000 + MyApplication (installed
on the server, no
>network)
>Table : Customer (12 varchar (50) + 1 image + 1 text + 1
uniqueidentifier +
>5 int = 20 Fields )
>            Record count = 0 (because we don't care)

>Result to open table (tested with stored procedure, view,
table)
>Total time = 200 ms = ADO open Customer table selecting
20 fields (200ms)
>and execute query by sql server (0 ms profiler)
>Total time = 010 ms = ADO open Customer table selecting 3
fields (010ms) and
>execute query by sql server (0 ms profiler)

>Does anyone know how it works? I need to select 20 fields
but faster !!!!

>.



Sun, 18 Jul 2004 23:34:37 GMT
 ADO Performence
Yes it's slow
I do not understand that ADO & SQL SERVER are used in web solution.
It means that 200ms*50 users = 10 secondes to get the result.
In one of my process, a query with 8 left join on 4 million records takes
136ms 5% of cpu and ADO want to take 200ms to read fields and 18% of cpu!!!!
And when i say read fields it does not include the transfer of the data.
You can make a query with 1000 result and 5 fields, it will be faster than a
query with 0 result and 20 fields.
I would like someone to explain this.



Quote:
> Hi,

> Do you think it is slow? I do not think so. What are you
> trying to achieve? 0 ms? It is impossable. SQL Server
> spends some time to retrieve data and provider spends some
> time to transfer that data to client, even if it is on
> same PC wher database server is.

> Val

> >-----Original Message-----
> >System : ADO + SQL SERVER 2000 + MyApplication (installed
> on the server, no
> >network)
> >Table : Customer (12 varchar (50) + 1 image + 1 text + 1
> uniqueidentifier +
> >5 int = 20 Fields )
> >            Record count = 0 (because we don't care)

> >Result to open table (tested with stored procedure, view,
> table)
> >Total time = 200 ms = ADO open Customer table selecting
> 20 fields (200ms)
> >and execute query by sql server (0 ms profiler)
> >Total time = 010 ms = ADO open Customer table selecting 3
> fields (010ms) and
> >execute query by sql server (0 ms profiler)

> >Does anyone know how it works? I need to select 20 fields
> but faster !!!!

> >.



Mon, 19 Jul 2004 00:14:12 GMT
 ADO Performence


Quote:
> Yes it's slow

Right. But we haven't seen your code.  There are 100's of ways to get data
and you might have picked the slowest.

Stephen Howe



Mon, 19 Jul 2004 00:16:11 GMT
 ADO Performence
My SQL SERVER SP
CUSTOMER_SELECT : 'SELECT TOP 100 * FROM CUSTOMER_TABLE'
please could you give just one way using ADO to get the result.
I will test it to see if it takes less than 200ms.
Thanks



Quote:



> > Yes it's slow

> Right. But we haven't seen your code.  There are 100's of ways to get data
> and you might have picked the slowest.

> Stephen Howe



Mon, 19 Jul 2004 00:49:19 GMT
 ADO Performence
Just one comment

I belive that your formula is not correct:
"200ms*50 users = 10 seconds"
That is not the way you calculate performance and your
results could be misleading you. You need to take into
consideration how SQL server internally works and handle
each query.

Anyway, in your exaple you are retrieving images and
stuff, if you are using disconnected recordsets then it
will take more time to transfer it over...

Quote:
>-----Original Message-----
>My SQL SERVER SP
>CUSTOMER_SELECT : 'SELECT TOP 100 * FROM CUSTOMER_TABLE'
>please could you give just one way using ADO to get the
result.
>I will test it to see if it takes less than 200ms.
>Thanks



crit dans le message
Quote:



>> > Yes it's slow

>> Right. But we haven't seen your code.  There are 100's
of ways to get data
>> and you might have picked the slowest.

>> Stephen Howe

>.



Mon, 19 Jul 2004 01:40:47 GMT
 ADO Performence
Just one comment

I belive that your formula is not correct:
"200ms*50 users = 10 seconds"
That is not the way you calculate performance and your
results could be misleading you. You need to take into
consideration how SQL server internally works and handle
each query.

Anyway, in your exaple you are retrieving images and
stuff, if you are using disconnected recordsets then it
will take more time to transfer it over...

Quote:
>-----Original Message-----
>My SQL SERVER SP
>CUSTOMER_SELECT : 'SELECT TOP 100 * FROM CUSTOMER_TABLE'
>please could you give just one way using ADO to get the
result.
>I will test it to see if it takes less than 200ms.
>Thanks



crit dans le message
Quote:



>> > Yes it's slow

>> Right. But we haven't seen your code.  There are 100's
of ways to get data
>> and you might have picked the slowest.

>> Stephen Howe

>.



Mon, 19 Jul 2004 01:40:50 GMT
 ADO Performence
Just one comment

I belive that your formula is not correct:
"200ms*50 users = 10 seconds"
That is not the way you calculate performance and your
results could be misleading you. You need to take into
consideration how SQL server internally works and handle
each query.

Anyway, in your exaple you are retrieving images and
stuff, if you are using disconnected recordsets then it
will take more time to transfer it over...

Quote:
>-----Original Message-----
>My SQL SERVER SP
>CUSTOMER_SELECT : 'SELECT TOP 100 * FROM CUSTOMER_TABLE'
>please could you give just one way using ADO to get the
result.
>I will test it to see if it takes less than 200ms.
>Thanks



crit dans le message
Quote:



>> > Yes it's slow

>> Right. But we haven't seen your code.  There are 100's
of ways to get data
>> and you might have picked the slowest.

>> Stephen Howe

>.



Mon, 19 Jul 2004 01:40:51 GMT
 ADO Performence
Right. You will not get 10 secs in that case. Also when
you run SP first time SQL server copiles it. It takes some
time. Next calls will work much faster then first one.
Anyway, could you post full text of SP, beginning CREATE
PROCEDURE .. and up to the end, because CUSTOME SELECT
does not say anything

Val

Quote:
>-----Original Message-----
>Just one comment

>I belive that your formula is not correct:
>"200ms*50 users = 10 seconds"
>That is not the way you calculate performance and your
>results could be misleading you. You need to take into
>consideration how SQL server internally works and handle
>each query.

>Anyway, in your exaple you are retrieving images and
>stuff, if you are using disconnected recordsets then it
>will take more time to transfer it over...

>>-----Original Message-----
>>My SQL SERVER SP
>>CUSTOMER_SELECT : 'SELECT TOP 100 * FROM CUSTOMER_TABLE'
>>please could you give just one way using ADO to get the
>result.
>>I will test it to see if it takes less than 200ms.
>>Thanks


>crit dans le message



>>> > Yes it's slow

>>> Right. But we haven't seen your code.  There are 100's
>of ways to get data
>>> and you might have picked the slowest.

>>> Stephen Howe

>>.

>.



Mon, 19 Jul 2004 02:01:47 GMT
 ADO Performence
Try to update?
Then you will see what is slow.


Quote:
> System : ADO + SQL SERVER 2000 + MyApplication (installed on the server,
no
> network)
> Table : Customer (12 varchar (50) + 1 image + 1 text + 1 uniqueidentifier
+
> 5 int = 20 Fields )
>             Record count = 0 (because we don't care)

> Result to open table (tested with stored procedure, view, table)
> Total time = 200 ms = ADO open Customer table selecting 20 fields (200ms)
> and execute query by sql server (0 ms profiler)
> Total time = 010 ms = ADO open Customer table selecting 3 fields (010ms)
and
> execute query by sql server (0 ms profiler)

> Does anyone know how it works? I need to select 20 fields but faster !!!!



Mon, 19 Jul 2004 16:08:35 GMT
 ADO Performence


Quote:
> My SQL SERVER SP
> CUSTOMER_SELECT : 'SELECT TOP 100 * FROM CUSTOMER_TABLE'
> please could you give just one way using ADO to get the result.

Well I would not use "SELECT *", ever. It takes time for the Server to look
up the fields names. Instead name them every time.

Anyway, what you have above is a SQL statement, not the relevant ADO objects
your using to return the data. It is the second thing that is commented in
this newsgroup. If you want to talk about performance of SQL servers and how
to improve them, you want another newsgroup inon this news server.

So where is your ADO code?

Stephen Howe



Tue, 20 Jul 2004 00:41:12 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. SQL Query Performence

2. SQL Server Performence

3. Performence with insert into

4. datatype change cause performence issue

5. Performence tunning

6. Performence Question

7. Parameters kills performence with Linked Servers

8. Oracle Bad Performence For Data Entry

9. Oracle Bad Performence for data entry

10. Performence problems

11. Bad performence using DESCENDING with index in ORACLE 7.3

12. Long Raw performence issues


 
Powered by phpBB® Forum Software