April 18, 2011 at 4:19 am
Hi,
I have a very large table (14 billion of record) and I have to query this table by web application (return time should be less than 1 minute).
My table is :
CREATE TABLE [dbo].[VIPT_TAB_003](
[PT03_KEY_RAP] [decimal](18, 0) NOT NULL,
[PT03_KEY_AN] [decimal](18, 0) NOT NULL,
[PT03_SKEY] [decimal](18, 0) NULL,
[PT03_NDG] [varchar](16) NULL,
[PT03_R_ABI] [varchar](5) NULL,
[PT03_R_CSER] [varchar](5) NULL,
[PT03_R_CFIL] [varchar](5) NULL,
[PT03_R_NUM] [decimal](18, 0) NULL,
[PT03_CAB] [varchar](5) NULL,
[PT03_CAT] [varchar](5) NULL,
[PT03_SOT_CAT] [varchar](5) NULL,
[PT03_FT] [varchar](5) NULL,
[PT03_CPRO] [varchar](12) NULL,
[PT03_CAN_AP] [varchar](5) NULL,
[PT03_TIP_CART] [varchar](5) NULL,
[PT03_ST_EFF] [varchar](5) NULL,
[PT03_NUM_EFF] [varchar](20) NOT NULL,
[PT03_TP_MES] [char](3) NOT NULL,
[PT03_TP_INCASSO] [char](2) NOT NULL,
[PT03_TP_OPERAZIONE] [char](2) NOT NULL,
[PT03_CAUSALE] [char](2) NOT NULL,
[PT03_IMPORTO_EFF] [decimal](15, 2) NULL,
[PT03_TASSO] [decimal](18, 2) NULL,
[PT03_IMP_COMM] [decimal](18, 2) NULL,
[PT03_DIVISA] [char](3) NULL,
[PT03_CRO] [varchar](11) NOT NULL,
[PT03_CTP_ABI] [varchar](5) NULL,
[PT03_ABI_TRM] [varchar](5) NULL,
[PT03_DT_ELAB] [char](10) NULL,
[PT03_DT_RIF] [char](10) NULL,
[PT03_DT_APP] [char](10) NULL,
[PT03_DT_REG] [char](10) NULL,
[PT03_DT_SCAD] [char](10) NULL,
[PT03_DT_LIM] [char](10) NULL,
[PT03_DT_PAG] [char](10) NULL,
[PT03_DIREZIONE] [char](1) NOT NULL,
[PT03_SEGNO_T] [char](1) NULL,
[PT03_SEGNO_C] [char](1) NULL,
[PT03_COD_AZI] [varchar](5) NULL,
[PT03_IDJOB] [decimal](8, 0) NULL,
[PT03_PART] [char](2) NULL,
[PT03_ANNOMESE] [decimal](6, 0) NULL,
[PT03_DEB_CFISC] [varchar](16) NULL,
[PT03_DEB_PIVA] [varchar](11) NULL,
[PT03_CRE_CFISC] [varchar](16) NULL,
[PT03_CRE_PIVA] [varchar](11) NULL,
[PT03_TMST] [timestamp] NULL,
PRIMARY KEY NONCLUSTERED
(
[PT03_KEY_RAP] ASC,
[PT03_KEY_AN] ASC,
[PT03_NUM_EFF] ASC,
[PT03_TP_MES] ASC,
[PT03_TP_INCASSO] ASC,
[PT03_TP_OPERAZIONE] ASC,
[PT03_CAUSALE] ASC,
[PT03_CRO] ASC,
[PT03_DIREZIONE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
my index is :
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_filtered
ON VIPT_TAB_003 (PT03_R_ABI, PT03_NUM_EFF)
Include (PT03_KEY_RAP,
PT03_KEY_AN,
PT03_SKEY,
PT03_NDG,
PT03_R_CSER,
PT03_R_CFIL,
PT03_R_NUM,
PT03_CAB,
PT03_CAT,
PT03_SOT_CAT,
PT03_FT,
PT03_CPRO,
PT03_CAN_AP,
PT03_TIP_CART,
PT03_ST_EFF ,
PT03_TP_MES ,
PT03_TP_INCASSO ,
PT03_TP_OPERAZIONE,
PT03_CAUSALE,
PT03_IMPORTO_EFF,
PT03_TASSO,
PT03_IMP_COMM,
PT03_DIVISA,
PT03_CRO,
PT03_CTP_ABI,
PT03_ABI_TRM,
PT03_DT_ELAB,
PT03_DT_RIF,
PT03_DT_APP,
PT03_DT_REG,
PT03_DT_SCAD,
PT03_DT_LIM,
PT03_DT_PAG,
PT03_DIREZIONE ,
PT03_SEGNO_T,
PT03_SEGNO_C,
PT03_COD_AZI,
PT03_IDJOB,
PT03_PART,
PT03_ANNOMESE,
PT03_DEB_CFISC,
PT03_DEB_PIVA,
PT03_CRE_CFISC,
PT03_CRE_PIVA)
WHERE PT03_ANNOMESE = 200902
WITH (PAD_INDEX = off, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
I have to access this table by PT03_R_ABI and PT03_NUM_EFF.
Could anyone help me to reach my goal?
Thanks for all
Best Regard
April 18, 2011 at 4:22 am
Post the queries that you are using. Without seeing exactly what the queries are, it's near impossible to suggest anything meaningful.
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
April 18, 2011 at 4:24 am
select * from vipt_tab_003
where pt03_annomese = 201001
and pt03_r_abi = 'XXXXX'
and pt03_num_eff = '569845456165748'
April 18, 2011 at 4:35 am
Do you really, absolutely need every single column? That'll make it near-impossible to cover the query properly.
Just select the absolute minimum columns that you need.
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
April 18, 2011 at 5:55 am
I can remove up to 3 columns from the resulset...
April 18, 2011 at 6:01 am
If the filtered index's value can be changed to match the query, that query is as efficient as its going to get.
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
April 18, 2011 at 7:56 am
This is a classic "are you sure really need everything, every time" discussion that you should have with your application team. Also, I suspect, it's hard to know based just on column names, that this table could easily be normalized to a more responsive structure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 18, 2011 at 8:01 am
aGuareschi (4/18/2011)
select * from vipt_tab_003where pt03_annomese = 201001
and pt03_r_abi = 'XXXXX'
and pt03_num_eff = '569845456165748'
What's the cardinality of this query? how many rows is it supposed to return?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 18, 2011 at 8:04 am
This query must return up to 500 rows.
All of the fields are necessary for my application....
April 18, 2011 at 8:13 am
You'll need an index on it since none of your current index fits (the second is filtered).
As said above, if you need so many rows over so many columns, your DB is probably not normalized correctly.
April 18, 2011 at 8:17 am
You're wrong because my database in normalized for a bank's application that shows the cash flow....
Thanks for all
April 18, 2011 at 9:04 am
aGuareschi (4/18/2011)
You're wrong because my database in normalized for a bank's application that shows the cash flow....Thanks for all
To what level is it normalised?
Also i see that you have a timestamp as a column, perhaps you could look at archiving older records off to a report table, reducing the number of rows in the table and possibly speeding up the queries..
April 18, 2011 at 9:10 am
Is there any advantage in includeing all of the columns in that index? Have you tried the same index with no included columns?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 18, 2011 at 4:15 pm
Oliiii (4/18/2011)
You'll need an index on it since none of your current index fits (the second is filtered).
Agreed. Please check execution plan, most likely you will see a full table scan.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 18, 2011 at 5:36 pm
1-Table should have a Clustered Index
2-Non-clustered Index should be SORT_IN_TEMPDB = ON
3-create non-clustered with this column also pt03_annomese
CREATE NONCLUSTERED INDEX IX_SalesOrderDetail_filtered
ON VIPT_TAB_003 (PT03_R_ABI, PT03_NUM_EFF,pt03_annomese)
Include (PT03_KEY_RAP,
PT03_KEY_AN,
PT03_SKEY,
PT03_NDG,
PT03_R_CSER,
PT03_R_CFIL,
PT03_R_NUM,
PT03_CAB,
PT03_CAT,
PT03_SOT_CAT,
PT03_FT,
PT03_CPRO,
PT03_CAN_AP,
PT03_TIP_CART,
PT03_ST_EFF ,
PT03_TP_MES ,
PT03_TP_INCASSO ,
PT03_TP_OPERAZIONE,
PT03_CAUSALE,
PT03_IMPORTO_EFF,
PT03_TASSO,
PT03_IMP_COMM,
PT03_DIVISA,
PT03_CRO,
PT03_CTP_ABI,
PT03_ABI_TRM,
PT03_DT_ELAB,
PT03_DT_RIF,
PT03_DT_APP,
PT03_DT_REG,
PT03_DT_SCAD,
PT03_DT_LIM,
PT03_DT_PAG,
PT03_DIREZIONE ,
PT03_SEGNO_T,
PT03_SEGNO_C,
PT03_COD_AZI,
PT03_IDJOB,
PT03_PART,
PT03_DEB_CFISC,
PT03_DEB_PIVA,
PT03_CRE_CFISC,
PT03_CRE_PIVA)
WHERE PT03_ANNOMESE = 200902
WITH (PAD_INDEX = off, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
4-If you have more than 1 CPU then add OPTION(MAXDOP 2) in the end of the query,it must be equal to or less then the available cpu in a system
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply