logical_reads
-
Above removing the order by and checking the disks.
Have you tried inserting intermediate results (for each join) to temp table? >> divide and conquer.
That would also tell you what join is screwing with you and where to stat optimizing.
-
Before removing the order by, make sure that the SSIS package output is not sorted. You will probably be better off to leave the sort in the query instead of adding a sort task to your SSIS package.
-
Ninja's_RGR'us (4/8/2011)
Above removing the order by and checking the disks.Have you tried inserting intermediate results (for each join) to temp table? >> divide and conquer.
That would also tell you what join is screwing with you and where to stat optimizing.
TempDB is on a separate disk.
Will check if divide and conquer helps once I have the temp tables loaded.
Only 1 of the temp tables has an index.
-
Does just the select portion run to completion (run it from SSMS)? If so, post the execution plan and IO statistics (SET STATISTICS IO ON). If not, post the estimated execution plan
SELECT t.field1_varchar_255,t.field2_varchar_255
,r.field1_int
,r.field2_datetime
,tkr.field1_varchar_255
FROM #temp t
INNER JOIN Table1 r -- rows = 53,909,104 & data = 4,584,088 KB
ON r.FK = t.ID
INNER JOIN Table2 aar -- rows = 198,426,511 & data = 19,389,648 KB
ON r.ID = aar.ID
AND aar.TYPE IN (4010,4110,4109,4120,2116,4087,4113,4347,4105,4010,4348,4345,4349)
INNER JOIN #temp2 ls -- rows = 60,056,519 & data = 1,953,000 KB
ON ls.FK = aar.ID
AND ls.TYPE = 10002
LEFT JOIN Table3 tkr -- rows = 1,604 & data = 88 KB
ON tkr.TYPE1 = aar.TYPE
AND tkr.TYPE2 = 'MyType'
WHERE r.field2_datetime >= CONVERT(varchar(7), DATEADD(mm, -1, GETDATE()), 121) + '-01' --1st of previous month
ORDER BY Field1
,Field2
,Field3
OPTION (MAXDOP 1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Charles Hearn (4/8/2011)
Before removing the order by, make sure that the SSIS package output is not sorted. You will probably be better off to leave the sort in the query instead of adding a sort task to your SSIS package.It all depends on whether the data needs to be in order in the file, however for tuning the query the Order by is not needed, then add in the order by after its working to see if that is causing the problem.
I would also be tempted to use an SQL statement on the OLEDB source, with an Order by Clause, if the Order by on the Insert query is the problem.
Its just a question of where you want the bottle neck, and which one gives a better end to end solution.
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
Actually this thread might segway well into the sort issue to find out how hard it's hitting the server.
http://www.sqlservercentral.com/Forums/Topic1090063-391-1.aspx#bm1090123
-
Sean,
There's a couple of things going on here and we're going to need some more information, unfortunately.
1) I know you mentioned this is psuedocode. We're probably going to need to see the real query to help long haul here.
2) As Gail mentioned, when optimizing any query, start with the query plan(s). Actuals by preference, estimated if necessary.
3) Describe the SSIS process to us, if you can. Snapshots here would help us visualize your process. I'm hoping we may be able to leverage pieces of SSIS to help you get this faster as well.
4) Is all this data coming from a single server or are you subtabling data on this server from other servers? This goes back more to your data flow design.
5) Your drive IO is one of the things killing you as previously mentioned (PAGELATCH_IO). What can you tell us about your drive arrangements? What system dbs are where? Are there multiple filegroups (on separate LUNS) on the source tables? Things like that.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply