stormsilverhand
asked on
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?
Thanks
Michael
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
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?
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 ;)
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 ;)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
I will try out AzraSounds suggestion on Monday.
Thanks
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.