[dba-SQLServer] Getting Data from another Database (Speed)

Hi David:

My knowledge of ADP applications is non-existent. I have only worked with
standard Access applications that attach to MS SQL/Oracle DBs. There must be
some place in the ADP model that will allow you to set recordset parameters.

Do you have access to the recordset CursorType?

With rsMyRecordset
 .CursorType = adOpenStatic
End With

I believe that it defaults to 'adOpenDynamic' and that is where your problem
would be.

HTH
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: Sunday, July 24, 2005 10:59 AM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Getting Data from another Database (Speed)

Thanks for the response.  Where do I set the stored procedure to Static?

David

At 25/07/2005, you wrote:
>Hi David:
>
>How is the receiving recordset configured? If the recordset is set to
>dynamic-linking then the processing is going to be slow. Make sure the
>receiving recordset is set to static.
>
>HTH
>Jim
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
>Emerson
>Sent: Sunday, July 24, 2005 9:44 AM
>To: dba-SQLServer at databaseadvisors.com
>Subject: [dba-SQLServer] Getting Data from another Database (Speed)
>
>Group,
>
>Access XP adp, SQL2000.
>
>I have a stored procedure which is the rowsource for a combo box thus:
>
>ALTER PROCEDURE spcbofrmCustServicePlansServicePlan
>
>AS
>SET NOCOUNT ON
>
>SELECT SQLLtdbe.dbo.tblServiceRates.ServiceRateID,
>SQLLtdbe.dbo.tblServicePlans.Name,
>          SQLLtdbe.dbo.tblServicePlanDistrict.District,
>SQLLtdbe.dbo.tblServiceRates.CurrentPlan as "Current"
>FROM SQLLtdbe.dbo.tblServicePlanDistrict INNER JOIN
>(SQLLtdbe.dbo.tblServicePlans INNER JOIN SQLLtdbe.dbo.tblServiceRates ON
>          (SQLLtdbe.dbo.tblServicePlans.ServicePlanID =
>SQLLtdbe.dbo.tblServiceRates.ServicePlanIDNo)) ON
>          (SQLLtdbe.dbo.tblServicePlanDistrict.ServPlanDistrictID =
>SQLLtdbe.dbo.tblServiceRates.DisctrictIDNo)
>ORDER BY SQLLtdbe.dbo.tblServiceRates.CurrentPlan DESC,
>SQLLtdbe.dbo.tblServicePlanDistrict.District,
>          SQLLtdbe.dbo.tblServicePlans.Name
>
>It returns about 3600 records.  This runs ok when the adp is connected to
>SQLLtdbe.  However, when I connect to another SQL database which also has
>the same sproc, it takes over 1/2 a minute from when the drop down list is
>opened to when a selected item appears in the combo box.  The screen seems
>to freeze between selecting the item and when the list automatically closes
>and the item is left in the box.
>
>It seems to be an issue of time taken to get all the data from the other
>database to populate the combobox.  Are there any tips in speeding up the
>process (apart from reducing the number of items in the list)?
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com