Different (incorrect) result when executing the same query from c#

Question

I am trying to do some complex manipulations in SQL Server. To prevent any performance penalty, I am trying to do all the manipulations on database side.

Below is my query in C#

"DECLARE @t AS TABLE (StartTime DATETIME, EndTime DateTime, Value REAL, V1 REAL, V2 REAL) " +

"DECLARE @i AS DateTime = @startDate " +
"WHILE @i <= @endDate " +
"BEGIN " +

    "DECLARE @min AS Real = " +
        "(SELECT TOP 1 RawStatus FROM CustomPollerStatistics_Detail " +
        "WHERE RawStatus IS NOT NULL AND CustomPollerAssignmentID = @pollerID " +
        "AND [DateTime] <= @i ORDER BY [DateTime] DESC) " +

    "DECLARE @max AS Real = " +
        "(SELECT TOP 1 RawStatus FROM CustomPollerStatistics_Detail " +
        "WHERE RawStatus IS NOT NULL AND CustomPollerAssignmentID = @pollerID " +
        "AND [DateTime] <= DATEADD(MI, @interval, @i) ORDER BY [DateTime] DESC) " +

    "INSERT INTO @t VALUES (@i, DATEADD(MI, @interval, @i), @max - @min, @min, @max) " +

    "SET @i = DATEADD(MI, @interval, @i) " +
"END " +

"SELECT * FROM @t";

I double checked the value for parameters in debug mode. I tried running the query with same parameters by declaring it on the top of the query with same values in SQL Server Management Studio which are being passed from C#, in this case, it produce correct result as expected. I have added parameters in following way.

com.Parameters.Add("@pollerID", System.Data.SqlDbType.UniqueIdentifier).Value = assignmentID;
com.Parameters.Add("@interval", System.Data.SqlDbType.Int).Value = accuracyMinutes;
com.Parameters.Add("@startDate", System.Data.SqlDbType.DateTime).Value = startDate;
com.Parameters.Add("@endDate", System.Data.SqlDbType.DateTime).Value = endDate;

When I run this query from C#, the columns @max - @min, @min, @max (3rd, 4th and 5th column) all evaluates to 0 always. I am not being able to find out the reason for this on my own. Please help. See screenshot below.

enter image description here

Sample Data http://pastebin.com/raw.php?i=QuBCSRw3. C# Code http://pastebin.com/raw.php?i=9wYfT0y8.

At query tere is a REAL DataType and in Visual Studio it's float. Maybe something related with conversion ? — MajkeloDev, May 22 '15 at 13:45
By looking at your query, I am sure you don't need loop on database side even. Can you add what you are trying to achieve. :( I hate cursors and Loops in db — Shantanu Gupta, May 22 '15 at 13:45
When you run a profiler trace, what sql command is being sent from your C# application? — Tab Alleman, May 22 '15 at 13:46
Oh man, you are running this query at an interval of minutes. PLEASE STOP and write a better query. You will kill your database. — Shantanu Gupta, May 22 '15 at 13:52
I would recommend making this into a stored procedure to minimize the agony of debugging this. Making this a pass through query like this just adds unneeded complication. There is absolutely no need for a loop here either. — Sean Lange, May 22 '15 at 13:56
@ShantanuGupta I am not running this query on any interval. Technically it is being used in reports. Once in a day. — shashwat, May 22 '15 at 13:57
How can I see complete built command which c# sends to database server..? I tried intellitrace, but it just displays command with parameter not being replaced by actual value. @ShantanuGupta. — shashwat, May 22 '15 at 13:58
@MajkeloDev - REAL equivalent is float in c#, I checked it in msdn documentations. — shashwat, May 22 '15 at 14:00
My sensor reports every 5 minutes. It is a power consumption sensor so data it sent keeps increasing every time. Say, my table has 2 columns, date and data. I need to find out the total power consumption in each n minutes to plot it in a line chart. N must be a variable. Sorry, but as a beginner, I was not being able to do this with a single select statement. @ShantanuGupta. — shashwat, May 22 '15 at 14:06
Add the following line `select @@servername, db_name();` and execute it both from c# and SQL Server Management Studio. Do the give the same result? — RBarryYoung, May 22 '15 at 14:19
can you post your C# code where you are setting your dynamic query to cmd. Also can you post some data from CustomPollerStatistics_Detail table if your company policies do not stop — Shantanu Gupta, May 22 '15 at 14:21
I agree with RBarryYoung's suggestion to make sure that you are executing both on the same server and database. Also, the .NET equivalent of SQL Server's `REAL` datatype is actually `Single`; likewise, the .NET equivalent of SQL Server's `FLOAT` is `double`. But I doubt that is the issue here since the `REAL` / `Single` data coming out of SQL Server will fit into a float / double. — Solomon Rutzky, May 22 '15 at 14:29
Sample Data http://pastebin.com/raw.php?i=QuBCSRw3. C# Code http://pastebin.com/raw.php?i=9wYfT0y8. Both paste will expire in 1 week. @ShantanuGupta — shashwat, May 22 '15 at 14:36
@RBarryYoung, just believe me, same server is being used. It is my local server. — shashwat, May 22 '15 at 14:47
just trust *me*, try it. This is by far the most common cause of this behavior. — RBarryYoung, May 22 '15 at 14:57
@AnimalsAreNotOursToEat Looking at your code I found something that could be contributing to the confusion: in the `while (dr.Read())` loop you have a try / catch structure, but the `catch`block doesn't do anything, hence if there is an error, it is being swallowed and the process continues the loop. Try adding `throw;` into the `catch` block. Even better would be to then move the try / catch to be around the `while` loop, and then add a `finally` block with the following in it: `dr.Dispose();`. — Solomon Rutzky, May 22 '15 at 15:16
I agree, But I've just one local server setup and one database with this tablename. It would have thrown an exception if there is no such table in database. — shashwat, May 22 '15 at 15:16
@srutzky Thanks. I tried it by putting a line-break there in where catch starts at line `catch { }`. It never throws any exception. I also tried it running step by step in debug mode. It never throws any exception there. — shashwat, May 22 '15 at 15:20
@AnimalsAreNotOursToEat Ok. You still need the `finally` with the `dr.Dispose();`. Also, what does the debugger show for the 4 input parameter values? — Solomon Rutzky, May 22 '15 at 15:21
Please find updated query. Hope it should work. I got lethargic thinking better again and again. so i added a quick solution — Shantanu Gupta, May 22 '15 at 15:23
@AnimalsAreNotOursToEat In addition to the values of the input params, what is the exact datatype of the `RawStatus` field in `CustomPollerStatistics_Detail` ? — Solomon Rutzky, May 22 '15 at 15:27

Shantanu Gupta · Answer 1 · 2015-05-22T15:26:53.697

To start with, ideally you should have two standard tables in your database with indexes as well. I have seen 60-70% reports using these tables.

for dates i.e. 1900-01-01 till 2079-06-06 (smalldatetime) maximum range
number table (1 to 1 million)

These will help you in resolving performance issues on reports.

If you can't, use following script to generate datetime range first. This will run n iterations i.e. 2^n

DROP TABLE #tbl;
CREATE TABLE #tbl( num INT,
                   startDt DATETIME2( 0 ),
                   endDt DATETIME2( 0 ),
                   dateWithMin AS DATEADD( mi,num,startDt ) PERSISTED
                                                            PRIMARY KEY
--will create clustered index for performance
);
DECLARE @startDt DATETIME2( 0 ) = GETDATE(),
        @endDt DATETIME2( 0 ) = DATEADD( Mi,15,GETDATE());
DECLARE @rc INT = 1,
        @max INT = DATEDIFF( MI,@startDt,@endDt );
SELECT @rc,
       @max;
INSERT INTO #tbl
VALUES( 0,@startDt,@endDt );
WHILE @rc * 2 <= @max
    BEGIN
        INSERT INTO #tbl
        SELECT num + @rc,
               @startDt,
               @endDt
          FROM #tbl;
        SET @rc = @rc * 2;
    END;
INSERT INTO #tbl
SELECT num + @rc,
       @startDt,
       @endDt
  FROM #tbl
  WHERE num + @rc <= @max;
SELECT *
  FROM #tbl;

This will generate result something like this

+-----+---------------------+---------------------+---------------------+
| num |       startDt       |        endDt        |     dateWithMin     |
+-----+---------------------+---------------------+---------------------+
|   0 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:37:24 |
|   1 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:38:24 |
|   2 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:39:24 |
|   3 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:40:24 |
|   4 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:41:24 |
|   5 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:42:24 |
|   6 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:43:24 |
|   7 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:44:24 |
|   8 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:45:24 |
|   9 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:46:24 |
|  10 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:47:24 |
|  11 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:48:24 |
|  12 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:49:24 |
|  13 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:50:24 |
|  14 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:51:24 |
|  15 | 2015-05-22 19:37:24 | 2015-05-22 19:52:24 | 2015-05-22 19:52:24 |
+-----+---------------------+---------------------+---------------------+

Assuming you have data for every minute, following query should give you the result. Otherwise you would need a time series table as discussed above.

EDIT:

SELECT dt,
       mini,
       maxi,
       ( 
         SELECT RawStatus
           FROM CustomPollerStatistics_Detail
           WHERE [DateTime] = maxi ) - ( 
                                         SELECT RawStatus
                                           FROM CustomPollerStatistics_Detail
                                           WHERE [DateTime] = mini ) AS PowerConsumed 
INTO #tmp
  FROM( 
        SELECT DATEADD( Mi,DATEDIFF( Mi,0,[DateTime] ),0 ) AS dt,
               MIN( [DateTime] ) mini,
               MAX( [DateTime] ) maxi
          FROM CustomPollerStatistics_Detail t
          WHERE [DateTime] BETWEEN @StartDate AND @EndDate
          GROUP BY DATEADD( Mi,DATEDIFF( Mi,0,[DateTime] ),0 )) x;
SELECT l.dt,
       DATEADD( Mi,@TimeInterval,l.dt ) AS NextDt,
       SUM( PowerConsumed ) AS Total
  FROM #tmp l
  WHERE l.dt BETWEEN l.dt AND( 
                               SELECT TOP 1 r.maxi AS NextDt
                                 FROM #tmp r
                                 WHERE r.dt = l.dt
                                 ORDER BY r.maxi DESC )
  GROUP BY l.dt,
           DATEADD( Mi,@TimeInterval,l.dt );

The database is provided by vendor application from I have purchased sensor and is used by its application. Changing the database structure will break the system. — shashwat, May 22 '15 at 14:12
Can you tell me a way to see complete rendered query in Visual Studio with parameters added in the query ..? — shashwat, May 22 '15 at 14:13
@AnimalsAreNotOursToEat: You are not touching any existing table. You can add a new table to database if possible, else use above script to remove extra iterations — Shantanu Gupta, May 22 '15 at 14:13
There are significantly better ways to generate a series than using a while loop. Generating the interval table first is reasonable advice, but using a loop to do it is awful. The article [Generate a set or series without a loop](http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1) (3 parts) puts various methods of doing this to the test. — GarethD, May 22 '15 at 14:27
I saw the link. It's great to generate date using that logic when there is no missing date in the series. However author suggest to use number table to generate dates if there is a hole. Also number table generation logic is nice. — Shantanu Gupta, May 22 '15 at 14:34
In your final query, I need flexible gap. Like, sometimes I need to find out total power consumption every 5 min and sometimes at 10 or 15 minutes. Can you please add a variable for this as well ..? — shashwat, May 22 '15 at 15:03
Thinking about that. We just need sum(PowerConsumed) based on datetime interval :) — Shantanu Gupta, May 22 '15 at 15:05
Just want to confirm one thing. My times in the database are not always rounded to 5 minutes. And sometimes, there may be case a sensors gives delayed response. — shashwat, May 22 '15 at 15:26
This 5 minute has to be replaced by your parameter apart from start date and end date. i.e. `@N, @StartDate, @EndDate`. Updated my answer. I am displaying data at minute level. However calculations are being performed at milli second level. — Shantanu Gupta, May 22 '15 at 15:29
Sorry @ShantanuGuptabut the results were unexpected. See query and result here at http://pastebin.com/raw.php?i=L92upfUd — shashwat, May 22 '15 at 16:10
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78566/discussion-between-shashwat-tripathi-and-shantanu-gupta). — shashwat, May 22 '15 at 18:04

Different (incorrect) result when executing the same query from c#

1 Answers1