• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 173
  • Last Modified:

ActiveX DLL's Running SQL Query

Hiya i have an ActiveX DLL which runs querys passed to it, everything works fine apart from 1 thing.

The querys take a long time (no way around this 5 mins plus) and while they are running my app looks like it has crashed. How can i keep the application from acting like this while the querys are running?

1 Solution
Execute the queries asynchronously.  If using the connection object, you can specify that the query is run asynchronously, something like:

adoConn.Execute strSQL, , adAsyncExecute

I dont recall if thats the exact name of the enum value to specify asynchronous behavior or not.  If you require the results of that query before your program continues, however, you will need to provide some sort of messaging mechanism between DLL and main App, most likely in the form of events.
Is your recorset set to asynchronous?  That will at least let the program have control while the recordset is running.

There could be any of several reasons the queries are taking a long time to run....

poorly written stored procedures including dynamic SQL statements (so they're not precompiled), use of unncessary cursors in the SP (SQL server doesn't run queries very fast with cursors), unncessary fields in the SELECT statement, etc..

lack of indexing or too many indexes on the tables in the db

dynamic SQL being passed from VB to the stored procedure (same thing, SP's run much faster when precompiled)

Could you post some sample code?
Aye ...

You need to create a connection with events then you can use async calls to get the data from your DB.

But 5 minutes is a verrry long time for a queerry (usually) so i think there are some other problems , like missing indexes that might cause table scanns or stuff like that.

Lemme dig up a question where i posted some async querry code ;)
stormsilverhandAuthor Commented:
There are problems with the database and every ones queries take ages and ages! (But that’s not up to me to sort out, the whole company uses a program which access info from an oracle database. It used to be very fast and reliable. Now it’s slow and useless!!!)
I will try out AzraSounds suggestion on Monday.


Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now