Four Part Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Four Part Table

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-05 : 20:06:19

I have a very large PO line item table. I want to seperate it (by Rows) into four tables, without the software knowing it is not one table. I have 3 Seperate SCSI Controllers on the Server each with at least three 36GB Drives Raid 0 Mirrored.

Any suggestions.

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2003-09-05 : 22:04:36

Why you want to separate it by rows? Is it degrading performance or a memory problem?

---------------------------
Sandesh - The Messanger

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-05 : 22:53:07

lots of g and t's right now....

but

I"d say

multiple drives...

separate tables...

partitioned views....

what'dya think?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-05 : 23:35:07

I want to decrease the cost of the table scan in my report queries, is my thinking skewed? I read somewhere, that this would be a good idea. Most users never query boyond the current 12 months of data. But I need to have 5 years available.

The app is antiquated,and I don't have the source code.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-06 : 20:15:32

Look at partitioned views.
The four tables will need check constraints on them and it will cause queries to only access the table which fulfils the check constraint.

Test it first though as there are instances where it doesn't work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-08 : 07:52:28

I will look into partitoned Views, thanks,

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-08 : 09:06:53

stupid question, but: Do you have proper indexes set up on the table? exactly how big is the table (how many rows) ?

just curious.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 10:02:44

quote:
Originally posted by TSQLMan

I want to decrease the cost of the table scan in my report queries

I don't think (again...damn, I hate when that happens) that a partitioned view will remove your scans...it's more likely the query.

Why don't you post a query..

A partitioned view will just make your scans "more efficient" (what's an oxymoron: Brett saying making a scan more efficient)

You need to find out why your are scanning in the first case.

Sometimes it's unavoidable, but usually you scan a "driver" result set...

But if you have details like your PO line table, you probably shouldn't be scanning it..

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-08 : 10:25:44

The row count 1,020,016 here are the data definitions. Find Below the data definitions one of my queries, that seems slow against the POORDLIN_SQL

[ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[line_no] [smallint] NOT NULL ,
[vend_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[job_no] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[item_desc_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[item_desc_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vend_item_no] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ord_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chg_cancel_cd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vend_late_cncl_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mn_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sb_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dp_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var_mn_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var_sb_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var_dp_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[acc_mn_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[acc_sb_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[acc_dp_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stk_uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[purch_uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[one_tm_uom] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[purch_to_stk_rat] [decimal](9, 5) NULL ,
[one_tm_uom_ratio] [decimal](9, 5) NULL ,
[exp_unit_cost] [decimal](13, 6) NULL ,
[act_unit_cost] [decimal](13, 6) NULL ,
[std_cost] [decimal](13, 6) NULL ,
[curr_cd_cost] [decimal](13, 6) NULL ,
[curr_cd_rt] [decimal](11, 6) NULL ,
[commodity_cd] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[notify_initials] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[move_to_loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bkt_tp] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stk_loc] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[shop_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oper_no] [smallint] NULL ,
[qty_ordered] [decimal](13, 4) NULL ,
[qty_ord_chg_amt] [decimal](13, 4) NULL ,
[qty_released] [decimal](13, 4) NULL ,
[qty_received] [decimal](13, 4) NULL ,
[qty_remaining] [decimal](13, 4) NULL ,
[qty_rejected] [decimal](13, 4) NULL ,
[qty_cancelled] [decimal](13, 4) NULL ,
[qty_inv] [decimal](13, 4) NULL ,
[qty_rec_insp] [decimal](13, 4) NULL ,
[qty_mat_rev] [decimal](13, 4) NULL ,
[dollars_inv] [decimal](14, 2) NULL ,
[landed_cost_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[req_date_chg_fg] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[request_dt] [int] NULL ,
[promise_dt] [int] NULL ,
[receipt_dt] [int] NULL ,
[byr_plnr] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mrp_byr_plnr] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rej_reason_cd] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[weight] [decimal](15, 6) NULL ,
[cmt_cd_1] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cmt_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cmt_cd_2] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cmt_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_1] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_2] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_3] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_4] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_def_fld_5] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_cd_1] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_name_1] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_1_item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_1_item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_cd_2] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_name_2] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_2_item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_2_item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_cd_3] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_name_3] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_3_item_no] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mfg_3_item_filler] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[path_no] [smallint] NULL ,
[shop_dtl_seq_no] [smallint] NULL ,
[shop_dtl_rec_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oe_ord_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var_qty_mn_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var_qty_sb_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[var_qty_dp_no] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[oe_line_no] [smallint] NULL ,
[filler_0003] [char] (55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL

---
INSERT _COMMITTED_COST

(MN_NO,SB_NO,DP_NO,ORD_DT_YTD,COMMITTED_YTD,ITEM_DESC_1,ORD_VEND,GL_DESCRIPTOR)

SELECT SY.MN_NO,SY.SB_NO,SY.DP_NO, PO.ORD_DT, POL.EXP_UNIT_COST * POL.QTY_ORDERED AS COMMITTED_YTD,

POL.ITEM_DESC_1, SUBSTRING (POL.ORD_NO,1,6) +' - '+APV.VEND_NAME AS ORDER_VEND,

SUBSTRING (POL.MN_NO,1,4)+'-'+SUBSTRING(POL.SB_NO,1,2)+'-'+SUBSTRING(POL.DP_NO,1,4)+'- '+SY.ACCT_DESC AS GL_DESCRIPTOR

FROM POORDHDR_SQL PO

INNER JOIN POORDLIN_SQL POL ON PO.ORD_NO = POL.ORD_NO

INNER JOIN APVENFIL_SQL APV ON POL.VEND_NO = APV.VEND_NO

INNER JOIN SYACTFIL_SQL SY ON POL.MN_NO = SY.MN_NO AND POL.SB_NO = SY.SB_NO AND POL.DP_NO = SY.DP_NO

--ORDER DATES IN THE CURRENT FISCAL YEAR ONLY

WHERE PO.ORD_DT BETWEEN 20030601 AND 20040531 AND PO.ORD_STATUS IN ('P','R')

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 10:44:54

Run this and post the results...


sp_spaceused POORDHDR_SQL
GO
sp_spaceused POORDLIN_SQL
GO
sp_spaceused APVENFIL_SQL
GO
sp_spaceused SYACTFIL_SQL
GO

Also...Do you have indexes on the predicates (WHERE clauses?)

Can you script the DDL for the tables and include the indexes?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-08 : 10:51:11

Not sure what you mean by a ddl

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
POORDHDR_SQL 46875 54888 KB 29144 KB 5584 KB 20160 KB

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
POORDLIN_SQL 102016 245640 KB 128968 KB 77856 KB 38816 KB

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
APVENFIL_SQL 6595 16864 KB 9168 KB 1056 KB 6640 KB

name rows reserved data index_size unused
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ------------------ ------------------ ------------------
SYACTFIL_SQL 2500 3808 KB 1552 KB 360 KB 1896 KB

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-08 : 13:02:28

Try this...

WHERE PO.ORD_DT >= 20030601 AND PO.ORD_DT <= 20040531

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

TSQLMan
Posting Yak Master

160 Posts

Posted - 2003-09-08 : 13:08:50

Thanks, speeds things a bit.

HERE ARE THE INDEXES.

Index Clustered Columns
IPOORDLIN_SQL1 Y ord_no,line_no
IPOORDLIN_SQL2 N job_no,item_no,vend_no
IPOORDLIN_SQL3 N ord_no,item_no,itemfiller,a4glidentity

dsdeming

479 Posts

Posted - 2003-09-08 : 13:26:03

If modified WHERE clause improved performance a bit, perhaps adding an index on PO.ORD_DT will really boost the speed.

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources