×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using ADO recordset is SLOW?

Using ADO recordset is SLOW?

Using ADO recordset is SLOW?

(OP)
My project involves the CR 8.5 RDC and Visual Basic and I need to basically let users type in their own SQL queries and press a button and have a report come up that reflects that query.

My first stab was to just use the regular ODBC connection stuff ( Add the tables I want to the report Database object ) and then change the SQLQueryString member when necessary.  But this didn't work out too well as I've been unable to get the report to refresh.

So I tried again using an ADO connection and it works just fine with the dynamic querying, however it is intolerably slow.  It takes about 20-30 seconds to generate a 90 page report, which takes 1-2 seconds to do with the ODBC connection.

Any suggestions on speeding up the performance of the ADO connection, or hints on how to achieve the same goal using the ODBC and SQLQueryString approach?

RE: Using ADO recordset is SLOW?

(OP)
There's a bit of a twist to this that I should add.

The full use case of the project here is:

1. User types in a SQL query, hits button
2. Up comes CRDesigner form
3. User chooses fields to use in report
4. User then previews the report and it should reflect only the rows selected by the query they typed in in step 1

The problem is that this doesn't work with the way I'm doing it.  When the report is previewed, it just shows all records.  When I look at the SQL query on the Designer it shows "original" query (for all rows).

RE: Using ADO recordset is SLOW?

Try passing an ado recordset at runtime. It's a lot cleaner way of doing everything. The minimum you have to use the designer is to set unbound fields on it and assign them at runtime.  I've posted the code throughout this site several times, but if you want it and can't find it let me know and I can email it to you.

Tammy
Tammy@theboyz.net

RE: Using ADO recordset is SLOW?

oops, never mind, I just now saw that you already tried that!

RE: Using ADO recordset is SLOW?

are you using the ado design time control or code?

RE: Using ADO recordset is SLOW?

(OP)
I'm doing it through code.  It looks like this:

Dim m_Application As New CRAXDDRT.Application
Dim m_Report As CRAXDDRT.Report
Dim m_ADOrs As ADODB.Recordset
Dim m_ADOcnn As ADODB.Connection

   Set m_ADOrs = CreateObject("adodb.recordset")
   Set m_ADOcnn = CreateObject("adodb.connection")
   m_ADOcnn.Open <connection string>

' When new query is provided then:

   Set m_Report = Nothing
   Set m_Report = m_Application.NewReport

   m_ADOrs.Open querystring, m_ADOcnn
   m_Report.Database.AddADOCommand m_ADOrs.ActiveConnection, m_ADOrs.ActiveCommand
   Call DisplayReport


RE: Using ADO recordset is SLOW?

Sorry, I've only heard that the ado design time control and odbc is slower than connecting to your server with ado.

RE: Using ADO recordset is SLOW?

...what database are you using? what does your connection string look like?

RE: Using ADO recordset is SLOW?

(OP)
It's a SQL Server 7.0.

Connection string is like this:

Driver={SQL Server};" & _
                 "Server=server;" & _
                 "Database=db;" & _
                 "Uid=me;" & _
                 "Pwd=password;"

RE: Using ADO recordset is SLOW?

Maybe its not actually ado, but the crystal reports active data driver. Whenever you pass a recordset to crystal reports it uses this driver. Try connecting using the active data driver in the little tree control they have and see if this gives you the same result. If so, that's the problem.

RE: Using ADO recordset is SLOW?

(OP)
Interesting!

I just used Crystal Reports itself, created a new report with the wizard, and chose to use the Active Data connection and I got the same kind of response.

It took about 20 seconds to generate the report.

So does this mean I can't use this approach unless I want to take the performance hit?

Thanks for your help itme! :)

RE: Using ADO recordset is SLOW?

Probably. It looks like you have some beef to take up with Seagate.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close