SQL - Record limits

[TechMonkey]

I'm trying to export many records from an SQL server. Now in my mind it isn't a lot of data but SQL seems to be having issues, which is annoying. It is a simple query to dump 4 columns from one database, no joins or lookups or where statements. I know there are 16000+ records in the table but I would expect SQL to be able to handle that. It seems to jam around 1500 records and then just churns away never ending, I left it to run last night and after 4 hours I killed it.

 

I've checked execution timeouts (set to 0) and remote timeouts (also set to 0) and can't think why it won't handle it. Server isn't stressed. I even cut the query down to a years worth, which is 3100 records and it still just goes to between 1000 and 1500 records.

 

Does anyone have any ideas about other limits that might be in place or a way to get all the data out easily?

[pcstru]

Any particular flavor of SQL server? MySQL, MsSQL, Oracle, Postgres etc? How are you connecting to the server and running the Query? What is then holding and pushing the result set to the file?

[jinnantonnixx]

Exporting 16,000 records should barely raise the pulse of any SQL server.

I was going to mention a cross-join throwing a spanner in the works, but you say that there are no joins. Paste your query here, just in case there's something strange with it.

[TechMonkey]

Sorry, writing things too quickly. This is MSSQL, using SQL Server Management Studio directly on the server. The only thing I can think of is if a transaction lock is causing an issue as it doesn't seem to time out, just stop. I may have to try a late at night query to see how that fairs.

 

I'm glad @Jinnantonixx you agree it shouldn't raise the pulse, that was my understanding and I began to doubt myself.

 

The SQL query is literally 6 lines, a SELECT, 4 columns, and the FROM.

 

Just had an idea to make a copy of the Database and try running against that so I'll see how that goes as that would eliminate the record lock theory and that still stalls so it isn't that.

[TechMonkey]

Right, I suddenly remembered I was using a view, not a direct table. So having investigated it looks like the view, or a function that view is using, is bad. If I get the data direct from the table it works as expected... Joy, now have to try and get the data that is missing.