How to quickly fetch the records from an SQL recordset

[DBCepull]

I'm using LW/CVI V7.1.1 and SQL Toolkit V2.06.

When displaying the recordset from a SELECT statement I use the following construct:

  SQLhandle1 = DBActivateSQL(DBhandle, SELECTtext);

  DBBindCol() statements.......

  numRecs = DBNumberOfRecords(SQLhandle1);
  for (n=1; n<=numRecs; n++) {
    DBFetchNext(SQLhandle1);

    display record to the user...
  }
 
  DBDeactivateSQL (SQLhandle1);

This has always worked fine for me when using local databases. Now I am developing an app for a remote database, and the fetching of each selected record is proving to be an issue. It takes at best, 60msecs for my round-trip network access to fetch each record. If selecting very many records, the fetching can add up to a considerable time delay.

My question is, how can I bind the entire recordset to my application variables, (or to a local table?) in a single request to the database? Does LW/CVI support such a method? Or perhaps someone knows an SQL method to help me?

Thanks

[Michael_W.]

Hi DBCepull,

I wanted to let you know that I need to look into this a bit further.  I'll pass on any information I can gather.

[Michael_W.]

Hi DBCepull,

I spoke with R&D and it seems that each call to DBFetchNext does hit the database which is leading to the behavior you are seeing. 

As a workaround, you may be able to use DBGetVariantArray which should return an array of records which you can loop through without hitting the database each time.

[DBCepull]

Hi Michael,

Thanks for the help. This is what I was looking for. Not sure why I missed it!

However, after trying it out, it doesn't seem to help. The statement:  DBGetVariantArray(SQLhandle, &array, &recs, &fields); seems to take the same amount of time to get the records as the individual DBFetchNext() statements. So if my SQL statement matches 100 records, the DBGetVariantArray() call will take 100*60msec to complete.

Is there a DB attribute setting that needs changed?

[Michael_W.]

Hi DBCepull,

I'm glad to hear you were able to implement my suggestion.  As far as the delays you are experiencing, it may simply be caused by the inherent delay of pulling from a remote database.  Have you tried using a different SQL client and running the same query?  It would be interesting to compare the performance of two different clients.

[DBCepull]

Hi Michael,

I have used different clients in the past and have always found that using the native client is fastest, (which I am using in this case). However, I haven't experimented with that in the context of this issue. I'll check that out.

I'm beginning to think that the best approach may be to develop a web-based application that runs on the remote database server.

Thanks,

Dale