April 25, 2002 at 4:20 am
Hello,
I'm writing a simple inner join on one temporary table and another permanent table to retrieve 300,000 records (total 20 character fields - all required). Join is only on one field.
Currently using Query Analyzer, after retrieving 280,000 records, client gives Virtual Memory full error and stops query execution.
Join on temp table and permanent table didn't take much time, returning data back to client gives the virtual memory error on client.
I can confirm this because I inserted the result in another temp table and then tried retrieval from new temp table.
I have few constraints - I'm using SQL 7.0 and I cannot avoid temp table as the process would be multi-user.
Can you please help me out??
Thanks in advance!!
April 25, 2002 at 4:33 am
This is not uncommon if the client machine does not have enough memory and VM to support the data amount. What is the clients total ram and their VM size (Should be set a minimum of 2 times the size of the physical and as much as possible on the max). Also if they have a small drive they may not have enough space if they use most of it. Check to free space in common areas (IE Temp, Temp folder, apps not used, data files not use, .tmp files).
2nd what do you mean cannot avoid temp tables as process is multi-user, can you describe what is going on? Temp tables are generally no more than unthought of subquries or joins but there are cases where they are not and I like to be sure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 04/25/2002 04:33:47 AM
April 25, 2002 at 5:05 am
If you're using ADO with a client side cursor it's going to pull every bit of that data to the client. Try switching to a server cursor. Beyond that, is 300k rows really workable?
Andy
April 25, 2002 at 12:08 pm
Let me explain the whole process here - We have IIS server, on which we are creating a text file having IDs. I'm passing the file name and UNC path to SP. In SP, I'm creating temp table with Index. Next step is I'm Bulk Inserting the data into temp table. There might be multiple users doing same process, hence I need temp table.
After that I'm joining it with another table (from main database). This complete operation takes 2-3 mins (for 300,000 records), which is within acceptable range.
Now, I want the data back to IIS server. Hence, I was trying to send it back to IIS (requestor). I don't mind if I could dump the data (after joinning) to a file (csv file). Later I can copy the file back to IIS.
I know I can do it in SQL 2K (correct me if I'm wrong) but I can not switch from SQL 7 to 2K at this moment. Can I any how do it in SQL 7??
Thanks a Lot!!
April 25, 2002 at 12:45 pm
You can use bcp and xp_cmdshell to export it.
You don't need a temp table, though that might be easier. you can make a real table and add a SPID column. Populate that with the SPID of the connection.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply