Conditional Views

I have a view which has to be versatile enough to perform different calculations. For instance, using the view below I may want the stock level for that particular wine or in another instance I would the need the stock level for all wines that share the same type. Note that I would never need a combonation of both in the same view.

Is there anyway to specify <myVar> before or when calling this view?

CREATE VIEW vwWines
AS

SELECT
tblWine.ID,
tblWine.WineTypeID,
StockQty =
CASE
WHEN <myVar> = 1
THEN tblWine.StockQty
ELSE
(
SELECT
SUM(StockQty)
FROM
tblWine AS tblWineTwo
WHERE
tblWine.WineTypeID = tblWineTwo.WineTypeID
)
END
FROM
tblWine
Note: This view has been simplified and been done on the fly so may contain errorsdo this as a stored procedure and pass the variable in from the application.|||Why? Why not have two views and let the front end decide which one to call, based on the need of the operator? Follow the KISS principle.|||Keep It Simple Stupid? It took me a minute to remember that one.

I do not know. Maybe having one peice of code to maintain instead of 2. It does not really matter. Either way is valid.|||Exactly, I had thought of that but there is other C# code that are executed on the results and to duplicate it would cause more work in the long run.

I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view. It automatically produces the stored procedures and connection code. I wouldn't want to do it this way as I'd have to provide other means of connecting to the database which again isn't good for maintainence.

I'm going to experiment a little more but if I can't crack it, I'm going to go with the method of creating two view just to keep the code cleaner.|||I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view.Dear Lord...please tell me you aren't using NHibernate.
Tools such as this are a bad idea. They inevitably lead to ineffecient code, unscalable applications, and insecure databases.|||Lol, I'm using something called MyGeneration dOOdads (http://www.mygenerationsoftware.com/portal/dOOdads/Overview/tabid/63/Default.aspx) and it seems to have worked find over the past two years over various projects. Obviously its not flexible enough to cover problems like these but for what it provides, it's definitely worth the trade off. It's the #1 downloaded .NET tool on Download.com, apparently.|||"An Amazing 48k Architecture that Supports the 1.1 and 2.0 .NET Framework
Transactions, Dynamic Queries, and a Highly Intuitive API"
Dynamic Queries are to be AVOIDED. Tools such as this violate the most basic principles of database application design. They do so in the name of short-term development gains, and at the expense of long-term quality.|||But surely you would require the use of dynamic queries even without the help of an ORM tool, for example, an advanced search form. Or am I thinking of the wrong sort of dynamic queries?|||No, you would not need dynamic queries for an automated search form. And if dynamic sql is required it should be constructed with a stored procedure, not by an interface or middle-tier, which should not even have access to the underlying tables.

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

conditional split for insert or update cause dead lock on database level

Hi

I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?

Thanks

Don't try and insert and update a table from the same data flow.

-Jamie

|||

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

aproaching Before:

conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords, OLE DB Destination was used to insert newRecords

aproaching Now:

Conditional Split on newRecords and changedRecords, OLE DB Command was used to update changedRecords,

Merge Join is used to left outer join newRecords with output of OLE DB Command ( this leave only newRecords is availible in output, but still wait for db update command finish), then

OLE DB Destination was used to insert output from merge join.

|||

Jun Fan wrote:

My read from db is very expensive. We can't afford to read same data twice. So I use another merge join to force waiting on updating records to finish before I insert. This solve my problem. Thanks anyway.

Why do you need to read data twice? Just push one of the data paths into a raw file and then insert/update/whatever that data from another dataflow.

-Jamie

|||

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

|||

Jun Fan wrote:

Thanks for sugestion. Pushing data into temp location (file or temp table) has been too slow for large amount new records. Another merge join to force wait on update finishing seems work great at this point.

Thanks again!

Jun Fan

Have you tried raw files? They're lightning fast.

By the way, merge join does not ensure anything. It slows up one datapath, sure, but that in no way guarantees that you will prevent your locking problem.

-Jamie

|||

If performance is a concern, I'm suprised that using the OLEDB Command is OK, as it tends to be pretty slow. I have had much better success using a Conditional Split to direct new rows (Inserts) to an OLEDB Destination that writes directly to the target table, and directs the update rows to a permanent temp table. Then I use an Execute SQL Task to issue a batch Update statement after the data flow. During performance testing in the environments I work in, this has proven to be the fastest approach.

This is a pattern that many of the regular posters on this forum use very successfully.

Concurrent transaction in READ COMMITED isolation level

Hello,
I currently work on Sql Serveur transactions (not distributed) and their
behaviors according to the selected isolation level.
I try to reproduce the default working of Oracle, and I have the following
problem:
With an isolation level READ COMMITED, by default Oracle "lock" no
request for reading modified records in another transaction (or connection)
not yet validated. Set apart the modifications made in the transaction in
progress, the result of a SELECT always return the "version" of the
validated recordings. Thus, there is no blocking in reading.
Sql Server 2000 function differently with the same isolation level. A
request SELECT is systematically blocked if the set of result must contain a
record new or modified not yet validated. Key word READPAST in request
SELECT solves only the problem related to the new records, the modified
record always block the request for reading!
Is there a solution?
Thank you in advance for your assistance.The key in either situation is to hold locks as short of a time span as
possible. READ COMMITTED puts a lock on an object, does it's thing with the
object, drops the lock on the object, and then moves to the next object
doing the same thing to the next object (usually we are talking about a row
in a table.)
So, in a well built system, the only contention is when you have a lot of
writes to rows that people also are trying to look at. So first question,
do you have people who need to see the data that people are actually
actively modifying? If so, then you do have a quandry. If not (and usually
not) then you may have indexing problems where you are scanning an entire
table instead of just some rows.
This is the place to start, but the crux of it all is simple, separate
readers from writers as much as possible, hold locks for as short a time
period as possible. It will be the same in 2005 when we get versioning as
well. The longer the locks, the more likely we get inconsistency and or
locking.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Christopher" <Dev@.effect.fr> wrote in message
news:utLZFZkWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I currently work on Sql Serveur transactions (not distributed) and their
> behaviors according to the selected isolation level.
> I try to reproduce the default working of Oracle, and I have the following
> problem:
> With an isolation level READ COMMITED, by default Oracle "lock" no
> request for reading modified records in another transaction (or
> connection) not yet validated. Set apart the modifications made in the
> transaction in progress, the result of a SELECT always return the
> "version" of the validated recordings. Thus, there is no blocking in
> reading.
> Sql Server 2000 function differently with the same isolation level. A
> request SELECT is systematically blocked if the set of result must contain
> a record new or modified not yet validated. Key word READPAST in request
> SELECT solves only the problem related to the new records, the modified
> record always block the request for reading!
> Is there a solution?
> Thank you in advance for your assistance.
>|||Thank you for this explanation.
Just a precision according to my context:
I don't know if some people need frequently to see the data that others
are actually modifying, but it's a possibility in any business application.
It can be a problem in some situations.
However, in all cases, I'm agree that it's better to hold locks for a
short time (short transaction).
Christopher.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message de
news: OIIrzzlWFHA.132@.TK2MSFTNGP14.phx.gbl...
> The key in either situation is to hold locks as short of a time span as
> possible. READ COMMITTED puts a lock on an object, does it's thing with
> the object, drops the lock on the object, and then moves to the next
> object doing the same thing to the next object (usually we are talking
> about a row in a table.)
> So, in a well built system, the only contention is when you have a lot of
> writes to rows that people also are trying to look at. So first question,
> do you have people who need to see the data that people are actually
> actively modifying? If so, then you do have a quandry. If not (and
> usually not) then you may have indexing problems where you are scanning an
> entire table instead of just some rows.
> This is the place to start, but the crux of it all is simple, separate
> readers from writers as much as possible, hold locks for as short a time
> period as possible. It will be the same in 2005 when we get versioning as
> well. The longer the locks, the more likely we get inconsistency and or
> locking.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Christopher" <Dev@.effect.fr> wrote in message
> news:utLZFZkWFHA.2420@.TK2MSFTNGP12.phx.gbl...
>|||> I don't know if some people need frequently to see the data that others
> are actually modifying, but it's a possibility in any business
> application.
It is a possibility, though I find in quite a few cases blocking comes from
poor planning for separation rather than a need for two people to work on
the same rows.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Christopher" <Dev@.effect.fr> wrote in message
news:ORK5XV4WFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Thank you for this explanation.
> Just a precision according to my context:
> I don't know if some people need frequently to see the data that others
> are actually modifying, but it's a possibility in any business
> application.
> It can be a problem in some situations.
> However, in all cases, I'm agree that it's better to hold locks for a
> short time (short transaction).
> Christopher.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message
> de news: OIIrzzlWFHA.132@.TK2MSFTNGP14.phx.gbl...
>

concurrency down to the column level - sql

Using VS05 pro SP1...

(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)

This is for a telephony application using Last In Wins concurrency...

Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.

We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...

- Fill table A and table B identically

- change column1 value in table A

- Update table A

- change column2 value in table B

- Update table B - this will generate a concurrency error.

Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.

So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.

I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.

Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.

Thanks!

Bob

hi Bob,

BobInIndy wrote:

(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)

This is for a telephony application using Last In Wins concurrency...

Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.

We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...

- Fill table A and table B identically

- change column1 value in table A

- Update table A

- change column2 value in table B

- Update table B - this will generate a concurrency error.

Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.

So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.

I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.

Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.

Thanks!

Bob

from a SQL Server point of view, you have to "build" a DDL script (aka stored procedure) for each single (and combination of) attribute of your entity, so that you can "execute" UPDATE obj SET singleCol = singleVal WHERE pk = xx, but this obviously will lead to a very high proliferation of stored procedures for each single base table.. another solution would be dynamic SQL, where the desired UPDATE statement can be "constructed" to reflect only the modified attributes, but I do personally not like accepting dynamic SQL against "my" tables...

or, depending on your NULL behaviour, you even could create a single proc where the NULL does not qualify for updates for the single attribute, that's to say something like

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Data0 varchar(10) NOT NULL, Data1 int NOT NULL DEFAULT 10, Data2 datetime NOT NULL DEFAULT '20070101', rv timestamp NOT NULL ); INSERT INTO [dbo].[TestTB] ([Id], [Data0], [Data1], [Data2]) VALUES ( 1, 'Test', DEFAULT, DEFAULT ); GO CREATE PROCEDURE dbo.usp_Test ( @.Id int, @.Data0 varchar(10) = NULL, @.Data1 int = NULL, @.Data2 datetime = NULL, @.rv timestamp OUTPUT ) AS BEGIN DECLARE @.r int; BEGIN TRY UPDATE dbo.TestTB SET [Data0] = ISNULL(@.Data0, [Data0]), [Data1] = ISNULL(@.Data1, [Data1]), [Data2] = ISNULL(@.Data2, [Data2]) WHERE [Id] = @.Id AND [rv] = @.rv; SET @.r = @.@.ROWCOUNT; IF @.r = 0 BEGIN SELECT @.rv = [rv] FROM dbo.TestTB WHERE [Id] = @.Id; RAISERROR ('Concurrency violation', 16, 10); END; RETURN 0; END TRY BEGIN CATCH DECLARE @.ErrorMSG varchar(2000); SET @.ErrorMSG = ERROR_MESSAGE(); RAISERROR (@.ErrorMSG, 16, 1); RETURN -100; END CATCH; END; GO DECLARE @.Id int, @.Data0 varchar(10), @.Data1 int, @.Data2 datetime, @.rv timestamp; SELECT @.Id = 1, @.Data0 = '1st Mod', @.Data1 = NULL, @.Data2 = NULL, @.rv = [rv] FROM dbo.TestTB WHERE [Id] = 1; PRINT 'parameters to use'; SELECT @.Id, @.Data0, @.Data1, @.Data2, @.rv; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; PRINT 'Modified data'; SELECT * FROM dbo.TestTB; PRINT 'key parameters'; SELECT @.Id, @.rv; PRINT '--'; PRINT 'now let''s try a concurrency exception;'; PRINT 'manual modification to simulate a conflict'; UPDATE dbo.TestTB SET [Data1] = 1000 WHERE [Id] = 1; PRINT 'which results in a timestamp change'; SELECT * FROM dbo.TestTB; SELECT @.Data0 = '2st Mod', @.Data1 = NULL, @.Data2 = NULL; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; SELECT * FROM dbo.TestTB; PRINT 'fails, as Data1 now is 1000'; PRINT 'but you have the new timestamp value, so just resubmitit'; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; PRINT 'only your modified attribute is persisted'; SELECT * FROM dbo.TestTB; GO DROP PROCEDURE dbo.usp_Test; DROP TABLE dbo.TestTB; --<- parameters to use -- - -- -- 1 1st Mod NULL NULL 0x00000000000007EF Modified data Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 10 2007-01-01 00:00:00.000 0x00000000000007F0 key parameters -- 1 0x00000000000007EF -- now let's try a concurrency exception; manual modification to simulate a conflict which results in a timestamp change Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F1 Msg 50000, Level 16, State 1, Procedure usp_Test, Line 35 Concurrency violation Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F1 fails, as Data1 now is 1000 but you have the new timestamp value, so just resubmitit only your modified attribute is persisted Id Data0 Data1 Data2 rv -- - -- -- 1 2st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F2

the logic is: if NULL is passed, the column will persists it's original value, else the new provided value is stored..

in case of concurrency violation (a trivial check is here performed), an exception is risen, and the new timestamp value is returned as output parameter.. you can this way do something (if you like) as informing you will overwrite someone's work (only if your modified column will collide with his/her column) and, having the current timestamp, you can re-execute the UDPATE statement..

this way a single procedure can accomplish the desired behaviour of modifying the "changed" columns, as in case of NULL the underlying attribute's value is conserved, but this obviously require your "logic" to use a NULL as a "logical" treshhold for "unchanged values"...

you should be able to access your original and current values in ado.net to populate the command parameters as "required"...

regards