Combining results - Performance Issues

Hi,
I am creating a view in the following way
CREATE VIEW TableView
AS
SELECT OriginalTable.*
FROM OriginalTable
WHERE ChangedFlag is null
UNION ALL
SELECT TableWithChanges.*
FROM TableWithChanges
OriginalTable and TableWithChanges have identical schemas. The data is in
OriginalTable and queries are run against it. However when I want to analyze
the query output with some changes I put the changed rows in
TableWithChanges and now run the queries against TableView. New and changed
rows are in TableWithChanges. Deletions and changes in OriginalTable are
handled by setting ChangedFlag = 1.
This way my earlier queries can keep running against OriginalTable and my
simulations can run against TableView.
The queries I run involve joins between many such tables, and sometimes self
joins too.
Problem: Performance is severely hit when I run queries against the view.
Specifically I observe that SQL Server does lot of processor intensive
activity. A query that was completing in 5 secs now is running for about 30
minutes (and has not completed yet). SQL Server is consuming close to 100%
CPU all this while.
Is there a better way in which I can combine the two data sets without
affecting performance to such an extent?
Please help me out here.
Thanks,
NitinPartitioned view is what you want. Though, you must follow its strict
guideline in order to get the benefits.
http://msdn.microsoft.com/library/e...des_06_17zr.asp
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am creating a view in the following way
> CREATE VIEW TableView
> AS
> SELECT OriginalTable.*
> FROM OriginalTable
> WHERE ChangedFlag is null
> UNION ALL
> SELECT TableWithChanges.*
> FROM TableWithChanges
> OriginalTable and TableWithChanges have identical schemas. The data is in
> OriginalTable and queries are run against it. However when I want to
> analyze the query output with some changes I put the changed rows in
> TableWithChanges and now run the queries against TableView. New and
> changed rows are in TableWithChanges. Deletions and changes in
> OriginalTable are handled by setting ChangedFlag = 1.
> This way my earlier queries can keep running against OriginalTable and my
> simulations can run against TableView.
> The queries I run involve joins between many such tables, and sometimes
> self joins too.
> Problem: Performance is severely hit when I run queries against the view.
> Specifically I observe that SQL Server does lot of processor intensive
> activity. A query that was completing in 5 secs now is running for about
> 30 minutes (and has not completed yet). SQL Server is consuming close to
> 100% CPU all this while.
> Is there a better way in which I can combine the two data sets without
> affecting performance to such an extent?
> Please help me out here.
> Thanks,
> Nitin
>|||Thanks OJ,
I have a explicit where clause [WHERE ChangedFlag is null] instead of the
check constraint. Will I give some better performance if I use check
constraints instead.
Also ChangedFlag column in this case is not a primary key column.
Is there any other trick to combine data?
Thanks,
Nitin
"oj" <nospam_ojngo@.home.com> wrote in message
news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
> Partitioned view is what you want. Though, you must follow its strict
> guideline in order to get the benefits.
> http://msdn.microsoft.com/library/e...des_06_17zr.asp
>
> --
> -oj
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:e7AWpMovFHA.4032@.TK2MSFTNGP15.phx.gbl...
>|||If you don't follow the guideline, you don't have a partitioned view. Thus,
sqlserver *will* be forced to scan every single table in your view
definition. PV is the trick to combine data.
-oj
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OpuTpcovFHA.1996@.TK2MSFTNGP10.phx.gbl...
> Thanks OJ,
> I have a explicit where clause [WHERE ChangedFlag is null] instead of the
> check constraint. Will I give some better performance if I use check
> constraints instead.
> Also ChangedFlag column in this case is not a primary key column.
> Is there any other trick to combine data?
> Thanks,
> Nitin
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:eXBArSovFHA.2556@.TK2MSFTNGP15.phx.gbl...
>|||Why did you mimic a 1950's magnetic tape file generational system in
SQL? Are yoiu really using flags in a RDBMS, as if you were writing
assembly language code?
Go back to the basics; same schema means same entity in an RDBMS. Your
data model has split a set over two tables when you should have had
only one. My guess woild be that you need to show a history, whcih
means that you will have a (start_time, end_time) pair in the table and
will get the current status by looking at (end_time IS NULL).|||Hi Celko,
I have a system in which the original tables are being used in a zillion
places. The system does some analysis using queries. Now there is a need to
do the same analysis with changes to original data, a simulation or a "what
if the data changes" sort of analysis.
In this situation if I want to keep two 'avtars' of a row in the same table
I will to think about what to do with the queries that already exist.
And all this while I do not want to disturb any reports etc. which are
accessing the original data. I want the simulation and the existing stuff to
run simulataneously.
Do tell me if there is a better way out.
Thanks,
Nitin
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127294968.322772.46020@.g49g2000cwa.googlegroups.com...
> Why did you mimic a 1950's magnetic tape file generational system in
> SQL? Are yoiu really using flags in a RDBMS, as if you were writing
> assembly language code?
>
> Go back to the basics; same schema means same entity in an RDBMS. Your
> data model has split a set over two tables when you should have had
> only one. My guess woild be that you need to show a history, whcih
> means that you will have a (start_time, end_time) pair in the table and
> will get the current status by looking at (end_time IS NULL).
>|||"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Hi Celko,
> I have a system in which the original tables are being used in a zillion
> places. The system does some analysis using queries. Now there is a need
> to do the same analysis with changes to original data, a simulation or a
> "what if the data changes" sort of analysis.
> In this situation if I want to keep two 'avtars' of a row in the same
> table I will to think about what to do with the queries that already
> exist.
> And all this while I do not want to disturb any reports etc. which are
> accessing the original data. I want the simulation and the existing stuff
> to run simulataneously.
> Do tell me if there is a better way out.
> Thanks,
> Nitin
Why not do the simulation on a copy of the database?|||<Why not do the simulation on a copy of the database?>
Wont this take lot of extra time and space too?
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23LDbZuqvFHA.2516@.TK2MSFTNGP12.phx.gbl...
> Why not do the simulation on a copy of the database?
>|||Space is cheap.
I don't understand the extra time comment.
Some compagnies even do reporting on a database copy.
Sure, the data isn't up to date as this depends on the backup frequency.
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23yk$hIrvFHA.4020@.TK2MSFTNGP10.phx.gbl...
> <Why not do the simulation on a copy of the database?>
> Wont this take lot of extra time and space too?
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uQCI7%23qvFHA.2728@.TK2MSFTNGP14.phx.gbl...
>