Search Results

Search found 14378 results on 576 pages for 'record count'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 80/576 | < Previous Page | 76 77 78 79 80 81 82 83 84 85 86 87  | Next Page >

  • How can I get a COUNT(col) ... GROUP BY to use an index?

    - by thecoop
    I've got a table (col1, col2, ...) with an index on (col1, col2, ...). The table has got millions of rows in it, and I want to run a query: SELECT col1, COUNT(col2) WHERE col1 NOT IN (<couple of exclusions>) GROUP BY col1 Unfortunately, this is resulting in a full table scan of the table, which takes upwards of a minute. Is there any way of getting oracle to use the index on the columns to return the results much faster?

    Read the article

  • Is there a better way to count the messages in an Message Queue (MSMQ)?

    - by Damovisa
    I'm currently doing it like this: MessageQueue queue = new MessageQueue(".\Private$\myqueue"); MessageEnumerator messageEnumerator = queue.GetMessageEnumerator2(); int i = 0; while (messageEnumerator.MoveNext()) { i++; } return i; But for obvious reasons, it just feels wrong - I shouldn't have to iterate through every message just to get a count, should I? Is there a better way?

    Read the article

  • In pdb how do you reset the list (l) command line count?

    - by Jorge Vargas
    From PDB (Pdb) help l l(ist) [first [,last]] List source code for the current file. Without arguments, list 11 lines around the current line or continue the previous listing. With one argument, list 11 lines starting at that line. With two arguments, list the given range; if the second argument is less than the first, it is a count. The "continue the previous listing" feature is really nice, but how do you turn it off?

    Read the article

  • How do I use a MySQL subquery to count the number of rows in a foreign table?

    - by James Skidmore
    I have two tables, users and reports. Each user has no, one, or multiple reports associated with it, and the reports table has a user_id field. I have the following query, and I need to add to each row a count of how many reports the user has: SELECT * FROM users LIMIT 1, 10 Do I need to use a subquery, and if so, how can I use it efficently? The reports table has thousands and thousands of rows.

    Read the article

  • Any way to make this PostgreSQL count query any faster?

    - by Ben Dauphinee
    I'm running a case-insensitive search on a table with 7.2 million rows, and I was wondering if there was any way to make this query any faster? Currently, it takes approx 11.6 seconds to execute, with just one search parameter, and I'm worried that as soon as I add more than one, this query will become massively slow. SELECT count(*) FROM "exif_parse" WHERE (description ~* 'canon')

    Read the article

  • How to convert SQL Statement with TOP, COUNT and GROUP BY to return an object list with LINQ

    - by Junior Mayhé
    Hello guys does anyone know how to convert this SQL statement to a LINQ to a List? SELECT TOP(5) COUNT(CategoryId), CategoryName FROM Tickets GROUP BY CategoryName The result would be something like public static List<Categories> List() { MyEntities db = new MyEntities(); /* here it should return a list o Category type */; return db.Category.GroupBy(...).OrderBy(...); }

    Read the article

  • For a Chemical Equation Balancer App (Android), how do I count the number of atoms of each element in each term?

    - by Upas
    This is my app: If someone enters "C6H12O6+O2=CO2+H2O", then I have already written code to split the equation into terms, so in an ArrayList called rterms I have the strings: C6H12O6 CO2 and in another ArrayList called pterms, I have: CO2 H2O I need to count the number of C's in each term of the reactants, so 6 for term 1, 0 for term 2, and then the H's and then O's. How would I do this? Any help is appreciated.

    Read the article

  • How do I get a array to just count how many numbers there are instead of counting the value of each number?

    - by Chad Loos
    //This is the output of the program *** start of 276 2D Arrays_03.cpp program *** Number Count Total 1 3 3 2 6 9 3 15 24 4 6 30 5 9 39 *** end of 276 2D Arrays_03.cpp program *** #include <iostream> #include <string> #include <iomanip> using namespace std; const int COLUMN_SIZE = 13; int main(void) { const int ROW_SIZE = 3; const int COUNT_SIZE = 5; void countValues(const int[][COLUMN_SIZE], const int, int[]); void display(const int [], const int); int numbers[ROW_SIZE][COLUMN_SIZE] = {{1, 3, 4, 5, 3, 2, 3, 5, 3, 4, 5, 3, 2}, {2, 1, 3, 4, 5, 3, 2, 3, 5, 3, 4, 5, 3}, {3, 4, 5, 3, 2, 1, 3, 4, 5, 3, 2, 3, 5}}; int counts[COUNT_SIZE] = {0}; string choice; cout << "*** start of 276 2D Arrays_03.cpp program ***" << endl; cout << endl; countValues(numbers, ROW_SIZE, counts); display(counts, COUNT_SIZE); cout << endl; cout << endl; cout << "*** end of 276 2D Arrays_03.cpp program ***" << endl << endl; cin.get(); return 0; } // end main() This is the function where I need to count each of the values. I know how to sum rows and cols, but I'm not quite sure of the code to just tally the values themselves. void countValues(const int numbers[][COLUMN_SIZE], const int ROW_SIZE, int counts[]) This is what I have so far. { for (int index = 0; index < ROW_SIZE; index++) counts[index]; {

    Read the article

  • Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host

    - by Paul J. Warner
    I am having an issue with a program where after 6 mins +- 5 secs we get the above exception. Some more info about the exception stacktrace is below. This all happens pretty religiously, 6 mins goes by and bam the following 3 exeptions. We have the application installed in 2 other environments and it is working fine there. I am hoping to find some server settings either IIS 6 or Server 2003 settings that may be causing this issue to occur. I have reviewed some of the similar questions and don't see very many answers. I am hoping that maybe the information I have provided may help a little bit. 208741,Exception,,,,2011-06-21 00:30:14.193,SERVERNAME,2624,1,CLIENTNAME,The underlying connection was closed: An unexpected error occurred on a receive. , at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request) at Microsoft.Web.Services3.WebServicesClientProtocol.GetResponse(WebRequest request, IAsyncResult result) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security._SslStream.StartFrameHeader(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.StartReading(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.ProcessRead(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.TlsStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead),2004437127,114,1 208742,Exception,,,,2011-06-21 00:30:14.227,SERVERNAME,2624,1,CLIENTNAME,Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. , at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security._SslStream.StartFrameHeader(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.StartReading(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.ProcessRead(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.TlsStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead),2004437127,114,1 208743,Exception,,,,2011-06-21 00:30:14.287,SERVERNAME,2624,1,CLIENTNAME,An existing connection was forcibly closed by the remote host , at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size),-691097507,62,1

    Read the article

  • Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.

    - by Paul J. Warner
    I am having an issue with a program where after 6 mins +- 5 secs we get the above exception. Some more info about the exception stacktrace is below. This all happens pretty religiously, 6 mins goes by and bam the following 3 exeptions. We have the application installed in 2 other environments and it is working fine there. I am hoping to find some server settings either IIS 6 or Server 2003 settings that may be causing this issue to occur. I have reviewed some of the similar questions and don't see very many answers. I am hoping that maybe the information I have provided may help a little bit. 208741,Exception,,,,2011-06-21 00:30:14.193,SERVERNAME,2624,1,CLIENTNAME,The underlying connection was closed: An unexpected error occurred on a receive. , at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request) at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request) at Microsoft.Web.Services3.WebServicesClientProtocol.GetResponse(WebRequest request, IAsyncResult result) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security._SslStream.StartFrameHeader(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.StartReading(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.ProcessRead(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.TlsStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead),2004437127,114,1 208742,Exception,,,,2011-06-21 00:30:14.227,SERVERNAME,2624,1,CLIENTNAME,Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. , at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count) at System.Net.Security._SslStream.StartFrameHeader(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.StartReading(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.Security._SslStream.ProcessRead(Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest) at System.Net.TlsStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.PooledStream.Read(Byte[] buffer, Int32 offset, Int32 size) at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead),2004437127,114,1 208743,Exception,,,,2011-06-21 00:30:14.287,SERVERNAME,2624,1,CLIENTNAME,An existing connection was forcibly closed by the remote host , at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size),-691097507,62,1

    Read the article

  • Refresh Button in java

    - by lakshmi
    I need to make a simple refresh button for a gui java program I have the button made but its not working properly. I just am not sure what the code needs to be to make the button work. Any help would be really appreciated.I figured the code below ` public class CompanySample extends Panel { DBServiceAsync dbService = GWT.create(DBService.class); Panel panel = new Panel(); public Panel CompanySampledetails(com.viji.example.domain.Record trec, int count) { panel.setWidth(800); Panel borderPanel = new Panel(); Panel centerPanel = new Panel(); final FormPanel formPanel = new FormPanel(); formPanel.setFrame(true); formPanel.setLabelAlign(Position.LEFT); formPanel.setPaddings(5); formPanel.setWidth(800); final Panel inner = new Panel(); inner.setLayout(new ColumnLayout()); Panel columnOne = new Panel(); columnOne.setLayout(new FitLayout()); GridPanel gridPanel = null; if (gridPanel != null) { gridPanel.getView().refresh(); gridPanel.clear(); } gridPanel = new SampleGrid(trec); gridPanel.setHeight(450); gridPanel.setTitle("Company Data"); final RowSelectionModel sm = new RowSelectionModel(true); sm.addListener(new RowSelectionListenerAdapter() { public void onRowSelect(RowSelectionModel sm, int rowIndex, Record record) { formPanel.getForm().loadRecord(record); } }); gridPanel.setSelectionModel(sm); gridPanel.doOnRender(new Function() { public void execute() { sm.selectFirstRow(); } }, 10); columnOne.add(gridPanel); inner.add(columnOne, new ColumnLayoutData(0.6)); final FieldSet fieldSet = new FieldSet(); fieldSet.setLabelWidth(90); fieldSet.setTitle("company Details"); fieldSet.setAutoHeight(true); fieldSet.setBorder(false); final TextField txtcompanyname = new TextField("Name", "companyname", 120); final TextField txtcompanyaddress = new TextField("Address", "companyaddress", 120); final TextField txtcompanyid = new TextField("Id", "companyid", 120); txtcompanyid.setVisible(false); fieldSet.add(txtcompanyid); fieldSet.add(txtcompanyname); fieldSet.add(txtcompanyaddress); final Button addButton = new Button(); final Button deleteButton = new Button(); final Button modifyButton = new Button(); final Button refeshButton = new Button(); addButton.setText("Add"); deleteButton.setText("Delete"); modifyButton.setText("Modify"); refeshButton.setText("Refresh"); fieldSet.add(addButton); fieldSet.add(deleteButton); fieldSet.add(modifyButton); fieldSet.add(refeshButton); final ButtonListenerAdapter buttonClickListener = new ButtonListenerAdapter() { public void onClick(Button button, EventObject e) { if (button == refeshButton) { sendDataToServ("Refresh"); } } }; addButton.addListener(new ButtonListenerAdapter() { @Override public void onClick(Button button, EventObject e) { if (button.getText().equals("Add")) { sendDataToServer("Add"); } } private void sendDataToServer(String action) { String txtcnameToServer = txtcompanyname.getText().trim(); String txtcaddressToServer = txtcompanyaddress.getText().trim(); if (txtcnameToServer.trim().equals("") || txtcaddressToServer.trim().equals("")) { Window .alert("Incomplete Data. Fill/Select all the needed fields"); action = "Nothing"; } AsyncCallback ascallback = new AsyncCallback<String>() { @Override public void onFailure(Throwable caught) { Window.alert("Record not inserted"); } @Override public void onSuccess(String result) { Window.alert("Record inserted"); } }; if (action.trim().equals("Add")) { System.out.println("Before insertServer"); dbService.insertCompany(txtcnameToServer, txtcaddressToServer, ascallback); } } }); deleteButton.addListener(new ButtonListenerAdapter() { @Override public void onClick(Button button, EventObject e) { if (button.getText().equals("Delete")) { sendDataToServer("Delete"); } } private void sendDataToServer(String action) { String txtcidToServer = txtcompanyid.getText().trim(); String txtcnameToServer = txtcompanyname.getText().trim(); String txtcaddressToServer = txtcompanyaddress.getText().trim(); if (!action.equals("Delete")) { if (txtcidToServer.trim().equals("") || txtcnameToServer.trim().equals("") || txtcaddressToServer.trim().equals("")) { Window .alert("Incomplete Data. Fill/Select all the needed fields"); action = "Nothing"; } } else if (txtcidToServer.trim().equals("")) { Window.alert("Doesn't deleted any row"); } AsyncCallback ascallback = new AsyncCallback<String>() { @Override public void onFailure(Throwable caught) { // TODO Auto-generated method stub Window.alert("Record not deleted"); } @Override public void onSuccess(String result) { Window.alert("Record deleted"); } }; if (action.trim().equals("Delete")) { System.out.println("Before deleteServer"); dbService.deleteCompany(Integer.parseInt(txtcidToServer), ascallback); } } }); modifyButton.addListener(new ButtonListenerAdapter() { @Override public void onClick(Button button, EventObject e) { if (button.getText().equals("Modify")) { sendDataToServer("Modify"); } } private void sendDataToServer(String action) { // TODO Auto-generated method stub System.out.println("ACTION :----->" + action); String txtcidToServer = txtcompanyid.getText().trim(); String txtcnameToServer = txtcompanyname.getText().trim(); String txtcaddressToServer = txtcompanyaddress.getText().trim(); if (txtcnameToServer.trim().equals("") || txtcaddressToServer.trim().equals("")) { Window .alert("Incomplete Data. Fill/Select all the needed fields"); action = "Nothing"; } AsyncCallback ascallback = new AsyncCallback<String>() { @Override public void onFailure(Throwable caught) { Window.alert("Record not Updated"); } @Override public void onSuccess(String result) { Window.alert("Record Updated"); } }; if (action.equals("Modify")) { System.out.println("Before UpdateServer"); dbService.modifyCompany(Integer.parseInt(txtcidToServer), txtcnameToServer, txtcaddressToServer, ascallback); } } }); inner.add(new PaddedPanel(fieldSet, 0, 10, 0, 0), new ColumnLayoutData( 0.4)); formPanel.add(inner); borderPanel.add(centerPanel, new BorderLayoutData(RegionPosition.CENTER)); centerPanel.add(formPanel); panel.add(borderPanel); return panel; } public void sendDataToServ(String action) { AsyncCallback<com.viji.example.domain.Record> callback = new AsyncCallback<com.viji.example.domain.Record>() { @Override public void onFailure(Throwable caught) { System.out.println("Failure"); } public void onSuccess(com.viji.example.domain.Record result) { CompanySampledetails(result, 1); } }; dbService.getRecords(callback); } } class SampleGrid extends GridPanel { private static BaseColumnConfig[] columns = new BaseColumnConfig[] { new ColumnConfig("Name", "companyname", 120, true), new ColumnConfig("Address", "companyaddress", 120, true), }; private static RecordDef recordDef = new RecordDef(new FieldDef[] { new IntegerFieldDef("companyid"), new StringFieldDef("companyname"), new StringFieldDef("companyaddress") }); public SampleGrid(com.viji.example.domain.Record trec) { Object[][] data = new Object[0][];// getCompanyDataSmall(); MemoryProxy proxy = new MemoryProxy(data); ArrayReader reader = new ArrayReader(recordDef); Store store = new SimpleStore(new String[] { "companyid", "companyname", "companyaddress" }, new Object[0][]); store.load(); ArrayList<Company> Companydetails = trec.getCompanydetails(); for (int i = 0; i < Companydetails.size(); i++) { Company company = Companydetails.get(i); store.add(recordDef.createRecord(new Object[] { company.getCompanyid(), company.getCompanyName(), company.getCompanyaddress() })); } store.commitChanges(); setStore(store); ColumnModel columnModel = new ColumnModel(columns); setColumnModel(columnModel); } } `

    Read the article

  • Bulk inserting best way to about it? + Helping me understand fully what I found so far

    - by chobo2
    Hi So I saw this post here and read it and it seems like bulk copy might be the way to go. http://stackoverflow.com/questions/682015/whats-the-best-way-to-bulk-database-inserts-from-c I still have some questions and want to know how things actually work. So I found 2 tutorials. http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx First way uses 2 ado.net 2.0 features. BulkInsert and BulkCopy. the second one uses linq to sql and OpenXML. This sort of appeals to me as I am using linq to sql already and prefer it over ado.net. However as one person pointed out in the posts what he just going around the issue at the cost of performance( nothing wrong with that in my opinion) First I will talk about the 2 ways in the first tutorial I am using VS2010 Express, .net 4.0, MVC 2.0, SQl Server 2005 Is ado.net 2.0 the most current version? Based on the technology I am using, is there some updates to what I am going to show that would improve it somehow? Is there any thing that these tutorial left out that I should know about? BulkInsert I am using this table for all the examples. CREATE TABLE [dbo].[TBL_TEST_TEST] ( ID INT IDENTITY(1,1) PRIMARY KEY, [NAME] [varchar](50) ) SP Code USE [Test] GO /****** Object: StoredProcedure [dbo].[sp_BatchInsert] Script Date: 05/19/2010 15:12:47 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_BatchInsert] (@Name VARCHAR(50) ) AS BEGIN INSERT INTO TBL_TEST_TEST VALUES (@Name); END C# Code /// <summary> /// Another ado.net 2.0 way that uses a stored procedure to do a bulk insert. /// Seems slower then "BatchBulkCopy" way and it crashes when you try to insert 500,000 records in one go. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchInsert() { // Get the DataTable with Rows State as RowState.Added DataTable dtInsertRows = GetDataTable(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("sp_BatchInsert", connection); command.CommandType = CommandType.StoredProcedure; command.UpdatedRowSource = UpdateRowSource.None; // Set the Parameter with appropriate Source Column Name command.Parameters.Add("@Name", SqlDbType.VarChar, 50, dtInsertRows.Columns[0].ColumnName); SqlDataAdapter adpt = new SqlDataAdapter(); adpt.InsertCommand = command; // Specify the number of records to be Inserted/Updated in one go. Default is 1. adpt.UpdateBatchSize = 1000; connection.Open(); int recordsInserted = adpt.Update(dtInsertRows); connection.Close(); } So first thing is the batch size. Why would you set a batch size to anything but the number of records you are sending? Like I am sending 500,000 records so I did a Batch size of 500,000. Next why does it crash when I do this? If I set it to 1000 for batch size it works just fine. System.Data.SqlClient.SqlException was unhandled Message="A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)" Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=20 LineNumber=0 Number=233 Server="" State=0 StackTrace: at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataTable dataTable) at TestIQueryable.Program.BatchInsert() in C:\Users\a\Downloads\TestIQueryable\TestIQueryable\TestIQueryable\Program.cs:line 124 at TestIQueryable.Program.Main(String[] args) in C:\Users\a\Downloads\TestIQueryable\TestIQueryable\TestIQueryable\Program.cs:line 16 InnerException: Time it took to insert 500,000 records with insert batch size of 1000 took "2 mins and 54 seconds" Of course this is no official time I sat there with a stop watch( I am sure there are better ways but was too lazy to look what they where) So I find that kinda slow compared to all my other ones(expect the linq to sql insert one) and I am not really sure why. Next I looked at bulkcopy /// <summary> /// An ado.net 2.0 way to mass insert records. This seems to be the fastest. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchBulkCopy() { // Get the DataTable DataTable dtInsertRows = GetDataTable(); using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = "TBL_TEST_TEST"; // Number of records to be processed in one go sbc.BatchSize = 500000; // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table // sbc.ColumnMappings.Add("ID", "ID"); sbc.ColumnMappings.Add("NAME", "NAME"); // Number of records after which client has to be notified about its status sbc.NotifyAfter = dtInsertRows.Rows.Count; // Event that gets fired when NotifyAfter number of records are processed. sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied); // Finally write to server sbc.WriteToServer(dtInsertRows); sbc.Close(); } } This one seemed to go really fast and did not even need a SP( can you use SP with bulk copy? If you can would it be better?) BatchCopy had no problem with a 500,000 batch size.So again why make it smaller then the number of records you want to send? I found that with BatchCopy and 500,000 batch size it took only 5 seconds to complete. I then tried with a batch size of 1,000 and it only took 8 seconds. So much faster then the bulkinsert one above. Now I tried the other tutorial. USE [Test] GO /****** Object: StoredProcedure [dbo].[spTEST_InsertXMLTEST_TEST] Script Date: 05/19/2010 15:39:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText) AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData INSERT INTO TBL_TEST_TEST(NAME) SELECT XMLProdTable.NAME FROM OPENXML(@hDoc, 'ArrayOfTBL_TEST_TEST/TBL_TEST_TEST', 2) WITH ( ID Int, NAME varchar(100) ) XMLProdTable EXEC sp_xml_removedocument @hDoc C# code. /// <summary> /// This is using linq to sql to make the table objects. /// It is then serailzed to to an xml document and sent to a stored proedure /// that then does a bulk insert(I think with OpenXML) /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertXMLBatch() { using (TestDataContext db = new TestDataContext()) { TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[500000]; for (int count = 0; count < 500000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; testRecords[count] = testRecord; } StringBuilder sBuilder = new StringBuilder(); System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder); XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[])); serializer.Serialize(sWriter, testRecords); db.insertTestData(sBuilder.ToString()); } } So I like this because I get to use objects even though it is kinda redundant. I don't get how the SP works. Like I don't get the whole thing. I don't know if OPENXML has some batch insert under the hood but I do not even know how to take this example SP and change it to fit my tables since like I said I don't know what is going on. I also don't know what would happen if the object you have more tables in it. Like say I have a ProductName table what has a relationship to a Product table or something like that. In linq to sql you could get the product name object and make changes to the Product table in that same object. So I am not sure how to take that into account. I am not sure if I would have to do separate inserts or what. The time was pretty good for 500,000 records it took 52 seconds The last way of course was just using linq to do it all and it was pretty bad. /// <summary> /// This is using linq to sql to to insert lots of records. /// This way is slow as it uses no mass insert. /// Only tried to insert 50,000 records as I did not want to sit around till it did 500,000 records. /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertAll() { using (TestDataContext db = new TestDataContext()) { db.CommandTimeout = 600; for (int count = 0; count < 50000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; db.TBL_TEST_TESTs.InsertOnSubmit(testRecord); } db.SubmitChanges(); } } I did only 50,000 records and that took over a minute to do. So I really narrowed it done to the linq to sql bulk insert way or bulk copy. I am just not sure how to do it when you have relationship for either way. I am not sure how they both stand up when doing updates instead of inserts as I have not gotten around to try it yet. I don't think I will ever need to insert/update more than 50,000 records at one type but at the same time I know I will have to do validation on records before inserting so that will slow it down and that sort of makes linq to sql nicer as your got objects especially if your first parsing data from a xml file before you insert into the database. Full C# code using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Xml.Serialization; using System.Data; using System.Data.SqlClient; namespace TestIQueryable { class Program { private static string connectionString = ""; static void Main(string[] args) { BatchInsert(); Console.WriteLine("done"); } /// <summary> /// This is using linq to sql to to insert lots of records. /// This way is slow as it uses no mass insert. /// Only tried to insert 50,000 records as I did not want to sit around till it did 500,000 records. /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertAll() { using (TestDataContext db = new TestDataContext()) { db.CommandTimeout = 600; for (int count = 0; count < 50000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; db.TBL_TEST_TESTs.InsertOnSubmit(testRecord); } db.SubmitChanges(); } } /// <summary> /// This is using linq to sql to make the table objects. /// It is then serailzed to to an xml document and sent to a stored proedure /// that then does a bulk insert(I think with OpenXML) /// http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx /// </summary> private static void LinqInsertXMLBatch() { using (TestDataContext db = new TestDataContext()) { TBL_TEST_TEST[] testRecords = new TBL_TEST_TEST[500000]; for (int count = 0; count < 500000; count++) { TBL_TEST_TEST testRecord = new TBL_TEST_TEST(); testRecord.NAME = "Name : " + count; testRecords[count] = testRecord; } StringBuilder sBuilder = new StringBuilder(); System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder); XmlSerializer serializer = new XmlSerializer(typeof(TBL_TEST_TEST[])); serializer.Serialize(sWriter, testRecords); db.insertTestData(sBuilder.ToString()); } } /// <summary> /// An ado.net 2.0 way to mass insert records. This seems to be the fastest. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchBulkCopy() { // Get the DataTable DataTable dtInsertRows = GetDataTable(); using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)) { sbc.DestinationTableName = "TBL_TEST_TEST"; // Number of records to be processed in one go sbc.BatchSize = 500000; // Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table // sbc.ColumnMappings.Add("ID", "ID"); sbc.ColumnMappings.Add("NAME", "NAME"); // Number of records after which client has to be notified about its status sbc.NotifyAfter = dtInsertRows.Rows.Count; // Event that gets fired when NotifyAfter number of records are processed. sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied); // Finally write to server sbc.WriteToServer(dtInsertRows); sbc.Close(); } } /// <summary> /// Another ado.net 2.0 way that uses a stored procedure to do a bulk insert. /// Seems slower then "BatchBulkCopy" way and it crashes when you try to insert 500,000 records in one go. /// http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbTrip.aspx#_Toc196622241 /// </summary> private static void BatchInsert() { // Get the DataTable with Rows State as RowState.Added DataTable dtInsertRows = GetDataTable(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand("sp_BatchInsert", connection); command.CommandType = CommandType.StoredProcedure; command.UpdatedRowSource = UpdateRowSource.None; // Set the Parameter with appropriate Source Column Name command.Parameters.Add("@Name", SqlDbType.VarChar, 50, dtInsertRows.Columns[0].ColumnName); SqlDataAdapter adpt = new SqlDataAdapter(); adpt.InsertCommand = command; // Specify the number of records to be Inserted/Updated in one go. Default is 1. adpt.UpdateBatchSize = 500000; connection.Open(); int recordsInserted = adpt.Update(dtInsertRows); connection.Close(); } private static DataTable GetDataTable() { // You First need a DataTable and have all the insert values in it DataTable dtInsertRows = new DataTable(); dtInsertRows.Columns.Add("NAME"); for (int i = 0; i < 500000; i++) { DataRow drInsertRow = dtInsertRows.NewRow(); string name = "Name : " + i; drInsertRow["NAME"] = name; dtInsertRows.Rows.Add(drInsertRow); } return dtInsertRows; } static void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) { Console.WriteLine("Number of records affected : " + e.RowsCopied.ToString()); } } }

    Read the article

  • How to properly name record creation(insertion) datetime field ?

    - by alpav
    If I create a table with datetime default getdate() field that is intended to keep date&time of record insertion, which name is better to use for that field ? I like to use Created and I've seen people use DateCreated or CreateDate. Other possible candidates that I can think of are: CreatedDate, CreateTime, TimeCreated, CreateDateTime, DateTimeCreated, RecordCreated, Inserted, InsertedDate, ... From my point of view anything with Date inside name looks bad because it can be confused with date part in case if I have 2 fields: CreateDate,CreateTime, so I wonder if there are any specific recommendations/standards in that area based on real reasons, not just style, mood or consistency. Of course, if there are 100 existing tables and this is table 101 then I would use same naming convention as used in those 100 tables for the sake of consistency, but this question is about first table in first database in first server in first application.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 76 77 78 79 80 81 82 83 84 85 86 87  | Next Page >