CODEWITHSUNDEEP
sqlsql-serverselectsql-view
seesharp
seesharp

Reputation: 23

Select from view takes too long

I have a query against a table that contains like 2 million rows using linked server.

Select * from OPENQUERY(LinkedServerName,
'SELECT
 PV.col1
,PV.col2
,PV.col3
,VTR.col1
,CTR.col1
,PSR.col1
FROM 
LinkedDbName.dbo.tbl1 PV
INNER JOIN LinkedDbName.dbo.tbl2 VTR 
ON PV.col_id = VTR.col_id
INNER JOIN LinkedDbName.dbo.tbl3 CTR 
ON PV.col_id = CTR.col_id
INNER JOIN LinkedDbName.dbo.tbl4 PSR 
ON PV.col_id = PSR.col_id
WHERE
PV.col_id = ''80C53C9B-6272-11DA-BB34-000E0C7F3ED2''')

That query results into 365 rows and is executed within 0 second. However when I make that query into a view it runs for about minimum of 20 seconds and sometimes it reaches to 40 seconds tops.

Here's my create view script

CREATE VIEW [dbo].[myview]
AS
Select * from OPENQUERY(LinkedServerName,
'SELECT
 PV.col1
,PV.col2
,PV.col3
,VTR.col1
,CTR.col1
,PSR.col1
FROM 
LinkedDbName.dbo.tbl1 PV
INNER JOIN LinkedDbName.dbo.tbl2 VTR 
ON PV.col_id = VTR.col_id
INNER JOIN LinkedDbName.dbo.tbl3 CTR 
ON PV.col_id = CTR.col_id
INNER JOIN LinkedDbName.dbo.tbl4 PSR 
ON PV.col_id = PSR.col_id')

then

Select * from myview where PV.col_id = '80C53C9B-6272-11DA-BB34-000E0C7F3ED2'

Any idea ? Thanks !

Upvotes: 1

Views: 1190

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Your queries are quite different. In the first, the where clause is part of the SQL statement passed to OPENQUERY(). This has two important effects:

  • The amount of data returned is much smaller, only being the rows that match the condition.
  • The query can be optimized with the WHERE clause.

If you need to share the table, I might suggest that you make a copy on the local server -- either using replication or scheduling a job to copy it over.

Upvotes: 3

Related Questions