Web Development

Ask something
Discussion Locked

ADO access to SQL for Large Databases

0 helpful
6 replies

by netgoofy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have created a simple application that works well except for one thing. My application accesses a sql database that is quite large and what happens is that when I run my application, it wants to cache or get all the data from the database upon loading the form. How I know this is that when I first ran my app, it took so long for the form to open that I thought it was locked up, so I ended task. But then tried again, and I watch the performance tab (task manager) and notice that it was getting lots of data and putting it in virtual memory. My ultimate goal is to just access the database and pull only the records that the user request. The user would input a account number, then return me the record I wish to see. Again, all works great except for this caching issue. Help greatly appreciated.

6 replies

by madestroitsolutions

I have experienced this problem before too. There are 2 workarounds I know of:

- You can set the recordset to a small recordset, rather than the whole table. For example, rather than setting it to TABLE, and assign a Table name, or TEXT and assign something like "SELECT * FROM Recordset", what you need to do is set it to some command that doesnt retrieve many records, or none at all, for exmaple: "SELECT TOP 1 * FROM Recordset".

- You can instantiate the Recorset object programatically, so it wont be initialized with your application. here is a sample in VB (you did not tell what language you are using), make sure you add a reference to ADO (Activex data objects x.xx) to your project by going to the project menu and selecting references. Then type the following wherever you want to access the recordset:

Dim Rs01 As Recordset
Dim Connection As Connection
Dim Connstring as string

Connstring = "YourConnectionString"
'Get this connection string from the ADO object
'Once you connect the ADO object you used before
'get the string from it, from the "use
'connection string" box

Set Connection = CreateObject("ADODB.Connection")
Connection.Open ConnString
Set Rs01 = CreateObject("ADODB.Recordset")

Rs01.Open "SELECT * FROM ....", Connection, adOpenStatic, adLockReadOnly

Then use the recordset "Rs01" which contains the result of the query.

---------------------------------------
The second approach requires more work. You might also create the recordsetas global upon start, but not fill it out by executing the Open method until you need the information. That way you can use it only when you need it.

I would recommend the first approach if you are not familiar with instantiating objects like in the second one.

If you have any problems, feel free to drop a line at: juanja01@optonline.net

Good luck!

by rrv

i assume u use ADO in VB6. basic knowledge about SQL tells that you use "WHERE" condition in SQL to retrieve data fast. In case u are using datareport or dataenvironment, rightclick the command object and take the hierarchy and copy the sql and write the code instead of using DE or Datareport. I use AS400's DB2/400 database from VB6 and ADO. My queries fetch single record from multimillion records of tables in no time. the sample code is below.

dim DB as new ADODB.Connection
'Open the DB as usual. if using DSN enable pooling.
dim RS as ADODB.Recordset
Set RS = New ADODB.Recordset
RS.Open "select * from library.table where Code='1234A'", DB, adOpenForwardOnly, adLockReadOnly
do while not RS.EOF
'use data
RS.movenext
loop
RS.Close

Also note to close the recordset if done with it. As more of them is kept open the memory pool and opened tables increase and affect performance.

All the best

This discussion is closed to new replies.