Performance on view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance on view

Author

Topic

Jaap
Starting Member

19 Posts

Posted - 2008-04-09 : 05:42:12

Hey there,

I have a view on 5 tables.
The first [tblOrder] 62000 records
The second joined on the first [tblOrderRegel] 2140000 records
The third joined on the second [tblAssortimentSamenstelling] 5800 records
The fourth [tblArtikelVerpakking] 1650 records
And the last also joined on the third [tblAssortiment] 30 records

When I use the next query
SELECT *
FROM [dbBIBSMonitor].[dbo].[vwOrderRegel_1]
WHERE IDOrder in(SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6)
GO

It will take more than 60 seconds before the resuld is there

The sub select 'SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6' returns a list of ID's. When I make the same select with this ID's instead of the sub select the result returns in 1 second

SELECT *
FROM [dbBIBSMonitor].[dbo].[vwOrderRegel_1]
WHERE IDOrder IN( 4296, 4374, 4433, 9261, 9714, 9732, 9876, 10182, 10223, 17677, 18117, 18393, 28116, 28125, 28246, 31263, 32384, 33482, 33605, 33721, 33792, 36838, 38000, 40579, 40920, 50205, 52292, 52884)
GO

In the first select I have on all tables en index SCAN on the primairy Index with Hash Match/Inner Join to match.

In the second select I have on all tables en index SEEK on the primairy Index with Nested loop/Inner Join to match.

For the sub select 'SELECT IDOrder FROM [vwOrder] WHERE IDRelatie = 6' is not the primairy key used but an other Unique index Containing three columns.
[IDOrder] (also the primairy Key),
[IDStatus] (1, 2 or 3. 90% = 3),
and a date field.

In the view on the subselect is an UNION of three selects.
SELECT IDOrder, Date_1
From tblOrder
WHERE IDStatus = 1
UNION
SELECT IDOrder, Date_2
From tblOrder
WHERE IDStatus = 2
UNION
SELECT IDOrder, Date_2
From tblOrder
WHERE IDStatus = 3

Thats the reason he usses the Unique Index

Is using this index the reason that he does not SEEK on the first table (also tblOrder) of the view in my select???
In other words: Does SQL-server can use only 1 index on a table for a SEEK?

I hope somebody can give me an answer on this complex story.

Thanks Jaap

By the way. I have already tried selects with a INNNER JOIN in stead of a sub query and also a corralated sub query.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-04-09 : 06:43:19

Hi,

try this...
create indexes on IDOrder of vwOrderRegel_1 and vwOrder views, then use joins instead of subquery.

Jaap
Starting Member

19 Posts

Posted - 2008-04-09 : 07:06:17

Thanks for replying.

I already tried this. Problemm was that the views are made on an other database. You can only make Indexed views when te views are in the same database as the tables are.
But I first have made some indexed views on the parent database and then made the selects on these indexed view.
No changes. But I was wondering, the execution plan didn't show me that he ussed the index on the view but still on the table indexes.
Is this normal or does he normal shows view-indexes when he use it?

Jaap

Jaap
Starting Member

19 Posts

Posted - 2008-04-09 : 10:33:21

I think I can declare the problemm, but I still not have a solution.
The optimiser suggets that the result of the sub query can return a big amount of ID's that he prevere to use a SCAN on the primairy index.

Jaap

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources