David Emerson
newsgrps at dalyn.co.nz
Wed Dec 5 09:40:02 CST 2007
Thanks for the response. In getting info to answer your questions I found that the second db is not actually referring to the first database for this query at all. This makes it even stranger as the database structures should be identical and the second database is about 3 times smaller than the first one. I have tried to reduce the data returned by running sprocs that put data into tables first (not true SQL temporary tables, but ones already created which have their records deleted prior to being filled with new records for the report), then use these tables for the main sproc. What I might try is replacing the views and sprocs from the second database with scripts from the first database. See if this helps. David At 5/12/2007, you wrote: >What do the query plan execution times look like? How do you ref the >data when you call it from the second db (the 5 min running one?) ? > >Have you tried minimizing the returning data and utilizing the >openquery method? > >On 12/3/07, David Emerson <newsgrps at dalyn.co.nz> wrote: > > I have an SQL2000 database with a number of tables. I have a second > > database which has a number of identically structured tables (but > > with different data) as well as tables with data that is used by both > > databases. Both databases are on the same server. > > > > In both databases I have a number of sprocs that join data from their > > unique data tables and the common tables. The sprocs work fine in > > the database that the houses the shared tables. However, the sprocs > > in the database which doesn't house the shared tables run very slow > > (for example in the second database a sproc may take 15 seconds to > > run, while on the first it might take over 5 minutes). > > > > What are the things I can do to speed up the sprocs that are pulling > > data from the other database? > > > > Combining the databases into one file is not an option as for company > > reasons they require the data separate. > > Repeating the shared tables in each database is not an option as this > > would double up the maintenance required to update the data. > > > > Regards > > > > David Emerson > > Dalyn Software Ltd > > Wellington, New Zealand > > > > _______________________________________________ > > dba-SQLServer mailing list > > dba-SQLServer at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > > http://www.databaseadvisors.com > > > > > > >-- >-Francisco >http://sqlthis.blogspot.com | Tsql and More... >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com