Filtering result set with a value selected from another Sql Server

Question

Filtering result set with a value selected from another Sql Server

WilburSmith

Hall of Fame

Points: 3225
More actions
February 6, 2024 at 12:08 pm

#4374149

Hi,
I have a very slow view at one of the customer's database.
I could speed up the select if I could filter the data with Value_Date.
The "problem" is that there are two Sql Servers in this equation and I don't yet know how to use variables / expressions to accomplish this...
I know I can use the OleDb 'Data access mode' Sql commad or sql command from variable but I don't know how to transfer the Value_Date from another query there - something like:
Select * from BIG_view where Value_Date = ?
Ville

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99128 More actions · Answer 1

So is this view using linked server queries? If so, that could well be the real problem, as such queries can often be slow, especially if the server running the query end up asking for every row from the other server's table(s), rather than just the rows it needs. Without the definition of the VIEw, it's difficult/impossible to say, but it might be that a VIEW is not the right solution here.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

WilburSmith Hall of Fame Points: 3225 More actions · Answer 2

Hello Thom,

No there are no linked server definitions between these two servers and there will not be either.

Like said, I'd like to read one value (from a parameter table) of one Sql Server and the use that value in the where clause of the main query of the other Sql Server.

How to accomplish this...?

Ville

Phil Parkin SSC Guru Points: 246956 More actions · Answer 3

First step is to create a variable to hold Value_Date. Despite the underlying datatype being (presumably) a date, you may find it easier to work with strings within SSIS expressions.

Then run an ExecuteSQL task which populates the variable.

Create a calculated variable to hold the SQL statement you need

"Select * from BIG_view where Value_Date = '" + @ValueDate + "'"

Use that calculated variable as your query source (SQL command from variable, as you suggest). If you can avoid SELECT * and instead explicitly name just those columns which you need, that will also help performance.

Take it step by step and you'll get there in the end. But as you've not done it before, you have some learning to do.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99128 More actions · Answer 4

Phil Parkin wrote:

Create a calculated variable to hold the SQL statement you need
"Select * from BIG_view where Value_Date = '" + @ValueDate + "'"

Wouldn't it make more sense for the query to be "Select * from BIG_view where Value_Date = ?" and then pass a parameter in the Execute T-SQL Task?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Phil Parkin SSC Guru Points: 246956 More actions · Answer 5

Thom A wrote:

Phil Parkin wrote:
Create a calculated variable to hold the SQL statement you need
"Select * from BIG_view where Value_Date = '" + @ValueDate + "'"
Wouldn't it make more sense for the query to be "Select * from BIG_view where Value_Date = ?" and then pass a parameter in the Execute T-SQL Task?

Are you looking at this backwards, perhaps?. Value_Date needs to be obtained first and then substituted into the query for the data flow data source.

The data source can be either SQL Command (with parameter) or SQL Command from Variable (which is what I suggested earlier). Either of these should work, though I find the second easier to debug because you get to see the full query. An ExecuteSQL task makes no sense at this point.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

WilburSmith Hall of Fame Points: 3225 More actions · Answer 6

Hi Phil,

I now tried to accomplish this in one of the packages.

Here is what I did and what happened.

Get the Value_date from another Sql server

'Execute Sql Task' to get the Value_Date for this data flow

Select 
   STG_src_value_date
From
   VAKAVARAISUUS.REF_VALUE_DATE
Where
   Datatunnus = 'LAINAT'

Result set - Single row

and then Ressult set name... and Variable name: User::STG_src_value_date__LAINAT

Creating the query variable for the other Sql server

I first added an 'Expression task' to the Control flow but then nothing was passed to the Source Editor query window...I wonder why? What is it for then...

Then I put the expression in the Variables declaration window

@[User::DURAATIO_Select] = "
SELECT 
    LAINANUMERO, TAPAHTUMA_YHTEENVETO_KDI, TILANNEPVM, DURAATIO_PO_KORKO_NPV
FROM 
    LAINA.DURAATIO
WHERE 
    TILANNEPVM = '" + @[User::STG_src_value_date__LAINAT]+ "'"

Using the query/select variable in the source editor

'Sql command from variable'

...and phaf...the VStudio got jammed...luckily I had backups of the package

Well I finally found the 'Delay validation' setting True🙂

So I succesfully piloted the mechanism,

thank you

Ville

Ps. This hanging of the VStudio happens also with one of my (Customer's) slow views...What is the tool doing? Can't it get the column metadata without possibly trying to run it...??

Phil Parkin SSC Guru Points: 246956 More actions · Answer 7

I first added an 'Expression task' to the Control flow but then nothing was passed to the Source Editor query window...I wonder why? What is it for then...

The Expression Task allows you to set the value of a variable at run time. After doing this, you should be able to use the variable as the source of your SQL Command from Variable source.

If you did exactly this and it did not work, please provide a bit more detail.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

WilburSmith Hall of Fame Points: 3225 More actions · Answer 8

Ah...of course, that didn't cross my novice SSIS mind, thanks

Have you ever been in the situation that you need to keep the Data Flow as Delay validation = True because otherwise VS hangs...?

Like said, this happens to me and I was just thinking what might me the situation that I needed to switch it back to False and got jammed again and maybe did not remember to save before that....

Phil Parkin SSC Guru Points: 246956 More actions · Answer 9

WilburSmith wrote:

Have you ever been in the situation that you need to keep the Data Flow as Delay validation = True because otherwise VS hangs...?

I haven't had this, as far as I remember. But that may be because my data sources are usually easily and quickly accessible.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin