Indexed View

[dhoward007]

I have a linked server connection to a sybase table and have created a view with a join that joins local MSSQL tables .. I am trying to create an indexed view but cannot. Is this even possible in this situation? I read where you can do this with mssql but can you do it using a linked server table on sybase? I am looking for ways to speed the query up ..
any suggestions would be great!!

 

[grahams]

Doesn't look like you can do what you're attempting - BOL says:

"All base tables referenced by the view must be in the same database as the view and have the same owner as the view"

Unless someone knows different....


Graham

 

[dhoward007]

Thanks grahams.. I can create a view with the linked server table joining my mssql tables.. That works fine. Do you know of any way to speed this view up.. It is really slow..

 

[grahams]

Off the top of my head (and without knowing anything about where these servers are, or the view definition), I suppose you could feasibly replicate the Sybase table to a copy in your local DB, then use that for the view and index it. This depends, of course, on how volatile the data in your remote table is and how up to date the view needs to be.

 

[JamesLean]

What version of SQL Server are you using? You can only create indexed views on SQL Server 2000 Enterprise or Developer editions (NOT Standard edition).

 

[dhoward007]

Thanks Grahams.. The tables change frequently and I would like to have realtime views. I tried to copy over the tables every 30 minutes and just query local tables but that is not working well because the changes are not reflected until the next import.

Can you replicate a sybase table to MSSQL? or are you speaking about just doing a select * into tablename from linked server table. every so often?

JamesLean.. I am running SQL 2000 Enterprise Edition.

 

[fluteplr]

can you post the code of your view on the remote database?

 

[tlbroadbent]

Indexed views can be created in SQL Server 2000 Standard Edition. However, as Graham noted, not on views with base tables from different databases or servers.

When creating a view that links local and remote tables, you must expect some performance problems. You might want to try using OpenQuery. OpenQuery causes the query to execute on the remote server. It will be most effective if you can filter rows on the remote server and only return necessary columns rather than using * to return all columns.

Select a.col1, a.col2, b.col3, b.col4
From LocalDB.dbo.LocalTable a
Join openquery(RemoteServer,'Select col1, col2, col3, col4, ..., colN From RemoteDB.dbo.RemoteTbl Where <criteria>') b
On a.col1=b.col1

Another feature of SQL Server is the ability to specify JOIN hints. The REMOTE hint &quot;Specifies that the join operation is performed on the site of the right table. This is useful when the left table is a local table and the right table is a remote table. REMOTE should be used only when the left table has fewer rows than the right table.&quot; (See SQL BOL)

Select a.col1, a.col2, b.col3, b.col4
From LocalDB.dbo.LocalTable a
Inner Remote Join RemoteServer.RemoteDB.dbo.RemoteTbl b
On a.col1=b.col1
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[JamesLean]

I'm afraid I have to take issue with Terry on this one. Reference to BOL:

You can create indexed views only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft SQL Server 2000 Developer Edition.

and then:

A view must meet these requirements before you can create a clustered index on it:

...

All base tables referenced by the view must be in the same database as the view and have the same owner as the view.

So although you have Enterprise Edition, you cannot create an indexed view in your situation. I would agree with Terry's comments about OPENQUERY though!

 

[tlbroadbent]

BOL was incorrect. The updated version of BOL contains the following.

Note: Indexed views can be created in any edition of SQL Server 2000. In SQL Server 2000 Enterprise Edition, the query optimizer will automatically consider the indexed view. To use an indexed view in all other editions, the NOEXPAND hint must be used.

Check the following link for the SQL BOL Update download.

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[JamesLean]

Apologies Terry, I stand corrected. That'll teach me to believe the manuals... ;-)

 

[dhoward007]

Below is a copy of the view.

The only table that is local is DBAs. The other two are located on Sybase.

CREATE VIEW vwOnCall AS

select a.startTime,
a.endTime,
b.firstName,
b.lastName,
c.workphone,
c.pagermail,
c.cellphone1,
c.nexteldirectid,
c.email
from sybaseconfigs.ConfigsDb.dbnCall a
JOIN
sybaseconfigs.ConfigsDb.dbo.Users b
ON a.userid = b.userid
JOIN DBAs c
ON b.mailid = c.Email
WHERE (getdate() between a.startTime and a.endTime)

 

[tlbroadbent]

You should be able to use OPENQUERY to select from the SYBASE tables. Openquery can improve performance because more work is done on the remote server, reducing the local workload and network traffic.

CREATE VIEW vwOnCall AS

Select
r.startTime, r.endTime, r.firstName, r.lastName,
c.workphone, c.pagermail, c.cellphone1, c.nexteldirectid, c.email
From
OpenQuery(sybaseconfigs,
'Select a.userid, a.starttime, a.endtime, b.firstname, b.lastname, b.mailid
From ConfigsDb.dbnCall a
Join ConfigsDb.dbo.Users b
On a.userid = b.userid
Where getdate() between a.startTime And a.endTime') r
Join DBAs c
On r.mailid = c.Email Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[dhoward007]

tlbroadbent.. The example you posted looks great but for some reason when I try to run it, I get an erro on the JOIN.. Are joins allowed using openquery??

I can take your query inside the quotes and it works but when I use openquery, I get an invalid syntax near join?

Any clues??

 

[tlbroadbent]

The JOIN should work in OpenQuery. However, Sybase may not accept the ANSI JOIN syntax. Change the query as follows.

CREATE VIEW vwOnCall AS

Select
r.startTime, r.endTime, r.firstName, r.lastName,
c.workphone, c.pagermail, c.cellphone1, c.nexteldirectid, c.email
From
OpenQuery(sybaseconfigs,
'Select a.userid, a.starttime, a.endtime, b.firstname, b.lastname, b.mailid
From ConfigsDb.dbnCall a, ConfigsDb.dbo.Users b
Where a.userid = b.userid
And getdate() between a.startTime And a.endTime') r
Join DBAs c
On r.mailid = c.Email Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[dhoward007]

WoW!!!!! tlbroadbent.. This was 100%faster.. It works great!!!!


This query went from 30 seconds to not even 1 second.. That is amazing.. I will start rewriting some of my queries .. I have been looking for a quicker way to get results.. This seems to be it..

Thank you very much..

 

[sguslan]

Wow, great insights by TLBroadbent ..