Exporting Data from a SQL Database

1

I need help thinking this through. One of our clients just need data for specific PCS numbers we have. There are about 84,000 records. All I was trying to do is a simple query to get the data.

Select* from pcs
where pcs_id IN (whatever 84,000 ids are)

I got this error....This timed out and said it was using too much resources.

Then I tried to do an insert statement into an actual table I was creating. That did not work either, I got the same error.

What Can I do to prevent this from happening. This is a one time extract.

Thanks in Advance.

2

please show us the schema of the pcs table? and any indices it has?

3

How many rows were you trying to insert into the table you were creating? If that also was 84,000, try inserting a few rows - maybe 10 - to see if it is indeed timeout due to the insert taking too long for the 84,000 rows.

If you get the same message while trying to insert 10 rows, it could be that the table is blocked by some other process. Start the insert job in one query editor window, and in another, run "exec sp_who2" and look at the "Blk By" column. You should be able to see if your insert job is being blocked by some other process.

If you are able to insert 10 rows, but not 84,000 it indeed could be timeout due to resources being insufficient. But 84,000 is not a huge number, so that seems a bit strange.

Does your query actually have 84,000 items in the IN(whatever 84,000 ids are) part of your query? If you do, instead of doing that, insert the 84,000 pcs_id's into a temp table and join to that table.

4

JamesK, Yes I am able to load in 10 rows of data. Yes, I have 84,000 records and it does seem a bit strange that I am not able to do this simple query. I have loaded more than that before in other SQL codes that I have done.

5

What is the code you are using to load these ID's? Where are you getting this list of ID's to be used in your query?

Ideally - if you are generating the list of ID's from a query you could just use that query as the source to your IN clause, or as a CTE and join to the original table.

6

Thanks for all of your help. I was able to extract the data into a different table.

I have a question though. When I tried to do a Insert Statement like this below

INSERT INTO [pcs_test]
SELECT * FROM IMPACT.dbo.PCS
WHERE pcs_ID1 IN( this is where I would pull the 84,000 records)

I would get this this error....This timed out and said it was using too much resources.

But if I did this INSERT INTO Statement below it worked and there were more records (143,031).

INSERT INTO [TRPN_PCS]

SELECT Distinct*

FROM IMPACT.dbo.PCS

WHERE pcs_id1 IN (SELECT Distinct PCSP_id1 FROM IMPACT.DBO.TRPN_PCSP)

Why would the one that had more records in it work and the one that did not, would not?

7

There is a limit to how many items can be used in a list - and it isn't only associated with IN. This has to do with how SQL Server parses the query and builds the execution plan.