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

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