BUG! The following code has a bug. Add sb.length=0 so the StringBuilder resets. See Post with correction. This code is now producing 150-200 files/second.
Going from best backwards, I took the code from yesterday and threw it into a script task (SQL Server 2008 R2). Code is below, and surprisingly, running out of debug mode, it ran very closely to the c# console application. I had to make only a few changes when migrating it to BIDS, so check it out.
Come up with anything faster than mine yet???
Lee Everest
---------------------
Script Task code - c#
- using System;
- using System.Data;
- using Microsoft.SqlServer.Dts.Runtime;
- using System.Windows.Forms;
- using System.IO;
- using System.Collections.Generic;
- using System.Text;
- using System.Data.SqlClient;
-
- namespace ST_eabdae14a6634c11b8193c743bc05b6a.csproj
- {
- [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
- public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
- {
-
- StringBuilder sb = new StringBuilder();
- #region VSTA generated code
- enum ScriptResults
- {
- Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
- Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
- };
- #endregion
-
- public void SaveToFile(string fileName)
- {
- System.IO.TextWriter w = new System.IO.StreamWriter(@"c:\temp\files\" + fileName + ".dat");
- w.Write(sb.ToString());
- w.Flush();
- w.Close();
- }
-
- public void Main()
- {
-
- SqlConnection sqlConnection = null;
- int maxvalue=151000;
- string dbconn = "Application Name=SqlTest;Data Source=SQLVM1;Trusted_Connection=yes;database=tempdb";
- using(sqlConnection = new SqlConnection(dbconn))
- {
- sqlConnection.Open();
- SqlParameter param = new SqlParameter("@id", SqlDbType.Int, 2);
- SqlDataReader reader;
-
- using (SqlCommand cmd = new SqlCommand(null, sqlConnection))
- {
- param.Value = 1;
- cmd.CommandType = CommandType.Text;
- param.Direction = ParameterDirection.Input;
- cmd.Parameters.Add(param);
- while ((int)param.Value<maxvalue)
- {
- cmd.CommandText = "SELECT id FROM deleteTablexus WHERE idx = @id";
- reader = cmd.ExecuteReader();
- while (reader.Read())
- sb.Append(reader[0].ToString() + "\r\n");
-
- SaveToFile(param.Value.ToString());
- reader.Close();
- param.Value = (int)param.Value + 1;
- }
- }
- }
-
- Dts.TaskResult = (int)ScriptResults.Success;
- }
- }
- }
Almost forgot – the code to create the table. Use this to generate the data, in case you decide to take on my challenge. L8rs.
TSQL to Gen the data...
-
-
- DROP TABLE deleteTablexus
- GO
- SELECT a1.idx, a1.idx + x.xx as id
- INTO deleteTablexus
- FROM
- (
- SELECT 1 AS ID UNION ALL
- SELECT 2 UNION ALL
- SELECT 3 UNION ALL
- SELECT 4
- ) a0
- CROSS JOIN
- (SELECT DISTINCT number AS idx FROM master..spt_values WHERE number BETWEEN 1 AND 2070
- UNION ALL
- SELECT DISTINCT number+2070 AS idx FROM master..spt_values WHERE number BETWEEN 1 AND 1699
- ) a1
- , (SELECT TOP 10 CAST(RAND() AS decimal (10,2)) as xx from sys.syscolumns)x
- ORDER BY 1,2
- GO
- CREATE NONCLUSTERED INDEX IDX ON [dbo].[deleteTablexus] ([idx])
- GO
8abfb5b2-29f9-4e53-999c-d0ec516809ab|0|.0