by e.. » Sat, 03 Feb 2001 03:49:51
Sent via Deja.com
http://www.deja.com/
by Doo » Sat, 03 Feb 2001 05:56:59
--
Doo
Senior Data Architect / DBA
PlanetJam Media Group
> Sent via Deja.com
> http://www.deja.com/
by e.. » Sat, 03 Feb 2001 06:38:09
> --
> Doo
> Senior Data Architect / DBA
> PlanetJam Media Group
Sent via Deja.comQuote:> > I am using SQL 7.0 SP2 and am trying partitioned views for the first
> > time. I have about 20 2 million row tables with the proper check
> > constraints. When I run a query directly against one of the tables
it
> > comes back in 8 seconds. When I run the same query against the
> > partitioned view, it takes 1.5 minutes. I would not have expected
such
> > a large difference. Does this sound familiar to anyone?
> > Sent via Deja.com
> > http://www.deja.com/
by Doo » Sat, 03 Feb 2001 07:15:25
Since you are SELECTing from the key you are accessing *all the tables* in
the view...HUGE difference in performance.
--
Doo
Senior Data Architect / DBA
PlanetJam Media Group
> > Depends...if you are selecting against the columns that the tables are
> > partitioned on it seems odd. If not it sounds right about right.
> > --
> > Doo
> > Senior Data Architect / DBA
> > PlanetJam Media Group
> > > I am using SQL 7.0 SP2 and am trying partitioned views for the first
> > > time. I have about 20 2 million row tables with the proper check
> > > constraints. When I run a query directly against one of the tables
> it
> > > comes back in 8 seconds. When I run the same query against the
> > > partitioned view, it takes 1.5 minutes. I would not have expected
> such
> > > a large difference. Does this sound familiar to anyone?
> > > Sent via Deja.com
> > > http://www.deja.com/
> Sent via Deja.com
> http://www.deja.com/
by Doo » Sat, 03 Feb 2001 07:46:48
--
Doo
Senior Data Architect / DBA
PlanetJam Media Group
> Since you are SELECTing from the key you are accessing *all the tables* in
> the view...HUGE difference in performance.
> --
> Doo
> Senior Data Architect / DBA
> PlanetJam Media Group
> > I am selecting on two columns, one is the column that the data is
> > partitioned on and the other one has an index built on it.
> > > Depends...if you are selecting against the columns that the tables are
> > > partitioned on it seems odd. If not it sounds right about right.
> > > --
> > > Doo
> > > Senior Data Architect / DBA
> > > PlanetJam Media Group
> > > > I am using SQL 7.0 SP2 and am trying partitioned views for the first
> > > > time. I have about 20 2 million row tables with the proper check
> > > > constraints. When I run a query directly against one of the tables
> > it
> > > > comes back in 8 seconds. When I run the same query against the
> > > > partitioned view, it takes 1.5 minutes. I would not have expected
> > such
> > > > a large difference. Does this sound familiar to anyone?
> > > > Sent via Deja.com
> > > > http://www.deja.com/
> > Sent via Deja.com
> > http://www.deja.com/
by e.. » Sun, 04 Feb 2001 06:30:52
Actually, I did have the partitioned column in the where clause.
However, I finally did get it to work. I had to create an index on each
table for the partitioned column, even though because of the check
constraint it only could have one value! This forced the other where
clause checks to be done prior to an expensive hash step later in the
query. Thanks for your help.
> --
> Doo
> Senior Data Architect / DBA
> PlanetJam Media Group
> > If you SELECT only from the columns that make the partition then
*only
> those
> > tables* will be accessed by the QA.
> > Since you are SELECTing from the key you are accessing *all the
tables* in
> > the view...HUGE difference in performance.
> > --
> > Doo
> > Senior Data Architect / DBA
> > PlanetJam Media Group
> > > > Depends...if you are selecting against the columns that the
tables are
> > > > partitioned on it seems odd. If not it sounds right about
right.
> > > > --
> > > > Doo
> > > > Senior Data Architect / DBA
> > > > PlanetJam Media Group
> > > > > I am using SQL 7.0 SP2 and am trying partitioned views for
the first
> > > > > time. I have about 20 2 million row tables with the proper
check
> > > > > constraints. When I run a query directly against one of the
tables
> > > it
> > > > > comes back in 8 seconds. When I run the same query against the
> > > > > partitioned view, it takes 1.5 minutes. I would not have
expected
> > > such
> > > > > a large difference. Does this sound familiar to anyone?
> > > > > Sent via Deja.com
> > > > > http://www.deja.com/
> > > Sent via Deja.com
> > > http://www.deja.com/
1. Partitioned View Performance Problem
Our database has a view that combines several tables with
sales transactions. We had been using a separate physical
table for each year, but when we consolidated a number of
the older years into a single "archive" table we have
suffered a serious degradation inperformance.
Among other things, inserts and updates became impossible
without an "Instead Of" trigger, and several simple SP's
that used to return data nearly instantaneously now take
on the order of several seconds. When processing against
100K rows in an AR table a process that used to take a
couple of hours now will take the better part of a week!
Estimated, of course! ;-) It seems that the CPU usage on
the server is way high (80-90%) when any of these hard hit
queries are run.
Any ideas on what could be causing this terrible drag, or
what we can look for in the view that might have caused
this?
TIA
3. Partitioned Views performance
5. Partitioned view performance puzzle
6. 'No current record' error when using Seek on a Data Control's recordset
7. MS SQL7 Partitioned View Performance
8. SQL Server Enterprise Manager Miscounting Rows?
9. performance of Insert with Partitioned View
10. Performance issue on Partition view
11. Performance of partitioned views
12. O7 to O8 Partitioned Views to Partitioned Tables
13. partition views vs partition tables