Query kills the server! Insert into....250k recs

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 2000 Forums

Transact-SQL (2000)

Query kills the server! Insert into....250k recs

Author

Topic

ASP_DRUG_DEALER
Yak Posting Veteran

61 Posts

Posted - 2004-05-21 : 15:15:27

Hey all-
I've got a query that I need to stream line a bit or figure out some other solution for.

I have data that I bring over to my sql box from a main frame. This happens at 1am so no preformance worries yet (I'll tweak it later). Once it is sql server (I call it the staging area) I suck it into various other databases that need it to do their daily jobs. So this query that is giving me problems is on the same box, just going from one database to the other. My query looks at the rec number(PK) in the staging area to see if it is already in the target db or not, if its not add it. I only have indexs (primary) on the rec nums in both tables and figure since this would be fine. Anyone got a slick way to do this or what I should do to my indexs?

Here is the query....

INSERT INTO E_MASTER ( E_ID, EO_YEARMONTH, EO_ACCOUNT, EO_DEPT, EO_C_ID, EO_J_ID, EO_UNITS, EO_DOLLARS, EO_P_ID, EO_O_ID, EO_B_ID, EO_DATE_LAST_MAINT, EO_TRANSFERDATE, ER_ACCOUNT, ER_DEPT, ER_C_ID, ER_J_ID, ER_P_ID, ER_O_ID, ER_B_ID, EO_TYPE )
SELECT JCDS_REC.REC_NUMBER, JCDS_REC.YEARMONTH, JCDS_REC.ACCOUNT, JCDS_REC.DEPARTMENT, JCDS_REC.CHRG_NUMBER, JCDS_REC.JOB_NUMBER, JCDS_REC.UNITS, JCDS_REC.DOLLARS, JCDS_REC.PART_NUMBER, JCDS_REC.OPERATION_SEQ_NO, JCDS_REC.BID_CATEGORY, JCDS_REC.DATE_LAST_MAINT, JCDS_REC.TRANSFER_DATE, JCDS_REC.ACCOUNT, JCDS_REC.DEPARTMENT, JCDS_REC.CHRG_NUMBER, JCDS_REC.JOB_NUMBER, JCDS_REC.PART_NUMBER, JCDS_REC.OPERATION_SEQ_NO, JCDS_REC.BID_CATEGORY, 'A' AS TYPE
FROM JCDS_REC LEFT JOIN E_MASTER ON JCDS_REC.REC_NUMBER = E_MASTER.E_ID
WHERE (((E_MASTER.E_ID) Is Null));

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-21 : 16:19:55

This query is pretty simple. The only thing to check is to make sure you have indexes on JCDS_REC.REC_NUMBER and E_MASTER.E_ID. If those are both the PK, then you already do.

You might consider doing this in batches though to avoid slowing down the server too much. You do it in batches with SET ROWCOUNT and looping til you are done. I do this for deletes so that other users aren't impacted by thousands/millions of rows being updated/inserted/deleted at once.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources