Sub Query Criteria Precidence

Transact-SQL
1

I tried to search the forms for an answer, but couldn't find an answer.

If I have a query like this:

SELECT foo.date, sq.name
FROM foo INNER JOIN OPENQUERY(db,'
    SELECT bar.date, bar.name
    FROM bar
    ')  sq ON foo.date = sq.date
WHERE foo.date = GETDATE()

Will the sub query pull all dates/names in the bar table or will it be limited by the foo.date = sq.date criteria?

2

The subquery will pull all the dates. It can't compare them until it gets the data back local to the other table.

1 Like
3

Thank you, Scott. That's what I thought, but wanted to double check. I hate having to set date criteria twice for a query. But if that is what I have to do, then so be it.

4

We get around this (but it is a pain / not straightforward) by either included all-possible-values in the OPENQUERY query, or pre-transferring them TO remote and JOINING them there.

Basically what I want, at the far end, is to include in the JOIN is the results of

SELECT DISTINCT foo.date
FROM foo
WHERE ...

Of course if that is billions of rows, and the remote [BAR] table only contains a dozen rows, then that would be pointless!

Horses-for-courses, but sometimes it is "enough" to include in the OPENQUERY query something just to limit the scope a bit, so may the results from

SELECT MIN(foo.date) AS [MinDate], MAX(foo.date) AS [MaxDate]
FROM foo
WHERE ...

is enough to reduce the rows pulled locally.

Then you could also try

SELECT foo.date, sq.name
FROM foo 
   INNER JOIN db.RemoteDatabase.dbo.bar AS sq
       ON foo.date = sq.date
WHERE ...

and see whether, for that query, the SQL query plan generated is smart enough to figure out the minimum rows to pull locally

5

Since you are joining to a single table on the linked server I would use 4 part naming.

SELECT foo.date, sq.name
FROM foo 
INNER JOIN db.db.dbo.bar sq ON foo.date = sq.date
WHERE foo.date = GETDATE()
6

I assumed that (1) column "date" was of data type date, and the asker was counting on SQL "reducing" GETDATE() to a date or (2) the user simplified the q because he/she knew how to care of date/datetime issues already

7

I assumed the example was trivialised, so I sincerely hope that isn't the answer!!

8

I tried the four part naming first, but for some reason it bogs down the server to the point of locking it up. Which doesn't make sense. It wasn't that way at other shops I've been in, only at the one I am currently in.

9

Kristen, you are correct, it was a very trivialized example.

ScottPletcher, you are also correct that "date" is data type of date.

The GETDATE() is actually a variable that contains a date. However, I cannot use the variable within the OPENQUERY. That has been one of my biggest challenges...reducing the amount of data returned by the OPENQUERY. I am not sure how etting the date to MIN(date), MAX(date) is going to limit the data.

Although, now that I think about it, I could use a BETWEEN and set a range to reduce the amount of data.

10

Exactly! Here's a quick example.

DECLARE @end_date date
DECLARE @sql varchar(8000)
DECLARE @start_date date

--as an example, assume we want to return all of Jan 2008.
SET @start_date = '20180101'
SET @end_date = '20180201'

SET @sql = '
SELECT foo.date, sq.name
FROM foo INNER JOIN OPENQUERY(db,''
    SELECT bar.date, bar.name
    FROM bar
    WHERE bar.date > ''''$start_date$'''' AND bar.date < ''''$end_date$''''
    '') sq ON foo.date = sq.date
'
SET @sql = REPLACE(REPLACE(@sql,
    '$start_date$', CONVERT(varchar(10), @start_date, 120)),
    '$end_date$', CONVERT(varchar(10), @end_date, 120))
PRINT @sql
--EXEC(@sql) --uncomment to exec code
11

Wish I could use variables and dynamic queries. Unfortunately, the 3rd party app will not allow it. But thank you for the suggestions.

12

The "variables" could come from parameters, such as ?, that other languages use for substitution. But, yes, you'd still probably need dynamic SQL.