October 16, 2002 at 11:57 am
Use Pubs
Go
Create table Balaji ( f1 int not null Primary key, f2 varchar(10) Not Null)
Go
Create table Balaji2 ( f1 int not null , f2 int Not Null , Constraint PK_Balaji2 Primary key (f1,f2))
Go
Use Northwind
Go
Create table Balaji ( f1 int not null Primary key, f2 varchar(10) Not Null)
Go
Create table Balaji2 ( f1 int not null , f2 int Not Null , Constraint PK_Balaji2 Primary key (f1,f2))
Go
Use Pubs
Go
Insert into Balaji (f1,f2) values (1, 'Test1')
Insert into Balaji (f1,f2) values (2, 'Test2')
Insert into Balaji (f1,f2) values (3, 'Test3')
Insert into Balaji2 (f1,f2) values (1, 1)
Insert into Balaji2 (f1,f2) values (2, 2)
Insert into Balaji2 (f1,f2) values (3, 3)
Go
Use Northwind
Go
Insert into Balaji (f1,f2) values (11, 'Test1')
Insert into Balaji (f1,f2) values (12, 'Test2')
Insert into Balaji (f1,f2) values (13, 'Test3')
Insert into Balaji2 (f1,f2) values (11, 1)
Insert into Balaji2 (f1,f2) values (12, 2)
Insert into Balaji2 (f1,f2) values (13, 3)
Go
Use pubs
Go
Create View vBalaji as
select * from pubs..balaji
union all
Select * from Northwind..balaji
go
Create View vBalaji2 as
select * from pubs..balaji2
union all
Select * from Northwind..balaji2
go
use Northwind
ALTER TABLE Balaji with check ADD Constraint chk_Balaji CHECK (f1 >= 1 and f1<= 10)
Go
use Pubs
ALTER TABLE Balaji with check ADD Constraint chk_Balaji CHECK (f1 >= 11 and f1<= 20)
Go
use Northwind
ALTER TABLE Balaji2 with check ADD Constraint chk_Balaji2 CHECK (f1 between 1 and 10 )
Go
use Pubs
ALTER TABLE Balaji2 with check ADD Constraint chk_Balaji2 CHECK (f1 between 1 and 10 )
Go
Select * from vBalaji where f1 = 1
Go
This query works fine. It just goes and query only the table Balaji in Northwind and bringing me the ouptput.
Select * from vBalaji where f1 = 1
Go
Where as this query goes and queries both Pubs and Northwind databases. Any one Please explain to correct the problem. It suppose to query only on Northwind database?
October 16, 2002 at 12:13 pm
OOPs ....Typo error . Disregard the previous one. Look in to this. Thanks
Use Pubs
Go
Create table Balaji ( f1 int not null Primary key, f2 varchar(10) Not Null)
Go
Create table Balaji2 ( f1 int not null , f2 int Not Null , Constraint PK_Balaji2 Primary key (f1,f2))
Go
Use Northwind
Go
Create table Balaji ( f1 int not null Primary key, f2 varchar(10) Not Null)
Go
Create table Balaji2 ( f1 int not null , f2 int Not Null , Constraint PK_Balaji2 Primary key (f1,f2))
Go
Use Pubs
Go
Insert into Balaji (f1,f2) values (1, 'Test1')
Insert into Balaji (f1,f2) values (2, 'Test2')
Insert into Balaji (f1,f2) values (3, 'Test3')
Insert into Balaji2 (f1,f2) values (1, 1)
Insert into Balaji2 (f1,f2) values (2, 2)
Insert into Balaji2 (f1,f2) values (3, 3)
Go
Use Northwind
Go
Insert into Balaji (f1,f2) values (11, 'Test1')
Insert into Balaji (f1,f2) values (12, 'Test2')
Insert into Balaji (f1,f2) values (13, 'Test3')
Insert into Balaji2 (f1,f2) values (11, 1)
Insert into Balaji2 (f1,f2) values (12, 2)
Insert into Balaji2 (f1,f2) values (13, 3)
Go
Use pubs
Go
Create View vBalaji as
select * from pubs..balaji
union all
Select * from Northwind..balaji
go
Create View vBalaji2 as
select * from pubs..balaji2
union all
Select * from Northwind..balaji2
go
use Northwind
ALTER TABLE Balaji with check ADD Constraint chk_Balaji CHECK (f1 >= 1 and f1<= 10)
Go
use Pubs
ALTER TABLE Balaji with check ADD Constraint chk_Balaji CHECK (f1 >= 11 and f1<= 20)
Go
use Northwind
ALTER TABLE Balaji2 with check ADD Constraint chk_Balaji2 CHECK (f1 between 1 and 10 )
Go
use Pubs
ALTER TABLE Balaji2 with check ADD Constraint chk_Balaji2 CHECK (f1 between 1 and 10 )
Go
Select * from vBalaji where f1 = 1
Go
This query works fine. It just goes and query only the table Balaji in Northwind and bringing me the ouptput.
Select * from vBalaji2 where f1 = 1 and f2 = 1
Go
This again works fine.
Select * from vBalaji2 where f1 = 1
Where as this query goes and queries both Pubs and Northwind databases. Any one Please explain to correct the problem. It suppose to query only on Northwind database?
October 16, 2002 at 12:55 pm
Please look in to this code
Use Pubs
Go
Create table Balaji ( f1 int not null Primary key, f2 varchar(10) Not Null)
Go
Create table Balaji2 ( f1 int not null , f2 int Not Null , Constraint PK_Balaji2 Primary key (f1,f2))
Go
Use Northwind
Go
Create table Balaji ( f1 int not null Primary key, f2 varchar(10) Not Null)
Go
Create table Balaji2 ( f1 int not null , f2 int Not Null , Constraint PK_Balaji2 Primary key (f1,f2))
Go
Use Northwind
Go
Insert into Balaji (f1,f2) values (1, 'Test1')
Insert into Balaji (f1,f2) values (2, 'Test2')
Insert into Balaji (f1,f2) values (3, 'Test3')
Insert into Balaji2 (f1,f2) values (1, 1)
Insert into Balaji2 (f1,f2) values (2, 2)
Insert into Balaji2 (f1,f2) values (3, 3)
Go
Use Pubs
Go
Insert into Balaji (f1,f2) values (11, 'Test1')
Insert into Balaji (f1,f2) values (12, 'Test2')
Insert into Balaji (f1,f2) values (13, 'Test3')
Insert into Balaji2 (f1,f2) values (11, 1)
Insert into Balaji2 (f1,f2) values (12, 2)
Insert into Balaji2 (f1,f2) values (13, 3)
Go
Use pubs
Go
Create View vBalaji as
select * from pubs..balaji
union all
Select * from Northwind..balaji
go
Create View vBalaji2 as
select * from pubs..balaji2
union all
Select * from Northwind..balaji2
go
use Northwind
ALTER TABLE Balaji with check ADD Constraint chk_Balaji CHECK (f1 >= 1 and f1<= 10)
Go
use Pubs
ALTER TABLE Balaji with check ADD Constraint chk_Balaji CHECK (f1 >= 11 and f1<= 20)
Go
use Northwind
ALTER TABLE Balaji2 with check ADD Constraint chk_Balaji2 CHECK (f1 between 1 and 10 )
Go
ALTER TABLE Balaji2 with check ADD Constraint Uq_Balaji2 Unique (f1)
Go
use Pubs
ALTER TABLE Balaji2 with check ADD Constraint chk_Balaji2 CHECK (f1 between 11 and 20 )
Go
ALTER TABLE Balaji2 with check ADD Constraint Uq_Balaji2 Unique (f1)
Go
Select * from vBalaji where f1 = 1
Go
Select * from vBalaji2 where f1 = 1 and f2 = 1
Both this works fine. It uses proper optimizers and queries only from Northwind database. For better understanding look at Query plan and optimizer.
Select * from vBalaji2 where f1 = 1
Thsi query doesn't work right. It queries from both pubs and Northwind database.
Please advise.
Thanks
October 17, 2002 at 2:41 am
Hi, on balji you have a primary key on only the first column. In the second table you have a primary key on both columns.
October 17, 2002 at 7:09 am
Hi Klas,
That is true, i want to know why query optimizer is not choosing the query plan to go and collect data only from Northwind when it is a composite primary key . If you see on Balaji table i have primary key on only one column and chooses proper query plan and queries only on Northwind database.
where as in Balaji2 it queries both Northwind and Pubs database even though the data is only in northwind. Look in to query plan to understand the problem. Please do advise .
Thanks
October 18, 2002 at 3:17 am
Hi, wat i ment is the view baji2 is a union on northwind and pubs. The primary key is built on 2 fields so if you only give one value the optimiser can not uniquely find a row and have to scan the index (which is in both databases) to find it. When you give 2 values the optimiser has a unique value and can directly point to the index in the correct database.
regards,
Klaas-Jan
October 18, 2002 at 9:50 am
Hi Klaas,
I am using a Partioned view and if you look in detail part of the primary key column f1 got a check constraint in it. As for Microsoft the query optimizer will first look for check constraint and then it goes and check for index optimizer. My question is why it is going and scanning the pubs database when the data requested is in Northwind only.
I figured out , what is happening. Even theough execution plans shows that it is scanning the Pubs database also , actually it is not scanning the table at all. If you see Statistics IO , it is not scanning the table, it just tries to check in the Pubs..Balaji2 table , because of the Check constraint it just comes out without scanning it. If you notice Scan count of Northwind..Balaji2 is 1 and scancount for Pubs..Balaji2 is 0.
Conclusion is it not affecting the performance and query optimizer works pretty good. Thanks for your time in this issue.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply