| Author |
SQL View Performance
|
|
| Auday Alwash 2005-11-24, 3:23 am |
| Hi all,
SQL Server 2000 service pack 3 running on Quad zeon windows 2003 server
with 6GB of ram.
A few months ago we split our databases into operational and completed
data...
So now instead of doing select * from tblTest, we do select * from
tblTestboth where tblTestboth is a view like this:
Select *
From Operational.dbo.tbltest
UNION
Select *
From Archive.dbo.tbltest
The tables on both databases are heavily indexed but it still takes much
longer to do the select * from tblTestBoth than it is to do
Select *
From Operational.dbo.tbltest
UNION
Select *
From Archive.dbo.tbltest
Can anyone please tell me why that is? We are starting to get timeouts
regularly...
Should I use indexed views even though the data is inserted/updated
multiple hundres of times per second?
Thank you very much in advance for your help...
Regards,
Auday
*** Sent via Developersdex http://www.droptable.com ***
| |
| Serge Rielau 2005-11-24, 3:23 am |
| Auday Alwash wrote:
> Hi all,
>
> SQL Server 2000 service pack 3 running on Quad zeon windows 2003 server
> with 6GB of ram.
>
> A few months ago we split our databases into operational and completed
> data...
>
> So now instead of doing select * from tblTest, we do select * from
> tblTestboth where tblTestboth is a view like this:
>
> Select *
> From Operational.dbo.tbltest
> UNION
> Select *
> From Archive.dbo.tbltest
>
> The tables on both databases are heavily indexed but it still takes much
> longer to do the select * from tblTestBoth than it is to do
>
> Select *
> From Operational.dbo.tbltest
> UNION
> Select *
> From Archive.dbo.tbltest
>
> Can anyone please tell me why that is? We are starting to get timeouts
> regularly...
>
> Should I use indexed views even though the data is inserted/updated
> multiple hundres of times per second?
>
> Thank you very much in advance for your help...
>
> Regards,
>
> Auday
>
> *** Sent via Developersdex http://www.droptable.com ***
You want UNION ALL
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
| |
| Madhivanan 2005-11-24, 9:23 am |
| UNION ALL is more efficient that UNION as UNION will unnecessarily
select Distinct and Order the data
Madhivanan
| |
|
| You also will want to change the view to an partitioned view. Is will allow
queries against the constraint to only run against the table it needs and
not both.
"Madhivanan" < madhivanan2001@gmail
.com> wrote in message
news:1132841546.627137.101470@g44g2000cwa.googlegroups.com...
> UNION ALL is more efficient that UNION as UNION will unnecessarily
> select Distinct and Order the data
>
> Madhivanan
>
|
|
|
|