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...
>

Combine multiple sql calls into 1

I have an old app that I'm trying to recode and improve performance.

From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.

Here is a quick example of the script

Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE

Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE

Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE

INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'

These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-ScottCheck out the INSERT ... EXECUTE(' ') syntax in BOL.

Column name with period

I heard a rumour that column names with a period "." in
the name had a negative affect on performance.
I haven't yet managed to find proof of this but was told
that someone found an article suggesting that it does.
If anyone knows of this could they please post a link or
explain?I couldn't find any collation with a period in the name:
select * from ::fn_helpcollations() where name like '%.%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Willem" <anonymous@.discussions.microsoft.com> wrote in message
news:1575001c41631$2c2d2060$a501280a@.phx
.gbl...
> I heard a rumour that column names with a period "." in
> the name had a negative affect on performance.
> I haven't yet managed to find proof of this but was told
> that someone found an article suggesting that it does.
> If anyone knows of this could they please post a link or
> explain?
>|||Tibor,
Go and visit your optician! ;-) The OP said column, not collation!
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||Willem,
I don't know whether it would affect performance or not but it is not "best
practise" to have non alphanumeric characters in column names. You are expos
ing yourself to bugs by doing this. I know that you can use square brackets
but in some situations you
may find yourself hitting a bug or two. I would rename the column in the nex
t release of your application, if possible.
There may be a performance hit, but I doubt you would notice it.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||> Go and visit your optician! ;-) The OP said column, not collation!
LOL... Thanks Mark :-)
Willem,
I have not heard anything to the effect that SQL Server should treat columns
differently in any way based on
the name of the column. Of course, using a period makes the name a non-stand
ard identifier and you will have
to handle that in every query you issue against that column. I never divert
from standard identifiers myself.
I have written code against databases which required me to use delimited ide
ntifiers, no fun...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:71883438-B332-4E1D-92B7-E0F8B6AA8962@.microsoft.com...
> Tibor,
> Go and visit your optician! ;-) The OP said column, not collation!
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk|||Thanks for the feedback.

>--Original Message--
>Willem,
>I don't know whether it would affect performance or not
but it is not "best practise" to have non alphanumeric
characters in column names. You are exposing yourself to
bugs by doing this. I know that you can use square
brackets but in some situations you may find yourself
hitting a bug or two. I would rename the column in the
next release of your application, if possible.
>There may be a performance hit, but I doubt you would
notice it.
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>.
>|||Great, thanks.

>--Original Message--
not collation!
>LOL... Thanks Mark :-)
>
>Willem,
>I have not heard anything to the effect that SQL Server
should treat columns differently in any way based on
>the name of the column. Of course, using a period makes
the name a non-standard identifier and you will have
>to handle that in every query you issue against that
column. I never divert from standard identifiers myself.
>I have written code against databases which required me
to use delimited identifiers, no fun...
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:71883438-B332-4E1D-92B7-
E0F8B6AA8962@.microsoft.com...
not collation!
>
>.
>