Are you allowed to replicate that table in another database? Using transactional replication, for latest data access. If yes, then I'd test a more cumbersome process like replicating the needed table, building another table or materialized view with a computed column as you need.
If replication is not an option, I'd say that you need to establish a scheduled process to take that table in your database and continue locally to process the data. Exactly like the first suggestion, but using your own scripts and schedule.
That, or I think you're stick to the scalar functions that aren't really the best option from performance point of view.
ok. It looks like you have 4 tables here with nothing to join them together. Perhaps the easiest solution at this point would be to create a mapping table called something like CarRental which would have the columns Carpoolid, cilentsid, dateinid, and dteoutid.
I think the way I would go would be to drop the datein and dateout table and put them on a table called car rental.
CREATE TABLE [dbo].[CarRental](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CarpoolID] [int] NOT NULL,
[ClientID] [int] NOT NULL,
[DateIN] [datetime] NULL,
[DateOut] [datetime] NOT NULL,
CONSTRAINT [PK_CarRental] 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
ALTER TABLE [dbo].[CarRental] WITH CHECK ADD CONSTRAINT [FK_CarRental_CarPool] FOREIGN KEY([CarpoolID])
REFERENCES [dbo].[CarPool] ([Id])
GO
ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_CarPool]
GO
ALTER TABLE [dbo].[CarRental] WITH CHECK ADD CONSTRAINT [FK_CarRental_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([Id])
GO
ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_Clients]
GO
Car rental now stores the relationship between the client and the car that they rented and for each record stores the datein and dateout.
If you dont want to script all of this out you can use the table designer to create the table by right clicking on tables and then choosing new table. You can use the database diagram to drag and drop columns in order to create your foreign key relationships by right clicking database diagrams and chosing New Database diagram.
If you use this structure then You would leave the DateIn field as NULL untill the car was returned. Your query to find out which clients and cars have not been returned would be
SELECT carpool.brand, clients.name, clients.lastname
from carrental
join carpool on carpool.id = carrental.carpoolid
join clients on clients.id = carretnal.cientid
where carretnal.datein IS NULL
In this case you dont need to update the records for people who havent returned a car. You only update the record to contain the date when they have returned the car.
UPDATE CarRental
SET Datein = GETDATE()
WHERE Clientid = @CLientid
AND Carpoolid = @Carpoolid
Best Answer
If I understand you correctly you have two tables (for this question):
and
Obviously not actual code there to create your tables...
And your application is somehow joining those tables behind the scenes. Probably on the ClientID. So your user runs a filter on "Acme Company" and behind the scenes SQL Server is doing a JOIN statement.
Again - pseudocode
So here is where having more information can really help. On first blush this could be a problem with a simple index create as a solution. Perhaps there is no index on the clientID in each table. Perhaps there is no index on the ClientName.
But there could be other factors at play, too. It is possible that the vendor issues this query as a contains search using the syntax of
CompanyName LIKE '%Acme Company%'and then no index on CompanyName would help. It could be that the database design requires a lot of key lookups and an index isn't the solution.Either way - 6 minutes for 15 rows is not acceptable. I would look at those tables and at least verify that the CompanyName and ClientID (or whatever the columns are that contain those values) are indexed on each side.
I would also strike up a conversation with the vendor. Even just you adding indexes could be an action that violates your support contract. Perhaps there are considerations that they already have. And you shouldn't have to be working through someone else's bad design or poorly performing application.
You can gather some data to help you. Play with the PAL tool and look at your performance data on your system. If you have a development/test environment and you can recreate this, perhaps through playing with Extended Events or SQL Trace you can see the query that issued and then run the query showing a query plan to get a sense for what is happening. But I would definitely loop the vendor in to a question like this.