SQL Server – can I replace those cursors with something else for better performance?

It was suggested to move this question here <- https://stackoverflow.com/questions/54277714/sql-server-can-i-replace-those-cursors-with-something-else

This is SQL Server 2008r2

First, here are some tables and data for test:

CREATE TABLE [dbo].[MyOrders] (     [ID] [int] NOT NULL,     [ref_type] [nchar](1) NOT NULL,     [ref_num] [nvarchar](15) NULL,     [req_cert] [nvarchar](255) NULL,  CONSTRAINT [PK_MyOrders] PRIMARY KEY CLUSTERED  (     [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO  CREATE TABLE [dbo].[MyJobs] (     [job_id] [nvarchar](15) NOT NULL,     [job_message] [nvarchar](255) NULL,  CONSTRAINT [PK_MyJobs] PRIMARY KEY CLUSTERED  (     [job_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO  CREATE TABLE [dbo].[MyTypes] (     [type] [nvarchar](255) NOT NULL,     [value] [nvarchar](255) NOT NULL ) ON [PRIMARY] GO  INSERT INTO [dbo].[MyOrders] ([ID], [ref_type], [ref_num], [req_cert]) VALUES (1, 'J', 'Job0001', 'Cert1') GO  INSERT INTO [dbo].[MyJobs] ([job_id], [job_message]) VALUES ('Job0001', 'Accepted') GO  INSERT INTO [dbo].[MyTypes] ([type], [value]) VALUES ('MyCerts', 'Cert1'),        ('MyCerts', 'Cert2') GO 

Table MyOrders holds my orders, which can reference a job in table MyJobs. MyOrder can specifiy a req_cert, which then will display in job_message field. req_cert will have values from MyTypes table where type == 'MyCert'

What I am trying to do is create a trigger, which when the column req_cert or ref_num of MyOrders table gets updated, it will do the following:

  1. Is one of those 2 fields updated?

  2. Is ref_type == J and ref_num is not null?

  3. Select the existing job_message and check if there is no value from MyTypes table.

  4. If there is, replace it with value from req_cert

  5. If there isn’t, append req_cert

I wrote this trigger to do this, but I don’t know if that’s the best way:

ALTER TRIGGER [dbo].[UpdateCert]  ON [dbo].[MyOrders] FOR UPDATE AS     SET NOCOUNT ON      IF (NOT UPDATE ([req_cert])         AND NOT UPDATE ([ref_num]))        RETURN      DECLARE @ID NVARCHAR(50)     DECLARE @Certificate NVARCHAR(255)     DECLARE @OldValue NVARCHAR(255)     DECLARE @Found TINYINT     DECLARE @JobMessage NVARCHAR(2000)      DECLARE InsertCursor CURSOR FAST_FORWARD FOR         SELECT ref_num, req_cert         FROM Inserted      OPEN InsertCursor      FETCH NEXT FROM InsertCursor INTO @ID, @Certificate      WHILE @@FETCH_STATUS = 0     BEGIN         IF (NOT EXISTS (SELECT ref_num                         FROM MyOrders                         WHERE ref_type = 'J'                           AND ref_num = @ID))         BEGIN             FETCH NEXT FROM InsertCursor INTO @ID, @Certificate             CONTINUE         END          SELECT @JobMessage = job_message         FROM MyJobs         WHERE job_id = @ID          DECLARE CertCursor CURSOR FAST_FORWARD FOR              SELECT [Value]              FROM MyTypes              WHERE [Type] = 'MyCerts'          OPEN CertCursor          FETCH NEXT FROM CertCursor INTO @OldValue          WHILE @@FETCH_STATUS = 0         BEGIN             IF (@JobMessage LIKE '%' + @OldValue + '%')             BEGIN                 SET @Found = 1                 BREAK             END              FETCH NEXT FROM CertCursor INTO @OldValue         END          CLOSE CertCursor         DEALLOCATE CertCursor          IF (@Found = 1)         BEGIN             SELECT @JobMessage = REPLACE(@JobMessage, @OldValue, '')         END          UPDATE MyJobs WITH (ROWLOCK)         SET job_message = ISNULL(@Certificate, '') + ISNULL(@JobMessage, '')         WHERE MyJobs.job_id = @ID          FETCH NEXT FROM InsertCursor INTO @ID, @Certificate     END      CLOSE InsertCursor     DEALLOCATE InsertCursor 

EXPECETD RESULTS (assuming data from above):

UPDATE MyOrders SET req_cert = 'Cert1' WHERE ID = 1 

job_message should be Cert1 Accepted

UPDATE MyOrders     SET req_cert = 'Cert2'     WHERE ID = 1 

job message should be Cert2 Accepted

UPDATE MyOrders SET ref_num = null WHERE ID = 1 GO UPDATE MyOrders SET  req_cert = 'Cert1' WHERE ID = 1 GO UPDATE MyOrders SET ref_num = 'Job0001' WHERE ID = 1 

job message should be Cert1 Accepted

SQL Server – can I replace those cursors with something else for better performance?

It was suggested to move this question here <- https://stackoverflow.com/questions/54277714/sql-server-can-i-replace-those-cursors-with-something-else

This is SQL Server 2008r2

First, here are some tables and data for test:

CREATE TABLE [dbo].[MyOrders] (     [ID] [int] NOT NULL,     [ref_type] [nchar](1) NOT NULL,     [ref_num] [nvarchar](15) NULL,     [req_cert] [nvarchar](255) NULL,  CONSTRAINT [PK_MyOrders] PRIMARY KEY CLUSTERED  (     [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO  CREATE TABLE [dbo].[MyJobs] (     [job_id] [nvarchar](15) NOT NULL,     [job_message] [nvarchar](255) NULL,  CONSTRAINT [PK_MyJobs] PRIMARY KEY CLUSTERED  (     [job_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO  CREATE TABLE [dbo].[MyTypes] (     [type] [nvarchar](255) NOT NULL,     [value] [nvarchar](255) NOT NULL ) ON [PRIMARY] GO  INSERT INTO [dbo].[MyOrders] ([ID], [ref_type], [ref_num], [req_cert]) VALUES (1, 'J', 'Job0001', 'Cert1') GO  INSERT INTO [dbo].[MyJobs] ([job_id], [job_message]) VALUES ('Job0001', 'Accepted') GO  INSERT INTO [dbo].[MyTypes] ([type], [value]) VALUES ('MyCerts', 'Cert1'),        ('MyCerts', 'Cert2') GO 

Table MyOrders holds my orders, which can reference a job in table MyJobs. MyOrder can specifiy a req_cert, which then will display in job_message field. req_cert will have values from MyTypes table where type == 'MyCert'

What I am trying to do is create a trigger, which when the column req_cert or ref_num of MyOrders table gets updated, it will do the following:

  1. Is one of those 2 fields updated?

  2. Is ref_type == J and ref_num is not null?

  3. Select the existing job_message and check if there is no value from MyTypes table.

  4. If there is, replace it with value from req_cert

  5. If there isn’t, append req_cert

I wrote this trigger to do this, but I don’t know if that’s the best way:

ALTER TRIGGER [dbo].[UpdateCert]  ON [dbo].[MyOrders] FOR UPDATE AS     SET NOCOUNT ON      IF (NOT UPDATE ([req_cert])         AND NOT UPDATE ([ref_num]))        RETURN      DECLARE @ID NVARCHAR(50)     DECLARE @Certificate NVARCHAR(255)     DECLARE @OldValue NVARCHAR(255)     DECLARE @Found TINYINT     DECLARE @JobMessage NVARCHAR(2000)      DECLARE InsertCursor CURSOR FAST_FORWARD FOR         SELECT ref_num, req_cert         FROM Inserted      OPEN InsertCursor      FETCH NEXT FROM InsertCursor INTO @ID, @Certificate      WHILE @@FETCH_STATUS = 0     BEGIN         IF (NOT EXISTS (SELECT ref_num                         FROM MyOrders                         WHERE ref_type = 'J'                           AND ref_num = @ID))         BEGIN             FETCH NEXT FROM InsertCursor INTO @ID, @Certificate             CONTINUE         END          SELECT @JobMessage = job_message         FROM MyJobs         WHERE job_id = @ID          DECLARE CertCursor CURSOR FAST_FORWARD FOR              SELECT [Value]              FROM MyTypes              WHERE [Type] = 'MyCerts'          OPEN CertCursor          FETCH NEXT FROM CertCursor INTO @OldValue          WHILE @@FETCH_STATUS = 0         BEGIN             IF (@JobMessage LIKE '%' + @OldValue + '%')             BEGIN                 SET @Found = 1                 BREAK             END              FETCH NEXT FROM CertCursor INTO @OldValue         END          CLOSE CertCursor         DEALLOCATE CertCursor          IF (@Found = 1)         BEGIN             SELECT @JobMessage = REPLACE(@JobMessage, @OldValue, '')         END          UPDATE MyJobs WITH (ROWLOCK)         SET job_message = ISNULL(@Certificate, '') + ISNULL(@JobMessage, '')         WHERE MyJobs.job_id = @ID          FETCH NEXT FROM InsertCursor INTO @ID, @Certificate     END      CLOSE InsertCursor     DEALLOCATE InsertCursor 

EXPECETD RESULTS (assuming data from above):

UPDATE MyOrders SET req_cert = 'Cert1' WHERE ID = 1 

job_message should be Cert1 Accepted

UPDATE MyOrders     SET req_cert = 'Cert2'     WHERE ID = 1 

job message should be Cert2 Accepted

UPDATE MyOrders SET ref_num = null WHERE ID = 1 GO UPDATE MyOrders SET  req_cert = 'Cert1' WHERE ID = 1 GO UPDATE MyOrders SET ref_num = 'Job0001' WHERE ID = 1 

job message should be Cert1 Accepted

Avoiding if else statements and switch statements

Consider I have a cron expression as “* * */2 * ” I have to extract the recurrence on days ,hours and minutes if it is not “” . I have the below function which works as well.

public void CronDivision(string cron) { string days, value;  string[] cronDiv = cron.split(' ');  if(cronDiv[2] != "*")  {   days = "Days"   value = cronDiv[2].split('/')[1];  }  else if(cron[1] != "*)  {   //Similar logic  }  else if(cron[0] != "*)  {   //Similar logic  }  else if(cron[3] != "*)  {   //different logic for extracting weeks  } else if(cron[3] != "*)  {   //different logic for extracting years  } } 

How do I generalize the above function so that I dont want to use if else/switch statements with hardcoded numbers like1,2,3 and so on after the division of cron expression.

Can it be made more readable without the obvious solution of splitting the actual cron expression and extracting it. Basically I need a cleaner approach for this problem without usage of numbers like 1,2,3 etc?

Simple local cache that checks by date else get from db

I’m looking for feedback on how this can be improved, basically what I’m trying to do is check if the cache has the object else check from the database.

I have list of ids, then I separate them based on if they already exist in the cache or not, based on the date as well. One list fetches from the cache, the other from the db. Then return the list. that way I don’t have to make multiple calls to cache/db.

The alternative is to implement a compound-key but I would rather not go that route

https://github.com/mel3kings/taxi-cab-api/blob/master/src/main/java/com/simple/controller/CabController.java

 private List<Cab> getFromCacheOrDb(CabTripsRequest request) {         Map<Boolean, List<String>> cachedCabs = request.getMedallions().stream().collect(                 partitioningBy(key -> {                     if (!cache.peek(key)) { return false; }                     return cache.get(key).stream().anyMatch(a -> a.getPickupDateTime().equals(request.getDate()));                 }));         List<Cab> response = cachedCabs.get(true).stream().map(k -> cache.get(k)).flatMap(List::stream)                 .filter(cab -> cab.getPickupDateTime().equals(request.getDate())).collect(toList());         if (cachedCabs.get(false).size() > 0) {             List<Cab> database = storage.fetch(cachedCabs.get(false), request.getDate());             response.addAll(database);             cache.save(database);         }         log.info("response from cache/db size:" + response.size());         return response;     } 

For purposes of Divine Sense or ‘detect evil and good’, how else, other than ‘hallow’, can an object or area be magically consecrated or desecrated?

The paladin’s Divine Sense feature allows the paladin to

…detect the presence of any place or object that has been consecrated or desecrated, as with the hallow spell.

Similarly, the spell detect evil and good allows the caster to

…know if there is a place or object within 30 feet of you that has been magically consecrated or desecrated.

How, other than via the spell hallow, can a place or object be consecrated or desecrated, so that it would be detected by Divine Sense or detect evil and good?

Question inspired by this one from earlier today, where we establish that Divine Sense cannot detect whether an object is cursed.

SQL if else statements with multiple select

I have a SP in SQl like below

ALTER PROCEDURE [dbo].[spGetCurrencyDetails] @CompanyId int,@Id int, @strCode varchar(1) = ''  AS BEGIN     SET NOCOUNT ON;          if @strCode='Z'              (select a.Companyid,a.CurrencyID curID,c.Symbol,e.SellingRate from acompanyMaster a             left outer join aCurrencyMaster c on a.companyid=c.companyid and a.CurrencyID=c.CurrencyID             left outer join ExchangeRateDetail e on a.companyid=e.companyid and a.CurrencyID=e.CurrencyID              where a.companyID=@CompanyID)         else if @strCode='C'             (select a.Companyid,a.CurrencyID curID,c.Symbol,e.SellingRate from acustomermaster a             left outer join aCurrencyMaster c on a.companyid=c.companyid and a.CurrencyID=c.CurrencyID             left outer join ExchangeRateDetail e on a.companyid=e.companyid and a.CurrencyID=e.CurrencyID             Where a.customerID = @Id and a.companyid=@CompanyID)         else if @strCode='D'             (select a.Companyid,a.depcurrencyid curID,c.Symbol,e.SellingRate from adepartmentMaster a             left outer join aCurrencyMaster c on a.companyid=c.companyid and a.depcurrencyid=c.CurrencyID             left outer join ExchangeRateDetail e on a.companyid=e.companyid and a.depcurrencyid=e.CurrencyID             where a.DepartmentID=@Id and a.CompanyID=@CompanyID)         else if @strCode='V'             (select a.Companyid,a.CurrencyID curID,c.Symbol,e.SellingRate from avendorMaster a             left outer join aCurrencyMaster c on a.companyid=c.companyid and a.CurrencyID=c.CurrencyID             left outer join ExchangeRateDetail e on a.companyid=e.companyid and a.CurrencyID=e.CurrencyID             Where a.VendorId = @Id and a.companyid=@CompanyID)         else if @strCode='A'             (select a.Companyid,a.CurrencyID curID,c.Symbol,e.SellingRate from aaccountmaster a             left outer join aCurrencyMaster c on a.companyid=c.companyid and a.CurrencyID=c.CurrencyID             left outer join ExchangeRateDetail e on a.companyid=e.companyid and a.CurrencyID=e.CurrencyID             Where a.accountID = @Id and a.companyid=@CompanyID)         else if @strCode='I'             (select aaccountmaster.currencyid curID from apackagesettings              left outer join aaccountmaster on aaccountmaster.accountid=apackagesettings.inventoryid              and apackagesettings.companyid = aaccountmaster.companyid and  aaccountmaster.CompanyId =@CompanyID)         else             (select a.Companyid,a.CurrencyID curID,c.Symbol,e.SellingRate from aCurrencyMaster a             left outer join aCurrencyMaster c on a.companyid=c.companyid and a.CurrencyID=c.CurrencyID             left outer join ExchangeRateDetail e on a.companyid=e.companyid and a.CurrencyID=e.CurrencyID             Where a.CurrencyID = @Id and a.companyid=@CompanyID)       SET NOCOUNT OFF;  END 

Here according to value of @strCode each loop is selected, and each using different set of tables and returning different column values.

This working fine now, but it is taking more time to load.

How do I make this run faster? is it possible to use a switch case condition so that it will only check the desired condition only

please help

Is this a proxy or bridge or something else?

I have an object that acts as a “bridge” between objects and I’m not really sure if it has a name.

Unity (the game engine) lets me build a concrete object and then link those objects in the inspector at design time. For obvious reasons, I can’t link objects that are created at runtime.

I’ve overcome that by creating this class:

class ManagerBridge : ScriptableObject {     public Manager instance { get; set } } 

I can then create an instance of this object at design time, in the Unity editor, and link it to any object that needs to use the manager:

class ManagerUser : MonoBehaviour {    public ManagerBridge manager; // filled in automatically in Unity     public void Update()    {         if(manager.instance != null)             manager.DoSomething();    } } 

And the manager sets itself on startup:

public class Manager : MonoBehaviour {     public ManagerBridge bridge;      public void Awake() // called when the manager is created by Unity     {           bridge.instance = this;     }      public void DoSomething() { } } 

So anyways, the “bridge” object does nothing but store a reference to and allow access to the real Manager object. I’m pretty sure it has to duplicate the Manager’s interface to qualify as a proxy, so what the heck should I call this thing?

Thanks.

How do I know if it was old chemicals that did not produce images on the film strip or something else?

enter image description here

My chemicals are about four years old and maybe used about six times. They have been stored inside my home, 75° F, and in a cabinet. Chemicals are in glass beer growlers with tight lids. This is the first time I developed and have no images. I am thinking it’s that my chemicals are now expired but I am unsure if this is what it looks like if they are bad. Or it’s a result of something else. Are are No images, no sign of any frames. And yes, the film seems to be thinner than normal. Can someone tell me if this is the result of bad chems? They also haven’t been used in a year