SQL Server : store data from VB.NET

Question

I've got a big problem. I have to store date times and values in my SQL Server database.

I wrote a website, which contains a table with entries.

One entry contains: the date, time and a value

For example:

    Entry A : -> 01.01.2014 - 07.06.2014 , 00:00 - 07:15 , Value: 450
    Entry A : -> 01.01.2014 - 07.06.2014 , 07:30 - 15:30 , Value: 230
    Entry A : -> 01.01.2014 - 07.06.2014 , 15:45 - 23:45 , Value: 300

    Entry B : -> 01.01.2014 - 07.06.2014 , 00:00 - 02:15 , Value: 130
    Entry B : -> 01.01.2014 - 07.06.2014 , 02:30 - 10:45 , Value: 250
    Entry B : -> 01.01.2014 - 07.06.2014 , 11:00 - 23:45 , Value: 340

    Entry C ...

For each datetime in the range there is stored one value in 15 minutes per step:

For example the database should looks like this:

    01.01.2014 00:00:00 | 450 | Entry A
    01.01.2014 00:15:00 | 450 | Entry A
    ..
    ..
    01.01.2014 07:15:00 | 450 | Entry A
    01.01.2014 07:30:00 | 230 | Entry A
    01.01.2014 07.45:00 | 230 | Entry A
    ..
    ..
    01.01.2014 15:30:00 | 230 | Entry A
    01.01.2014 15:45:00 | 300 | Entry A
    01.01.2014 16:00:00 | 300 | Entry A
    ..
    ..
    01.01.2014 23:45:00 | 300 | Entry A
    01.01.2014 07:30:00 | 300 | Entry A
    ..
    ..
    01.01.2014 07:15:00 | 230 | Entry A
    01.01.2014 07:30:00 | 230 | Entry A
    ..
    ..
    02.01.2014 ...

The same for every entry and for every day till the end.

I think the problem are the many inserts. Over five month for three entries, every day, every 15 minutes.

I wrote two scripts, both are working. But it takes a long time to save the entries.

Script 1: I have executed the SQL statement directly in VB.

 Dim cmd As DbCommand = New SqlCommand()
 cmd.CommandType = Data.CommandType.Text
 Temp2 = DateTime.Parse(DateStart+ " " + TimeStart)
 Temp3 = DateTime.Parse(DateBegin+ " " + TimeBegin)

 While (Temp2 <= Temp3)
       Date = Temp2
       Temp1 = Temp3

       While (Temp <= Temp1)
             cmd.CommandText = "INSERT INTO [dbo].[_Table_Data]([pr_id],[pr_Nr],[Date],[Value],[nvv]) VALUES (" & pr_id & "," & pr_nr & ", '" & Date & "', " & Value & " , '" & Entry &"')"
             cmd.Connection = con
             con.Open()
             Dim dr As SqlDataReader = cmd.ExecuteReader

             While (dr.Read())
                   '    save entrys
             End While

             con.Close()
             Temp = DateAdd(DateInterval.Minute, 15, Temp)
       End While

       Temp2 = DateAdd(DateInterval.Day, 1, Temp2)
  End While

The second script was a SQL procedure, I sent the values and dates to a SQL procedure. But no success, worse as the first script.

I also tried a CTE

;with cte as
(
   select DATEADD(MONTH,DATEDIFF(MONTH,0,@StDate),0) as Sdate 
   union all
   select DATEADD(MINUTE,15,SDate) from cte where SDate < @Enddate
)

But in this case I only have one Startdate and one Enddate. If i have to use 01.01.2014 - 06.06.2014 with one value, it works great. But for different times, i can't use it.

Please help, I need a quite fast solution.

In this case , I need to send a query more than 30,000 times I need to figure out, how I can save the data faster. In this case the Website timedout before i stored all the data. Every day is divided into different time periods and i have to store these different time periods with values for each day. in this Case from 01/01/2014 to 07/06/2014 - 00:00 to 07:15 with the value 400. This means, each day - 00:00 to 07:15 clock has the value 400. In the rest time Periode ( 07:30 - 15:30 ) and ( 15:45 - 23:45 ) there is one other value. — user3119547, Nov 26 '14 at 23:54
it takes a lot of time to store all the data step by step. The website timed out. :( — user3119547, Nov 27 '14 at 00:21
Which bit is slow though - have you profiled it at all? Also, is your SQL Server local, or remote (i.e. is network latency an issue); do you have any clustered indexes at all? — Rowland Shaw, Nov 27 '14 at 08:35
Also why are you trying to `ExecuteReaderr` on an `INSERT` command? I'd normally expect you to `ExecuteNonQuery`, as the insert (as written) won't return any rows to the client — Rowland Shaw, Nov 27 '14 at 08:42
possible duplicate of [Bulk Insert Sql Server millions of record](http://stackoverflow.com/q/50477) — Rowland Shaw, Nov 27 '14 at 08:45

score 0 · Accepted Answer · answered Nov 27 '14 at 20:43

I just tried the way with the CTE again. The following code is my solution and it works great. Now I just have to write a procedure and transfer the parameter.

Thank you all for reading and supporting

declare @StDate datetime = '01/01/2013 00:00.000'
declare @Enddate datetime = '07/07/2043 00:00.000'
declare @stime datetime = '00:00:00'
declare @etime datetime = '23:45:00'

declare @value as int = 387
declare @id as int = 999


;with cte as
(
 select DATEADD(MINUTE,DATEDIFF(Minute,0,@stime),0) as Stime
 union all
 select DATEADD(MINUTE,15,Stime) from cte where Stime < @etime

 ), cte2 as (
 select DATEADD(MONTH,DATEDIFF(MONTH,0,@StDate),0) as Sdate 
 union all
 select DATEADD(day,1,SDate) from cte2 where SDate < @Enddate
 ) 

Insert into [dbo].[_DV_Prognose_Werte_Temp](pr_id,prognosen_Nummer,zeitstempel,[time],leistungsprognose,nvv) (select '1','1',  Combined = CAST(SDate AS DATETIME) + CAST(stime AS DATETIME),stime, @value,'2' from cte cross join cte2)
OPTION (maxrecursion 0)

score -1 · Answer 2 · answered Nov 26 '14 at 17:59

-1

Pseudocode (sorry, I can tell you the way, but I don´t have the time to code it ...)

foreach line ...
    date = first date+time
    enddate = second date+time
    while date >= enddate
        insert into db (please use Parameters)
        date increment by 15 minutes

answered Nov 26 '14 at 17:59

Thomas Krojer

1,018
7
9

this is the slow solution, for example from 01.01.2014 to 07.06.2014 there are 157 days. There are 15072 inserts for one Entry. Look, every day is devided into different time ranges. in my example from 00:00 - 07:15 and 07:30 - 15:30 and 15.45 - 23:45 and this for multiply entries and multiply values per day. In the worst case i have 20 Entries and a range from more than a half year and every day is devided into different time ranges. This would be more than 200000 SQL Statements with "Insert Into" – user3119547 Nov 27 '14 at 00:08
Two Options remaining: first: Bulk Inserts (also possible from within code), or write as TSQL Procedure (i read the "SQL-Server" tag as MS-SQL Server) – Thomas Krojer Nov 27 '14 at 08:03
you find it here: http://stackoverflow.com/questions/10731179/bulk-insert-sql-server-millions-of-record – Thomas Krojer Nov 27 '14 at 08:08

SQL Server : store data from VB.NET

2 Answers2