Parameter Optimization

Hi guys,
I have a rather complex query which accepts 2 date parameters. When the
dates are hard-coded the query runs in 2 seconds; however when a parameter
is passed with the same values the query runs in excess of 2 minutes.
I got around this by building the entire query into a varchar variable, and
then using the exec(strexpression).
Is there a problem in the way the SQL server optimizer handles parameterized
queries? And what are the alternate ways to get around this?
Thanks,
JustinMaybe post some ddl on this specifying what the datatypes are.
"Justin" wrote:

> Hi guys,
>
> I have a rather complex query which accepts 2 date parameters. When the
> dates are hard-coded the query runs in 2 seconds; however when a parameter
> is passed with the same values the query runs in excess of 2 minutes.
>
> I got around this by building the entire query into a varchar variable, an
d
> then using the exec(strexpression).
>
> Is there a problem in the way the SQL server optimizer handles parameteriz
ed
> queries? And what are the alternate ways to get around this?
>
> Thanks,
> Justin
>
>|||I noticed that if you have many records in tables, conversion from varchar
to nvarchar or back can cause behavior you talk about. maybe when you
hardcode dates u use different type than when you pass them as params?
peter

Parallel execution of source SQL commands

Hi,

we're accessing a SQL Server as a source for some SSIS packages using quite complex SQL commands. We have dataflows getting data from up to 10 queries. The problem is that SSIS starts all these queries in parallel using up all the memory of the server (the source SQL server, not the server SSIS is running on). So the queries are very slow. Is there any way to force SSIS to start the queries after each other?

I already browsed the web for some answers on that and I'm not very optimistic... Maybe the only solution is really to feed the result of the query in raw files and process them later...

Thanks,

Thomas,

I know you'll already have considered this but what is wrong executing them in sequence using precedence constraints?

Alternatively you could change the package's MaxConcurrentExectuables property which I'm guessing is currently set to -1.

-Jamie

|||

Jamie,

thanks for the fast reply...

As far as I understand (and that's what I see at least while debugging) precendence constraints and MaxConcurrentExecutables control only the workflow, not what's happening in the dataflow... The problem is that it's one dataflow with 10 queries feeding one destination. I guess both options don't help in this case...

|||

Thomas,

I'm sorry, I misunderstood. I thought you meant the queries were all in seperate data-flows. Now I understand your comment about raw files. Currently that is definately the only way to go.

Mind you, if they are all on the same server can you not just join all the tables together using a single SQL statement?

I have raised a similar request on Microsoft Connect about being able to define the order in which we insert to the destination adapters. You can see it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058

Why not click-through and add some detail about your source adapter problem as well? Or open it as a seperate issue. I would definately vote for it.

-Jamie

|||

Hi Jamie,

I hoped that there is a tweak for it... But that's what I expected...

I voted for your suggestion and opened up a new one since the problem and the solution is a little bit different... http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178069

THANKS!

|||

Excellent. i've voted and commented on it.

-Jamie