ADODB Connections are veeeery slow

[stibay]

I wonder if it anyone know any good tips & tricks to improve performance on ADODB Connections.
Sometimes it goes veeery slow to open the connection and running the query takes under a second and other times opening the connection goes fast but running the query takes ages.

I have noticed that its usually the first time the query is runned its taking the longest. If the same query is beeing run again it goes ALOT faster.
I have tried running a simpler query to the DB when opening the worksheet to improve the time it takes to open the connection, but it doesnt seem to help that much for some reason.

The SQL statements are also pretty huge, around 2000 characters (no spaces)
I read somewhere else that adding:
rec1.CursorLocation = adUseClient
rec1.CursorType = adOpenStatic
might help. But didn't see much difference.

Please Login or Register  to view this content.

As you can see from the code its 2 different servers that the querys can go to. So one question is that if I run a query on Server 1 does the connection to server 2 get terminated?
Is there a way to keep both connections open?

Here are the methods I run when the sheet is opened. Theese query goes very fast to open connection and run. So question again is if this actually should help? Are the connections beeing kept alive?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[gsnidow]

Here are the methods I run when the sheet is opened. Theese query goes very fast to open connection and run. So question again is if this actually should help? Are the connections beeing kept alive?

stibay, I'm assuming you are connecting to SQL Server (based on the "SELECT TOP 1...")? If I am not mistaken, SQL Server will cache the execution plan of a query by a user. So, that is why a slow running query often seems to run faster the next time you run it. However, once the connection is terminated, the cached execution plan is gone. But you would need to ask your SQL Server guy to trace what is happening. Now, your "SELECT TOP 1..." querries are going to be fast since you are only selecting the top 1 or 10 or 100 or whatever, based on the indexes on the tables. Once you take out the "TOP 1...", the rest is back to square 1, in that the query optimizer will have to create a new execution plan for you. I would think even if your Excel file kept the connection alive, SQL Server is going to kill it after an amount of idle time. I think you will be able to answer a lot of your questions by working with the DB admin.

Greg

[stibay]

It's a Sybase server.
More spesificly Sybase ASE 11/12/15 - com.sybase.jdbc4.jdbc.SybDriver

The querys run very fast in a sybase query program.
It's more opening the connection and running it in Excel which takes alot longer for some reason.