SQL Optimise HELP - 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

General SQL Server Forums

Database Design and Application Architecture

SQL Optimise HELP

Author

Topic

arnab.chatterjee83
Starting Member

2 Posts

Posted - 2010-08-10 : 10:34:17

Dear All

I need some help to optimise table script.I have one table name as "MACSOrderLinrBom". In real application this table always had more then 60 lac data.So when I have try to use this table every query take lots of time.

I am requesting all please help me to optimise this table so that data fetching take less time.

Please Run this bellow script to create that table.

=====================================================================

/****** Object: Database TIMS_TESTDB Script Date: 8/10/2010 7:28:55 PM ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TIMS_TESTDB')
DROP DATABASE [TIMS_TESTDB]
GO

CREATE DATABASE [TIMS_TESTDB] ON (NAME = N'TIMS_MAINDB_Data', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\TIMS_TESTDB_Data.MDF' , SIZE = 183, FILEGROWTH = 10%) LOG ON (NAME = N'TIMS_MAINDB_Log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\TIMS_TESTDB_log.lDF' , SIZE = 38, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'TIMS_TESTDB', N'autoclose', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'bulkcopy', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'trunc. log', N'true'
GO

exec sp_dboption N'TIMS_TESTDB', N'torn page detection', N'true'
GO

exec sp_dboption N'TIMS_TESTDB', N'read only', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'dbo use', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'single', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'autoshrink', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'ANSI null default', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'recursive triggers', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'ANSI nulls', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'concat null yields null', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'cursor close on commit', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'default to local cursor', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'quoted identifier', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'ANSI warnings', N'false'
GO

exec sp_dboption N'TIMS_TESTDB', N'auto create statistics', N'true'
GO

exec sp_dboption N'TIMS_TESTDB', N'auto update statistics', N'true'
GO

if( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) )
exec sp_dboption N'TIMS_TESTDB', N'db chaining', N'false'
GO

use [TIMS_TESTDB]
GO

/****** Object: Table [dbo].[MACSOrderlineBom] Script Date: 8/10/2010 7:28:57 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MACSOrderlineBom]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MACSOrderlineBom]
GO

/****** Object: User dbo Script Date: 8/10/2010 7:28:56 PM ******/
/****** Object: User guest Script Date: 8/10/2010 7:28:56 PM ******/
if not exists (select * from dbo.sysusers where name = N'guest' and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

/****** Object: Table [dbo].[MACSOrderlineBom] Script Date: 8/10/2010 7:29:00 PM ******/
CREATE TABLE [dbo].[MACSOrderlineBom] (
[BranchID] [int] NOT NULL ,
[MACSOrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MACSOrderLineNo] [int] NOT NULL ,
[SEQ_NO] [int] NOT NULL ,
[PARTCODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FINISHCODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STANDARD_LENGTH] [int] NULL ,
[CUT_LENGTH] [int] NULL ,
[HEIGHT] [decimal](18, 6) NULL ,
[WIDTH] [decimal](18, 6) NULL ,
[QUANTITY] [int] NULL ,
[UNITOF_MEASURE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT_COST] [decimal](10, 2) NULL ,
[BOM_COST] [decimal](10, 4) NULL ,
[INVENTORY_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MachiningDetails] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIMSInventoryTypeID] [int] NULL ,
[TIMSPartID] [int] NULL ,
[TIMSPartLength] [int] NULL ,
[TIMSFinishID] [int] NULL ,
[TIMSFinishTypeID] [int] NULL ,
[TIMSOrderStatus] [int] NULL ,
[BranchOwnPart] [bit] NULL ,
[GlassOrder] [int] NULL ,
[IsOffCut] [bit] NULL ,
[Multi_Reserved] [bit] NOT NULL ,
[GLASS_FIXED_OR_SASH] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXTN_MITRE_CUT] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IsPreOrdered] [bit] NOT NULL ,
[GlassOrderedPre] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MACSOrderlineBom] WITH NOCHECK ADD
CONSTRAINT [PK_MACSOrderlineBom] PRIMARY KEY CLUSTERED
(
[BranchID],
[MACSOrderNo],
[MACSOrderLineNo],
[SEQ_NO]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MACSOrderlineBom] ADD
CONSTRAINT [DF_MACSOrderlineBom_TIMSOrderStatus] DEFAULT (0) FOR [TIMSOrderStatus],
CONSTRAINT [DF_MACSOrderlineBom_BranchOwnPart] DEFAULT (0) FOR [BranchOwnPart],
CONSTRAINT [DF_MACSOrderlineBom_GlassOrder] DEFAULT (0) FOR [GlassOrder],
CONSTRAINT [DF__MACSOrder__Multi__076CEECC] DEFAULT (0) FOR [Multi_Reserved],
CONSTRAINT [DF__MACSOrder__IsPre__595C0B59] DEFAULT (0) FOR [IsPreOrdered]
GO

CREATE UNIQUE INDEX [IX_MACSOrderlineBom] ON [dbo].[MACSOrderlineBom]([BranchID], [MACSOrderNo], [MACSOrderLineNo], [SEQ_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

exec sp_addextendedproperty N'MS_Description', N'1-Ordered, 2 Recieved', N'user', N'dbo', N'table', N'MACSOrderlineBom', N'column', N'GlassOrder'
GO
exec sp_addextendedproperty N'MS_Description', N'0-None, 1-MACSDownloaded, 2-LinesOrdered, 3-FullReserved , 5=WIP , 6=OPtimise', N'user', N'dbo', N'table', N'MACSOrderlineBom', N'column', N'TIMSOrderStatus'

GO

=====================================================================
Thanks to all in advance

Regards
Arnab

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-10 : 12:45:21

It's impossible to optimise a table without knowing how it's used. What are the queries that typically run against this table (the slow ones)?

--
Gail Shaw
SQL Server MVP

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-08-11 : 15:05:40

As well as what queries.
1)Have you got any maintenance tasks running on this database\tables?
2)It would be useful to have an execution plan associated with the slow queries?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

arnab.chatterjee83
Starting Member

2 Posts

Posted - 2010-08-12 : 01:20:33

Dear All

Thank you reply me.

On my last post I have put table script for one base table. I am sending you two more table which is associate with that base table

1)****OrderHead
2)****OrderLine

===================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MACSOrderHead]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MACSOrderHead]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MACSOrderLine]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MACSOrderLine]
GO

CREATE TABLE [dbo].[MACSOrderHead] (
[BranchID] [int] NOT NULL ,
[MACSOrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[QuoteNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MACSOrderDate] [datetime] NULL ,
[CustomerCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeliveryAddress1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeliverySuburb] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeliveryPostCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SalesRepresentative] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CutomerRequiredDate] [datetime] NULL ,
[DeliveryScheduleddate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[FactoryRequiredDate] [datetime] NULL ,
[IBTRequiredDate] [datetime] NULL ,
[ManfacturingDivision] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ManfacturingSite] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrderStatusField] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIMSOrderStatus] [int] NULL ,
[ReservationStatus] [int] NULL ,
[GlassOrdered] [int] NULL ,
[ReservedinFull] [bit] NULL ,
[Comment] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CustomerOrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Optimise] [bit] NULL ,
[ORDER_SPLIT_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExcludeMetalUnReservation] [bit] NULL ,
[MetalOrdered] [bit] NULL ,
[ReservationRun] [bit] NULL ,
[DownloadDateTime] [datetime] NOT NULL ,
[Multi_Reserved] [bit] NOT NULL ,
[PHold] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suspended] [bit] NULL ,
[CKD_Order] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MACSOrderLine] (
[BranchID] [int] NOT NULL ,
[MACSOrderNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MACSOrderLineNo] [int] NOT NULL ,
[PartQuantity] [int] NULL ,
[PartNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartHeight] [decimal](18, 2) NULL ,
[PartWidth] [decimal](18, 2) NULL ,
[PartFinish] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartProductionLevel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartComment] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartRoom] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PartIBTRequiredDate] [datetime] NULL ,
[PartFactoryRequiredDate] [datetime] NULL ,
[PartStatus] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TIMSOrderStatus] [int] NULL ,
[Option_Values] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRODN_LINE] [tinyint] NULL ,
[ReservationRun] [bit] NULL ,
[TIMSMetalOrdered] [int] NULL ,
[Multi_Reserved] [bit] NOT NULL ,
[GlassOrdered] [bit] NULL ,
[DoNotOrderGlass] [bit] NULL
) ON [PRIMARY]
GO

=================================================================

So please run all three script to create tables.

please fine the slow query in bellow

=================================================================
Select a.Reservedqty OrgReservedqty, isnull(Sum(r.Reservedqty),0) Reservedqty, case a.CKD_Order when 'Y' then 1 else 0 end CKD_Order,
RevealID, a.BranchID, StockQty, StockTypeID, Obsolete, WarehouseID, a.Length, StockType, InventoryTypeID,
FinishID, RevealCode, RevealDescription, BranchRevealID, MinReOrderQty, CriticalStockLevel,
WarehouseLocationID, UnAllocatedQty, BranchOwnStock
from (Select b.CKD_Order,b.macsorderno, a.* From vw_EnquiryReveals a Inner Join
(Select isnull(mh.ckd_order,'N') as CKD_Order,mb.BranchId, mb.TIMSInventorytypeid, mb.BranchOwnPart,
mb.MACSOrderNo, TIMSPartId, TIMSFinishId, TIMSPartLength
From MacsorderLineBom mb Left Join macsorderhead mh on mb.branchid=mh.branchid
and mb.macsorderno=mh.macsorderno where isnull(mh.ckd_order,'N')='N'
Group By mh.CKD_Order,mb.BranchId, mb.TIMSInventorytypeid, mb.BranchOwnPart, mb.MACSOrderNo,
TIMSPartId, TIMSFinishId, TIMSPartLength ) b On
a.Branchid=b.Branchid and a.Inventorytypeid=b.TIMSInventorytypeid And a.RevealID=b.TIMSPartId
and isnull(a.finishid,0)=isnull(b.TIMSfinishid,0) and a.Length=b.TIMSPartLength and a.BranchOwnStock=b.Branchownpart
--where a.branchid=41 and a.partid=12 and a.finishid=6)
) a
Left Join Reservation r
on a.MacsorderNo=r.Macsorderno and a.Branchid=r.Branchid and a.Inventorytypeid=r.TIMSInventorytypeid
and a.RevealID=r.TIMSPartId and a.Finishid=r.TIMSFinishid and a.length=r.Length
and a.BranchOwnStock=r.Branchown
--where a.branchid=41 and a.partid=50 and a.finishid=6
Group by a.Reservedqty ,a.CKD_Order,RevealID, a.BranchID, StockQty, StockTypeID, Obsolete, WarehouseID, a.Length, StockType, InventoryTypeID,
FinishID, RevealCode, RevealDescription, BranchRevealID, MinReOrderQty, CriticalStockLevel,
WarehouseLocationID, UnAllocatedQty, BranchOwnStock

=====================================================================

Please help me on this.................

Thanks in advance

Regards
Arnab

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-12 : 01:53:35

Can you post the execution plan?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources