performance tune a query - 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

New to SQL Server Programming

performance tune a query

Author

Topic

mad_man12
Starting Member

3 Posts

Posted - 2010-02-02 : 11:30:26

Hi I am running the following query in syabase 11.5 which is taking long time (aroung 8 hrs to execute).

When i look into sysprocesses it gives sleeping for insert statment so i could figure out that
select statement is to much of time to execute.

The three table in the query tc_it_booking it,tc_gltrxdet gl,aptrx ap are very huge tables and
the other are very small

It is due to very large size of these the query is taking lot of time.

I need help on how to set indexes in the below query to performance tune it.

insert into it_cursor_supp
select
gl.journal_ctrl_num,
it.tc_holiday_num,
it.branch,
it.departure_date,
it.departure_date,
gl.balance,
null,
it.principal_code,
it.ar_invoice_num,
new.new_gl_actual_sales,
old.gl_actual_sales,
it.departure_date,
it.product_type
from
tc_new_product_type new,
tc_it_booking it,
tc_product_type old, aptrx ap, tc_gltrxdet gl
where it.departure_date > '10/31/2002'
and it.posted_flag ='S'
and ap.doc_ctrl_num = it.ar_invoice_num
and ap.gl_trx_id = gl.journal_ctrl_num
and datediff(day, it.departure_date, gl.date_posted) >= 0
and datediff(day, getdate(), gl.date_posted) >= -12
and datediff(day, getdate(), gl.date_posted) <= 1
and gl.seg2_code = '210460'
and gl.discount_posted_flag <> 'Z'
and it.product_type <> 'FS'
and new.product_type=it.product_type
and old.product_type=it.product_type
and it.principal_code not in
('00010028', '00010655','00010656', around 15 items here.............)

i have following indexes in it_booking table

tc_itb_ind0 clustered, unique located on default tc_holiday_num, principal_code, product_type
tc_itb_ind1 nonclustered located on default principal_code, ref_num
tc_itb_ind2 nonclustered located on default cust_name
tc_itb_ind3 nonclustered located on default ar_invoice_num
tc_itb_ind4 nonclustered located on default departure_date, trx_status, principal_code
tc_itb_ind5 nonclustered located on default posted_flag
tc_itb_ind6 nonclustered located on default tc_holiday_num
tc_itb_ind7 nonclustered located on default product_type
tc_itb_accurate nonclustered located on default ref_num

the following indexes in tc_gltrxdet table

tc_gltrxdet_ind0 clustered, unique located on default journal_ctrl_num, sequence_id
tc_gltrxdet_ind_8 nonclustered located on default document_1, seg2_code
tc_gltrxdet_ind1 nonclustered located on default journal_ctrl_num, account_code
tc_gltrxdet_tmp_6 nonclustered located on default seg2_code, date_posted
tc_gltrxdet_ind_4 nonclustered located on default discount_posted_flag
tc_gltrxdet_ind_5 nonclustered located on default departure_date
tc_gltrxdet_ind_6 nonclustered located on default product_type
tc_gltrxdet_ind_7 nonclustered located on default seg2_code
tc_gltrxdet_ind_2 nonclustered located on default account_code, date_applied

the following indexes in aptrx table

aptrx_ind_0 clustered located on default trx_ctrl_num, trx_type
aptrx_ind_1 nonclustered, unique located on default trx_ctrl_num, doc_ctrl_num, trx_type
aptrx_ind_7 nonclustered located on default trx_type, paid_flag
aptrx_ind_3 nonclustered located on default doc_ctrl_num, trx_type
tc_aptrx_ind_1 nonclustered located on default vendor_code, paid_flag, date_due

I was thinking in terms of creating a new non clustered index in it_booking table based in departure_date,posted_flag,principal_code and
product_type so that i get a quicker result(yet to create it)

Please Advice what else can be done if i can use existing indexes to help performance tune my query or else what is the best combination
for creating a index in IT booking or gltrxdet.

Greatly Appreciate your help on same!!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-02 : 11:56:53

You need to change this:


and datediff(day, it.departure_date, gl.date_posted) >= 0 
and datediff(day, getdate(), gl.date_posted) >= -12 
and datediff(day, getdate(), gl.date_posted) <= 1

These lines can't make use of any available index.

the first line should probably be:



AND it.departure_date >= DATEADD(DAY, DATEDIFF(DAY, 0, gl.date_posted), 0)

This pushes the function call onto tc_gltrxdet table and lets you use an index on it.departure_date. However you still won't get use of any index on gl.

Ideally for maximum performance you'd want to precompute these columns if you can or add another column to each table that strips the time information letting you do a direct >= comparison.

The other two lines can be similarly rewritten.

Also -- use ANSI JOIN instead of the old school WHERE joins.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mad_man12
Starting Member

3 Posts

Posted - 2010-02-03 : 08:58:11

hi charlie,

Thanks for the info, i changed the datediff function calls
and created a new non clustered index on departure_date,posted_flag,principal_code and
product_type but still i am not getting any improvements in the
query run.

Please Advice if there are any other options

shall i create an index on date_posted on gltrxdet table...any idea if that would help

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-03 : 10:27:32

what does the execution plan tell you is taking the most time?

try running the select statement in isolation without doing an insert and have a look at the actual execution plan.

You've got a lot of indices and it's probable that the query isn't picking the right one / able to use the right one.

Also repost your modified sql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mad_man12
Starting Member

3 Posts

Posted - 2010-02-03 : 10:58:05

SELECT gl.journal_ctrl_num,

it.tc_holiday_num,

it.branch,

it.departure_date,

it.departure_date,

gl.balance,

null,

it.principal_code,

it.ar_invoice_num,

new.new_gl_actual_sales,

old.gl_actual_sales,

it.departure_date,

it.product_type

FROM tc_new_product_type new, tc_it_booking it(index tc_itb_revrec), tc_product_type old, aptrx ap, tc_gltrxdet gl

WHERE it.departure_date > '10/31/2002'

AND it.posted_flag ='S'

AND ap.doc_ctrl_num = it.ar_invoice_num

AND ap.gl_trx_id = gl.journal_ctrl_num

AND it.departure_date <= gl.date_posted

AND gl.date_posted >= dateadd(dd, -12, getdate())

AND gl.date_posted <= dateadd(dd, -180, getdate())

AND gl.seg2_code = '210460'

AND gl.discount_posted_flag <> 'Z'

AND it.product_type <> 'FS'

AND new.product_type=it.product_type

AND old.product_type=it.product_type

AND it.principal_code not in

(

12345678,.........
)

Here tc_itb_revrec is the index on departure_date,posted_flag,principal_code and product_type

shall i use gl.date_posted between dateadd(dd, -180, getdate()) and dateadd(dd, -12, getdate())
and also create an index on gl.date_posted.

I am running the select in isolation ......apologise but not sure about how to get the execution path....

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-03 : 12:00:28

if you are running it in management studio just hit cntl + M or go to query - Display actual execution plan.

Then rerun the query.

Your date lines are fine. If there is an index then the query can use it, Lets see what you get on the execution plan.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-02-04 : 06:49:09

Charlie......you may have overlooked this from the original statement. "syabase 11.5". are you referring to "management studio" in SQL Server?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-04 : 08:12:48

Sigh -- yup you are totally right.

mad_man12 -- I've got no idea if the advice I gave you is even applicable for your environment. Sorry

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-02-05 : 04:39:03

Charlie, I think it's on the right track. We just need the execution plan from that environment using a different technology/tool.

OP - please post if you can.

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 05:02:09

I think the problem may be that index

tc_gltrxdet_tmp_6 nonclustered located on default seg2_code, date_posted

may not be being used if seg2_code is not sufficiently selective. How many rows total i.e. COUNT(*), and how many COUNT(DISTINCT seg2_code) ?

I would like to see an index on:
date_posted (must be the first column), seg2_code, journal_ctrl_num, discount_posted_flag
so that covers the columns used in WHERE clause for tc_gltrxdet (you could consider adding [balance] too so that that SELECT is also "covered", but I presume [balance] changes often, so that may not be a good idea (in terms of update performance)

Then I would like to see a DATE RANGE test in the WHERE clause for date_posted - as T.C. has already mentioned


and gl.date_posted >= DATEADD(day, -12, getdate())
and gl.date_posted <  DATEADD(day, 1, getdate())

(double check that I have understood your ranges correctly please, note that second test is "Less than one-day-after-cutoff" to catch any date with a time on the desired final day)

And if that doesn't dramatically improve it then we need to see the Query Plan please

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 05:03:57

I expect the Query Plan command is still the same in Sybase as MSSQL? Here's the one from MSSQL:


-- Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

... *** YOUR QUERY HERE *** ...

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SHOWPLAN_TEXT OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources