Connection with ADO is slow 1

[iker3000]

Hi!

I've a litle problem I can't understand: I am running a rather complicated stored procedure (about 3-4000 lines) calculating some financial things. This procedure returns a result set of about 400 records (10 columns). If I'am running the query from query analyzer, I get the result set after 5 seconds. Doing the same through ADO causes something else: the result starts to come after 5 seconds but it lasts another 45 seconds to get all the rows. How can this be?

Iker

 

[topher0303]

This could really depend on how you are using ADO. There are a lot of variables that can cause this problem.

Where are you using ADO from? ASP? COM+ component and passing it to ASP?

Are you using a client-side cursor or server-side?

What are you doing to pull all the records into ADO? Are you looping through it using ASP?

Just to give you an example, someone had written an ASP page that would pull quite a few records and iterate through them for display. The recordset was coming from a COM+ component using server-side cursor. The page would timeout before it could display it. Simply by changing it to a client-side disconnected recordset, the page now loads in less than 5 seconds.

A couple of other performance issues are that ADO is a bit slow when iterating a lot of records when using notation such as rst("columnname&quot to get the data. There is quite a bit of overhead.

Also, if using ASP, it is an interpreted language. It is a bit slower than a compiled language.

Chris.

 

[iker3000]

Hi, thanks for your quick reply.

I'm using ADO from Visual C++, with a connection of asynchronous execution (adAsyncExecute) and a forward-only, read-only recordset and I'm just looping through it. The cursor is the default.(Server-side cursor, I think.)

Do you think using disconnected recordsets would help? What are the drawbacks of it?

Iker

 

[SQLSister]

First I would avoid using a cursor at all especially if I'm serving up a web page which could have many simultaneous users. Cursors are slow and are almost never necessary when accessing user data. Do your calculations in the select statment itself. Look at the use of the case keyword and derived tables and subqueries one of those will probably help. IF you absultely can't avoid a cursor pull out the relevant data to a temp table first and the use the cursor on the temp table. At least this way you aren't cruising through evry record in the tables one at a time.

Normally we'd ask to post the stored procedure, but that one is so long. If you email it to me, I'll look at it, but I have a deadline at noon today and a meeting most of the afternoon so probably won't get to it till Monday. Well off to write the proposal for our next project.

 

[topher0303]

Disconnected recordset help you out when you marshall the recordset between processes.

Chris.