Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Perfromance Issue with the Query...


Perfromance Issue with the Query...

Author
Message
mote.ajit2
mote.ajit2
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 53
Hi everyone,

I have written one sql which is having performance problem.
This table is partitioned on EVENTDATE column and having 10 crs records in it.

The block of qry which slow down I am posting here. In short what I did in that block is

I have one temporary table where I collected sub result so that I can use it for further data fetch.

I am possessing 15000 records from that temporary table each time as this help me in performance with compare to situation when I processed all temp table in one shot

when this temporary table having 100000 records performance is ~6 mins that hv to reduce.

declare @ROWPROCESSED int
SELECT @ROWPROCESSED = 0
while (@ROWPROCESSED <= (select MAX(ROWNO) from #temp))
begin
insert into #temp2(QUANSTOCKNKG, QUANCONDNKG, EVENTDATE)
select T1.QUANSTOCKNKG as QUANSTOCKNKG , T1.QUANCONDNKG as QUANCONDNKG, T1.EVENTDATE as EVENTDATE from(



SELECT O.QUANSTOCKNKG as QUANSTOCKNKG , O.QUANCONDNKG as QUANCONDNKG, O.EVENTDATE as EVENTDATE
from ORDERSTOCKFACTS O --#temp T
--join ORDERSTOCKFACTS O on O.MILLORDNO = T.MILLORDNO and O.MACHCHAINNO = T.MACHCHAINNO and O.ORDERLINENO = T.ORDERLINENO
where
exists(select 1 from #temp T where O.MILLORDNO = T.MILLORDNO and O.MACHCHAINNO = T.MACHCHAINNO and O.ORDERLINENO = T.ORDERLINENO
and T.ROWNO > @ROWPROCESSED and T.ROWNO < @ROWPROCESSED + 15001)
and O.EVENTDATE >= convert(smalldatetime,'01-12-2011',105) and O.EVENTDATE <= convert(smalldatetime,'01-5-2015',105)
and O.QUALITYCODE = 'P' AND O.INVSTATUS = 'UN' AND O.READINESS = 'F'
--and T.ROWNO > @ROWPROCESSED and T.ROWNO < @ROWPROCESSED + 15001



)T1
select @ROWPROCESSED = @ROWPROCESSED + 15000
end

The middle block is the query which is actually time consuming. Can anybody suggest what can I do to get ride on this performance issue.

table definition and index I have attached. find attachment for execution plan also...

Please suggest if you feel what we could have to improve the performance

Many thanks.
Attachments
Performance.xlsx (4 views, 9.00 KB)
execution_plan.sqlplan (14 views, 276.00 KB)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8995 Visits: 19028
For testing purposes, comment out the INSERT part of the batch. Run this test batch a couple of times to obtain a performance baseline.

The difference between actual and estimated rows for the index seek (node 9) on table ORDERSTOCKFACTS (index ORDERSTOCKFACTS_I6) suggest that statistics may be out of date. Run
UPDATE STATISTICS ORDERSTOCKFACTS WITH FULLSCAN
then run the test batch a couple of times.

Next, the plan shows a cost of 92% is attributable to a key lookup. Index ORDERSTOCKFACTS_I6 doesn't contain columns QUANSTOCKNKG and QUANCONDNKG, so SQL Server has to use the cluster key in index ORDERSTOCKFACTS_I6 to look these up in the table. Adding these two columns to index ORDERSTOCKFACTS_I6 as INCLUDE columns would eliminate the key lookup at the expense of slightly slower index seeks. With only two columns it's likely to be well worth it. If you're not sure how to add these two columns to the index, post the CREATE INDEX script for ORDERSTOCKFACTS_I6.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17605 Visits: 32267
Looking it over, the only hope for tuning is, as was already pointed out, to eliminate the key lookup. Otherwise, we're just processing a lot of data and that's going to take more time. Are you returning 100K rows to the client? Is that an export of data for other purposes or is the assumption that people are consuming 100K rows? If the latter, that never happens. You could push back on the requirements if so.

One other point, this is an estimated plan, so it's hard to know if we're seeing accurate statistics without being able to compare to an actual plan. I'd suggest capturing the actual plan and doing that comparison to see if your statistics maintenance is adequate.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
mote.ajit2
mote.ajit2
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 53
Thanks for reply!

Earlier I was collecting data in some temporary table so that I can use it as input to query on main table. Now I have removed that part and created another non clustered index on that table on field SALESCOMPANY(as per suggestion from Senior person) and querying data on main table directly.

Now performance is shortened to almost half time.

I hope it is acceptable Smile
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8995 Visits: 19028
mote.ajit2 (1/27/2014)
Thanks for reply!

Earlier I was collecting data in some temporary table so that I can use it as input to query on main table. Now I have removed that part and created another non clustered index on that table on field SALESCOMPANY(as per suggestion from Senior person) and querying data on main table directly.

Now performance is shortened to almost half time.

I hope it is acceptable Smile


That's certainly an improvement. Personally I think there's scope for far more improvement. If you wish to explore this possibility, then post the actual execution plan (as a .sqlplan file) of the new query.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search