question

paws27284 avatar image
paws27284 asked

additional criteria results in longer processing time

I am running an SSIS Package with in which 16 dataflow tasks reading an Oracle DB complete in about an hour. I found one task that was returning 54M rows in which we were processing every day. This is the original query running on SQL 2005;using Native OLE db\Microsoft OLE DB Provider for Oracle; SELECT COMPANY , EMPLOYEE , CHECK_ID , OBJ_ID , DEPARTMENT , RECORD_TYPE , CHECK_TYPE , DST_ACCT_UNIT , DST_ACCOUNT , DIST_COMPANY , DED_CODE , DIST_AMT , GL_DATE , JOB_CODE , POSITION FROM LAWSON.PRDISTRIB WHERE COMPANY = 30 AND DED_CODE <> ' ' AND DED_CODE IS NOT NULL We are moving to a new set of servers and I decided we did not need to reprocess history every time and added date criteria to narrow our results. It now looks like this using SQL 2008 R2 and Native OLE DB\Oracle Provider for OLE DB and has a much faster processor. SELECT COMPANY , EMPLOYEE , CHECK_ID , OBJ_ID , DEPARTMENT , RECORD_TYPE , CHECK_TYPE , DST_ACCT_UNIT , DST_ACCOUNT , DIST_COMPANY , DED_CODE , DIST_AMT , GL_DATE , JOB_CODE , POSITION FROM LAWSON.PRDISTRIB WHERE COMPANY = 30 AND GL_DATE > '2012-09-30 00:00:00' AND DED_CODE <> ' ' AND DED_CODE IS NOT NULL The package now takes over 3 hours to pull data. Both are running 32 bit mode. Would adding the date make that much of a difference? I thought it would be better to reduce the number of rows coming into the pipe. thoughts? suggestions? thanks! Paws
sql-server-2005ssissql-server-2008-r2oracle32-bit
13 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Could be down to indexes. Can you post your query plan?
1 Like 1 ·
And check your statistics. But a query plan would be best.
1 Like 1 ·
Wait. Is that query getting data from Oracle or from SQL Server?
1 Like 1 ·
Query is getting data from Oracle. There are no indexes on the Sql side, don't know about the Oracle side. Last night I ran the original query in the new environment and it took almost 6 hours. How do I look at a query plan if the query is hitting an Oracle database?
0 Likes 0 ·
How many rows are returned with the date filter?
0 Likes 0 ·
Show more comments

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.