SQL Function, get max dates from 2 tables then compare with given paramter to function

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
0
down vote

favorite












ALTER FUNCTION [dbo].[GetLastModifiedDate](@ProductURL nvarchar(255), @ProductLastModified datetime)
RETURNS datetime
AS
BEGIN

 DECLARE @Result datetime;
 DECLARE @NotesLastMod datetime;
 DECLARE @DealsLastMod datetime;

 SELECT @NotesLastMod = MAX(LastModified) FROM Notes WHERE ListURL LIKE @ProductURL + '%';
 SELECT @DealsLastMod = MAX(DealLastModified) FROM Deals WHERE ListURL LIKE @ProductURL + '%';

 SELECT @Result = MAX(LastMod) FROM (VALUES(@ProductLastModified),(@NotesLastMod),(@DealsLastMod)) T(LastMod);
 RETURN @Result
END


A very simple self explaining function, gets latest dates from two tables and compare it with given datetime.



Usage is like this,



// SQL Query 1
// SQL Query 2
SELECT dbo.LastModified(Products.URL, Products.LastModified) AS LastModified
FROM PRODUCTS
// Some Inner Joins
// Some Where clauses


Now this function takes around 41 seconds if Notes and Deals tables have 25000 and 300000 rows respectively. We are using SQL Server 2012




sql




share|improve this question



















  • You need index on ListURL fields of both tables
    – Eralper
    Feb 7 at 15:07
















up vote
0
down vote

favorite












ALTER FUNCTION [dbo].[GetLastModifiedDate](@ProductURL nvarchar(255), @ProductLastModified datetime)
RETURNS datetime
AS
BEGIN

 DECLARE @Result datetime;
 DECLARE @NotesLastMod datetime;
 DECLARE @DealsLastMod datetime;

 SELECT @NotesLastMod = MAX(LastModified) FROM Notes WHERE ListURL LIKE @ProductURL + '%';
 SELECT @DealsLastMod = MAX(DealLastModified) FROM Deals WHERE ListURL LIKE @ProductURL + '%';

 SELECT @Result = MAX(LastMod) FROM (VALUES(@ProductLastModified),(@NotesLastMod),(@DealsLastMod)) T(LastMod);
 RETURN @Result
END


A very simple self explaining function, gets latest dates from two tables and compare it with given datetime.



Usage is like this,



// SQL Query 1
// SQL Query 2
SELECT dbo.LastModified(Products.URL, Products.LastModified) AS LastModified
FROM PRODUCTS
// Some Inner Joins
// Some Where clauses


Now this function takes around 41 seconds if Notes and Deals tables have 25000 and 300000 rows respectively. We are using SQL Server 2012




sql




share|improve this question



















  • You need index on ListURL fields of both tables
    – Eralper
    Feb 7 at 15:07












up vote
0
down vote

favorite









up vote
0
down vote

favorite











ALTER FUNCTION [dbo].[GetLastModifiedDate](@ProductURL nvarchar(255), @ProductLastModified datetime)
RETURNS datetime
AS
BEGIN

 DECLARE @Result datetime;
 DECLARE @NotesLastMod datetime;
 DECLARE @DealsLastMod datetime;

 SELECT @NotesLastMod = MAX(LastModified) FROM Notes WHERE ListURL LIKE @ProductURL + '%';
 SELECT @DealsLastMod = MAX(DealLastModified) FROM Deals WHERE ListURL LIKE @ProductURL + '%';

 SELECT @Result = MAX(LastMod) FROM (VALUES(@ProductLastModified),(@NotesLastMod),(@DealsLastMod)) T(LastMod);
 RETURN @Result
END


A very simple self explaining function, gets latest dates from two tables and compare it with given datetime.



Usage is like this,



// SQL Query 1
// SQL Query 2
SELECT dbo.LastModified(Products.URL, Products.LastModified) AS LastModified
FROM PRODUCTS
// Some Inner Joins
// Some Where clauses


Now this function takes around 41 seconds if Notes and Deals tables have 25000 and 300000 rows respectively. We are using SQL Server 2012




sql




share|improve this question











ALTER FUNCTION [dbo].[GetLastModifiedDate](@ProductURL nvarchar(255), @ProductLastModified datetime)
RETURNS datetime
AS
BEGIN

 DECLARE @Result datetime;
 DECLARE @NotesLastMod datetime;
 DECLARE @DealsLastMod datetime;

 SELECT @NotesLastMod = MAX(LastModified) FROM Notes WHERE ListURL LIKE @ProductURL + '%';
 SELECT @DealsLastMod = MAX(DealLastModified) FROM Deals WHERE ListURL LIKE @ProductURL + '%';

 SELECT @Result = MAX(LastMod) FROM (VALUES(@ProductLastModified),(@NotesLastMod),(@DealsLastMod)) T(LastMod);
 RETURN @Result
END


A very simple self explaining function, gets latest dates from two tables and compare it with given datetime.



Usage is like this,



// SQL Query 1
// SQL Query 2
SELECT dbo.LastModified(Products.URL, Products.LastModified) AS LastModified
FROM PRODUCTS
// Some Inner Joins
// Some Where clauses


Now this function takes around 41 seconds if Notes and Deals tables have 25000 and 300000 rows respectively. We are using SQL Server 2012





sql





share|improve this question










share|improve this question




share|improve this question









asked Feb 6 at 8:44









Mathematics

3581521




3581521











  • You need index on ListURL fields of both tables
    – Eralper
    Feb 7 at 15:07
















  • You need index on ListURL fields of both tables
    – Eralper
    Feb 7 at 15:07















You need index on ListURL fields of both tables
– Eralper
Feb 7 at 15:07




You need index on ListURL fields of both tables
– Eralper
Feb 7 at 15:07















active

oldest

votes











Your Answer




StackExchange.ifUsing("editor", function ()
return StackExchange.using("mathjaxEditing", function ()
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix)
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
);
);
, "mathjax-editing");

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "196"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);








 

draft saved


draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f186893%2fsql-function-get-max-dates-from-2-tables-then-compare-with-given-paramter-to-fu%23new-answer', 'question_page');

);

Post as a guest






















active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes










 

draft saved


draft discarded


























 


draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f186893%2fsql-function-get-max-dates-from-2-tables-then-compare-with-given-paramter-to-fu%23new-answer', 'question_page');

);

Post as a guest
































































-

Popular posts from this blog

Greedy Best First Search implementation in Rust

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 1 down vote favorite I have implemented a Greedy Best First Search algorithm in Rust, since I couldn't find an already implemented one in the existing crates. I have a small pet project I do in Rust, and the Greedy BFS is at the core of it. The algorithm is designed to be as flexible as possible. Theoretically, this algorithm could be used as a greedy depth-first search or as a greedy a*. I want to design a good algorithm that I could later use in any other project needing it, not only this one. I am quite new to Rust and this is the second project I do in this language. Also, I am new to writing optimized algorithms. Since this search will be called often, my main concern is performance. What are some tips and tricks to make the algorithm perform better? Of course any other critique and comment is welcome. Bellow is the code: //! Here is
Read more

Function to Return a JSON Like Objects Using VBA Collections and Arrays

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 1 down vote favorite My goal is to create a compact function that can create a JSON Like object from JSON string. I want a function with a small footprint that I or anyone who wants to use it, can simply paste into a module and use. At 61 lines of code, I am happy with its size and portability. Here is an image of JSON Object created from string data using a ScriptControl . Although the Locals Window displays the properties and values correctly, the object itself is extremely difficult to work with. This image shows an object created using getJSONCollection . Because it is made of VBA Collections and Arrays, it is very easy to work with. Option Explicit Private Function getJSONCollection(ByVal Value As Variant, Optional ScriptEngine As Object) As Variant Const DELIMITER As String = "||" Dim col As Collection, JSON As Object, KeyNam
Read more

C++11 CLH Lock Implementation

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 1 down vote favorite 1 As a hobbyist programmer I have been reading "The Art of Multiprocessor Programming" (Herlihy, Maurice; Shavit, Nir) and converting the Java spin locks into C++ - I have enjoyed this and learned a lot. I have implemented the CLH spin queue lock (due to Travis Craig, Erik Hagersten, and Anders Landin) but I feel unhappy about the way that I have the raw pointers interacting with smart pointers and would be grateful for code review advice. As I understand it I must use a smart pointer, in this case std::unique_ptr , for my thread local node pointer or the lock will not deallocate properly and the clh_lock will leak. But... I also have no choice but to use a raw atomic tail queue pointer in order access the atomic exchange member function. Therefore, I have to come up with some combination of either, raw or smart
Read more