data retrival time

How can i speed up the return time from search between 2 different server?

I am currently using SQL 2000 and SQL 2005 With the same query, the searching time is much faster to select between database within the same server instead of two different one. Is there something that i can do in the server configuration to speed up the searching time?

Thanks in advance

Could you be more specific? The query in question, the execution plans and the table metadata, including the indexes plus some basic data on the rough row counts/value distributions would be very helpful too.|||

Hi Tengiz,

I am Calling a SP with Views inside that will cross over defferent Server,

For Example,

in Server 2005 i have a SP called spSample in the data base called dbDEV

within that SP I have View1, View2 and View3 in my databse , Tempdt, in Server 2000

so inside my SP it'll be something like the following

CREATE PROCEDURE [dbo].[spSample ]
(
@.Key_ID int
)
As
SELECT *

FROM tblBK WITH (NOLOCK)
right JOIN dbo.Tempdt.View1 ON dbo.dbDEV.tblBK.BK_ID = dbo.Tempdt.View1.BK_FK
left JOIN dbo.Tempdt.View2 ON dbo.dbDEV.tblBK.State_FK = dbo.Tempdt.View2.State_ID
left JOIN dbo.Tempdt.View3 ON dbo.dbDEV.tblBK.Country_FK = dbo.Tempdt.View3 .Country_ID
where (dbo.dbDEV.tblBK.Key_ID = @.Key_ID )
ORDER BY tblBK.POL

GO

the sp Works find if both database was with in the same server, but was very slow if i moved on of the database to a different server. What can i do to make returning data time faster?

Thanks

|||

Since the SP is created in the database dbDEV and the 'local' (with respect to the database the SP is created in) table tblBK participates in an outer join in the way that assumes that the entire table also participating in the join (View1) will be pulled in order to produce the result you request, depending on the size of the table this could indeed take a long time.

Do you really need the following outer join here: tblBK RIGHT join View1? Considering the where clause on tblBK.Key_ID it doesn’t look like having the tblBK participate in a right join matters here since the rows where tblBK.Key_ID is NULL are filtered out anyway.

Hence if you simply remove the 'RIGHT' from the query text and use the normal inner join instead you should see the improvement in the perf and see the results that you request:

CREATE PROCEDURE [dbo].[spSample ]
(
@.Key_ID int
)
As
SELECT *

FROM tblBK WITH (NOLOCK)
JOIN dbo.Tempdt.View1 ON dbo.dbDEV.tblBK.BK_ID = dbo.Tempdt.View1.BK_FK
left JOIN dbo.Tempdt.View2 ON dbo.dbDEV.tblBK.State_FK = dbo.Tempdt.View2.State_ID
left JOIN dbo.Tempdt.View3 ON dbo.dbDEV.tblBK.Country_FK = dbo.Tempdt.View3 .Country_ID
where (dbo.dbDEV.tblBK.Key_ID = @.Key_ID )
ORDER BY tblBK.POL

Data not visible??

I recently imported some Access tables into SQL Server. I can go into enterprise manager and return all rows and the data is there.

I build an ASP page to edit the data. After the connection the data for the record is displayed in a form. For some reason some fields are not coming through?

Is this a problem when converting to SQL Server. It just doesn't make sense that the table in Enterprise manager says the field is populated but when I do a pull via ASP the data doesn't come through.

Does anyone know anything about this?

Any help or pointing in the right direction would be greatly appreciated.

Thanks,

JWExecute your query in Query Analyzer. Compare the results to those obtained through your ASP page.|||There is nothing special about the query. It's select all from the table but by the vaiable ID number that is passed.

See the weird thing is, is that it doesn't bomb out or anything and brings back some fields in the recordset but not all. But then I return all rows in query analyzer and the data for those fields is populated.|||Please post the ASP code that executes the query and displays the data from the recordset.|||<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=golfinam;UID=golfinam;PWD=XXXXXX;DATABASE=XXXX XX"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM tbl_Courses where CourseID = " & request.querystring("CourseID")
objRS.Open strSQL, objConn%>
<% if ObjRS.eof then %><% else %>

'Then this is where the form is with each forms default set to a field,
<textarea rows="7" name="PTPDirections" cols="53"><%=objRS("PTPDirections")%></textarea>

<%End If%>
<%'Close the Recordset object
objRS.Close

'Delete the Recordset Object
Set objRS = Nothing

'Close the Connection object
objConn.Close

'Delete the Connection Object
Set objConn = Nothing
%>|||Is there a setting or something in the import that would cause it to act this way?

Data not showing for some users

Hi there,

Some users, when they select a table and try to "Return all rows" in Enterprise Manager, they get the word Binary instead of the data that should be there. The table has 4 columns, an ID column and three data columns. The ID field data is correct but the data in the other columns just show the word 'Binary'.

I got the user to connect to the database using the 'sa' login and the problem is still there. I can connect from my machine and see the data fine. Only one user is experiencing these problems.

The problem occurs with all the tables in the database. Any field that should have text data, e.g. nchar, nvarchar, etc., is showing 'Binary'. Fields that have numeric or date types show fine.

If the users runs a select query from Query Analyser, then the data appears fine.

Any help??This problem seems to have occured since i tried to install a SQLBase odbc driver. The data in the database is fine but Enterprise Manager seems to be corrupted.

Any help??

Data Missing from Report

I have created a RS report that contains 2 datasets and 2 lists. This is a
consolidated corporate report grouped to return data for 53 companies (one
page per company). Both datasets return data for all 53 companies when
viewing from the data pane. However, when the report is viewed in the
preview pane (or rendered), the 1st dataset returns data for all 53
companies, but there are 8 of the companies missing data from the 2nd dataset.
The problem varies in severity depending on the Begin and End date
parameters used to generate the report. What is strange is that a narrower
date range (several days) leaves off more data than a broader date range
(full month).
Anyone have any ideas?Has anyone out there ever experienced data missing from a RS generated report
that is returned when the same query is run in Query Analyzer?
"Lynn" wrote:
> I have created a RS report that contains 2 datasets and 2 lists. This is a
> consolidated corporate report grouped to return data for 53 companies (one
> page per company). Both datasets return data for all 53 companies when
> viewing from the data pane. However, when the report is viewed in the
> preview pane (or rendered), the 1st dataset returns data for all 53
> companies, but there are 8 of the companies missing data from the 2nd dataset.
> The problem varies in severity depending on the Begin and End date
> parameters used to generate the report. What is strange is that a narrower
> date range (several days) leaves off more data than a broader date range
> (full month).
> Anyone have any ideas?
>
>