February 25, 2013 at 2:49 pm
Hi All
I have the following two tables:
CREATE TABLE [ipc].[TValueTransaction](
[ValueTransaction_OID] [decimal](15, 0) NOT NULL,
[LastChange] [timestamp] NULL,
[ValueDate] [datetime] NOT NULL,
[ApprovedTime] [datetime] NULL,
[Status] [char](20) NOT NULL,
[SummaryOrder_OID] [decimal](15, 0) NOT NULL,
[Currency_OID] [decimal](15, 0) NOT NULL,
[AccountingVoucher_OID] [decimal](15, 0) NULL,
[TransactionType_OID] [decimal](15, 0) NOT NULL,
[BusinessFunction_OID] [decimal](15, 0) NOT NULL,
[TAN] [int] NOT NULL,
[IsStorno] [int] NOT NULL,
[StornoTAN] [int] NULL,
[LastChangedTime] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ValueTransaction_OID] ASC
)
)
CREATE TABLE [ipc].[TPrimaryTransaction](
[PrimaryTransaction_OID] [decimal](15, 0) NOT NULL,
[LastChange] [timestamp] NULL,
[AmountAM] [decimal](20, 6) NOT NULL,
[AmountDB] [int] NOT NULL,
[PrintedOnPageNumber] [int] NULL,
[AccountFolio_OID] [decimal](15, 0) NULL,
[ValueTransaction_OID] [decimal](15, 0) NOT NULL,
[TransactionComponent_OID] [decimal](15, 0) NOT NULL,
[PrimaryAccount_OID] [decimal](15, 0) NULL,
[InternalAccount_OID] [decimal](15, 0) NULL,
[Comment] [varchar](160) NULL,
[ExpID] [char](20) NULL,
[VoucherLine_OID] [decimal](15, 0) NULL,
PRIMARY KEY CLUSTERED
(
[PrimaryTransaction_OID] ASC
)
)
[ValueTransaction_OID] is the link between TPrimaryTransaction and TValueTransaction. TValueTransaction has 180 million records and TPrimaryTransaction has 360 million records.
These two tables consist over 95% of the database. I have to optimize that database.
I tried partitioning in two ways:
1) Made partitioning by their primary keys
2) Made partitioning by ValueTransaction_OID which is common for both tables.
In both cases I did not achieved improvement.
If I make partitioning of TValueTransaction by ValueDate then I cannot link the two tables.
The queries that use TvalueTransaction usually use ValueDate in range. All queries are covered by the tables indexes. However the execution is slow.
Can someone propose something better?
Thanks in advance
Igor Micev,My blog: www.igormicev.com
February 25, 2013 at 2:57 pm
What exactly are you trying to improve? Reads? Writes? Can we get some stats on what you're trying to do?
February 25, 2013 at 2:58 pm
Erin Ramsay (2/25/2013)
What exactly are you trying to improve? Reads? Writes? Can we get some stats on what you're trying to do?
I want to improve Reads only
Igor Micev,My blog: www.igormicev.com
February 25, 2013 at 3:34 pm
Really not enough information.
Partitioning really isn't used for performancing tuning queries. You need to look at the code and the indexes. Using the actual execution plans for the queries can help you figure out what changes need to be accomplished.
February 25, 2013 at 3:44 pm
https://www.simple-talk.com/sql/database-administration/gail-shaws-sql-server-howlers/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2013 at 3:44 pm
Lynn Pettis (2/25/2013)
Really not enough information.Partitioning really isn't used for performancing tuning queries. You need to look at the code and the indexes. Using the actual execution plans for the queries can help you figure out what changes need to be accomplished.
Okey, thank you all anyway.
I tried various approaches. For example indexed views produced very good results, but it costs me more space on the hard disk. It is the application. I'll post another post.
Thank you
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply