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

David Emerson newsgrps at dalyn.co.nz
Sun Jul 24 11:44:19 CDT 2005


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



More information about the dba-SQLServer mailing list