?
Solved

ActiveX DLL's Running SQL Query

Posted on 2003-02-21
5
Medium Priority
?
167 Views
Last Modified: 2010-04-07
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?

Thanks
Michael
0
Comment
Question by:stormsilverhand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 7993925
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.
0
 
LVL 1

Expert Comment

by:jeffreyhamby
ID: 7993937
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?
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 7993980
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 ;)
0
 
LVL 11

Accepted Solution

by:
rdrunner earned 400 total points
ID: 7994030
0
 

Author Comment

by:stormsilverhand
ID: 7994044
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.

Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month8 days, 15 hours left to enroll

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question