sql query optimization 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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

sql query optimization help

Author

Topic

sqlusr1
Starting Member

6 Posts

Posted - 2012-06-18 : 12:08:31

Hi ! I am executing the following query and its taking a long time... can anyone help me how to improve performance of this query
select cus.cust_name, cus.cust_num, ce.descr, ot.descr,
oq.long_descr, ai.model_num, ad.qty, ai.audit_num, ai.rt_serial,
au.billing_code, au.cust_ref, substring(ai.sku_num,19,1),
isnull(id.date,ah.date), m.manf_name, ai.serial_num, ai.asset_tag, ai.comment,
cl.descr
from dbo.acc_tbl_document_header ah (nolock)
join dbo.acc_tbl_document_detail ad (nolock) on ad.doc_num=ah.doc_num and ad.entry_type=10
and ah.trx_type=2
join dbo.audit_item ai (nolock) on ai.rt_serial=ad.rt_serial
join dbo.audit au (nolock) on au.audit_num=ai.audit_num
join dbo.cus_tbl_customer cus (nolock) on cus.cust_num=au.cust_num
join dbo.center ce (nolock) on ce.center_id=au.center_id
join dbo.opt_acc_trxtypes ot (nolock) on ot.trx_type=ah.trx_type
join dbo.opt_eq oq (nolock) on oq.sku_abbr=substring(ai.sku_num,4,2)
join dbo.manf m (nolock) on m.manf_abbr=ai.manf
join dbo.cus_tbl_location cl (nolock) on cl.location_id=au.location_id
left outer join (select rt_serial, trx_num, date=max(date) from dbo.inv_tbl_trx_log (nolock)
group by rt_serial, trx_num) as id
on id.rt_serial=ai.rt_serial and id.trx_num=ah.reference_num
where au.cust_num in ('A0247','A0288','A0850','A0883')
and convert(date,isnull(id.date,ah.date)) between GETDATE()-30 and GETDATE() - 1

Most of these tables have couple of millions of rows

siri

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-18 : 12:58:11

Are the join columns indexed - with covering indexes

and convert(date,isnull(id.date,ah.date)) between GETDATE()-30 and GETDATE() - 1
isn't going to be good.
Do you need the convert(date?
How long does the derived table take to create and how big is it could create it before the query in a temp table.

Mght be worth getting the data needed from acc_tbl_document_header and the derived table then using that for the rest of the query.

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

sqlusr1
Starting Member

6 Posts

Posted - 2012-06-18 : 14:29:40

All the joined columns have indexes with covered and included columns.

Here are the indexes on all the tables in the join
CREATE NONCLUSTERED INDEX [PS_acc_tbl_document_header_trx_type] ON [dbo].[acc_tbl_document_header]
(
[doc_num] ASC,
[trx_type] ASC,
[sub_type] ASC,
[date] ASC
)
INCLUDE ( [reference_num]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO

CREATE NONCLUSTERED INDEX [NIEX10_acc_tbl_document_detail] ON [dbo].[acc_tbl_document_detail]
(
[entry_type] ASC,
[doc_num] ASC,
[rt_serial] ASC,
[line_num] ASC
)
INCLUDE ( [qty]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO

CREATE UNIQUE CLUSTERED INDEX [ndx_ai_rt_clus] ON [dbo].[audit_item]
(
[rt_serial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
Clustered Index Seek cost:14%

CREATE UNIQUE CLUSTERED INDEX [ndx_au_clus] ON [dbo].[audit]
(
[audit_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

Clustered Index Seek Cost: 13%

ALTER TABLE [dbo].[center] ADD CONSTRAINT [PK_CENTER] PRIMARY KEY CLUSTERED
(
[center_id] ASC
)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, FILLFACTOR = 90) ON [PRIMARY]
GO
Clustered Index Seek Cost: 12%

CREATE NONCLUSTERED INDEX [IDX7_CUS_TBL_CUSTOMER] ON [dbo].[Cus_tbl_Customer]
(
[Cust_Num] ASC,
[intra_company] ASC,
[Tax_Id] ASC,
[Cust_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
Index seek Cost : 12%

CREATE NONCLUSTERED INDEX [NIEX4_manf] ON [dbo].[manf]
(
[manf_abbr] ASC
)
INCLUDE ( [manf_name]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

Index seek Cost : 11%

CREATE UNIQUE CLUSTERED INDEX [IDX1_OPT_EQ] ON [dbo].[opt_EQ]
(
[SKU_abbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
Clustered Index seek Cost : 11%

ALTER TABLE [dbo].[Cus_tbl_Location] ADD CONSTRAINT [PK_CUS_TBL_LOCATION] PRIMARY KEY CLUSTERED
(
[Location_Id] ASC
)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, FILLFACTOR = 90) ON [PRIMARY]
GO

Clustered Index seek Cost : 11%
CREATE NONCLUSTERED INDEX [NIEX2_Inv_tbl_Trx_log] ON [dbo].[Inv_tbl_Trx_log]
(
[Rt_serial] ASC,
[Trx_num] ASC
)
INCLUDE ( [Date]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [Report]
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources