Query hangs during a seperate write

When writing 100's of records to 1 or more tables within a transaction, the
server seems to hang any query trying to read from those tables until either
a Timeout or the write process completes. I'm not doing a lock.
The writes and reads are separate applications.
What would cause this?How are you doing the write?
AMB
"Joe" wrote:

> When writing 100's of records to 1 or more tables within a transaction, th
e
> server seems to hang any query trying to read from those tables until eith
er
> a Timeout or the write process completes. I'm not doing a lock.
> The writes and reads are separate applications.
> What would cause this?
>
>|||using ADO.NET. The DataAdaptor is doing Inserts into the tables.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> How are you doing the write?
>
> AMB
>
> "Joe" wrote:
>
the
either|||Do you know the isolation level being used in the transaction?
You can use Profiler to trace the locks, Lock:Acquired / Lock:Escalation and
Lock:Released.
AMB
"Joe" wrote:

> using ADO.NET. The DataAdaptor is doing Inserts into the tables.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:9960EB96-B274-4A6C-85C0-3BB485E1D411@.microsoft.com...
> the
> either
>
>|||Use with (nolock) on your select statements, for ex.
SELECT au_lname FROM authors WITH (NOLOCK)
This will ensure that select stmt. does the dirty rather than acquiring
an shared lock while the exclusive lock is on.|||You may have a deadlock, and insert is relying on an ID inserted in another
table which itself is waiting for the hanging insert to finish.
If it happens all the time - simply check your logic does the flow work? -
you not done something silly in a transaction.
Otherwise if its intermitant find what process blocks it and syncronise the
order which both processes use DB tables. Try and minimise the time spent in
transactions.
Use profiler to / or current actvity pane in Enterprise man to explore the
processes
Just an approach I've found useful.

Query for multiple tables

Hi,

I have a query that returns values from three seperate data tables into a dataset:

SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]

FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier

WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')

Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.

FileIdentifier FieldName FieldText

1 Field 1 Hello

1 Field 2 Goodbye

The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.

The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!

At the moment mt results gridlooks like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello Hello

1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye

I need it to look like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye

Thanks,

Guy

Depends on which version of SQL Server you are using. Using SQL 2k5 you should take a look on Cross apply.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Guy,

Could you be more clear as to what your goal is, and what the purpose of these fields might be, their scope, the number of overlapping entries, and generally how you view this thing to be structured? On first blush, this seems like a situation where a relational database setup could save you some storage and access time in the long run if you can identify the shared fields and refactor your tables. The problem with setting Field1 and Field2 appropriately is also interesting in that you want Field1 to have "hello" in it and field2 to have "goodbye" in it whereas other than that string nothing differs between the records. This behaviour seems highly specific to your problem, and so knowing more about what your goals are for this database might help us to identify how best to solve your immediate problems in such a manner as to have long-term benefits.

Some natural things to weigh might be:

How do these components logically relate, and what are valid values for things in the database? (e.g. identical entries with field1 and field2 values, should this happen?, should field1 and field2 be mirrored?, etc.).

Does this solution need to last, or is it a one-time only operation?

How important is it to retain the database structure?

Is there a rule to how you want this join to work in the long run that requires domain specific knowledge?

How important is the performance of your database in the long run?

How often do you intend to perform this operation, and how much development work is "worth it"?

I think that as we understand the constraints of your task and what you intend to accomplish, we can find a solution that best fits your needs.

Thanks,

John (MSFT)

Query fails.

I have a two development environments running both RS and Database.
Everything works fine. I then set up a test environment with RS on a seperate
server and using a the database server for both reportserver database as well
as datasource for the reports. (medium deployment scenario). I use OLAP cubes
as data source for may reports. When requesting the database from the Test
webserver(with RS) I get OK reports a couple of times. BUT...when I access
the report a third, fourth or fifth time ... I get timeouts and errors in the
ReportServer log stating that the MDX failed. Same MDX works fine in two
different dev environments as well as when using the Test database from one
of the dev servers as datasource... I get confused by this behaviour... The
Test RS server is a w2k server with all necessary SPs... Could this have
something to do with buffersizes used by aspnet_wp ?
The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.When the query fails the aspnet_wp locks and no new requests are processed
until this worker process is restarted...
"GunnarL" wrote:
> I have a two development environments running both RS and Database.
> Everything works fine. I then set up a test environment with RS on a seperate
> server and using a the database server for both reportserver database as well
> as datasource for the reports. (medium deployment scenario). I use OLAP cubes
> as data source for may reports. When requesting the database from the Test
> webserver(with RS) I get OK reports a couple of times. BUT...when I access
> the report a third, fourth or fifth time ... I get timeouts and errors in the
> ReportServer log stating that the MDX failed. Same MDX works fine in two
> different dev environments as well as when using the Test database from one
> of the dev servers as datasource... I get confused by this behaviour... The
> Test RS server is a w2k server with all necessary SPs... Could this have
> something to do with buffersizes used by aspnet_wp ?
> The only difference on dev and test is that on dev I have both MDAC 2.7 SP1
> and SP1 refresh while on test server I only have MDAC 2.7 SP1 Refresh.