On Thu, 17 Nov 2005 17:04:46 +0100, Andrea Montanari wrote:
>Microsoft is quiet deprecating NG in favour of it's
>own forum
Hi Andrea,
Do you have any idea why they are doing that?
Until recently, the Microsoft community pages hosted a portal to the
various microsoft newsgroups. When I clicked the link in your message, I
was quite surprised to see a new (?) forum, totally unrelated to the
newsgroups.
For me personally, it would be a shame if the newsgroupw were abandoned
in favor of a web forum. With Agent (the tool of my choice), Outlook, or
may other NNTP enabled software, it's very easy to follow discussions in
usenet groups. I can easily skip threads about subjects that are of no
interest to me, keep messages that require some research or testing on
hold until I have the time to reply to them, re-read older messages, and
type and post answers when I have time. I am convinced that I'd never be
able to post anywhere near the amount of answers I currently do if I
can't use Agent or a similar tool to access the groups.
If anyone from Microsoft is reading this, can you please confirm or deny
what Andrea wrote? And if it's true, can you explain your reasons for
this move?
Inquiring mind wants to know....
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
hi Hugo,
Hugo Kornelis wrote:
> On Thu, 17 Nov 2005 17:04:46 +0100, Andrea Montanari wrote:
>
> Hi Andrea,
> Do you have any idea why they are doing that?
actually not... I think I've heard it at SQL Server MVPs summit in Dallas,
but I was not paying attention a lot due to alcoolic distractions (:D) ...
they told us that they are increasing Forums support and NNTP flavours will
not be dropped in the next future, and lot of MVPs were very glad to hear
about these new forum where only part of them were disappointed about this
NNTP deprecate status (me first of all :D)
> Until recently, the Microsoft community pages hosted a portal to the
> various microsoft newsgroups. When I clicked the link in your
> message, I was quite surprised to see a new (?) forum, totally
> unrelated to the newsgroups.
> For me personally, it would be a shame if the newsgroupw were
> abandoned in favor of a web forum. With Agent (the tool of my
> choice), Outlook, or may other NNTP enabled software, it's very easy
> to follow discussions in usenet groups. I can easily skip threads
> about subjects that are of no interest to me, keep messages that
> require some research or testing on hold until I have the time to
> reply to them, re-read older messages, and type and post answers when
> I have time. I am convinced that I'd never be able to post anywhere
> near the amount of answers I currently do if I can't use Agent or a
> similar tool to access the groups.
> If anyone from Microsoft is reading this, can you please confirm or
> deny what Andrea wrote? And if it's true, can you explain your
> reasons for this move?
hope they will answer "Andrea is totally wrong, we'll never do it and will
increase NNTP flavours.." :D
> Inquiring mind wants to know....
ROTFL
:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql
Sunday, March 25, 2012
(Optimizer) - Number of Indexes Per Table
Hi all,
We were all told that SQL 7.0 and up could use more than one index per table
during query optimization. In practice, how often does this actually occur?
If and when it does occur, how does this appear in the output of the 'Show
Estimated Execution Plan?". It seems to us that this almost never occurs.
We're seeing the first index, and then after that all HASH joins and such.
Thanks!
James HokesHi James
It happens when you have two where clauses referencing two different columns
with nonclustered indexes, and for each, there are only a few rows that
satisfy the condition.
In the showplan output, it looks like a join, but both tables are the same
table, and it's usually a hash join. SQL Server has build an internal
worktable from the results of using each of the indexes, and then those two
worktables have to be joined. SInce there are no good indexes on the
worktables, a hash join is used to find the rows in common between the
worktables.
Here is an example
USE Northwind
select * into od from [order details]
create index qnt_index on od(quantity)
create index price_index on od(unitprice)
go
select * from od
where quantity = 4 and unitprice = 6
Look at the plan for the select, after building the table and the two
indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Sounds like you have a complicated multi-table join. After the first couple
joins all you have are work tables (intermediate data results) which have no
indexes. In this case it may be necessary to do hash joins due to the lack
of indexes. The key is to whittle down the result sets to a manageable set
in the first joins using the appropriate indexes so the joining of the
intermediate tables are relatively painless. Maybe if you post a query
example and the associated query plan we can cnfirm or deny that theory.
--
Andrew J. Kelly SQL MVP
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Kalen,
Interesting that it would only happen for 'a few rows'.
That being the case, I suspect that this is due to 'total page reads', i.e.
query cost.
Is that a safe assumption?
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi James
> It happens when you have two where clauses referencing two different
columns
> with nonclustered indexes, and for each, there are only a few rows that
> satisfy the condition.
> In the showplan output, it looks like a join, but both tables are the same
> table, and it's usually a hash join. SQL Server has build an internal
> worktable from the results of using each of the indexes, and then those
two
> worktables have to be joined. SInce there are no good indexes on the
> worktables, a hash join is used to find the rows in common between the
> worktables.
> Here is an example
> USE Northwind
> select * into od from [order details]
> create index qnt_index on od(quantity)
> create index price_index on od(unitprice)
> go
> select * from od
> where quantity = 4 and unitprice = 6
> Look at the plan for the select, after building the table and the two
> indexes.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||This is a general principle for any use of nonclustered indexes. They will
only be cost effective if there are only a few rows that meet the condition,
so only a few pages need to be read. If you need to use a nc index to access
100's of rows, which means 100s of pages, it is often considered by the
optimizer to be more cost effective to just scan the table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eoTyWLMxDHA.3208@.tk2msftngp13.phx.gbl...
> Kalen,
> Interesting that it would only happen for 'a few rows'.
> That being the case, I suspect that this is due to 'total page reads',
i.e.
> query cost.
> Is that a safe assumption?
> Thanks,
> James Hokes
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi James
> >
> > It happens when you have two where clauses referencing two different
> columns
> > with nonclustered indexes, and for each, there are only a few rows that
> > satisfy the condition.
> >
> > In the showplan output, it looks like a join, but both tables are the
same
> > table, and it's usually a hash join. SQL Server has build an internal
> > worktable from the results of using each of the indexes, and then those
> two
> > worktables have to be joined. SInce there are no good indexes on the
> > worktables, a hash join is used to find the rows in common between the
> > worktables.
> >
> > Here is an example
> >
> > USE Northwind
> >
> > select * into od from [order details]
> > create index qnt_index on od(quantity)
> > create index price_index on od(unitprice)
> > go
> > select * from od
> > where quantity = 4 and unitprice = 6
> >
> > Look at the plan for the select, after building the table and the two
> > indexes.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Andrew,
Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
clause, multi-column output, multi-million rows. My main curiosity revolved
around the 'multiple tables per query'. I had pretty much put that out of my
mind for the last few years, because it never happens in this environment.
One of our team asked about it this morning during a debugging session, and
I remarked that I never see it. As for whittling the result sets down,
there's just no way.
Oh, I should mention that there's also no possibility of building covering
indices here, because of disk space constraints, as well as the constant
influx of new data, and the need for high UPDATE performance.
Hence, we got curious as to when it actually _would_ happen, and by the
sounds of it, pretty much never. :-)
Thanks,
James Hokes.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Sounds like you have a complicated multi-table join. After the first
couple
> joins all you have are work tables (intermediate data results) which have
no
> indexes. In this case it may be necessary to do hash joins due to the
lack
> of indexes. The key is to whittle down the result sets to a manageable
set
> in the first joins using the appropriate indexes so the joining of the
> intermediate tables are relatively painless. Maybe if you post a query
> example and the associated query plan we can cnfirm or deny that theory.
> --
> Andrew J. Kelly SQL MVP
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||However, it is not true that you won't have indexes to use even when you
have a multi-table join. If your join density (the average number of
duplicates on your join column) is not too high, and you have an index on
the join column, I have seen cases where an index is used for every table in
a 5 or 6 table join.
Have you tried index tuning wizard?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
> clause, multi-column output, multi-million rows. My main curiosity
revolved
> around the 'multiple tables per query'. I had pretty much put that out of
my
> mind for the last few years, because it never happens in this environment.
> One of our team asked about it this morning during a debugging session,
and
> I remarked that I never see it. As for whittling the result sets down,
> there's just no way.
> Oh, I should mention that there's also no possibility of building covering
> indices here, because of disk space constraints, as well as the constant
> influx of new data, and the need for high UPDATE performance.
> Hence, we got curious as to when it actually _would_ happen, and by the
> sounds of it, pretty much never. :-)
> Thanks,
> James Hokes.
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Sounds like you have a complicated multi-table join. After the first
> couple
> > joins all you have are work tables (intermediate data results) which
have
> no
> > indexes. In this case it may be necessary to do hash joins due to the
> lack
> > of indexes. The key is to whittle down the result sets to a manageable
> set
> > in the first joins using the appropriate indexes so the joining of the
> > intermediate tables are relatively painless. Maybe if you post a query
> > example and the associated query plan we can cnfirm or deny that theory.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Right.
I never said it wasn't using indexes; all of our queries do. I've tuned them
all.
I was just wondering about the 'multiple indexes per table' in the optimizer
step,
since we never see it happen. My original post referenced the fact that we
_are_ seeing the one index used per table,
but never multiple.
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, didn't mean to make it sound like it couldn't use indexes. Just
that it can and does happen<g>.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||No harm done!
I just didn't want people to spend any of their newsgroup time on that part
of the thread.
I know I only get to go on here a little bit, so I know your time and the
time of others is also valuable.
Thanks for yours!
James Hokes
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uev3vyRxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> Sorry, didn't mean to make it sound like it couldn't use indexes. Just
> that it can and does happen<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> > However, it is not true that you won't have indexes to use even when you
> > have a multi-table join. If your join density (the average number of
> > duplicates on your join column) is not too high, and you have an index
on
> > the join column, I have seen cases where an index is used for every
table
> in
> > a 5 or 6 table join.
> >
> > Have you tried index tuning wizard?
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Andrew,
> > >
> > > Right on. We're talking multi-table, multi-column JOIN, multi-column
> WHERE
> > > clause, multi-column output, multi-million rows. My main curiosity
> > revolved
> > > around the 'multiple tables per query'. I had pretty much put that out
> of
> > my
> > > mind for the last few years, because it never happens in this
> environment.
> > > One of our team asked about it this morning during a debugging
session,
> > and
> > > I remarked that I never see it. As for whittling the result sets down,
> > > there's just no way.
> > >
> > > Oh, I should mention that there's also no possibility of building
> covering
> > > indices here, because of disk space constraints, as well as the
constant
> > > influx of new data, and the need for high UPDATE performance.
> > >
> > > Hence, we got curious as to when it actually _would_ happen, and by
the
> > > sounds of it, pretty much never. :-)
> > >
> > > Thanks,
> > > James Hokes.
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Sounds like you have a complicated multi-table join. After the
first
> > > couple
> > > > joins all you have are work tables (intermediate data results) which
> > have
> > > no
> > > > indexes. In this case it may be necessary to do hash joins due to
the
> > > lack
> > > > of indexes. The key is to whittle down the result sets to a
> manageable
> > > set
> > > > in the first joins using the appropriate indexes so the joining of
the
> > > > intermediate tables are relatively painless. Maybe if you post a
> query
> >
> > > > example and the associated query plan we can cnfirm or deny that
> theory.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > > Hi all,
> > > > >
> > > > > We were all told that SQL 7.0 and up could use more than one index
> per
> > > > table
> > > > > during query optimization. In practice, how often does this
actually
> > > > occur?
> > > > >
> > > > > If and when it does occur, how does this appear in the output of
the
> > > 'Show
> > > > > Estimated Execution Plan?". It seems to us that this almost never
> > > occurs.
> > > > > We're seeing the first index, and then after that all HASH joins
and
> > > such.
> > > > >
> > > > > Thanks!
> > > > > James Hokes
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
We were all told that SQL 7.0 and up could use more than one index per table
during query optimization. In practice, how often does this actually occur?
If and when it does occur, how does this appear in the output of the 'Show
Estimated Execution Plan?". It seems to us that this almost never occurs.
We're seeing the first index, and then after that all HASH joins and such.
Thanks!
James HokesHi James
It happens when you have two where clauses referencing two different columns
with nonclustered indexes, and for each, there are only a few rows that
satisfy the condition.
In the showplan output, it looks like a join, but both tables are the same
table, and it's usually a hash join. SQL Server has build an internal
worktable from the results of using each of the indexes, and then those two
worktables have to be joined. SInce there are no good indexes on the
worktables, a hash join is used to find the rows in common between the
worktables.
Here is an example
USE Northwind
select * into od from [order details]
create index qnt_index on od(quantity)
create index price_index on od(unitprice)
go
select * from od
where quantity = 4 and unitprice = 6
Look at the plan for the select, after building the table and the two
indexes.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Sounds like you have a complicated multi-table join. After the first couple
joins all you have are work tables (intermediate data results) which have no
indexes. In this case it may be necessary to do hash joins due to the lack
of indexes. The key is to whittle down the result sets to a manageable set
in the first joins using the appropriate indexes so the joining of the
intermediate tables are relatively painless. Maybe if you post a query
example and the associated query plan we can cnfirm or deny that theory.
--
Andrew J. Kelly SQL MVP
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> We were all told that SQL 7.0 and up could use more than one index per
table
> during query optimization. In practice, how often does this actually
occur?
> If and when it does occur, how does this appear in the output of the 'Show
> Estimated Execution Plan?". It seems to us that this almost never occurs.
> We're seeing the first index, and then after that all HASH joins and such.
> Thanks!
> James Hokes
>|||Kalen,
Interesting that it would only happen for 'a few rows'.
That being the case, I suspect that this is due to 'total page reads', i.e.
query cost.
Is that a safe assumption?
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> Hi James
> It happens when you have two where clauses referencing two different
columns
> with nonclustered indexes, and for each, there are only a few rows that
> satisfy the condition.
> In the showplan output, it looks like a join, but both tables are the same
> table, and it's usually a hash join. SQL Server has build an internal
> worktable from the results of using each of the indexes, and then those
two
> worktables have to be joined. SInce there are no good indexes on the
> worktables, a hash join is used to find the rows in common between the
> worktables.
> Here is an example
> USE Northwind
> select * into od from [order details]
> create index qnt_index on od(quantity)
> create index price_index on od(unitprice)
> go
> select * from od
> where quantity = 4 and unitprice = 6
> Look at the plan for the select, after building the table and the two
> indexes.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||This is a general principle for any use of nonclustered indexes. They will
only be cost effective if there are only a few rows that meet the condition,
so only a few pages need to be read. If you need to use a nc index to access
100's of rows, which means 100s of pages, it is often considered by the
optimizer to be more cost effective to just scan the table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:eoTyWLMxDHA.3208@.tk2msftngp13.phx.gbl...
> Kalen,
> Interesting that it would only happen for 'a few rows'.
> That being the case, I suspect that this is due to 'total page reads',
i.e.
> query cost.
> Is that a safe assumption?
> Thanks,
> James Hokes
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e2zkDXLxDHA.1932@.TK2MSFTNGP09.phx.gbl...
> > Hi James
> >
> > It happens when you have two where clauses referencing two different
> columns
> > with nonclustered indexes, and for each, there are only a few rows that
> > satisfy the condition.
> >
> > In the showplan output, it looks like a join, but both tables are the
same
> > table, and it's usually a hash join. SQL Server has build an internal
> > worktable from the results of using each of the indexes, and then those
> two
> > worktables have to be joined. SInce there are no good indexes on the
> > worktables, a hash join is used to find the rows in common between the
> > worktables.
> >
> > Here is an example
> >
> > USE Northwind
> >
> > select * into od from [order details]
> > create index qnt_index on od(quantity)
> > create index price_index on od(unitprice)
> > go
> > select * from od
> > where quantity = 4 and unitprice = 6
> >
> > Look at the plan for the select, after building the table and the two
> > indexes.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:#bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Andrew,
Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
clause, multi-column output, multi-million rows. My main curiosity revolved
around the 'multiple tables per query'. I had pretty much put that out of my
mind for the last few years, because it never happens in this environment.
One of our team asked about it this morning during a debugging session, and
I remarked that I never see it. As for whittling the result sets down,
there's just no way.
Oh, I should mention that there's also no possibility of building covering
indices here, because of disk space constraints, as well as the constant
influx of new data, and the need for high UPDATE performance.
Hence, we got curious as to when it actually _would_ happen, and by the
sounds of it, pretty much never. :-)
Thanks,
James Hokes.
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Sounds like you have a complicated multi-table join. After the first
couple
> joins all you have are work tables (intermediate data results) which have
no
> indexes. In this case it may be necessary to do hash joins due to the
lack
> of indexes. The key is to whittle down the result sets to a manageable
set
> in the first joins using the appropriate indexes so the joining of the
> intermediate tables are relatively painless. Maybe if you post a query
> example and the associated query plan we can cnfirm or deny that theory.
> --
> Andrew J. Kelly SQL MVP
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Hi all,
> >
> > We were all told that SQL 7.0 and up could use more than one index per
> table
> > during query optimization. In practice, how often does this actually
> occur?
> >
> > If and when it does occur, how does this appear in the output of the
'Show
> > Estimated Execution Plan?". It seems to us that this almost never
occurs.
> > We're seeing the first index, and then after that all HASH joins and
such.
> >
> > Thanks!
> > James Hokes
> >
> >
>|||However, it is not true that you won't have indexes to use even when you
have a multi-table join. If your join density (the average number of
duplicates on your join column) is not too high, and you have an index on
the join column, I have seen cases where an index is used for every table in
a 5 or 6 table join.
Have you tried index tuning wizard?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"James Hokes" <no_spam@.thank_you.com> wrote in message
news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> Right on. We're talking multi-table, multi-column JOIN, multi-column WHERE
> clause, multi-column output, multi-million rows. My main curiosity
revolved
> around the 'multiple tables per query'. I had pretty much put that out of
my
> mind for the last few years, because it never happens in this environment.
> One of our team asked about it this morning during a debugging session,
and
> I remarked that I never see it. As for whittling the result sets down,
> there's just no way.
> Oh, I should mention that there's also no possibility of building covering
> indices here, because of disk space constraints, as well as the constant
> influx of new data, and the need for high UPDATE performance.
> Hence, we got curious as to when it actually _would_ happen, and by the
> sounds of it, pretty much never. :-)
> Thanks,
> James Hokes.
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Sounds like you have a complicated multi-table join. After the first
> couple
> > joins all you have are work tables (intermediate data results) which
have
> no
> > indexes. In this case it may be necessary to do hash joins due to the
> lack
> > of indexes. The key is to whittle down the result sets to a manageable
> set
> > in the first joins using the appropriate indexes so the joining of the
> > intermediate tables are relatively painless. Maybe if you post a query
> > example and the associated query plan we can cnfirm or deny that theory.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Hi all,
> > >
> > > We were all told that SQL 7.0 and up could use more than one index per
> > table
> > > during query optimization. In practice, how often does this actually
> > occur?
> > >
> > > If and when it does occur, how does this appear in the output of the
> 'Show
> > > Estimated Execution Plan?". It seems to us that this almost never
> occurs.
> > > We're seeing the first index, and then after that all HASH joins and
> such.
> > >
> > > Thanks!
> > > James Hokes
> > >
> > >
> >
> >
>|||Right.
I never said it wasn't using indexes; all of our queries do. I've tuned them
all.
I was just wondering about the 'multiple indexes per table' in the optimizer
step,
since we never see it happen. My original post referenced the fact that we
_are_ seeing the one index used per table,
but never multiple.
Thanks,
James Hokes
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, didn't mean to make it sound like it couldn't use indexes. Just
that it can and does happen<g>.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> However, it is not true that you won't have indexes to use even when you
> have a multi-table join. If your join density (the average number of
> duplicates on your join column) is not too high, and you have an index on
> the join column, I have seen cases where an index is used for every table
in
> a 5 or 6 table join.
> Have you tried index tuning wizard?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "James Hokes" <no_spam@.thank_you.com> wrote in message
> news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > Andrew,
> >
> > Right on. We're talking multi-table, multi-column JOIN, multi-column
WHERE
> > clause, multi-column output, multi-million rows. My main curiosity
> revolved
> > around the 'multiple tables per query'. I had pretty much put that out
of
> my
> > mind for the last few years, because it never happens in this
environment.
> > One of our team asked about it this morning during a debugging session,
> and
> > I remarked that I never see it. As for whittling the result sets down,
> > there's just no way.
> >
> > Oh, I should mention that there's also no possibility of building
covering
> > indices here, because of disk space constraints, as well as the constant
> > influx of new data, and the need for high UPDATE performance.
> >
> > Hence, we got curious as to when it actually _would_ happen, and by the
> > sounds of it, pretty much never. :-)
> >
> > Thanks,
> > James Hokes.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Sounds like you have a complicated multi-table join. After the first
> > couple
> > > joins all you have are work tables (intermediate data results) which
> have
> > no
> > > indexes. In this case it may be necessary to do hash joins due to the
> > lack
> > > of indexes. The key is to whittle down the result sets to a
manageable
> > set
> > > in the first joins using the appropriate indexes so the joining of the
> > > intermediate tables are relatively painless. Maybe if you post a
query
> > > example and the associated query plan we can cnfirm or deny that
theory.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > Hi all,
> > > >
> > > > We were all told that SQL 7.0 and up could use more than one index
per
> > > table
> > > > during query optimization. In practice, how often does this actually
> > > occur?
> > > >
> > > > If and when it does occur, how does this appear in the output of the
> > 'Show
> > > > Estimated Execution Plan?". It seems to us that this almost never
> > occurs.
> > > > We're seeing the first index, and then after that all HASH joins and
> > such.
> > > >
> > > > Thanks!
> > > > James Hokes
> > > >
> > > >
> > >
> > >
> >
> >
>|||No harm done!
I just didn't want people to spend any of their newsgroup time on that part
of the thread.
I know I only get to go on here a little bit, so I know your time and the
time of others is also valuable.
Thanks for yours!
James Hokes
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:uev3vyRxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> Sorry, didn't mean to make it sound like it couldn't use indexes. Just
> that it can and does happen<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:uwIYkmMxDHA.1912@.TK2MSFTNGP09.phx.gbl...
> > However, it is not true that you won't have indexes to use even when you
> > have a multi-table join. If your join density (the average number of
> > duplicates on your join column) is not too high, and you have an index
on
> > the join column, I have seen cases where an index is used for every
table
> in
> > a 5 or 6 table join.
> >
> > Have you tried index tuning wizard?
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > news:e6sE9NMxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > Andrew,
> > >
> > > Right on. We're talking multi-table, multi-column JOIN, multi-column
> WHERE
> > > clause, multi-column output, multi-million rows. My main curiosity
> > revolved
> > > around the 'multiple tables per query'. I had pretty much put that out
> of
> > my
> > > mind for the last few years, because it never happens in this
> environment.
> > > One of our team asked about it this morning during a debugging
session,
> > and
> > > I remarked that I never see it. As for whittling the result sets down,
> > > there's just no way.
> > >
> > > Oh, I should mention that there's also no possibility of building
> covering
> > > indices here, because of disk space constraints, as well as the
constant
> > > influx of new data, and the need for high UPDATE performance.
> > >
> > > Hence, we got curious as to when it actually _would_ happen, and by
the
> > > sounds of it, pretty much never. :-)
> > >
> > > Thanks,
> > > James Hokes.
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> > > news:eiAOiZLxDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Sounds like you have a complicated multi-table join. After the
first
> > > couple
> > > > joins all you have are work tables (intermediate data results) which
> > have
> > > no
> > > > indexes. In this case it may be necessary to do hash joins due to
the
> > > lack
> > > > of indexes. The key is to whittle down the result sets to a
> manageable
> > > set
> > > > in the first joins using the appropriate indexes so the joining of
the
> > > > intermediate tables are relatively painless. Maybe if you post a
> query
> >
> > > > example and the associated query plan we can cnfirm or deny that
> theory.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "James Hokes" <no_spam@.thank_you.com> wrote in message
> > > > news:%23bXed3KxDHA.1740@.TK2MSFTNGP12.phx.gbl...
> > > > > Hi all,
> > > > >
> > > > > We were all told that SQL 7.0 and up could use more than one index
> per
> > > > table
> > > > > during query optimization. In practice, how often does this
actually
> > > > occur?
> > > > >
> > > > > If and when it does occur, how does this appear in the output of
the
> > > 'Show
> > > > > Estimated Execution Plan?". It seems to us that this almost never
> > > occurs.
> > > > > We're seeing the first index, and then after that all HASH joins
and
> > > such.
> > > > >
> > > > > Thanks!
> > > > > James Hokes
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
(ODBC driver) timeout expired when query "DELETE FROM mytable"
Hi,
I'm running SQL server 2000. I have at table with about 12 million records.
I want to empty the table. I use the Query option from Tables/mytable/opentable/query.
I type in the query and select Run. The process runs for some time, then raises
an error box as follows: (title)SQL Server Enterprise Manager. The body text says:
[Microsoft][ODBC SQL Server Driver] Timeout Expired. I've tried every timeout
setting I can find. I've tried setting all timeouts to 0 (infinite) to no avail.
Please help.........
Note: I can get the table empty if I select TOP n records, then DELETE FROM but
that takes forever!! It is also not a process that's very amenable to a clean programatic
solution.
Thanks, jack
That's very strange. Setting timeout to '0' should do the trick.
If all you need to do is empty the table you could just call "truncate table table_name". That should run pretty quickly.
Sorry you're having issues. Please reactivate this thread if the 'truncate' command doesn't fix your issue.
~Warren
|||Warren,
Thanks!
I have changed the query to "Truncate Table" and it's much faster. I haven't
tried it with the large table yet -- I'll have to reload the data before I know for
sure. I used the "Delete" command only because I hadn't stumbled on the "Truncate"
command. I still would like to know why I'm getting the timeout since I'm sure it will
jump up and bite me later because I'm dealing with such large tables, and Murphy is
looking over my shoulder -- ha!
jack
|||Consider that when you execute DELETE, the operation is logged so you are essentially "moving" the deleted rows to the transaction log with all of the associated disk IO expenses. Truncate table is also logged, but simply tells SQL Server to "drop" the data and leave the schema--it's dramatically simpler and faster.|||William,
"Dramatically" is an understatement! I'm amazed at how quickly the table is
emptied using "Truncate". Although knowing why, it makes perfect sense. Thanks
for the expanation of the 'why', that's even more valuable than the 'how'. Is there an
explicit means of preventing the transaction logging - since it's so time costly? Is
there a downside to such a thing if it exists?
I'm still bewildered about the "timeout expired" error inspired by the length of time
the DELETE takes. I guess I'll have to pull my copy of the "Guide to..." off the shelf
and review ADO/ODBC query timeouts etc.
thanks, jack
|||No, you can't (and should not) "turn off" the transaction log--it's your safety net. Yes, there are other operations that can be executed without the log getting in the way (like BulkCopy).
Consider that the Delete command must also delete the Index(es) for each row as well as reallocate space and execute other operations that take CPU time, RAM and disk IO. While the Truncate is fast, it also means that the server can clean up the freed space when it has idle time and it needs the space. For long operations you can set the CommandTimeout to a higher number, but whenever I find a neeed to do this I look for a more efficient way to handle the task...
hth
|||William,
Thanks! I'm in good shape now.
jack
(ObjKommun IN (xxxx%, oooo%))
My question, is it valid to use "IN" as in "LIKE" conserning strings? (Using the "%" operator)
Thx in advance!Hello,
you cant you % in this way (for Oracle). Use SUBSTR(var, start, length)
Hope this helps ?
Greetz
Manfred Peter
(Alligator Company)
http://www.alligatorsql.de|||This is just a short copy of a SP im using in SQL server... now the problem appears if there will be to many parameters involved.. (HUGE query)
My main problem is how to reduce the length of the query..
So, instead of doing it like this..:
AND (((xxxx like 'yyyy%') OR (xxxx like 'zzzz%')))
I was thinking of doing like this..:
xxxx IN ('yyyy%', 'zzzz%')
But when im using the "IN" im not able to use the % (Cause sometimes strings are weird) and no result is shown, but when removing the % i get the satisfying result, not depending on Case sensitivity which is perfect, but still...
And im really really lousy to explain things, hope its ok anyways ;)|||No, IN cannot be used as a substitute for 'like', it will not work. You could try looking at applying a string function on the column side, e.g. WHERE substring( mycolumn, 0, 4) IN ( 'stri', '1234', 'etc.' )|||Okay, thx for the response.
Think I will use "IN" but forget about trying to make it work like "LIKE" :)
Most important is that the length of the query is shortened.
Best wishes from Sweden.|||Thats it :)
Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
Thx in advance!Hello,
you cant you % in this way (for Oracle). Use SUBSTR(var, start, length)
Hope this helps ?
Greetz
Manfred Peter
(Alligator Company)
http://www.alligatorsql.de|||This is just a short copy of a SP im using in SQL server... now the problem appears if there will be to many parameters involved.. (HUGE query)
My main problem is how to reduce the length of the query..
So, instead of doing it like this..:
AND (((xxxx like 'yyyy%') OR (xxxx like 'zzzz%')))
I was thinking of doing like this..:
xxxx IN ('yyyy%', 'zzzz%')
But when im using the "IN" im not able to use the % (Cause sometimes strings are weird) and no result is shown, but when removing the % i get the satisfying result, not depending on Case sensitivity which is perfect, but still...
And im really really lousy to explain things, hope its ok anyways ;)|||No, IN cannot be used as a substitute for 'like', it will not work. You could try looking at applying a string function on the column side, e.g. WHERE substring( mycolumn, 0, 4) IN ( 'stri', '1234', 'etc.' )|||Okay, thx for the response.
Think I will use "IN" but forget about trying to make it work like "LIKE" :)
Most important is that the length of the query is shortened.
Best wishes from Sweden.|||Thats it :)
Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
(Object required) DTSPackageLog
I get "(Object Required)" when trying to
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?I too have been trying to make this work. I found a script on SQLDTS.com that I've cut and pasted and whenever I try to run it, I am faced with the Object Required: 'DTSPackageLog' error.
http://www.sqldts.com/default.aspx?6,101,238,0,1
Any suggestions would be helpful!
Thanks,
Bob
Originally posted by Hollerith
I get "(Object Required)" when trying to
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?I too have been trying to make this work. I found a script on SQLDTS.com that I've cut and pasted and whenever I try to run it, I am faced with the Object Required: 'DTSPackageLog' error.
http://www.sqldts.com/default.aspx?6,101,238,0,1
Any suggestions would be helpful!
Thanks,
Bob
Originally posted by Hollerith
I get "(Object Required)" when trying to
"DTSPackageLog.WriteStringToLog sErrorMessage" in transformation vbscripts being used by DTS.
All available documentation indicates that this should not be possible as it is 'intrinsic' like "DTSGLobalVariables".
What's the secret?
(nothing = 0) = true !
I have a bit type field that can have values 0, 1 and NULL in SQL database.
I want to hide an image when the value is different from 0.
The expression used in the visibility.Hidden property is
"=First(Fields!myfield.Value, "DataSet1") <>0"
And when the database value is NULL this expression returns false.
Please help !
(a French user...)I really canâ't understand why something like that does not work. I always
have to use something like the following:
=iif(Expression, 1=1, 1=0)
I hope it helps you!
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||thanks for you answer but it doen't help me...
I made this test : expression "= nothing = 0" in a new field, it's return
true !
"Soan" wrote:
> I really canâ't understand why something like that does not work. I always
> have to use something like the following:
> =iif(Expression, 1=1, 1=0)
> I hope it helps you!
>
> > I have a bit type field that can have values 0, 1 and NULL in SQL database.
> > I want to hide an image when the value is different from 0.
> > The expression used in the visibility.Hidden property is
> > "=First(Fields!myfield.Value, "DataSet1") <>0"
> > And when the database value is NULL this expression returns false.
> >
> > Please help !
> >
> > (a French user...)
> >|||I haven't tried it myself, but you may want to try the following:
"=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
(First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
Hope this helps.
Andy
tonio932 wrote:
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||When comparing with NULLs you have to use "is Nothing".
E.g. =iif(Fields!myField.Value is Nothing, True-Part, False-Part)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tonio932" <tonio932@.discussions.microsoft.com> wrote in message
news:C4E4FDE8-81C8-41E4-AE37-09671864812A@.microsoft.com...
>I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||I have noticed that when a column value from the database is null, doing
myfield.Value is the same as doing null.Value, which makes RS blow.
Andy Hardin wrote:
> I haven't tried it myself, but you may want to try the following:
> "=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
> (First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
> Hope this helps.
> Andy
>
> tonio932 wrote:
>> I have a bit type field that can have values 0, 1 and NULL in SQL
>> database.
>> I want to hide an image when the value is different from 0.
>> The expression used in the visibility.Hidden property is
>> "=First(Fields!myfield.Value, "DataSet1") <>0"
>> And when the database value is NULL this expression returns false.
>> Please help !
>> (a French user...)sql
I want to hide an image when the value is different from 0.
The expression used in the visibility.Hidden property is
"=First(Fields!myfield.Value, "DataSet1") <>0"
And when the database value is NULL this expression returns false.
Please help !
(a French user...)I really canâ't understand why something like that does not work. I always
have to use something like the following:
=iif(Expression, 1=1, 1=0)
I hope it helps you!
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||thanks for you answer but it doen't help me...
I made this test : expression "= nothing = 0" in a new field, it's return
true !
"Soan" wrote:
> I really canâ't understand why something like that does not work. I always
> have to use something like the following:
> =iif(Expression, 1=1, 1=0)
> I hope it helps you!
>
> > I have a bit type field that can have values 0, 1 and NULL in SQL database.
> > I want to hide an image when the value is different from 0.
> > The expression used in the visibility.Hidden property is
> > "=First(Fields!myfield.Value, "DataSet1") <>0"
> > And when the database value is NULL this expression returns false.
> >
> > Please help !
> >
> > (a French user...)
> >|||I haven't tried it myself, but you may want to try the following:
"=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
(First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
Hope this helps.
Andy
tonio932 wrote:
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||When comparing with NULLs you have to use "is Nothing".
E.g. =iif(Fields!myField.Value is Nothing, True-Part, False-Part)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tonio932" <tonio932@.discussions.microsoft.com> wrote in message
news:C4E4FDE8-81C8-41E4-AE37-09671864812A@.microsoft.com...
>I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||I have noticed that when a column value from the database is null, doing
myfield.Value is the same as doing null.Value, which makes RS blow.
Andy Hardin wrote:
> I haven't tried it myself, but you may want to try the following:
> "=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
> (First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
> Hope this helps.
> Andy
>
> tonio932 wrote:
>> I have a bit type field that can have values 0, 1 and NULL in SQL
>> database.
>> I want to hide an image when the value is different from 0.
>> The expression used in the visibility.Hidden property is
>> "=First(Fields!myfield.Value, "DataSet1") <>0"
>> And when the database value is NULL this expression returns false.
>> Please help !
>> (a French user...)sql
(NOLOCK) and Linked Servers
I know that you can't have an optimizer hint when running an ad-hoc query
from another server. My question is why? What prevents it from using the
hint?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C0F66FA391C@.SQLMonster.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using the
>> hint?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
--
Andrew J. Kelly SQL MVP
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1
from another server. My question is why? What prevents it from using the
hint?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1PButler,
Use the OPENQUERY function instead of the 4 part name.
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C0F66FA391C@.SQLMonster.com...
>I know that you can't have an optimizer hint when running an ad-hoc query
> from another server. My question is why? What prevents it from using the
> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||Thank you Jerry,
I guess I'm looking for a deeper explination of why? What prevents it from
using the hints in this way?
Also, Are there any performance issues with using OPENQUERY? Is it
faster/slower in general?
Jerry Spivey wrote:
>PButler,
>Use the OPENQUERY function instead of the 4 part name.
>HTH
>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using the
>> hint?
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||If I had to guess I would guess that it is because of where the processing
occurs with respect to the 4 part name vs. OPENQUERY. You'll probably find
OPENQUERY slightly to much faster depending on the number of rows in the
destination table(s).
HTH
Jerry
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1|||I am not a linked server expert but I suspect it has to do with the fact it
simply may not be supported on the other end. A linked server can be to
many things not just sql server. In addition to that there are distributed
transaction issues that may come into play. If you want all the features
supported you should create a stored proc on the lined server and call that.
Then all the processing is done on the other server just as if it was issued
locally and only the results are sent back.
--
Andrew J. Kelly SQL MVP
"PButler via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54C468F4CB638@.SQLMonster.com...
> Thank you Jerry,
> I guess I'm looking for a deeper explination of why? What prevents it
> from
> using the hints in this way?
> Also, Are there any performance issues with using OPENQUERY? Is it
> faster/slower in general?
> Jerry Spivey wrote:
>>PButler,
>>Use the OPENQUERY function instead of the 4 part name.
>>HTH
>>Jerry
>>I know that you can't have an optimizer hint when running an ad-hoc query
>> from another server. My question is why? What prevents it from using
>> the
>> hint?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1
Subscribe to:
Posts (Atom)