Parameter Information cannot be derived from SQL Statements with sub-select queries

I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.

>--Original Message--
>Osk wrote:
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX degree OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX degree OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.comsql

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
Many thanks,
Osk
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
David Gugick
Imceda Software
www.imceda.com
|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk
|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.

>--Original Message--
>Osk wrote:
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
David Gugick
Imceda Software
www.imceda.com
|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
Thanks,
Osk

>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com

Parallel vs. serial plan

Hi,
What would be the advantages of lengthy report parallel vs.
serial execution, say on a 4-way SMP system, if the query
in itself is not able to produce sustained load of more
than 4 seconds on a single CPU of the system. I'm using MS
SQL Server 2000 on MS Windows 2000 with the lastest service
packs.
--
Many thanks,
OskOsk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
I would say that 4 seconds is a long time in the CPU world. It may not
be an issue if this is run off-hours, but during production hours, if
run many times, it could be more of an issue. Parallel executions
generally consume more CPU than their single-CPU brothers. It's just
more costly to manage parallel executions. So in your case, you are
potentially tying up one CPU out of four for four seconds, leaving the
other three to perform other tasks. With a parallel plan, depending on
how many CPUs SQL Server chooses to use, you'd end up tying up more CPU
for more than four CPU seconds, but the query could finish in 2.5
seconds.
Are you saying you have disabled parallel plans for this query using a
MAXDOP hint or have changed the corresponding server setting to prevent
parallel executions?
--
David Gugick
Imceda Software
www.imceda.com|||SQL-Server does not know how important a particular query is for you. It
doesn't discriminate queries that way. It just estimates (for each
query) whether executing the parallel plan is likely to be faster than
the serial plan. If it is, and the server is not too stressed, then it
will execute the parallel plan. Otherwise, it will execute the serial
plan.
However, there is a threshold below which a parallel plan will not be
considered. By default, this "cost threshold for parallelism" is set to
5. With this setting, SQL-Server will not consider a parallel plan if
the estimated execution time of the serial plan is less than 5 seconds.
Hope this helps,
Gert-Jan
Osk wrote:
> Hi,
> What would be the advantages of lengthy report parallel vs.
> serial execution, say on a 4-way SMP system, if the query
> in itself is not able to produce sustained load of more
> than 4 seconds on a single CPU of the system. I'm using MS
> SQL Server 2000 on MS Windows 2000 with the lastest service
> packs.
> --
> Many thanks,
> Osk|||Yes, I have changed the 'max degree of parallelism' server
setting to 1. So as I understand it now, when the optimizer
thinks the query can benefit from executing in parllel at
the same time when the rest of CPUs are more or less idle
then, despite the parallel plan being more costly in terms
of CPU time, the query will return results faster. So I
need look at what kind of load is placed on the rest of
CPUs during the time the query runs to determine whether
there will be any benefit or parallelism.
>--Original Message--
>Osk wrote:
>> Hi,
>> What would be the advantages of lengthy report parallel vs.
>> serial execution, say on a 4-way SMP system, if the query
>> in itself is not able to produce sustained load of more
>> than 4 seconds on a single CPU of the system. I'm using MS
>> SQL Server 2000 on MS Windows 2000 with the lastest service
>> packs.
>I would say that 4 seconds is a long time in the CPU
world. It may not
>be an issue if this is run off-hours, but during
production hours, if
>run many times, it could be more of an issue. Parallel
executions
>generally consume more CPU than their single-CPU brothers.
It's just
>more costly to manage parallel executions. So in your
case, you are
>potentially tying up one CPU out of four for four seconds,
leaving the
>other three to perform other tasks. With a parallel plan,
depending on
>how many CPUs SQL Server chooses to use, you'd end up
tying up more CPU
>for more than four CPU seconds, but the query could finish
in 2.5
>seconds.
>Are you saying you have disabled parallel plans for this
query using a
>MAXDOP hint or have changed the corresponding server
setting to prevent
>parallel executions?
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Yes, I have changed the 'max degree of parallelism' server
> setting to 1. So as I understand it now, when the optimizer
> thinks the query can benefit from executing in parllel at
> the same time when the rest of CPUs are more or less idle
> then, despite the parallel plan being more costly in terms
> of CPU time, the query will return results faster. So I
> need look at what kind of load is placed on the rest of
> CPUs during the time the query runs to determine whether
> there will be any benefit or parallelism.
>
I thik you might be overthinking this a bit. Why have you decided to
change the MAX DEGREE OF PARALLELISM on the server and not let SQL
Server manage parallel queries on its own? Occasionally, someone reports
an issue with a parallel plan which can be easily fixed with a MAXDOP
(1) in the query.
--
David Gugick
Imceda Software
www.imceda.com|||Well, that's how I understood it. If I'm wrong please
correct me.
I've set the server-wide MAXDOP to 1 because of two reasons:
1) to see whether placing the load of reports on a single
CPU will allow smaller queries have fulfilled their CPU
needs quicker than when they would have to wait for a
report to execute in parallel on all CPUs (although this
seems to work only in cases when the report doesn't hold
locks incompatible with other query's locks)
2) I can't provide hints at the T-SQL level yet
--
Thanks,
Osk
>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>> Yes, I have changed the 'max degree of parallelism' server
>> setting to 1. So as I understand it now, when the optimizer
>> thinks the query can benefit from executing in parllel at
>> the same time when the rest of CPUs are more or less idle
>> then, despite the parallel plan being more costly in terms
>> of CPU time, the query will return results faster. So I
>> need look at what kind of load is placed on the rest of
>> CPUs during the time the query runs to determine whether
>> there will be any benefit or parallelism.
>I thik you might be overthinking this a bit. Why have you
decided to
>change the MAX DEGREE OF PARALLELISM on the server and not
let SQL
>Server manage parallel queries on its own? Occasionally,
someone reports
>an issue with a parallel plan which can be easily fixed
with a MAXDOP
>(1) in the query.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||Osk wrote:
> Well, that's how I understood it. If I'm wrong please
> correct me.
> I've set the server-wide MAXDOP to 1 because of two reasons:
> 1) to see whether placing the load of reports on a single
> CPU will allow smaller queries have fulfilled their CPU
> needs quicker than when they would have to wait for a
> report to execute in parallel on all CPUs (although this
> seems to work only in cases when the report doesn't hold
> locks incompatible with other query's locks)
> 2) I can't provide hints at the T-SQL level yet
>
MAXDOP at the server level does not mean that reports are only going to
run on a single CPU. Unless what you're saying is that only one report
can run at a time and because of that it will only run on a single CPU.
MAXDOP 1 just means SQL Server will only use a single CPU for a query,
but the CPU used could be any available (maybe you already understood
that). Obviously, if 4 reports were executed simultaneously and you had
4 CPUs on the server, they could be running on different CPUs.
If you have concerns, I guess you could leave the option on, but I would
suggest you test with the default settings as well.
David Gugick
Imceda Software
www.imceda.com

Parallel execution of DTS steps.

Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
MaxYes, you can. Basically, tasks run in parallel, unless you have set up
workflows that force them to be run serially. That said, if your source is
the same for all of your data pumps, then the process gets serialized, since
the connection is in use. In that case, create multiple connections, each
of which point to the same server.
For example, if you are pumping from ServerA to ServerB, create, say, 4
ServerA connections - ServrA1, ServerA2, etc. and the same goes for ServerB.
Then, make sure your pumps use the different connections.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Maxwell2006" <alanalan@.newsgroup.nospam> wrote in message
news:eobZWSBWGHA.5044@.TK2MSFTNGP09.phx.gbl...
Hi,
I have a DTS package that exports 50 database tables from SQL Server to
Oracle.
At this point the DTS package runs steps after each other (serial
execution). Since both servers are extremely powerful, the can handle more
than one concurrent DTS step at a time.
Is there any way to have DTS execute five or six steps in parallel fashion?
Any help would be appreciated,
Max|||Then you will have to use a different connection for each datapump.
AMB
"Maxwell2006" wrote:
> Hi,
>
> I have a DTS package that exports 50 database tables from SQL Server to
> Oracle.
> At this point the DTS package runs steps after each other (serial
> execution). Since both servers are extremely powerful, the can handle more
> than one concurrent DTS step at a time.
>
> Is there any way to have DTS execute five or six steps in parallel fashion?
>
> Any help would be appreciated,
> Max
>
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp