can't update column if there are autocomputed columns?

Hello,
I would greatly appreciate if someone could kindly help me in this matter:
I am running a large OLAP database with the biggest table of 255 million+
records. Recently I've added two autocomputed and persisted columns to this
table. Ever since then, I'm not able to update this table, although I am able
to INSERT into the table.
When I attempt to UPDATE, there's no error, but the update command takes
forever and nothing is happening. In Microsoft SQL Server Management Studio,
this is accompanied by a nice hourglass icon.
The table is properly indexed, so the problem is not there. The data is
accessible, the column that I am updating is not autocomputed nor is there
any restriction on that column whatsoever. It's of type "bit".
It worked before I added the two autocomputed columns. As I am updating this
OLAP database once a week, something else might have happened and this issue
is maybe not related to those autocomputed columns at all. But who knows.
Just for the sake of perfect clarity, let's say I'm going to run this command:
SELECT * FROM Tick WHERE TickID = 123456789
It completes immediately and returns the desired record. But the following
similar command:
UPDATE Tick WHERE TickID = 123456789 SET UniqueTick = 1
is a "permawaiter" :)
Am I missing something?
Thank you very much for any input!
Michal Kreslik
michal.kreslik(at)kreslik.comThis is how the table looks like when scripted:
-- begin script
---
USE [FXdataDB]
GO
/****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:04:50
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Tick](
[TickID] [int] IDENTITY(1,1) NOT NULL,
[SourceFileID] [smallint] NOT NULL,
[FXPairID] [tinyint] NOT NULL,
[Year] [smallint] NOT NULL,
[Month] [tinyint] NOT NULL,
[Day] [tinyint] NOT NULL,
[DayOfWeekID] [tinyint] NOT NULL,
[Hour] [tinyint] NOT NULL,
[Minute] [tinyint] NOT NULL,
[Second] [tinyint] NOT NULL,
[Bid] [real] NOT NULL,
[Ask] [real] NOT NULL,
[UniqueTick] [bit] NULL,
[IsWithinSession] AS
([dbo].[GetIsWithinSessionWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second])) PERSISTED,
[MinsFromSStart] AS
([dbo].[GetMinsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second])) PERSISTED,
[SecsFromSStart] AS
([dbo].[GetSecsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second])) PERSISTED,
[TickTime] AS
((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[Day])+(100000000)*[Month])+(10000000000.)*[Year]) PERSISTED
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo',
@.level1type=N'TABLE',@.level1name=N'Tick',
@.level2type=N'COLUMN',@.level2name=N'SourceFileID'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds the
number of minutes from Forex session start (17:00:00 EST Sunday)' ,
@.level0type=N'SCHEMA',@.level0name=N'dbo',
@.level1type=N'TABLE',@.level1name=N'Tick',
@.level2type=N'COLUMN',@.level2name=N'MinsFromSStart'
---
-- end of script
As stated above, I am attempting to UPDATE the UniqueTick column here.
Thanks,
Michal Kreslik|||Do you have any indexes on the table? I expect that a table scan of 255
million rows to take a while, even if only 1 row is updated.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:55499C83-6ACB-4AE1-8319-A2D5F05473E6@.microsoft.com...
> This is how the table looks like when scripted:
> -- begin script
> ---
> USE [FXdataDB]
> GO
> /****** Object: Table [dbo].[Tick] Script Date: 02/08/2007 23:04:50
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[Tick](
> [TickID] [int] IDENTITY(1,1) NOT NULL,
> [SourceFileID] [smallint] NOT NULL,
> [FXPairID] [tinyint] NOT NULL,
> [Year] [smallint] NOT NULL,
> [Month] [tinyint] NOT NULL,
> [Day] [tinyint] NOT NULL,
> [DayOfWeekID] [tinyint] NOT NULL,
> [Hour] [tinyint] NOT NULL,
> [Minute] [tinyint] NOT NULL,
> [Second] [tinyint] NOT NULL,
> [Bid] [real] NOT NULL,
> [Ask] [real] NOT NULL,
> [UniqueTick] [bit] NULL,
> [IsWithinSession] AS
> ([dbo].[GetIsWithinSessionWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second]))
> PERSISTED,
> [MinsFromSStart] AS
> ([dbo].[GetMinsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second]))
> PERSISTED,
> [SecsFromSStart] AS
> ([dbo].[GetSecsFromSStartWithSchemaBinding]([DayOfWeekID],[Hour],[Minute],[Second]))
> PERSISTED,
> [TickTime] AS
> ((((([Second]+(100)*[Minute])+(10000)*[Hour])+(1000000)*[Day])+(100000000)*[Month])+(10000000000.)*[Year])
> PERSISTED
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo',
> @.level1type=N'TABLE',@.level1name=N'Tick',
> @.level2type=N'COLUMN',@.level2name=N'SourceFileID'
> GO
> EXEC sys.sp_addextendedproperty @.name=N'MS_Description', @.value=N'Holds
> the
> number of minutes from Forex session start (17:00:00 EST Sunday)' ,
> @.level0type=N'SCHEMA',@.level0name=N'dbo',
> @.level1type=N'TABLE',@.level1name=N'Tick',
> @.level2type=N'COLUMN',@.level2name=N'MinsFromSStart'
> ---
> -- end of script
>
> As stated above, I am attempting to UPDATE the UniqueTick column here.
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:
> Do you have any indexes on the table? I expect that a table scan of 255
> million rows to take a while, even if only 1 row is updated.
Yes, I have 6 indexes on the table. But before adding the extra two
autocomputed columns, UPDATE worked fine even with all those indexes in
place. It took about 5 ms to update.
So there's something fishy about that.
What this behaviour could be caused by?
Thanks,
Michal Kreslik|||> What this behaviour could be caused by?
Have you looked at the execution plan to ensure indexes are used as
expected? These symptoms can be due to blocking - have you checked activity
during the update?
I can try to repro if you provide the script for the user-defined function
and all indexes too. I didn't see anything obvious in the scripts you
posed. I assume your actual UPDATE statement is:
UPDATE Tick
SET UniqueTick = 1
WHERE TickID = 123456789
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:15AECAEA-A24D-496D-8101-80B21DAF63EC@.microsoft.com...
> "Dan Guzman" wrote:
>> Do you have any indexes on the table? I expect that a table scan of 255
>> million rows to take a while, even if only 1 row is updated.
> Yes, I have 6 indexes on the table. But before adding the extra two
> autocomputed columns, UPDATE worked fine even with all those indexes in
> place. It took about 5 ms to update.
> So there's something fishy about that.
> What this behaviour could be caused by?
> Thanks,
> Michal Kreslik|||"Dan Guzman" wrote:
> > What this behaviour could be caused by?
> Have you looked at the execution plan to ensure indexes are used as
> expected? These symptoms can be due to blocking - have you checked activity
> during the update?
> I can try to repro if you provide the script for the user-defined function
> and all indexes too. I didn't see anything obvious in the scripts you
> posed. I assume your actual UPDATE statement is:
> UPDATE Tick
> SET UniqueTick = 1
> WHERE TickID = 123456789
>
Thanks very much for your swift reply.
Yes, obviously I execute the statement with the correct syntax, sorry.
I have just checked the execution plan and everything seems to be ok.
>- have you checked activity during the update?
You mean, whether SQL server is using CPU time? No, there's almost no CPU
activity during this strange UPDATE.
I have uploaded the CREATE script for the entire database here:
http://kreslik.com/files/FXdataDB_Scripted_All.zip
Also, ever since I added those two autocomputed columns, the SQL server
seems to be "caching" something to disk all the time (using up to 50% of
CPU). It always starts a couple of minutes after the SQL server starts and
the only way to stop it (although I guess there's no reason why SQL server
should be "recomputing" persisted columns) was to restart the SQL server.
Today it seems it is no longer "caching", but I'm not sure why. Could this be
related to my problem? How do I find out what exactly what is SQL server
doing and eventually how do I put and end to such a nonsensical "caching"?
Thanks much for any help.
Michal Kreslik|||> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
What about blocking and disk activity?
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:A49FAE08-3EDD-42D1-9EA9-6E0A3FD139DE@.microsoft.com...
> "Dan Guzman" wrote:
>> > What this behaviour could be caused by?
>> Have you looked at the execution plan to ensure indexes are used as
>> expected? These symptoms can be due to blocking - have you checked
>> activity
>> during the update?
>> I can try to repro if you provide the script for the user-defined
>> function
>> and all indexes too. I didn't see anything obvious in the scripts you
>> posed. I assume your actual UPDATE statement is:
>> UPDATE Tick
>> SET UniqueTick = 1
>> WHERE TickID = 123456789
> Thanks very much for your swift reply.
> Yes, obviously I execute the statement with the correct syntax, sorry.
> I have just checked the execution plan and everything seems to be ok.
>>- have you checked activity during the update?
> You mean, whether SQL server is using CPU time? No, there's almost no CPU
> activity during this strange UPDATE.
> I have uploaded the CREATE script for the entire database here:
> http://kreslik.com/files/FXdataDB_Scripted_All.zip
> Also, ever since I added those two autocomputed columns, the SQL server
> seems to be "caching" something to disk all the time (using up to 50% of
> CPU). It always starts a couple of minutes after the SQL server starts and
> the only way to stop it (although I guess there's no reason why SQL server
> should be "recomputing" persisted columns) was to restart the SQL server.
> Today it seems it is no longer "caching", but I'm not sure why. Could this
> be
> related to my problem? How do I find out what exactly what is SQL server
> doing and eventually how do I put and end to such a nonsensical "caching"?
> Thanks much for any help.
> Michal Kreslik|||"Dan Guzman" wrote:
> > You mean, whether SQL server is using CPU time? No, there's almost no CPU
> > activity during this strange UPDATE.
> What about blocking and disk activity?
No, there doesn't seem to be any disk activity.
Michal Kreslik|||Hi, Michal. I haven't been able to repro your problem on this end. I
created the database (without the last 2 computed columns) and loaded the
Tick table with 10M rows of test data. I then added the computed columns
and index and the update statement ran quickly.
Can you post the output of the following query against the hung spid?
Perhaps this and/or the other dm views will provide a clue as to what's
going on.
SELECT *
FROM sys.dm_exec_requests
WHERE session_id = ?
FOR XML PATH ('dm_exec_requests')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:8564911C-2F36-4938-9B4A-3829152D03C6@.microsoft.com...
> "Dan Guzman" wrote:
>> > You mean, whether SQL server is using CPU time? No, there's almost no
>> > CPU
>> > activity during this strange UPDATE.
>> What about blocking and disk activity?
> No, there doesn't seem to be any disk activity.
> Michal Kreslik|||Hello, Dan,
thanks for this idea. Quite suprisingly, the UPDATE command started working
all by itself out of the blue.
But anyway, this strange behavior should not be left unexplained as it might
happen again.
I ran the SQL command you suggested against the (now running ok) UPDATE
spid. If you can see something that might have been causing the UPDATE
getting stuck, please let me know.
XML result:
<dm_exec_requests>
<session_id>58</session_id>
<request_id>0</request_id>
<start_time>2007-02-13T17:42:35.430</start_time>
<status>suspended</status>
<command>UPDATE</command>
<sql_handle>AgAAANT0Yi4+B5OkY6CYr8CIw0pT0XYp</sql_handle>
<statement_start_offset>30</statement_start_offset>
<statement_end_offset>-1</statement_end_offset>
<plan_handle>BgAHANT0Yi64oVkEAAAAAAAAAAAAAAAA</plan_handle>
<database_id>7</database_id>
<user_id>1</user_id>
<connection_id>0E8F7597-F1AC-44D5-AE5A-48F3081915E7</connection_id>
<blocking_session_id>0</blocking_session_id>
<wait_type>WRITELOG</wait_type>
<wait_time>0</wait_time>
<last_wait_type>WRITELOG</last_wait_type>
<wait_resource></wait_resource>
<open_transaction_count>1</open_transaction_count>
<open_resultset_count>1</open_resultset_count>
<transaction_id>1633798</transaction_id>
<context_info></context_info>
<percent_complete>0.0000000e+000</percent_complete>
<estimated_completion_time>0</estimated_completion_time>
<cpu_time>0</cpu_time>
<total_elapsed_time>2</total_elapsed_time>
<scheduler_id>0</scheduler_id>
<task_address>AGiKeA==</task_address>
<reads>135</reads>
<writes>325</writes>
<logical_reads>413692</logical_reads>
<text_size>2147483647</text_size>
<language>us_english</language>
<date_format>mdy</date_format>
<date_first>7</date_first>
<quoted_identifier>1</quoted_identifier>
<arithabort>0</arithabort>
<ansi_null_dflt_on>1</ansi_null_dflt_on>
<ansi_defaults>0</ansi_defaults>
<ansi_warnings>1</ansi_warnings>
<ansi_padding>1</ansi_padding>
<ansi_nulls>1</ansi_nulls>
<concat_null_yields_null>1</concat_null_yields_null>
<transaction_isolation_level>2</transaction_isolation_level>
<lock_timeout>-1</lock_timeout>
<deadlock_priority>0</deadlock_priority>
<row_count>1</row_count>
<prev_error>0</prev_error>
<nest_level>0</nest_level>
<granted_query_memory>0</granted_query_memory>
<executing_managed_code>0</executing_managed_code>
</dm_exec_requests>
Thank you very much, Dan,
Michal Kreslik|||> thanks for this idea. Quite suprisingly, the UPDATE command started
> working
> all by itself out of the blue.
> But anyway, this strange behavior should not be left unexplained as it
> might
> happen again.
I agree that it's best to identify the underlying cause. Unfortunately,
this is very difficult to do forensically with a problem that is not
repeatable. I assume you checked the SQL Server error log for unusual
messages?
> I ran the SQL command you suggested against the (now running ok) UPDATE
> spid. If you can see something that might have been causing the UPDATE
> getting stuck, please let me know.
As you can see, this healthy request is waiting on WRITELOG, which is normal
and expected. Should the problem happen again, the wait_type should provide
more clues as to what the query is waiting on and you can drill-down
accordingly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"michal.kreslik" <michalkreslik@.discussions.microsoft.com> wrote in message
news:BD88E108-A5AE-4710-9F61-4E1DB23B465A@.microsoft.com...
> Hello, Dan,
> thanks for this idea. Quite suprisingly, the UPDATE command started
> working
> all by itself out of the blue.
> But anyway, this strange behavior should not be left unexplained as it
> might
> happen again.
> I ran the SQL command you suggested against the (now running ok) UPDATE
> spid. If you can see something that might have been causing the UPDATE
> getting stuck, please let me know.
> XML result:
> <dm_exec_requests>
> <session_id>58</session_id>
> <request_id>0</request_id>
> <start_time>2007-02-13T17:42:35.430</start_time>
> <status>suspended</status>
> <command>UPDATE</command>
> <sql_handle>AgAAANT0Yi4+B5OkY6CYr8CIw0pT0XYp</sql_handle>
> <statement_start_offset>30</statement_start_offset>
> <statement_end_offset>-1</statement_end_offset>
> <plan_handle>BgAHANT0Yi64oVkEAAAAAAAAAAAAAAAA</plan_handle>
> <database_id>7</database_id>
> <user_id>1</user_id>
> <connection_id>0E8F7597-F1AC-44D5-AE5A-48F3081915E7</connection_id>
> <blocking_session_id>0</blocking_session_id>
> <wait_type>WRITELOG</wait_type>
> <wait_time>0</wait_time>
> <last_wait_type>WRITELOG</last_wait_type>
> <wait_resource></wait_resource>
> <open_transaction_count>1</open_transaction_count>
> <open_resultset_count>1</open_resultset_count>
> <transaction_id>1633798</transaction_id>
> <context_info></context_info>
> <percent_complete>0.0000000e+000</percent_complete>
> <estimated_completion_time>0</estimated_completion_time>
> <cpu_time>0</cpu_time>
> <total_elapsed_time>2</total_elapsed_time>
> <scheduler_id>0</scheduler_id>
> <task_address>AGiKeA==</task_address>
> <reads>135</reads>
> <writes>325</writes>
> <logical_reads>413692</logical_reads>
> <text_size>2147483647</text_size>
> <language>us_english</language>
> <date_format>mdy</date_format>
> <date_first>7</date_first>
> <quoted_identifier>1</quoted_identifier>
> <arithabort>0</arithabort>
> <ansi_null_dflt_on>1</ansi_null_dflt_on>
> <ansi_defaults>0</ansi_defaults>
> <ansi_warnings>1</ansi_warnings>
> <ansi_padding>1</ansi_padding>
> <ansi_nulls>1</ansi_nulls>
> <concat_null_yields_null>1</concat_null_yields_null>
> <transaction_isolation_level>2</transaction_isolation_level>
> <lock_timeout>-1</lock_timeout>
> <deadlock_priority>0</deadlock_priority>
> <row_count>1</row_count>
> <prev_error>0</prev_error>
> <nest_level>0</nest_level>
> <granted_query_memory>0</granted_query_memory>
> <executing_managed_code>0</executing_managed_code>
> </dm_exec_requests>
> Thank you very much, Dan,
> Michal Kreslik|||Dan, so I have finally resolved the issue today.
Also, I have found out why this puzzling behavior was seemingly random.
Today the UPDATE part of my program stopped working again. Thanks to you and
your good idea about looking into the sys.dm_exec_requests for the particular
session_id, I have found out that the UPDATE is being blocked by a SELECT
statement.
This SELECT statment is used to select and sort rows from the large OLAP
table and prepares the data for further work.
When the result is ready, my algorithm goes thru the rows one after another,
performs some calculations and based on the calculations, it UPDATEs the
rows.
But the problem is that I am using the SqlDataReader object in C#.NET which
is free to return first row to you BEFORE it finished executing the entire
command.
So basically, SqlDataReader might return the first 10000 rows out of 2
million SELECTed to speed up further processing.
But this is also the catch as it seems that you can't tell SqlDataReader to
not behave like this. That means that if your processing code contains
something (UPDATE) that can't be performed before the SqlDataReader command
(SELECT) is finished, you are going to get a lock (which is what I got) as
you can't UPDATE the row in the table where the SELECT on that row is just
being processed.
The solution that I'm now going to implement is to simply use the
SqlDataAdapter object in C#.NET to fill the DataTable object and this
DataTable object will be used as a source for calculations that are doing
UPDATE to the SELECTed data. As the DataTable object is just a memory
representation of the SQL data, it is not going to interfere with UPDATE in
any way.
The reason why this behavior was random is that SqlDataReader sometimes
finishes the entire SELECT before returning the first row and sometimes it
doesn't. I would infer that the bigger is the data chunk that you are
retrieving with SqlDataReader, the higher is the probability that
SqlDataReader won't finish the entire SELECT statement before returning the
firt row for processing. But it seems you can't control this aspect in any
way.
My algorithm is being fed with the data chunk that equals the weekly update
for the OLAP database (new data for that week). This data chunk varies
somewhere between 700k rows and 1.5 mil rows (forex price tick data for 16 FX
pairs for that week).
When the amount of data is small enough for the SqlDataReader to complete
the SELECT before it returns the first row, everything is ok. But when the
amount of data is such that SqlDataReader returns the first row BEFORE it
finishes the SELECT, you get the lock.
Thanks for helping me out. Anyther mystery solved.
Michal Kreslik
michal.kreslik(at)kreslik.com|||Dan, so I have finally resolved the issue today.
Also, I have found out why this puzzling behavior was seemingly random.
Today the UPDATE part of my program stopped working again. Thanks to you and
your good idea about looking into the sys.dm_exec_requests for the particular
session_id, I have found out that the UPDATE is being blocked by a SELECT
statement.
This SELECT statment is used to select and sort rows from the large OLAP
table and prepares the data for further work.
When the result is ready, my algorithm goes thru the rows one after another,
performs some calculations and based on the calculations, it UPDATEs the
rows.
But the problem is that I am using the SqlDataReader object in C#.NET which
is free to return first row to you BEFORE it finished executing the entire
command.
So basically, SqlDataReader might return the first 10000 rows out of 2
million SELECTed to speed up further processing.
But this is also the catch as it seems that you can't tell SqlDataReader to
not behave like this. That means that if your processing code contains
something (UPDATE) that can't be performed before the SqlDataReader command
(SELECT) is finished, you are going to get a lock (which is what I got) as
you can't UPDATE the row in the table where the SELECT on that row is just
being processed.
The solution that I'm now going to implement is to simply use the
SqlDataAdapter object in C#.NET to fill the DataTable object and this
DataTable object will be used as a source for calculations that are doing
UPDATE to the SELECTed data. As the DataTable object is just a memory
representation of the SQL data, it is not going to interfere with UPDATE in
any way.
The reason why this behavior was random is that SqlDataReader sometimes
finishes the entire SELECT before returning the first row and sometimes it
doesn't. I would infer that the bigger is the data chunk that you are
retrieving with SqlDataReader, the higher is the probability that
SqlDataReader won't finish the entire SELECT statement before returning the
firt row for processing. But it seems you can't control this aspect in any
way.
My algorithm is being fed with the data chunk that equals the weekly update
for the OLAP database (new data for that week). This data chunk varies
somewhere between 700k rows and 1.5 mil rows (forex price tick data for 16 FX
pairs for that week).
When the amount of data is small enough for the SqlDataReader to complete
the SELECT before it returns the first row, everything is ok. But when the
amount of data is such that SqlDataReader returns the first row BEFORE it
finishes the SELECT, you get the lock.
Thanks for helping me out. Anyther mystery solved.
Michal Kreslik
michal.kreslik(at)kreslik.com|||Dan, so I have finally resolved the issue today.
Also, I have found out why this puzzling behavior was seemingly random.
Today the UPDATE part of my program stopped working again. Thanks to you and
your good idea about looking into the sys.dm_exec_requests for the particular
session_id, I have found out that the UPDATE is being blocked by a SELECT
statement.
This SELECT statment is used to select and sort rows from the large OLAP
table and prepares the data for further work.
When the result is ready, my algorithm goes thru the rows one after another,
performs some calculations and based on the calculations, it UPDATEs the
rows.
But the problem is that I am using the SqlDataReader object in C#.NET which
is free to return first row to you BEFORE it finished executing the entire
command.
So basically, SqlDataReader might return the first 10000 rows out of 2
million SELECTed to speed up further processing.
But this is also the catch as it seems that you can't tell SqlDataReader to
not behave like this. That means that if your processing code contains
something (UPDATE) that can't be performed before the SqlDataReader command
(SELECT) is finished, you are going to get a lock (which is what I got) as
you can't UPDATE the row in the table where the SELECT on that row is just
being processed.
The solution that I'm now going to implement is to simply use the
SqlDataAdapter object in C#.NET to fill the DataTable object and this
DataTable object will be used as a source for calculations that are doing
UPDATE to the SELECTed data. As the DataTable object is just a memory
representation of the SQL data, it is not going to interfere with UPDATE in
any way.
The reason why this behavior was random is that SqlDataReader sometimes
finishes the entire SELECT before returning the first row and sometimes it
doesn't. I would infer that the bigger is the data chunk that you are
retrieving with SqlDataReader, the higher is the probability that
SqlDataReader won't finish the entire SELECT statement before returning the
firt row for processing. But it seems you can't control this aspect in any
way.
My algorithm is being fed with the data chunk that equals the weekly update
for the OLAP database (new data for that week). This data chunk varies
somewhere between 700k rows and 1.5 mil rows (forex price tick data for 16 FX
pairs for that week).
When the amount of data is small enough for the SqlDataReader to complete
the SELECT before it returns the first row, everything is ok. But when the
amount of data is such that SqlDataReader returns the first row BEFORE it
finishes the SELECT, you get the lock.
Thanks for helping me out. Anyther mystery solved.
Michal Kreslik
michal.kreslik(at)kreslik.com|||So I've implemented the solution as described above and everything works
correctly.
The interesting twist to this is that the overall processing time with
DataTable is now half of what it was with SqlDataReader.
Michal Kreslik
michal.kreslik(at)kreslik.com