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

 Try this...

SELECT 
sr.ServiceRateID,
sp.Name,
spd.District,
sr.CurrentPlan as "Current"

FROM SQLLtdbe.dbo.tblServiceRates sr (nolock)

INNER JOIN SQLLtdbe.dbo.tblServicePlans sp (nolock)
ON sp.ServicePlanID = sr.SevicePlanIDNo

INNER JOIN SQLLtdbe.dbo.tblServicePlanDistrict spd (nolock)
ON spd.ServPlanDistrictID = sr.DistrictIDNo
 
ORDER BY sr.CurrentPlan DESC, spd.District, sp.Name


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



                                                                                                                                                                      
                                                                                                                                                                                                             ----------------------------------------------------------------
The information contained in this e-mail message and any file, document, previous e-mail message and/or attachment transmitted 
herewith is confidential and may be legally privileged. It is intended solely for the private use of the addressee and must not be 
disclosed to or used by anyone other than the addressee. If you receive this transmission by error, please immediately notify the 
sender by reply e-mail and destroy the original transmission and its attachments without reading or saving it in any manner.  If you 
are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any 
disclosure, copying, distribution or use of any of the information contained in or attached to this transmission is STRICTLY 
PROHIBITED. E-mail transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses.
The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of 
email transmission. Users and employees of the e-mail system are expressly required not to make defamatory statements and not 
to infringe or authorize any infringement of copyright or any other legal right by email communications. Any such communication is 
contrary to company policy. The company will not accept any liability in respect of such communication.