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

Chat program with C++ and SFML

Image
Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 2 down vote favorite This is my client code: (My server is very similar at the moment but I will upload it below this one just in case somebody wants to run it) #include <SFML/Graphics.hpp> #include <SFML/Network.hpp> #include <thread> int main() sf::RenderWindow main_win(sf::VideoMode(800, 800), "Chatcy!"); main_win.setPosition(sf::Vector2i(sf::VideoMode::getDesktopMode().width/2 -400, sf::VideoMode::getDesktopMode().height/2 -400)); main_win.setVerticalSyncEnabled(true); //chatcy logo sf::Texture chatcy; chatcy.loadFromFile("image_chatcy.png"); sf::Sprite logo; logo.setTexture(chatcy); //200x800 //font sf::Font font; font.loadFromFile("FreeSerif.ttf"); //name sf::String name("Client"); name += ": "; //text input area and background sf::RectangleShape in_backg
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

Will my employers contract hold up in court?

Image
Clash Royale CLAN TAG #URR8PPP up vote 8 down vote favorite 2 When I started a new job back in July of 2017, I signed a contract that had the following bindings or restrictions if I was ever to leave the company: Cannot work for a competitor within 25 miles of current companies location for the next two years Cannot offer my services to any organization within 25 miles of current companies location for the next two years Now, when mentioned “my services” this can be related to my profession of website design. This contract seems incredibly binding, 2 years in length, and I’m looking to see if it would actually hold up in an Arizona court. contract employment non-compete arizona share | improve this question edited Aug 7 at 15:35 feetwet ♦ 13.8k 9 37 85 asked Aug 6 at 19:05 Alex S. 46 2 What does your company do and what services do they provide? What is your role (i.e. internal work or work for customers).
Read more