So, we know that the optimizer may choose certain join types under different
memory loads or pressures, but will the optimizer vary the degree of
paralellism based on the number of already active threads or the system cpu
load?
--
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----Not at run time... At most the optimizer will output 2 plans, one serial and
one parallell... If for any reason there are not enough resources at run
time to do the parallel plan, the serial plan is run... SQL does not
currently back off of the number of processors, etc...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> So, we know that the optimizer may choose certain join types under
different
> memory loads or pressures, but will the optimizer vary the degree of
> paralellism based on the number of already active threads or the system
cpu
> load?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
>|||No, is based only upon cost of the query, not current activity not current
load.
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> So, we know that the optimizer may choose certain join types under
different
> memory loads or pressures, but will the optimizer vary the degree of
> paralellism based on the number of already active threads or the system
cpu
> load?
> --
> Kevin Connell, MCDBA
> ----
> The views expressed here are my own
> and not of my employer.
> ----
>|||> No, is based only upon cost of the query, not current activity not
current
> load.
From the SQL Server 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
SQL Server monitors CPU usage and adjusts the degree of parallelism at
the query startup time. Lower degrees of parallelism are chosen if CPU
usage is high.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> No, is based only upon cost of the query, not current activity not
current
> load.
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > So, we know that the optimizer may choose certain join types under
> different
> > memory loads or pressures, but will the optimizer vary the degree of
> > paralellism based on the number of already active threads or the
system
> cpu
> > load?
> >
> > --
> > Kevin Connell, MCDBA
> > ----
> > The views expressed here are my own
> > and not of my employer.
> > ----
> >
> >
>|||That's what I was looking for. Maybe I'll send a request for a "knob" on
this one to sqlwish :)
Kevin Connell, MCDBA
----
The views expressed here are my own
and not of my employer.
----
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:eLpn4XvhDHA.944@.TK2MSFTNGP11.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> SQL Server monitors CPU usage and adjusts the degree of parallelism at
> the query startup time. Lower degrees of parallelism are chosen if CPU
> usage is high.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > So, we know that the optimizer may choose certain join types under
> > different
> > > memory loads or pressures, but will the optimizer vary the degree of
> > > paralellism based on the number of already active threads or the
> system
> > cpu
> > > load?
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > >
> > >
> >
> >
>|||Dan,
That is interesting. Do you know if any number of CPU's is being
considered? Or will SQL-Server scale back to a fixed number of CPU's
(for example 2, 4, 8) or will it simply scale back to 1 (and use the
serial query plan)?
I assume the degree of parallism is determined before the query plan for
this number of CPU's is compiled (or fetched from cache).
Gert-Jan
Dan Guzman wrote:
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> From the SQL Server 2000 Books Online:
> <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> SQL Server monitors CPU usage and adjusts the degree of parallelism at
> the query startup time. Lower degrees of parallelism are chosen if CPU
> usage is high.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > No, is based only upon cost of the query, not current activity not
> current
> > load.
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > So, we know that the optimizer may choose certain join types under
> > different
> > > memory loads or pressures, but will the optimizer vary the degree of
> > > paralellism based on the number of already active threads or the
> system
> > cpu
> > > load?
> > >
> > > --
> > > Kevin Connell, MCDBA
> > > ----
> > > The views expressed here are my own
> > > and not of my employer.
> > > ----
> > >
> > >
> >
> >|||I'm planning on doing some testing. Should be pretty straightforward to
figure out.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F79D788.139C5712@.toomuchspamalready.nl...
> Dan,
> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan for
> this number of CPU's is compiled (or fetched from cache).
> Gert-Jan
>
> Dan Guzman wrote:
> >
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> >
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> >
> > SQL Server monitors CPU usage and adjusts the degree of parallelism at
> > the query startup time. Lower degrees of parallelism are chosen if CPU
> > usage is high.
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > So, we know that the optimizer may choose certain join types under
> > > different
> > > > memory loads or pressures, but will the optimizer vary the degree of
> > > > paralellism based on the number of already active threads or the
> > system
> > > cpu
> > > > load?
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > >
> > > >
> > >
> > >|||> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan
for
> this number of CPU's is compiled (or fetched from cache).
My interpretation of the following (from the same topic) is that the
number of CPUs is variable and the optimal number of threads is
reevaluated for each execution:
<Excerpt>
SQL Server reexamines the optimal number of thread decisions each time a
query execution plan is retrieved from the procedure cache. For example,
one execution of a query can result in use of a serial plan, a later
execution of the same query can result in a parallel plan using three
threads, and a third execution can result in a parallel plan using four
threads.
</Excerpt>
I haven't played around with this, though. Hopefully, Kevin will post
the results of his tests.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3F79D788.139C5712@.toomuchspamalready.nl...
> Dan,
> That is interesting. Do you know if any number of CPU's is being
> considered? Or will SQL-Server scale back to a fixed number of CPU's
> (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> serial query plan)?
> I assume the degree of parallism is determined before the query plan
for
> this number of CPU's is compiled (or fetched from cache).
> Gert-Jan
>
> Dan Guzman wrote:
> >
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> >
> > From the SQL Server 2000 Books Online:
> >
> > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> >
> > SQL Server monitors CPU usage and adjusts the degree of parallelism
at
> > the query startup time. Lower degrees of parallelism are chosen if
CPU
> > usage is high.
> >
> > </Excerpt>
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > No, is based only upon cost of the query, not current activity not
> > current
> > > load.
> > >
> > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > So, we know that the optimizer may choose certain join types
under
> > > different
> > > > memory loads or pressures, but will the optimizer vary the
degree of
> > > > paralellism based on the number of already active threads or the
> > system
> > > cpu
> > > > load?
> > > >
> > > > --
> > > > Kevin Connell, MCDBA
> > > > ----
> > > > The views expressed here are my own
> > > > and not of my employer.
> > > > ----
> > > >
> > > >
> > >
> > >|||> I'm planning on doing some testing. Should be pretty straightforward
to
> figure out.
Please share your results with the group.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> I'm planning on doing some testing. Should be pretty straightforward
to
> figure out.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3F79D788.139C5712@.toomuchspamalready.nl...
> > Dan,
> >
> > That is interesting. Do you know if any number of CPU's is being
> > considered? Or will SQL-Server scale back to a fixed number of CPU's
> > (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> > serial query plan)?
> >
> > I assume the degree of parallism is determined before the query plan
for
> > this number of CPU's is compiled (or fetched from cache).
> >
> > Gert-Jan
> >
> >
> > Dan Guzman wrote:
> > >
> > > > No, is based only upon cost of the query, not current activity
not
> > > current
> > > > load.
> > >
> > > From the SQL Server 2000 Books Online:
> > >
> > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > >
> > > SQL Server monitors CPU usage and adjusts the degree of
parallelism at
> > > the query startup time. Lower degrees of parallelism are chosen if
CPU
> > > usage is high.
> > >
> > > </Excerpt>
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > No, is based only upon cost of the query, not current activity
not
> > > current
> > > > load.
> > > >
> > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > So, we know that the optimizer may choose certain join types
under
> > > > different
> > > > > memory loads or pressures, but will the optimizer vary the
degree of
> > > > > paralellism based on the number of already active threads or
the
> > > system
> > > > cpu
> > > > > load?
> > > > >
> > > > > --
> > > > > Kevin Connell, MCDBA
> > > > > ----
> > > > > The views expressed here are my own
> > > > > and not of my employer.
> > > > > ----
> > > > >
> > > > >
> > > >
> > > >
>|||Either my test is no good or the documentation is incorrect. DOP is not
affected by system CPU load.
I've been running a machine at 100% CPU load for hours now and large
paralell queries continue to come in at DOP=4
Environment:
4 CPU ML-570G2 (4x2.0Ghz XEON P4)
Win2k Std Sp3
SQL 2K Enterprise Sp3+ms03-031
4GB RAM
sp_config:maxdop = 0
Measuring DOP with profiler event class "Degree of Paralellism" and
verifying execution plan is paralell via QA.
Would love to know if someone else can verify these findings.
Kevin Connell, MCDBA
----
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23zdG5CIiDHA.2192@.TK2MSFTNGP09.phx.gbl...
> > I'm planning on doing some testing. Should be pretty straightforward
> to
> > figure out.
> Please share your results with the group.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>
> "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> > I'm planning on doing some testing. Should be pretty straightforward
> to
> > figure out.
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:3F79D788.139C5712@.toomuchspamalready.nl...
> > > Dan,
> > >
> > > That is interesting. Do you know if any number of CPU's is being
> > > considered? Or will SQL-Server scale back to a fixed number of CPU's
> > > (for example 2, 4, 8) or will it simply scale back to 1 (and use the
> > > serial query plan)?
> > >
> > > I assume the degree of parallism is determined before the query plan
> for
> > > this number of CPU's is compiled (or fetched from cache).
> > >
> > > Gert-Jan
> > >
> > >
> > > Dan Guzman wrote:
> > > >
> > > > > No, is based only upon cost of the query, not current activity
> not
> > > > current
> > > > > load.
> > > >
> > > > From the SQL Server 2000 Books Online:
> > > >
> > > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > > >
> > > > SQL Server monitors CPU usage and adjusts the degree of
> parallelism at
> > > > the query startup time. Lower degrees of parallelism are chosen if
> CPU
> > > > usage is high.
> > > >
> > > > </Excerpt>
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > > No, is based only upon cost of the query, not current activity
> not
> > > > current
> > > > > load.
> > > > >
> > > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > > So, we know that the optimizer may choose certain join types
> under
> > > > > different
> > > > > > memory loads or pressures, but will the optimizer vary the
> degree of
> > > > > > paralellism based on the number of already active threads or
> the
> > > > system
> > > > > cpu
> > > > > > load?
> > > > > >
> > > > > > --
> > > > > > Kevin Connell, MCDBA
> > > > > > ----
> > > > > > The views expressed here are my own
> > > > > > and not of my employer.
> > > > > > ----
> > > > > >
> > > > > >
> > > > >
> > > > >
> >
> >
>|||I'll see what I can find out.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:O7Z4WPViDHA.2592@.tk2msftngp13.phx.gbl...
> Either my test is no good or the documentation is incorrect. DOP is
not
> affected by system CPU load.
> I've been running a machine at 100% CPU load for hours now and large
> paralell queries continue to come in at DOP=4
> Environment:
> 4 CPU ML-570G2 (4x2.0Ghz XEON P4)
> Win2k Std Sp3
> SQL 2K Enterprise Sp3+ms03-031
> 4GB RAM
> sp_config:maxdop = 0
> Measuring DOP with profiler event class "Degree of Paralellism" and
> verifying execution plan is paralell via QA.
> Would love to know if someone else can verify these findings.
> Kevin Connell, MCDBA
> ----
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%23zdG5CIiDHA.2192@.TK2MSFTNGP09.phx.gbl...
> > > I'm planning on doing some testing. Should be pretty
straightforward
> > to
> > > figure out.
> >
> > Please share your results with the group.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> >
> > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > news:u7Ux3%238hDHA.944@.TK2MSFTNGP11.phx.gbl...
> > > I'm planning on doing some testing. Should be pretty
straightforward
> > to
> > > figure out.
> > >
> > >
> > > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > > news:3F79D788.139C5712@.toomuchspamalready.nl...
> > > > Dan,
> > > >
> > > > That is interesting. Do you know if any number of CPU's is being
> > > > considered? Or will SQL-Server scale back to a fixed number of
CPU's
> > > > (for example 2, 4, 8) or will it simply scale back to 1 (and use
the
> > > > serial query plan)?
> > > >
> > > > I assume the degree of parallism is determined before the query
plan
> > for
> > > > this number of CPU's is compiled (or fetched from cache).
> > > >
> > > > Gert-Jan
> > > >
> > > >
> > > > Dan Guzman wrote:
> > > > >
> > > > > > No, is based only upon cost of the query, not current
activity
> > not
> > > > > current
> > > > > > load.
> > > > >
> > > > > From the SQL Server 2000 Books Online:
> > > > >
> > > > > <Excerpt href="http://links.10026.com/?link=architec.chm::/8_ar_sa_163x.htm">
> > > > >
> > > > > SQL Server monitors CPU usage and adjusts the degree of
> > parallelism at
> > > > > the query startup time. Lower degrees of parallelism are
chosen if
> > CPU
> > > > > usage is high.
> > > > >
> > > > > </Excerpt>
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > --
> > > > > SQL FAQ links (courtesy Neil Pike):
> > > > >
> > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > http://www.sqlserverfaq.com
> > > > > http://www.mssqlserver.com/faq
> > > > > --
> > > > >
> > > > > "Anthony Zessin" <Anthony.Zessin@.rrtc.com> wrote in message
> > > > > news:urGQxfthDHA.2212@.tk2msftngp13.phx.gbl...
> > > > > > No, is based only upon cost of the query, not current
activity
> > not
> > > > > current
> > > > > > load.
> > > > > >
> > > > > > "Kevin" <ReplyTo@.Newsgroups.only> wrote in message
> > > > > > news:OaXAe4GhDHA.2080@.TK2MSFTNGP12.phx.gbl...
> > > > > > > So, we know that the optimizer may choose certain join
types
> > under
> > > > > > different
> > > > > > > memory loads or pressures, but will the optimizer vary the
> > degree of
> > > > > > > paralellism based on the number of already active threads
or
> > the
> > > > > system
> > > > > > cpu
> > > > > > > load?
> > > > > > >
> > > > > > > --
> > > > > > > Kevin Connell, MCDBA
> > > > > > > ----
> > > > > > > The views expressed here are my own
> > > > > > > and not of my employer.
> > > > > > > ----
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > >
> > >
> >
> >
>
Showing posts with label types. Show all posts
Showing posts with label types. Show all posts
Monday, March 26, 2012
Monday, March 12, 2012
Optimise these 3 queries into one?
I have these 3 queries - they are the same except each fetches record counts
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?
IF @.Error = 0
BEGIN
SELECT @.nSubsets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 0
SET @.Error = @.ERROR
END
IF @.Error = 0
BEGIN
SELECT @.nAssets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 1
SET @.Error = @.ERROR
END
IF @.Error = 0
BEGIN
SELECT @.nImages = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND
Node.Type = 2
SET @.Error = @.ERROR
ENDSELECT
@.nSubsets = COUNT(CASE Node.type WHEN 0 THEN 1 END),
@.nAssets = COUNT(CASE Node.type WHEN 1 THEN 1 END),
@.nImages = COUNT(CASE Node.type WHEN 2 THEN 1 END)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
--
David Portas
----
Please reply only to the newsgroup
--|||Robin,
I'm assuming you wanted the result in one row. This is untested, but
you get the idea...
IF @.Error = 0
BEGIN
SELECT SUM (CASE Node.Type
WHEN 0 THEN 1
ELSE 0
END) AS nSubSets_cnt
,SUM (CASE Node.Type
WHEN 1 THEN 1
ELSE 0
END) AS nAssets_cnt
,SUM (CASE Node.Type
WHEN 2 THEN 1
ELSE 0
END) AS nImages_cnt
FROM Node
INNER JOIN
Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
AND Node.Type = in (0, 1, 2)
SET @.Error = @.ERROR
END
Christian.|||How about this?
SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
@.nAssets = sum(case when node.type = 1 then 1 else 0 end),
@.nImages = sum(case when node.type = 2 then 1 else 0 end),
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @.nodepath + '%'
I think tyhis will get you what you are looking for. I haven't run it
against anything, but I'm fairly sure it's good to go.
Hope it works for you,
Carl
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> I have these 3 queries - they are the same except each fetches record counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
> nImages (type 2). Is there any way I could get all 3 of these (based on the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||Use the CASE function.
EG:
select
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 0 then 1
else 0 end "NT=0",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 1 then 1
else 0 end "NT=1",
CASE when Adjacency.Path LIKE @.nodepath + '%' AND Node.Type = 2 then 1
else 0 end "NT=2",
from ...
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:bs9jhc$gfo$1$8300dec7@.news.demon.co.uk...
> I have these 3 queries - they are the same except each fetches record
counts
> for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> nImages (type 2). Is there any way I could get all 3 of these (based on
the
> Node.Type integer) with a single query?
> IF @.Error = 0
> BEGIN
> SELECT @.nSubsets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 0
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nAssets = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 1
> SET @.Error = @.ERROR
> END
> IF @.Error = 0
> BEGIN
> SELECT @.nImages = COUNT(*)
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> AND
> Node.Type = 2
> SET @.Error = @.ERROR
> END|||All good stuff. Thanks for all 3 replys.
"Carl Reeds" <creeds0001@.msn.com> wrote in message
news:956eb1fa.0312231411.4e7cec31@.posting.google.c om...
> How about this?
> SELECT @.nSubsets = sum(case when node.type = 0 then 1 else 0 end),
> @.nAssets = sum(case when node.type = 1 then 1 else 0 end),
> @.nImages = sum(case when node.type = 2 then 1 else 0 end),
> FROM Node
> INNER JOIN Adjacency
> ON Adjacency.ID_Node = Node.ID
> WHERE Adjacency.Path LIKE @.nodepath + '%'
> I think tyhis will get you what you are looking for. I haven't run it
> against anything, but I'm fairly sure it's good to go.
> Hope it works for you,
> Carl
>
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bs9jhc$gfo$1$8300dec7@.news.demon.co.uk>...
> > I have these 3 queries - they are the same except each fetches record
counts
> > for one of 3 different record types, nSubsets (type 0), nAssets (type 1)
and
> > nImages (type 2). Is there any way I could get all 3 of these (based on
the
> > Node.Type integer) with a single query?
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nSubsets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 0
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nAssets = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 1
> > SET @.Error = @.ERROR
> > END
> > IF @.Error = 0
> > BEGIN
> > SELECT @.nImages = COUNT(*)
> > FROM Node
> > INNER JOIN Adjacency
> > ON Adjacency.ID_Node = Node.ID
> > WHERE Adjacency.Path LIKE @.nodepath + '%'
> > AND
> > Node.Type = 2
> > SET @.Error = @.ERROR
> > END
Subscribe to:
Posts (Atom)