Import using SQLBulkCopy for big CSV files question - sqlserver-faq
This is a discussion on Import using SQLBulkCopy for big CSV files question - sqlserver-faq ; Ok Thanks William....
 |
| | LinkBack | Thread Tools | Display Modes |
|
#11
11-11-2008, 03:26 PM
| |||
| |||
 Re: Import using SQLBulkCopy for big CSV files question  |
|
#12
11-12-2008, 04:54 PM
| |||
| |||
| Re: Import using SQLBulkCopy for big CSV files question
Hi There, I found out that the indexes made the memory leak really bad. Removing the indexes changed the whole picture. Importing 24 mil records now consumes only 2 gb of memory instead of the entire memory of the computer which is 8gb. That memory is taken by the ms sql 2005 server service. Now I need to figure out how to realese the memory back to the OS after every file import. Since I need to import multipe files to one table. Regards, Steve |
|
#13
11-12-2008, 06:57 PM
| |||
| |||
| Re: Import using SQLBulkCopy for big CSV files question
Okay, I'm sitting next to Gert Drapers at SQL Connections. He said this is to be expected. When you use SqlBulkCopy or BCP with indexes enabled, the engine has to build a workspace in the buffer pool to hold the entire rowset as it imports the rows--sorting as it goes. Once the last row has been received, it releases this memory. So yes, disabling indexes while importing data makes sense. hth -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Steve" Hi There, I found out that the indexes made the memory leak really bad. Removing the indexes changed the whole picture. Importing 24 mil records now consumes only 2 gb of memory instead of the entire memory of the computer which is 8gb. That memory is taken by the ms sql 2005 server service. Now I need to figure out how to realese the memory back to the OS after every file import. Since I need to import multipe files to one table. Regards, Steve |
|
#14
11-12-2008, 08:47 PM
| |||
| |||
| Re: Import using SQLBulkCopy for big CSV files question
Hi William, I assume after I enable the indexes, after the import it will update itself for the new records. There are 2 ways either remove the indexes or disable them before the import. Which one is the preferred way? Regards, Steve "William Vaughn (MVP)" Okay, I'm sitting next to Gert Drapers at SQL Connections. He said this is to be expected. When you use SqlBulkCopy or BCP with indexes enabled, the engine has to build a workspace in the buffer pool to hold the entire rowset as it imports the rows--sorting as it goes. Once the last row has been received, it releases this memory. So yes, disabling indexes while importing data makes sense. hth -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Steve" Hi There, I found out that the indexes made the memory leak really bad. Removing the indexes changed the whole picture. Importing 24 mil records now consumes only 2 gb of memory instead of the entire memory of the computer which is 8gb. That memory is taken by the ms sql 2005 server service. Now I need to figure out how to realese the memory back to the OS after every file import. Since I need to import multipe files to one table. Regards, Steve |
|
#15
11-13-2008, 06:01 PM
| |||
| |||
| Re: Import using SQLBulkCopy for big CSV files question
I would disable them... -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Steve" Hi William, I assume after I enable the indexes, after the import it will update itself for the new records. There are 2 ways either remove the indexes or disable them before the import. Which one is the preferred way? Regards, Steve "William Vaughn (MVP)" Okay, I'm sitting next to Gert Drapers at SQL Connections. He said this is to be expected. When you use SqlBulkCopy or BCP with indexes enabled, the engine has to build a workspace in the buffer pool to hold the entire rowset as it imports the rows--sorting as it goes. Once the last row has been received, it releases this memory. So yes, disabling indexes while importing data makes sense. hth -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Steve" Hi There, I found out that the indexes made the memory leak really bad. Removing the indexes changed the whole picture. Importing 24 mil records now consumes only 2 gb of memory instead of the entire memory of the computer which is 8gb. That memory is taken by the ms sql 2005 server service. Now I need to figure out how to realese the memory back to the OS after every file import. Since I need to import multipe files to one table. Regards, Steve |
|
#16
11-14-2008, 10:50 AM
| |||
| |||
| Re: Import using SQLBulkCopy for big CSV files question
Thanks "William Vaughn (MVP)" I would disable them... -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Steve" Hi William, I assume after I enable the indexes, after the import it will update itself for the new records. There are 2 ways either remove the indexes or disable them before the import. Which one is the preferred way? Regards, Steve "William Vaughn (MVP)" Okay, I'm sitting next to Gert Drapers at SQL Connections. He said this is to be expected. When you use SqlBulkCopy or BCP with indexes enabled, the engine has to build a workspace in the buffer pool to hold the entire rowset as it imports the rows--sorting as it goes. Once the last row has been received, it releases this memory. So yes, disabling indexes while importing data makes sense. hth -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) __________________________________________________ __________________________________________ "Steve" Hi There, I found out that the indexes made the memory leak really bad. Removing the indexes changed the whole picture. Importing 24 mil records now consumes only 2 gb of memory instead of the entire memory of the computer which is 8gb. That memory is taken by the ms sql 2005 server service. Now I need to figure out how to realese the memory back to the OS after every file import. Since I need to import multipe files to one table. Regards, Steve |
|
#17
01-29-2010, 10:58 AM
| |||
| |||
| Re: Import using SQLBulkCopy for big CSV files question
I have 2 questions. 1. Can you send me the ISBN for "Hitchhiker's Guide to Visual Studio and SQL Server"? 2. Is there a why to do this using a DSN-less connection and if so, how? I like the fact that you are using a DataReader as opposed to a DataTable. but I need to be able to run this on client machines without the worry of creating the DSN. Thank you. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
All times are GMT -4. The time now is 08:18 PM.
