Using ADO recordset is SLOW?

[bdotzour]

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?

 

[bdotzour]

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).

 

[itme]

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

 

[itme]

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

 

[itme]

are you using the ado design time control or code?

 

[bdotzour]

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&quot
Set m_ADOcnn = CreateObject("adodb.connection&quot
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

 

[itme]

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

 

[itme]

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

 

[bdotzour]

It's a SQL Server 7.0.

Connection string is like this:

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

 

[itme]

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.

 

[bdotzour]

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!

 

[itme]

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