Styjun

How do you respond to a colleague from another team when they're wrongly expecting that you'll help them?

Is it improper etiquette to ask your opponent what his/her rating is before the game?

How to set Output path correctly for a Single Image render?

Did arcade monitors have same pixel aspect ratio as TV sets?

A social experiment. What is the worst that can happen?

Is Asuka Langley-Soryu disgusted by Shinji?

Interest Rate Futures Question from Hull, 8e

Why did the EU agree to delay the Brexit deadline?

Has Darkwing Duck ever met Scrooge McDuck?

Is it possible to have a strip of cold climate in the middle of a planet?

How do I repair my stair bannister?

Proving a function is onto where f(x)=|x|.

Can someone explain how this makes sense electrically?

Translation of Scottish 16th century church stained glass

Schmidt decomposition - example

Should I install hardwood flooring or cabinets first?

Why does Async/Await work properly when the loop is inside the async function and not the other way around?

How much character growth crosses the line into breaking the character

Does a 'pending' US visa application constitute a denial?

Greco-Roman egalitarianism

Varistor? Purpose and principle

Fuse symbol on toroidal transformer

Will adding a BY-SA image to a blog post make the entire post BY-SA?

Query about absorption line spectra

Joining Calendar table

What is the difference between “INNER JOIN” and “OUTER JOIN”?Add a column with a default value to an existing table in SQL ServerTable Naming Dilemma: Singular vs. Plural NamesLEFT JOIN vs. LEFT OUTER JOIN in SQL ServerDifference between JOIN and INNER JOINInsert results of a stored procedure into a temporary tableImprove INSERT-per-second performance of SQLite?Finding duplicate values in a SQL tableWhat's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?To get the join columns from a SQL (query) using PYparsing, python script

0

I've created a calendar table (with indexes) that has dates, day of week, fiscal year, day name, etc. The range on my calendar table is between 2005 - 2071. I don't have any issues when I query the calendar table.

I want to use the date column on the calendar table to filter 2 different date columns on 2 different tables.

The issue I'm running into, is when searching a large date range using the date column on the calendar table(i.e. 2010-2019), it's taking a VERY long time to return the results. Whereas, if I query the same date range on each table, the results are returned in seconds. Here's the query i'm working with:

Select 
 a.Col1, 
 a.Col2, 
 a.Col3, 
 a.Date1, 
 b.Date1 
 From 
 TableA as A
 FULL JOIN TableB as B 
 on A.Col1 = B.Col1
 INNER JOIN TableCal as C 
 on A.Date1 = C.Date1 
 or B.Date1 = C.Date1
 WHERE 
 C.date1 between '2010' and '2019'

If there's a better way to write this or if I'm going about it the wrong way, I'm open to all advice / suggestions! Thanks!

sql sql-server database performance

share|improve this question

edited Mar 21 at 14:25

DRapp

38k1056115

asked Mar 21 at 13:41

SD617SD617

11

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Start here on how to get help with a slow query. Also, although you have already made your own, you may find Aaron Bertrand's calendar table more useful / more dynamic. Also from Aaron, be sure you really need NOLOCK and understand why it can be a bad idea. It is not a "go faster" button.
  
  – scsimon
  Mar 21 at 13:42
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  First of all, remove those NOLOCK hints. They don't make anything run faster. Second, does your Calendar table have indexes on its columns? If not, it's going to be slow. Finally, post the actual query. What you posted here doesn't join dates and filters the Date column by a year string. A calendar table is supposed to have separate fields for each date element and the date field should be a date. If WHERE c.date between '2010' and '2019' doesn't raise an error it's probably because the date values were implicitly converted to strings, forcing a full table scan
  
  – Panagiotis Kanavos
  Mar 21 at 13:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sample data and desired results would really help. FULL OUTER JOIN is rarely needed.
  
  – Gordon Linoff
  Mar 21 at 14:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  C.date1 is really a varchar of the year?
  
  – scsimon
  Mar 21 at 16:34
  

  
  
  
  

add a comment | 

0

I've created a calendar table (with indexes) that has dates, day of week, fiscal year, day name, etc. The range on my calendar table is between 2005 - 2071. I don't have any issues when I query the calendar table.

I want to use the date column on the calendar table to filter 2 different date columns on 2 different tables.

The issue I'm running into, is when searching a large date range using the date column on the calendar table(i.e. 2010-2019), it's taking a VERY long time to return the results. Whereas, if I query the same date range on each table, the results are returned in seconds. Here's the query i'm working with:

Select 
 a.Col1, 
 a.Col2, 
 a.Col3, 
 a.Date1, 
 b.Date1 
 From 
 TableA as A
 FULL JOIN TableB as B 
 on A.Col1 = B.Col1
 INNER JOIN TableCal as C 
 on A.Date1 = C.Date1 
 or B.Date1 = C.Date1
 WHERE 
 C.date1 between '2010' and '2019'

If there's a better way to write this or if I'm going about it the wrong way, I'm open to all advice / suggestions! Thanks!

sql sql-server database performance

share|improve this question

edited Mar 21 at 14:25

DRapp

38k1056115

asked Mar 21 at 13:41

SD617SD617

11

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Start here on how to get help with a slow query. Also, although you have already made your own, you may find Aaron Bertrand's calendar table more useful / more dynamic. Also from Aaron, be sure you really need NOLOCK and understand why it can be a bad idea. It is not a "go faster" button.
  
  – scsimon
  Mar 21 at 13:42
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  First of all, remove those NOLOCK hints. They don't make anything run faster. Second, does your Calendar table have indexes on its columns? If not, it's going to be slow. Finally, post the actual query. What you posted here doesn't join dates and filters the Date column by a year string. A calendar table is supposed to have separate fields for each date element and the date field should be a date. If WHERE c.date between '2010' and '2019' doesn't raise an error it's probably because the date values were implicitly converted to strings, forcing a full table scan
  
  – Panagiotis Kanavos
  Mar 21 at 13:44
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Sample data and desired results would really help. FULL OUTER JOIN is rarely needed.
  
  – Gordon Linoff
  Mar 21 at 14:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  C.date1 is really a varchar of the year?
  
  – scsimon
  Mar 21 at 16:34
  

  
  
  
  

add a comment | 

I've created a calendar table (with indexes) that has dates, day of week, fiscal year, day name, etc. The range on my calendar table is between 2005 - 2071. I don't have any issues when I query the calendar table.

I want to use the date column on the calendar table to filter 2 different date columns on 2 different tables.

The issue I'm running into, is when searching a large date range using the date column on the calendar table(i.e. 2010-2019), it's taking a VERY long time to return the results. Whereas, if I query the same date range on each table, the results are returned in seconds. Here's the query i'm working with:

Select 
 a.Col1, 
 a.Col2, 
 a.Col3, 
 a.Date1, 
 b.Date1 
 From 
 TableA as A
 FULL JOIN TableB as B 
 on A.Col1 = B.Col1
 INNER JOIN TableCal as C 
 on A.Date1 = C.Date1 
 or B.Date1 = C.Date1
 WHERE 
 C.date1 between '2010' and '2019'

If there's a better way to write this or if I'm going about it the wrong way, I'm open to all advice / suggestions! Thanks!

sql sql-server database performance

share|improve this question

edited Mar 21 at 14:25

DRapp

38k1056115

asked Mar 21 at 13:41

SD617SD617

11

Select 
 a.Col1, 
 a.Col2, 
 a.Col3, 
 a.Date1, 
 b.Date1 
 From 
 TableA as A
 FULL JOIN TableB as B 
 on A.Col1 = B.Col1
 INNER JOIN TableCal as C 
 on A.Date1 = C.Date1 
 or B.Date1 = C.Date1
 WHERE 
 C.date1 between '2010' and '2019'

sql sql-server database performance

sql sql-server database performance

share|improve this question

edited Mar 21 at 14:25

DRapp

38k1056115

asked Mar 21 at 13:41

SD617SD617

11

share|improve this question

edited Mar 21 at 14:25

DRapp

38k1056115

asked Mar 21 at 13:41

SD617SD617

11

edited Mar 21 at 14:25

DRapp

38k1056115

edited Mar 21 at 14:25

DRapp

38k1056115

asked Mar 21 at 13:41

SD617SD617

11

asked Mar 21 at 13:41

SD617SD617

11

1 Answer
1

active

oldest

votes

0

Formally, the answer is to use equijoins, like this:

Select a.Col1, a.Col2, a.Col3
From (a join
 cal ca 
 on a.col2 = ca.col2
 ) full join
 (b join
 cal cb
 on b.col2 = cb.col2
 ) 
 on a.col1 = b.col1
where ca.date between '2010' and '2019' or
 cb.date between '2010' and '2019' ;

share|improve this answer

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418

add a comment | 

Your Answer

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: "1"
;
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55281775%2fjoining-calendar-table%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Formally, the answer is to use equijoins, like this:

Select a.Col1, a.Col2, a.Col3
From (a join
 cal ca 
 on a.col2 = ca.col2
 ) full join
 (b join
 cal cb
 on b.col2 = cb.col2
 ) 
 on a.col1 = b.col1
where ca.date between '2010' and '2019' or
 cb.date between '2010' and '2019' ;

share|improve this answer

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418

add a comment | 

0

Formally, the answer is to use equijoins, like this:

Select a.Col1, a.Col2, a.Col3
From (a join
 cal ca 
 on a.col2 = ca.col2
 ) full join
 (b join
 cal cb
 on b.col2 = cb.col2
 ) 
 on a.col1 = b.col1
where ca.date between '2010' and '2019' or
 cb.date between '2010' and '2019' ;

share|improve this answer

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418

add a comment | 

Formally, the answer is to use equijoins, like this:

Select a.Col1, a.Col2, a.Col3
From (a join
 cal ca 
 on a.col2 = ca.col2
 ) full join
 (b join
 cal cb
 on b.col2 = cb.col2
 ) 
 on a.col1 = b.col1
where ca.date between '2010' and '2019' or
 cb.date between '2010' and '2019' ;

share|improve this answer

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418

Select a.Col1, a.Col2, a.Col3
From (a join
 cal ca 
 on a.col2 = ca.col2
 ) full join
 (b join
 cal cb
 on b.col2 = cb.col2
 ) 
 on a.col1 = b.col1
where ca.date between '2010' and '2019' or
 cb.date between '2010' and '2019' ;

share|improve this answer

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418

answered Mar 21 at 14:02

Gordon LinoffGordon Linoff

790k35314418