February 6, 2024 at 12:08 pm
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
February 6, 2024 at 12:27 pm
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
February 6, 2024 at 1:48 pm
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
February 6, 2024 at 1:54 pm
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
February 6, 2024 at 2:27 pm
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
February 6, 2024 at 2:46 pm
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
February 8, 2024 at 11:42 am
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...??
February 8, 2024 at 12:12 pm
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
February 8, 2024 at 12:39 pm
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....
February 8, 2024 at 1:53 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply