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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Sunday, March 25, 2012
(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
(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
(MDX) EXISTING operator - inconsistent behaviour when using subcubes?
I am getting what I think is inconsistent behaviour when using the EXISTING operator to obtain a set defined by the current query context... See the two queries below (explanation follows).
WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM [Adventure Works]
WHERE [Date].[Date].[October 26, 2002]
... returns [26-10-2002]
WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM
(
SELECT [Date].[Date].[October 26, 2002] ON 0
FROM [Adventure Works]
)
... return [31-08-2004] (the last date in the Adventure Works time dimension).
From my point of view the two queries should return the same date (October 26, 2002), since the context is the same. The only difference is that in the first query the context is specified in the WHERE clause, whereas the second query specifies the context in a subcube. There really should not be any difference, should there?
Chris, I was looking for an answer from you - not quite the answer I hoped for though. 
I see your point, but if what you are saying is true, the value of the EXISTING operator is not very high and it will be very questionable if Moshas approach to writing "multiselect friendly MDX calculations" (http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx) is applicable (which I really hope it is!). Does it not also fail to explain why the following query only returns the sum of [Internet Sales Amount] from 2002 and 2003 (which - to me - is expected behavior)?
WITH MEMBER [Measures].[Test 2] AS
SUM(EXISTING [Date].[Date], [Measures].[Internet Sales Amount])
SELECT [Measures].[Test 2] ON 0
FROM
(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])
If what you are saying is true, wouldn't the calculated member return a sum for all years - since the calculated member would "look outside" the subsube, which is restricted to 2002 and 2003?
|||I think you've made a mistake in your query - the expression [Date].[Date] resolves to the All Member of that hierarchy. I think what you wanted to do was this:
WITH MEMBER [Measures].[Test 2] AS
SUM(EXISTING [Date].[Date].[Date].Members, [Measures].[Internet Sales Amount])
SELECT [Measures].[Test 2] ON 0
FROM
(SELECT {[Date].[Calendar Year].&[2002], [Date].[Calendar Year].&[2003]} ON 0 FROM [Adventure Works])
Which returns a different (and higher) value, the sum of all years.
You're right, though, it does cause problems if you're using subcubes instead of sets in the where clause and following Mosha's advice on multi-select friendly MDX.
Chris
|||You're right Chris - thanks... I will try go get Mosha to comment on this issue... I will follow up in this thread.|||I know this was an area where there was some discussion about what the most useful behaviour would be - on balance, I think they've done the right thing. As far as the multi-select issue goes, is there any reason why you're not using sets in the WHERE clause instead?
<Cheeky Plug>
This topic is discussed in the second edition of 'MDX Solutions' in one of George's chapters:
http://www.amazon.co.uk/exec/obidos/redirect?link_code=ur2&tag=chriswebbsbib-21&camp=1634&creative=6738&path=ASIN%2F0471748080%2Fqid%3D1141661836%2Fsr%3D8-3%2Fref%3Dsr_8_xs_ap_i3_xgl
</Cheeky Plug>
Chris
|||Just placed an order for this book yesterday, so I am looking forward to reading the good arguments for the current behavior.
Why am I not using sets in the where clause for multi-select? There are more than one reason actually.
1) If a hierarchy is placed in the WHERE clause, the same hierarchy cannot - as you know - be placed on an axis (which, in many cases, in desireable).
2) The use of the WHERE clause vs. the use of subcubes for restricting the query result varies from client tool to client tool. The cube browser in SSMS, for example, uses both subcubes and sets in the WHERE clause depending on how you setup the query.
One could argue - perhaps - that the client tool should be smart enough to always place any hierarchy not already on an axis in the WHERE clause?! I wonder if this would always solve any problem? Hmm...
EXISTING operator takes into account current coordinate. The whole difference between WHERE clause and subselect, is that WHERE clause sets current coordinate, while subselects merely do top level Exists with Axis (BTW WHERE clause also does it), and apply visual totals.
The confusion between WHERE and subselects is big and seem to grow every day :( It is unfortunate that not everybody realizes the semantic difference between the two and when each should be used. Perhaps we need another operator in MDX which will take into account the current restriction of subselect and/or CREATE subcube. Excel 12 is only going to add to the confusion, since it uses subselects extensively and it uses them for multiselect too, rendering my advice on how to write multiselect friendly calculations less useful...
Mosha
|||I blogged on this topic.
http://sqljunkies.com/WebLog/reckless/archive/2006/03/08/18601.aspx
Comments are welcome...
(long) querry doesn't fit in "query-string" window
hi,
DIM x AS New System.Text.StringBuilder()
x.Append("SELECT ") ' etc...
RETURN x.ToString()
End Function
i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
Any solutions?
put it in a stored procedure 
i tried already to edit the rdl file directly. (i paste my whole query in the xml structure), but then when i ask a report preview via BIDS, the BIDS crashes !!!!!!!!!!!!!!!!
i have no other choice then do the workaround via the stored procedure,
but it is really a bug in microsoft i say, isn't it?
|||
You have another alternative...
Create your query string in embedded code, like this:
Function GetSQLDIM x AS New System.Text.StringBuilder()
x.Append("SELECT ") ' etc...
RETURN x.ToString()
End Function
... Now your query string looks like this
= Code.GetSQL()
HTH, and yes it works <g>, and when you're trying to create a dynamic query out of a lot of parameters it's a heck of a lot more maintainable/legible, too,
>L<
(local) Alias does not function in an virtual sql server?
When I try to connect to (local) server (in Query Analyzer for example) on an
virtual sql server, the application tries to connect to the active node
instead, resulting in an error message (SQL Server does not exist or access
denied).
Is it possible to configure this alias to connect to the virtual sql server?
Furthermore I recognized, that DTS-Packages with (local)-Data Sources still
work on the virtual server. On a second, similar installed server, this
results in the same error message.
Any thoughts?
Martin Saalmann
There is an Environment Variable you can use in order to run the SQLDIAG
utility on the active node. I'm not sure if it uses the (local) or the "."
designation, but it would be worth a shot.
Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK NAME, and then press ENTER.
NOTE: The SQL NETWORK NAME is the SQL Server virtual name for a named
instance. This is only the first part of the name. For example, if the
instance name is VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
INF: How To Run SQLDIAG On a Clustered/Virtual SQL Server
http://support.microsoft.com/kb/233332/EN-US/
Sincerely,
Anthony Thomas
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
|||Martin,
you could create an Alias on each Node in the Client Network Utility with
name (local) and then the real name to point at the SQL Virtual Name
Andy.
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
> an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
> access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
> server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
> still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
virtual sql server, the application tries to connect to the active node
instead, resulting in an error message (SQL Server does not exist or access
denied).
Is it possible to configure this alias to connect to the virtual sql server?
Furthermore I recognized, that DTS-Packages with (local)-Data Sources still
work on the virtual server. On a second, similar installed server, this
results in the same error message.
Any thoughts?
Martin Saalmann
There is an Environment Variable you can use in order to run the SQLDIAG
utility on the active node. I'm not sure if it uses the (local) or the "."
designation, but it would be worth a shot.
Type set _CLUSTER_NETWORK_NAME_=SQL NETWORK NAME, and then press ENTER.
NOTE: The SQL NETWORK NAME is the SQL Server virtual name for a named
instance. This is only the first part of the name. For example, if the
instance name is VSQL2\INST2, the SQL NETWORK NAME is VSQL2.
INF: How To Run SQLDIAG On a Clustered/Virtual SQL Server
http://support.microsoft.com/kb/233332/EN-US/
Sincerely,
Anthony Thomas
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
|||Martin,
you could create an Alias on each Node in the Client Network Utility with
name (local) and then the real name to point at the SQL Virtual Name
Andy.
"Martin Saalmann" <MartinSaalmann@.discussions.microsoft.com> wrote in
message news:8CD4BBE4-7548-4BD5-BDF1-7B437D2A102E@.microsoft.com...
> When I try to connect to (local) server (in Query Analyzer for example) on
> an
> virtual sql server, the application tries to connect to the active node
> instead, resulting in an error message (SQL Server does not exist or
> access
> denied).
> Is it possible to configure this alias to connect to the virtual sql
> server?
> Furthermore I recognized, that DTS-Packages with (local)-Data Sources
> still
> work on the virtual server. On a second, similar installed server, this
> results in the same error message.
> Any thoughts?
> Martin Saalmann
>
(Harder?) cube query & design question
Hi,
I have an MDX query (and worst case a cube design) problem that I haven't been able to solve, any ideas on how to go about this? Here's a simplified description, starting with the
Dimension & Attributes
* We make phone [Call]s.
* In each [Call], and for a number of [Product]s we ask a number of [Question]s.
* Each [Question] results in an [AnswerText]. These also reside in a user hierarchy [Answer Dimension].[Q and A].
* All of the above are attributes in the [Answer Dimension].
Facts
* Measures.[Answer Count], which at the granular level is always one, i.e. each fact records that we recieved a single [AnswerText] to a single [Question] about a single [Product] in a single [Call].
* Measures.[Call Count], which is the number of [Call]s made.
NB: We have thousands of different questions and answers, so surfacing each individual question and answer as a measure is not an option.
Queries & issues
* Counting the answers to a single particular question is easy:
SELECT
Measures.[Answer Count] ON 0
FROM cube
WHERE ([Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue])
* What I can't figure out is how to get Measures.[Answer Count] for multiple simultaneous questions, i.e.:
"For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]"
I've tried (unsuccessfully) the following:
1) Various ways that equate to doing an intersection between the first and the second question. This fails since it returns the empty set - a single fact Measure.[Answer Count] only correspond to a single question.
2) Creating sets at the [Answer Dimension] leaf level where [Answer Count] = 1, and counting the number of tuples in the set. Although this looked promising it still failed me, the dimensionality either didn't allow combining the two questions or didn't slice the facts at all when say using a Filter() to combine the sets, even when using two different attribute to specify the answers.
3) Aggregating [Answer Count] to the set {[Call] * [Product]}, and Filter() where both questions have [Answer Count] >= 1. Again promising, but can't figure out the syntax to use.
Big Questions
* Is 2) or 3) above doable at all? What is the rough syntax needed?
* Is there a better (working!-) way to query this cube?
* Is there a better way to design the cube for answering these types of combined questions (remembering we have thousands of distinct questions and answers, and new ones get added over time, and a total of millions of facts)?
Any and all suggestions Much Appreciated!
Kristian
Very complex problem. My question is simply if you have tried data mining and a decision tree model on this problem? Is DM not an option?
Regards
Thomas Ivarsson
|||Assuming that the question: "For how many products and calls are &[What color is it?].&[Blue] AND &[What shape is it?].&[Round]" refers to counting product/call combinations, cascading NonEmpty() might compute what you're looking for, like:
Count(NonEmpty(NonEmpty({[Call].[Call].[Call] * [Product].[Product].[Product]},
{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),
{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))
|||Yes! This gives the right total on my mini test cube. One (hopefully simple) follow-up question:The calculation now happens at the right
[Call].[Call].[Call] * [Product].[Product].[Product]
level. How do I write the query so that I get the Count in a colum, and the list of Products on the rows? I.e.
Occurrences
Car 2
Bowl 1
For instance, this won't work:
WITH
SET MySet AS
[... Deepak's code from above ...]
MEMBER Measures.Occurrences AS
Count(MySet)
SELECT
[Product].[Product].[Product] ON 0
FROM cube
since it will give all Products the same total. Ideas?
Many thanks!
Kristian
|||
Hi Kristian,
To your query above, maybe you can add "Existing", to select only relevant tuples for each cell context:
MEMBER Measures.Occurrences AS
Count(Existing MySet)
When using large sets, the SSAS service crashes when running the above query. Any ideas on how to make the above query not crash the service, either through increasing limits on the SSAS instance or optimizing the query itself, any ideas?
Kristian|||
Kristian, unfortunately I haven't done much tweaking of memory settings for AS 2005 - maybe someone else can comment on this?
One alternative, which may save memory but be much slower, is to avoid creating the cross-joined named set. So, the Occurrences calculated measure could be directly defined as:
Count(NonEmpty(NonEmpty({(Existing [Call].[Call].[Call]) * (Existing [Product].[Product].[Product])},
{[Answer Dimension].[Q and A].[Question].&[What color is it?].&[Blue]}),
{[Answer Dimension].[Q and A].[Question].&[What shape is it?].&[Round]}))
sqlMonday, March 19, 2012
"Static variable" in function
Hi!
I have a function that uses a constant value on its calculations. This value is defined on a table. I don't want to query this table everytime I call the function (I call it on a loop from my Java code). Is there anything like a static variable I could use?
Thank you!
No. There is no such functionality in TSQL. Your approach sounds fine. Alternatively, you can define a scalar UDF that returns the constant value instead of storing it in the table."Select all" as default for a multi-value parameter
Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
...Does anybody know how to accomplish this?
(...or the reason why this is impossible?)
Stefano
|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||StefanoEbitAET wrote:
Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
Thank you very much:
I had already tried this solution before -without success- and quitted...
after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).
Thanks again
Stefano
|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)
"Select all" as default for a multi-value parameter
Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
...Does anybody know how to accomplish this?
(...or the reason why this is impossible?)
Stefano
|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
Thank you very much:
I had already tried this solution before -without success- and quitted...
after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).
Thanks again
Stefano
|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)
Friday, March 16, 2012
"Select all" as default for a multi-value parameter
Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
...Does anybody know how to accomplish this?
(...or the reason why this is impossible?)
Stefano
|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
Thank you very much:
I had already tried this solution before -without success- and quitted...
after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).
Thanks again
Stefano
|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)
"Select all" as default for a multi-value parameter
Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
...Does anybody know how to accomplish this?
(...or the reason why this is impossible?)
Stefano
|||If you set the default to query and then point it to the same query that populates the choices all will be selected.|||StefanoEbitAET wrote:
Hi!
I have the following problem:
In my report I have -among others- a multi-value parameter, populated by a query (so I cannot a priori know the content of the list).
I would like my report to start without any user choice, through default parameters, so what I need is the "select all" choice selected by default. How can I achieve this?
[The only default value I am able to pass to the multi-value parameter is one of the elements populating the list, statically writing it in the "Non-queried" section of "Default values": "From query" option seems not to work for multi-valued]
Any help will be greatly appreciated!
Thanks
Stefano
Thank you very much:
I had already tried this solution before -without success- and quitted...
after reading your reply I've been pushed to try again and I discovered the problem: my query was returning 4 good values and a "null" one... and apparently this is not allowed (no default value was considered).
Thanks again
Stefano
|||I've tried pointing back as well - it works, but it seems like the query then gets executed another time. I wish there was a way to prevent this..Something I'd like to do: Leaving the parameter blank.
Then RS complains that it wants a value. Wouldn't that be a bug if 'allow blank' is checked?|||I dont think so(but i am not sure), because no record has blank as key. If you want "blank" as an option you can use allow null instead.|||Thanks a lot.
It worked & solved my problem (Even without raising a thread!!!)
Thursday, March 8, 2012
"Less Than or Equal To" MDX query
The following MDX query is generated by the Reporting Services? I need to edit the this MDX query so that it will accept the parameter value "Less Than Or Equal To" the @.AccountPeriodAccountPeriod parameter to filter teh cube data. Thanks.
SELECT NON EMPTY { [Measures].[Costing], [Measures].[Distinct Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[Account Period].[Account Period].ALLMEMBERS * [Account Period].[Prescription Date].[Prescription Date].ALLMEMBERS * [Drug].[Protocol Code].[Protocol Code].ALLMEMBERS * [Drug].[Drug Name].[Drug Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED)) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED) ) ON COLUMNS FROM [Drug Cost By Account Period])) WHERE ( IIF( STRTOSET(@.DrugDrugFirstWord, CONSTRAINED).Count = 1, STRTOSET(@.DrugDrugFirstWord, CONSTRAINED), [Drug].[Drug First Word].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Assuming that there is an [AccountPeriod].[AccountPeriod] attribute, ordered ascending by time:
SELECT (Filter([AccountPeriod].[AccountPeriod].[AccountPeriod].Members,
Rank([AccountPeriod].[AccountPeriod].CurrentMember,
[AccountPeriod].[AccountPeriod].[AccountPeriod].Members) < =
Rank(STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED).Item(0).Item(0),
[AccountPeriod].[AccountPeriod].[AccountPeriod].Members)))
|||Thank you Deepak Puri and it is worked beautifully. However I forgot to include one other condition that only the AgencyID has the Account Period equal to the @.AccountPeriod should be allowed in the final dataset. AgencyID is from another dimension different from the Account Period dimension. Thanks.|||If you want only those AgencyID members with [Measures].[Costing] data for the selected Account Period, try replacing:
[AgencyID].[Agency Id].[Agency Id].ALLMEMBERS
with:
NonEmpty([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS,
{[Measures].[Costing]} *
STRTOSET(@.AccountPeriodAccountPeriod, CONSTRAINED))
Tuesday, March 6, 2012
"FOR XML EXPLICIT" query Works on SQL 2000 but same does not...
We are using a stored procedure which uses FOR XML EXPLICIT and it works fine with SQL Server 2000 but doesnt work with SQL Server 2005. Can anyone help me out in understanding the reason behind such a behavior and any possible solution. Please find the details of the problem below:
The procedure runs fine in SQL 2000 the input xml and gives us the correct XML:
SELECT
1 AS TAG,
NULL AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
NULL AS [user!2!title!element],
NULL AS [user!2!firstname!xml],
NULL AS [user!2!lastname!xml],
NULL AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@.idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
UNION ALL
SELECT 2 AS TAG,
1 AS PARENT,
[TEST:mailboxaddress] AS [mailbox!1!mailbox-name!element],
[TEST:status] AS [mailbox!1!mailbox-status!element],
tUser.title_lookup_id AS [user!2!title!element],
tUser.firstname AS [user!2!firstname!xml],
tUser.lastname AS [user!2!lastname!xml],
tUser.login_name AS [user!2!login!element],
tUser.id AS [user!2!userID!element]
FROM TbUser AS tUser
INNER JOIN
OPENXML (@.idoc, '//TEST:mbox',2)
WITH ([TEST:mailboxaddress] NVARCHAR(100),
[TEST:status] NVARCHAR(20),
[TEST:userid] UNIQUEIDENTIFIER) AS mailbox
ON mailbox.[TEST:userid] = tUser.id
order by [mailbox!1!mailbox-name!element], [user!2!userID!element]
FOR XML EXPLICIT
This runs perfectly fine in 2000 but in the 2005 the XML is not correctly formed .. i am not able to figure out why this issue is occuring. In 2005 tt gives me all the tags with the expected values but the sequence is not correct.
Thanks,
Gaurav
Can you give me a more specific case to reproduce your problem so I might help you to find the reason/solution?|||You'll need to give some sample data, but have you tried just adding ORDER BY to the queries. The data will be returned in no particular order if you don't specify ORDER BY, so if it is "correct" on 2000 but "incorrect" on 2005 that is purely by chance.|||You asked the same question in sql server central. I answered your question there a week ago and don't know solved your problem or not. The answer is to change "tUser.id AS [user!2!userID!element]" to "NULL AS [user!2!userID!element]" in the first SELECT statement in the UNION.
"distinct" in select query
when i use select :
select flattened
(select productid, $support from [model name].[table] )......
i have result with many record same.
so i write :
select flattened
(select distinct productid, $support from [model name].[table] )......
but when i run, it's error. ( don't know what error).
how can i do to get table record with not same record (loop)
note : it write in DTS and i use sql 2000
I assume you mean an Analysis Services query.
Try: SELECT DISTINCT [model name].[table].[productid] FROM [model name]
This should return the distinct list of products
|||
i can't use query like you show.
i want you "distinct " in DTS syntax like that:
select flattened
(select productid, productname, categoryid ,[$probability] from predict([model name].[table name], incluse_statistics) where productid > 1000)
from [model name]
prediction join
shape{....}
appen{....}
on....
How can i put Distinct to have different record?
|||You can't use DISTINCT in that context. However, I think what you want is to be able to discriminate the records by the user. In this case, the selection will return the product id at most once per input case, but since you don't include an external id, there's no way to discriminate which prediction is for which customer.
I think you need something like
SELECT FLATTENED t.CustomerID, (select ....
This query will return the id from the source data query along with the results of the prediction.
Saturday, February 25, 2012
"Could not find installable ISAM"
Hi, when l use the ADO.NET with the following query,
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
i get the following error, i.e.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Any idea, what's wrong?
Regards,
YB Lim
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
i get the following error, i.e.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
Any idea, what's wrong?
Regards,
YB Lim
I read before... a fix for that could be to reinstall MDAC.
http://support.microsoft.com/default.aspx/kb/283881
http://support.microsoft.com/default.aspx/kb/209805
http://support.microsoft.com/default.aspx/kb/90111
Sunday, February 19, 2012
"Advanced search" query in SQL Server2000
Hi query-experts,
I have an application that connects to a db running on SQL server 2000. I want the user to be able to do a google-like search, which returns all records containing all given keywords specified by the user in any of the fields, example:
Table has the fields: id, fruit, color
User enters: apple pear green
The query should return all records that contain apple AND pear AND green in any of the fields.
Now I do it like this:
SELECT * FROM table WHERE (fruit LIKE '%apple%' OR fruit LIKE '%pear%' OR fruit LIKE '%green%') AND (color LIKE '%apple%' OR color LIKE '%pear%' OR color LIKE '%green%')
this query works fine, but since my actual table contains about 10 fields that have to be searched, the query get's quite huge, especially when 2 or more keywords are given.
Question: is there an easier way to achieve this?
(this is what my query actually looks like, using the keywords dekker warmenhuizen 0226:)
SELECT
tKlant.nKlant AS nKlant,
MAX(sAchternaam) AS sAchternaam,
MAX(sVoornaam) AS sVoornaam,
MAX(sVoorletters) AS sVoorletters,
MAX(sBedrijfsnaam) AS sBedrijfsnaam,
MAX(sPlaats) AS sPlaats,
MAX(sStraat + ' ' + sHuisnummer) AS sAdres,
MAX(sPostcode) AS sPostcode,
MAX(sCommunicatie) AS sCommunicatie,
(CASE DATALENGTH(mOverig) WHEN 0 THEN 0 ELSE 1 END) AS HasOverig,
MAX(sKlantGroep) AS sKlantGroep
FROM tKlant
LEFT JOIN tKlantGroep ON tKlant.nKlantGroep = tKlantGroep.nKlantGroep
LEFT JOIN tCommunicatie ON tKlant.nKlant = tCommunicatie.nKlant
LEFT JOIN tAdres ON tKlant.nKlant = tAdres.nKlant
WHERE (tKlant.sAchternaam LIKE '%dekker%' OR tKlant.sBedrijfsnaam LIKE '%dekker%' OR tKlant.sKlant LIKE '%dekker%' OR tKlant.sVoornaam LIKE '%dekker%' OR tKlant.mOverig LIKE '%dekker%' OR tCommunicatie.sCommunicatie LIKE '%dekker%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%dekker%' OR tAdres.sPostcode LIKE '%dekker%' OR tAdres.sPlaats LIKE '%dekker%' OR tKlantGroep.sKlantGroep LIKE '%dekker%')
AND (tKlant.sAchternaam LIKE '%warmenhuizen%' OR tKlant.sBedrijfsnaam LIKE '%warmenhuizen%' OR tKlant.sKlant LIKE '%warmenhuizen%' OR tKlant.sVoornaam LIKE '%warmenhuizen%' OR tKlant.mOverig LIKE '%warmenhuizen%' OR tCommunicatie.sCommunicatie LIKE '%warmenhuizen%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%warmenhuizen%' OR tAdres.sPostcode LIKE '%warmenhuizen%' OR tAdres.sPlaats LIKE '%warmenhuizen%' OR tKlantGroep.sKlantGroep LIKE '%warmenhuizen%')
AND (tKlant.sAchternaam LIKE '%0226%' OR tKlant.sBedrijfsnaam LIKE '%0226%' OR tKlant.sKlant LIKE '%0226%' OR tKlant.sVoornaam LIKE '%0226%' OR tKlant.mOverig LIKE '%0226%' OR tCommunicatie.sCommunicatie LIKE '%0226%' OR tAdres.sStraat + ' ' + tAdres.sHuisnummer LIKE '%0226%' OR tAdres.sPostcode LIKE '%0226%' OR tAdres.sPlaats LIKE '%0226%' OR tKlantGroep.sKlantGroep LIKE '%0226%')
GROUP BY tKlant.nKlant, DATALENGTH(mOverig)
ORDER BY sAchternaam, sPlaats, sAdres;
SELECT
*
FROM table
WHERE fruit in ('apple','pear','green')
and color in ('apple','pear','green')
I recommend you take a look at Erland's article:
http://www.sommarskog.se/dyn-search.html
|||Thanks! I'll check it out
<Select a Value> inconsistent
Hi, has anyone else seen this or know of why it's happening?
I have a report with a parameter Region. The region values are pulled
from a query:
select null as id, '' as name
union
select regionid, regionname from region
The Region parameter allows nulls and has a default value of null.
In the visual studio designer everything looks great. The dropdown
parameter is blank and my region values are there if I pull it down.
When I deploy the report to the server, the region parameter looks
different. It now defaults to <Select a Value>. The blank (null) line
exists but is not defaulted to on open of the report.
Another weird aspect of this is that I have a number of other reports
that "seem" to be set up exactly in this manner that work corrrectly
(i.e. The Region dropdown show up blank with valid regions in the
dropdown and no <select a value> option) in the designer and on the
server.
Thanks for your help,
BrettOn your report parameter have you set a non-queried default value of
=Nothing ?
HTH, Magendo_man
"Brett" wrote:
> Hi, has anyone else seen this or know of why it's happening?
> I have a report with a parameter Region. The region values are pulled
> from a query:
> select null as id, '' as name
> union
> select regionid, regionname from region
> The Region parameter allows nulls and has a default value of null.
> In the visual studio designer everything looks great. The dropdown
> parameter is blank and my region values are there if I pull it down.
> When I deploy the report to the server, the region parameter looks
> different. It now defaults to <Select a Value>. The blank (null) line
> exists but is not defaulted to on open of the report.
> Another weird aspect of this is that I have a number of other reports
> that "seem" to be set up exactly in this manner that work corrrectly
> (i.e. The Region dropdown show up blank with valid regions in the
> dropdown and no <select a value> option) in the designer and on the
> server.
> Thanks for your help,
> Brett
>|||Thanks magendo! That worked! I still don't understand why this
parameter acts differently than other parameters (or why setting the
default to NULL does not work), but at this point, I'm just happy it
works. Thanks a bunch.
Brett
I have a report with a parameter Region. The region values are pulled
from a query:
select null as id, '' as name
union
select regionid, regionname from region
The Region parameter allows nulls and has a default value of null.
In the visual studio designer everything looks great. The dropdown
parameter is blank and my region values are there if I pull it down.
When I deploy the report to the server, the region parameter looks
different. It now defaults to <Select a Value>. The blank (null) line
exists but is not defaulted to on open of the report.
Another weird aspect of this is that I have a number of other reports
that "seem" to be set up exactly in this manner that work corrrectly
(i.e. The Region dropdown show up blank with valid regions in the
dropdown and no <select a value> option) in the designer and on the
server.
Thanks for your help,
BrettOn your report parameter have you set a non-queried default value of
=Nothing ?
HTH, Magendo_man
"Brett" wrote:
> Hi, has anyone else seen this or know of why it's happening?
> I have a report with a parameter Region. The region values are pulled
> from a query:
> select null as id, '' as name
> union
> select regionid, regionname from region
> The Region parameter allows nulls and has a default value of null.
> In the visual studio designer everything looks great. The dropdown
> parameter is blank and my region values are there if I pull it down.
> When I deploy the report to the server, the region parameter looks
> different. It now defaults to <Select a Value>. The blank (null) line
> exists but is not defaulted to on open of the report.
> Another weird aspect of this is that I have a number of other reports
> that "seem" to be set up exactly in this manner that work corrrectly
> (i.e. The Region dropdown show up blank with valid regions in the
> dropdown and no <select a value> option) in the designer and on the
> server.
> Thanks for your help,
> Brett
>|||Thanks magendo! That worked! I still don't understand why this
parameter acts differently than other parameters (or why setting the
default to NULL does not work), but at this point, I'm just happy it
works. Thanks a bunch.
Brett
Thursday, February 16, 2012
>= AND <= or just = ?
This is just a general question. I just ran a query with several columns in
the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
looked something like this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ...
GO
I played around with it, and found that re-forming the WHERE Clause like
this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
...
GO
Resulted in the same query being executed in less than 1 second. Anyone
know why these two seemingly equivalent WHERE clauses would be so
drastically different in practice?
Maybe the data is part of the dirty buffers, and is retrieved from cache
in the second query?
But seriously: check out the query plans and look at the differences.
There lies the answer.
If there really is a significant difference, then it would be
interesting to know what difference the query plan shows...
Gert-Jan
Michael C# wrote:
> This is just a general question. I just ran a query with several columns in
> the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
> looked something like this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ...
> GO
> I played around with it, and found that re-forming the WHERE Clause like
> this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
> ...
> GO
> Resulted in the same query being executed in less than 1 second. Anyone
> know why these two seemingly equivalent WHERE clauses would be so
> drastically different in practice?
|||According to the Query Plans it looks like switching from "=" syntax to ">=
AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
about 350,000 to about 7,000. That's the only difference - but wow, what a
difference! Anyone have any ideas on why this happens, and better yet, why
the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
what kind of effect it will have on non-clustered indexes and non-indexed
fields...
Thanks
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601468.9462CCDF@.toomuchspamalready.nl...[vbcol=seagreen]
> Maybe the data is part of the dirty buffers, and is retrieved from cache
> in the second query?
> But seriously: check out the query plans and look at the differences.
> There lies the answer.
> If there really is a significant difference, then it would be
> interesting to know what difference the query plan shows...
> Gert-Jan
>
> Michael C# wrote:
|||Tried it with a query on a couple of columns in a non-clustered Index, and
ended up with not-so-promising results. So far it appears to work best on
Clustered Indexes...
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:Og1YtKfQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> According to the Query Plans it looks like switching from "=" syntax to
> ">= AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek
> from about 350,000 to about 7,000. That's the only difference - but wow,
> what a difference! Anyone have any ideas on why this happens, and better
> yet, why the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm
> wondering what kind of effect it will have on non-clustered indexes and
> non-indexed fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
>
|||I hope column DOB_Year doesn't happen to be a varchar? That would be an
explanation.
Does the performance also increase if you change just one of the
predicates? Or does a rewrite of each predicate improve the performance?
If the guess (above) about the data type turns out to be correct, then
only the predicate with DOB_Year would make the difference.
In addition, if the clustered index seek cuts down the estimated number
of rows, then the seek parameters must be different (or a different
index is used).
Gert-Jan
Michael C# wrote:[vbcol=seagreen]
> According to the Query Plans it looks like switching from "=" syntax to ">=
> AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
> about 350,000 to about 7,000. That's the only difference - but wow, what a
> difference! Anyone have any ideas on why this happens, and better yet, why
> the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
> what kind of effect it will have on non-clustered indexes and non-indexed
> fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
|||DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
(INT) columns. I re-wrote all the predicates - did not try them
individually since this got me such a good result (no point in breaking it).
There is one Clustered Index on this table. No non-clustered indexes.
The Seek Parameters must be different then, and it appears to be caused by
the different operators used. Nothing else on the query, or on the table or
index have been changed. I just find this whole thing fascinating. I was
thinking this might be common knowledge I had missed out on somewhere along
the way. Anyways, thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426022AB.7C56005D@.toomuchspamalready.nl...[vbcol=seagreen]
>I hope column DOB_Year doesn't happen to be a varchar? That would be an
> explanation.
> Does the performance also increase if you change just one of the
> predicates? Or does a rewrite of each predicate improve the performance?
> If the guess (above) about the data type turns out to be correct, then
> only the predicate with DOB_Year would make the difference.
> In addition, if the clustered index seek cuts down the estimated number
> of rows, then the seek parameters must be different (or a different
> index is used).
> Gert-Jan
>
> Michael C# wrote:
|||> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking
> it).
Might be worth trying it so you'll know exactly what is going on next time
this comes up. Wouldn't hurt to validate Gert-Jan's theories, either.
|||If the only index on the table is a clustered index, then only the
columns in this index are relevant. If any of the three column
(FirstName, LastName, DOB_Year) is not part of this index, then
rewriting them probably makes no difference.
When you look at the query plan, make sure you differentiate between
SEEK parameter and the predicates mentioned in the WHERE clause of the
SEEK operator. It is the SEEK parameter that primarily determines the
performance, because that determines which rows are read.
I must say, I am starting to get very curious to see the actual
(estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
ON" before you run the query, then SQL-Server will only generate a query
plan. Please post both query plans. Maybe there is a data type mismatch
somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
we have overlooked something.
Gert-Jan
Michael C# wrote:
> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking it).
> There is one Clustered Index on this table. No non-clustered indexes.
> The Seek Parameters must be different then, and it appears to be caused by
> the different operators used. Nothing else on the query, or on the table or
> index have been changed. I just find this whole thing fascinating. I was
> thinking this might be common knowledge I had missed out on somewhere along
> the way. Anyways, thanks.
<snip>
|||OK, they just got the network up down there and I was able to run a few
tests. Here are the results for you:
Test 1:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,328. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year >= 1960
AND od.DOB_Year <= 1960
GO
SET SHOWPLAN_TEXT OFF
GO
-- ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [od].[DOB_Year]
>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=
([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
WHERE[od].[DOB_Ye
Test 3:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 357,915. Took about 10 secs to run.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test
It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =
as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
> <snip>
|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=
> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
> WHERE[od].[DOB_Ye
> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test
> It seems like >= AND <= in place of = on the clustered index columns made
> a significant difference for me. It would be cool if the Query Optimizer
> would automatically take care of this conversion for me internally in this
> situation. I would think SQL Server would recognize the >= AND <= and the
> = as being equivalent and handle that when it generated the plan.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42602F3B.9612B18B@.toomuchspamalready.nl...
>
the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
looked something like this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ...
GO
I played around with it, and found that re-forming the WHERE Clause like
this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
...
GO
Resulted in the same query being executed in less than 1 second. Anyone
know why these two seemingly equivalent WHERE clauses would be so
drastically different in practice?
Maybe the data is part of the dirty buffers, and is retrieved from cache
in the second query?
But seriously: check out the query plans and look at the differences.
There lies the answer.
If there really is a significant difference, then it would be
interesting to know what difference the query plan shows...
Gert-Jan
Michael C# wrote:
> This is just a general question. I just ran a query with several columns in
> the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
> looked something like this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ...
> GO
> I played around with it, and found that re-forming the WHERE Clause like
> this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
> ...
> GO
> Resulted in the same query being executed in less than 1 second. Anyone
> know why these two seemingly equivalent WHERE clauses would be so
> drastically different in practice?
|||According to the Query Plans it looks like switching from "=" syntax to ">=
AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
about 350,000 to about 7,000. That's the only difference - but wow, what a
difference! Anyone have any ideas on why this happens, and better yet, why
the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
what kind of effect it will have on non-clustered indexes and non-indexed
fields...
Thanks
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601468.9462CCDF@.toomuchspamalready.nl...[vbcol=seagreen]
> Maybe the data is part of the dirty buffers, and is retrieved from cache
> in the second query?
> But seriously: check out the query plans and look at the differences.
> There lies the answer.
> If there really is a significant difference, then it would be
> interesting to know what difference the query plan shows...
> Gert-Jan
>
> Michael C# wrote:
|||Tried it with a query on a couple of columns in a non-clustered Index, and
ended up with not-so-promising results. So far it appears to work best on
Clustered Indexes...
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:Og1YtKfQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> According to the Query Plans it looks like switching from "=" syntax to
> ">= AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek
> from about 350,000 to about 7,000. That's the only difference - but wow,
> what a difference! Anyone have any ideas on why this happens, and better
> yet, why the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm
> wondering what kind of effect it will have on non-clustered indexes and
> non-indexed fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
>
|||I hope column DOB_Year doesn't happen to be a varchar? That would be an
explanation.
Does the performance also increase if you change just one of the
predicates? Or does a rewrite of each predicate improve the performance?
If the guess (above) about the data type turns out to be correct, then
only the predicate with DOB_Year would make the difference.
In addition, if the clustered index seek cuts down the estimated number
of rows, then the seek parameters must be different (or a different
index is used).
Gert-Jan
Michael C# wrote:[vbcol=seagreen]
> According to the Query Plans it looks like switching from "=" syntax to ">=
> AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
> about 350,000 to about 7,000. That's the only difference - but wow, what a
> difference! Anyone have any ideas on why this happens, and better yet, why
> the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
> what kind of effect it will have on non-clustered indexes and non-indexed
> fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
|||DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
(INT) columns. I re-wrote all the predicates - did not try them
individually since this got me such a good result (no point in breaking it).
There is one Clustered Index on this table. No non-clustered indexes.
The Seek Parameters must be different then, and it appears to be caused by
the different operators used. Nothing else on the query, or on the table or
index have been changed. I just find this whole thing fascinating. I was
thinking this might be common knowledge I had missed out on somewhere along
the way. Anyways, thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426022AB.7C56005D@.toomuchspamalready.nl...[vbcol=seagreen]
>I hope column DOB_Year doesn't happen to be a varchar? That would be an
> explanation.
> Does the performance also increase if you change just one of the
> predicates? Or does a rewrite of each predicate improve the performance?
> If the guess (above) about the data type turns out to be correct, then
> only the predicate with DOB_Year would make the difference.
> In addition, if the clustered index seek cuts down the estimated number
> of rows, then the seek parameters must be different (or a different
> index is used).
> Gert-Jan
>
> Michael C# wrote:
|||> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking
> it).
Might be worth trying it so you'll know exactly what is going on next time
this comes up. Wouldn't hurt to validate Gert-Jan's theories, either.
|||If the only index on the table is a clustered index, then only the
columns in this index are relevant. If any of the three column
(FirstName, LastName, DOB_Year) is not part of this index, then
rewriting them probably makes no difference.
When you look at the query plan, make sure you differentiate between
SEEK parameter and the predicates mentioned in the WHERE clause of the
SEEK operator. It is the SEEK parameter that primarily determines the
performance, because that determines which rows are read.
I must say, I am starting to get very curious to see the actual
(estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
ON" before you run the query, then SQL-Server will only generate a query
plan. Please post both query plans. Maybe there is a data type mismatch
somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
we have overlooked something.
Gert-Jan
Michael C# wrote:
> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking it).
> There is one Clustered Index on this table. No non-clustered indexes.
> The Seek Parameters must be different then, and it appears to be caused by
> the different operators used. Nothing else on the query, or on the table or
> index have been changed. I just find this whole thing fascinating. I was
> thinking this might be common knowledge I had missed out on somewhere along
> the way. Anyways, thanks.
<snip>
|||OK, they just got the network up down there and I was able to run a few
tests. Here are the results for you:
Test 1:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,328. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year >= 1960
AND od.DOB_Year <= 1960
GO
SET SHOWPLAN_TEXT OFF
GO
-- ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [od].[DOB_Year]>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]AS [od]), SEEK
[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
WHERE
[od].[DOB_YeTest 3:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 357,915. Took about 10 secs to run.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test
It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =
as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
> <snip>
|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]> AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND> [od].[DOB_Year]
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]> AS [od]), SEEK
[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <= ([@.3], [@.4])),
> WHERE
[od].[DOB_Ye> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]> AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test
> It seems like >= AND <= in place of = on the clustered index columns made
> a significant difference for me. It would be cool if the Query Optimizer
> would automatically take care of this conversion for me internally in this
> situation. I would think SQL Server would recognize the >= AND <= and the
> = as being equivalent and handle that when it generated the plan.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42602F3B.9612B18B@.toomuchspamalready.nl...
>
>= AND <= or just = ?
This is just a general question. I just ran a query with several columns in
the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
looked something like this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ..
.
GO
I played around with it, and found that re-forming the WHERE Clause like
this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
...
GO
Resulted in the same query being executed in less than 1 second. Anyone
know why these two seemingly equivalent WHERE clauses would be so
drastically different in practice?Maybe the data is part of the dirty buffers, and is retrieved from cache
in the second query?
But seriously: check out the query plans and look at the differences.
There lies the answer.
If there really is a significant difference, then it would be
interesting to know what difference the query plan shows...
Gert-Jan
Michael C# wrote:
> This is just a general question. I just ran a query with several columns
in
> the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
> looked something like this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 .
.
> GO
> I played around with it, and found that re-forming the WHERE Clause like
> this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
> ...
> GO
> Resulted in the same query being executed in less than 1 second. Anyone
> know why these two seemingly equivalent WHERE clauses would be so
> drastically different in practice?|||According to the Query Plans it looks like switching from "=" syntax to ">=
AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
about 350,000 to about 7,000. That's the only difference - but wow, what a
difference! Anyone have any ideas on why this happens, and better yet, why
the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
what kind of effect it will have on non-clustered indexes and non-indexed
fields...
Thanks
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601468.9462CCDF@.toomuchspamalready.nl...[vbcol=seagreen]
> Maybe the data is part of the dirty buffers, and is retrieved from cache
> in the second query?
> But seriously: check out the query plans and look at the differences.
> There lies the answer.
> If there really is a significant difference, then it would be
> interesting to know what difference the query plan shows...
> Gert-Jan
>
> Michael C# wrote:|||Tried it with a query on a couple of columns in a non-clustered Index, and
ended up with not-so-promising results. So far it appears to work best on
Clustered Indexes...
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:Og1YtKfQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> According to the Query Plans it looks like switching from "=" syntax to
> ">= AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek
> from about 350,000 to about 7,000. That's the only difference - but wow,
> what a difference! Anyone have any ideas on why this happens, and better
> yet, why the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm
> wondering what kind of effect it will have on non-clustered indexes and
> non-indexed fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
>|||I hope column DOB_Year doesn't happen to be a varchar? That would be an
explanation.
Does the performance also increase if you change just one of the
predicates? Or does a rewrite of each predicate improve the performance?
If the guess (above) about the data type turns out to be correct, then
only the predicate with DOB_Year would make the difference.
In addition, if the clustered index seek cuts down the estimated number
of rows, then the seek parameters must be different (or a different
index is used).
Gert-Jan
Michael C# wrote:[vbcol=seagreen]
> According to the Query Plans it looks like switching from "=" syntax to ">
=
> AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
> about 350,000 to about 7,000. That's the only difference - but wow, what
a
> difference! Anyone have any ideas on why this happens, and better yet, wh
y
> the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
> what kind of effect it will have on non-clustered indexes and non-indexed
> fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...|||DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
(INT) columns. I re-wrote all the predicates - did not try them
individually since this got me such a good result (no point in breaking it).
There is one Clustered Index on this table. No non-clustered indexes.
The Seek Parameters must be different then, and it appears to be caused by
the different operators used. Nothing else on the query, or on the table or
index have been changed. I just find this whole thing fascinating. I was
thinking this might be common knowledge I had missed out on somewhere along
the way. Anyways, thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426022AB.7C56005D@.toomuchspamalready.nl...[vbcol=seagreen]
>I hope column DOB_Year doesn't happen to be a varchar? That would be an
> explanation.
> Does the performance also increase if you change just one of the
> predicates? Or does a rewrite of each predicate improve the performance?
> If the guess (above) about the data type turns out to be correct, then
> only the predicate with DOB_Year would make the difference.
> In addition, if the clustered index seek cuts down the estimated number
> of rows, then the seek parameters must be different (or a different
> index is used).
> Gert-Jan
>
> Michael C# wrote:|||> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking
> it).
Might be worth trying it so you'll know exactly what is going on next time
this comes up. Wouldn't hurt to validate Gert-Jan's theories, either.|||If the only index on the table is a clustered index, then only the
columns in this index are relevant. If any of the three column
(FirstName, LastName, DOB_Year) is not part of this index, then
rewriting them probably makes no difference.
When you look at the query plan, make sure you differentiate between
SEEK parameter and the predicates mentioned in the WHERE clause of the
SEEK operator. It is the SEEK parameter that primarily determines the
performance, because that determines which rows are read.
I must say, I am starting to get very curious to see the actual
(estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
ON" before you run the query, then SQL-Server will only generate a query
plan. Please post both query plans. Maybe there is a data type mismatch
somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
we have overlooked something.
Gert-Jan
Michael C# wrote:
> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking it
).
> There is one Clustered Index on this table. No non-clustered indexes.
> The Seek Parameters must be different then, and it appears to be caused by
> the different operators used. Nothing else on the query, or on the table
or
> index have been changed. I just find this whole thing fascinating. I was
> thinking this might be common knowledge I had missed out on somewhere alon
g
> the way. Anyways, thanks.
<snip>|||OK, they just got the network up down there and I was able to run a few
tests. Here are the results for you:
Test 1:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,328. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year >= 1960
AND od.DOB_Year <= 1960
GO
SET SHOWPLAN_TEXT OFF
GO
-- ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
-- |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [
;od].[DOB_Year]
>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK[od].[LName]=[@.1] AND ([od].[FName],
[od].[DOB_Year]) >=
([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <=
([@.3], [@.4])),
WHERE[od].[DOB_Ye
Test 3:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 357,915. Took about 10 secs to run.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_Off
ender_Details]
AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]=&
#91;@.2] AND
[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test
It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =
as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
> <snip>|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]
=[@.2] AND
> [od].[DOB_Year]
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK[od].[LName]=[@.1] AND ([od].[FName
], [od].[DOB_Year]) >=
> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <
= ([@.3], [@.4])),
> WHERE[od].[DOB_Ye
> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT[OffenderData].[dbo].[Offender_Details].[IX_O
ffender_Details]
> AS [od]), SEEK[od].[LName]=[@.1] AND [od].[FName]
=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test
> It seems like >= AND <= in place of = on the clustered index columns made
> a significant difference for me. It would be cool if the Query Optimizer
> would automatically take care of this conversion for me internally in this
> situation. I would think SQL Server would recognize the >= AND <= and the
> = as being equivalent and handle that when it generated the plan.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42602F3B.9612B18B@.toomuchspamalready.nl...
>
the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
looked something like this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 ..
.
GO
I played around with it, and found that re-forming the WHERE Clause like
this:
DBCC DROPCLEANBUFFERS
GO
... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
...
GO
Resulted in the same query being executed in less than 1 second. Anyone
know why these two seemingly equivalent WHERE clauses would be so
drastically different in practice?Maybe the data is part of the dirty buffers, and is retrieved from cache
in the second query?
But seriously: check out the query plans and look at the differences.
There lies the answer.
If there really is a significant difference, then it would be
interesting to know what difference the query plan shows...
Gert-Jan
Michael C# wrote:
> This is just a general question. I just ran a query with several columns
in
> the WHERE Clause. It took about 10 seconds to complete. The WHERE clause
> looked something like this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName = 'Smith' AND FirstName = 'James' AND DOB_Year = 1960 .
.
> GO
> I played around with it, and found that re-forming the WHERE Clause like
> this:
> DBCC DROPCLEANBUFFERS
> GO
> ... WHERE LastName >= 'Smith' AND LastName <= 'Smith' AND FirstName >=
> 'James' AND FirstName <= 'James' AND DOB_Year >= 1960 AND DOB_Year <= 1960
> ...
> GO
> Resulted in the same query being executed in less than 1 second. Anyone
> know why these two seemingly equivalent WHERE clauses would be so
> drastically different in practice?|||According to the Query Plans it looks like switching from "=" syntax to ">=
AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
about 350,000 to about 7,000. That's the only difference - but wow, what a
difference! Anyone have any ideas on why this happens, and better yet, why
the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
what kind of effect it will have on non-clustered indexes and non-indexed
fields...
Thanks
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42601468.9462CCDF@.toomuchspamalready.nl...[vbcol=seagreen]
> Maybe the data is part of the dirty buffers, and is retrieved from cache
> in the second query?
> But seriously: check out the query plans and look at the differences.
> There lies the answer.
> If there really is a significant difference, then it would be
> interesting to know what difference the query plan shows...
> Gert-Jan
>
> Michael C# wrote:|||Tried it with a query on a couple of columns in a non-clustered Index, and
ended up with not-so-promising results. So far it appears to work best on
Clustered Indexes...
Thanks
"Michael C#" <howsa@.boutdat.com> wrote in message
news:Og1YtKfQFHA.2584@.TK2MSFTNGP15.phx.gbl...
> According to the Query Plans it looks like switching from "=" syntax to
> ">= AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek
> from about 350,000 to about 7,000. That's the only difference - but wow,
> what a difference! Anyone have any ideas on why this happens, and better
> yet, why the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm
> wondering what kind of effect it will have on non-clustered indexes and
> non-indexed fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...
>|||I hope column DOB_Year doesn't happen to be a varchar? That would be an
explanation.
Does the performance also increase if you change just one of the
predicates? Or does a rewrite of each predicate improve the performance?
If the guess (above) about the data type turns out to be correct, then
only the predicate with DOB_Year would make the difference.
In addition, if the clustered index seek cuts down the estimated number
of rows, then the seek parameters must be different (or a different
index is used).
Gert-Jan
Michael C# wrote:[vbcol=seagreen]
> According to the Query Plans it looks like switching from "=" syntax to ">
=
> AND <=" syntax cut down the Estimated Rows on my Clustered Index Seek from
> about 350,000 to about 7,000. That's the only difference - but wow, what
a
> difference! Anyone have any ideas on why this happens, and better yet, wh
y
> the Query Optimizer doesn't convert "=" to ">= AND <="? Now I'm wondering
> what kind of effect it will have on non-clustered indexes and non-indexed
> fields...
> Thanks
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42601468.9462CCDF@.toomuchspamalready.nl...|||DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
(INT) columns. I re-wrote all the predicates - did not try them
individually since this got me such a good result (no point in breaking it).
There is one Clustered Index on this table. No non-clustered indexes.
The Seek Parameters must be different then, and it appears to be caused by
the different operators used. Nothing else on the query, or on the table or
index have been changed. I just find this whole thing fascinating. I was
thinking this might be common knowledge I had missed out on somewhere along
the way. Anyways, thanks.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426022AB.7C56005D@.toomuchspamalready.nl...[vbcol=seagreen]
>I hope column DOB_Year doesn't happen to be a varchar? That would be an
> explanation.
> Does the performance also increase if you change just one of the
> predicates? Or does a rewrite of each predicate improve the performance?
> If the guess (above) about the data type turns out to be correct, then
> only the predicate with DOB_Year would make the difference.
> In addition, if the clustered index seek cuts down the estimated number
> of rows, then the seek parameters must be different (or a different
> index is used).
> Gert-Jan
>
> Michael C# wrote:|||> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking
> it).
Might be worth trying it so you'll know exactly what is going on next time
this comes up. Wouldn't hurt to validate Gert-Jan's theories, either.|||If the only index on the table is a clustered index, then only the
columns in this index are relevant. If any of the three column
(FirstName, LastName, DOB_Year) is not part of this index, then
rewriting them probably makes no difference.
When you look at the query plan, make sure you differentiate between
SEEK parameter and the predicates mentioned in the WHERE clause of the
SEEK operator. It is the SEEK parameter that primarily determines the
performance, because that determines which rows are read.
I must say, I am starting to get very curious to see the actual
(estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
ON" before you run the query, then SQL-Server will only generate a query
plan. Please post both query plans. Maybe there is a data type mismatch
somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
we have overlooked something.
Gert-Jan
Michael C# wrote:
> DOB_Year is an INT. In addition, there are DOB_Month (INT) and DOB_Day
> (INT) columns. I re-wrote all the predicates - did not try them
> individually since this got me such a good result (no point in breaking it
).
> There is one Clustered Index on this table. No non-clustered indexes.
> The Seek Parameters must be different then, and it appears to be caused by
> the different operators used. Nothing else on the query, or on the table
or
> index have been changed. I just find this whole thing fascinating. I was
> thinking this might be common knowledge I had missed out on somewhere alon
g
> the way. Anyways, thanks.
<snip>|||OK, they just got the network up down there and I was able to run a few
tests. Here are the results for you:
Test 1:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,328. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year >= 1960
AND od.DOB_Year <= 1960
GO
SET SHOWPLAN_TEXT OFF
GO
-- ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND [;od].[DOB_Year]
>= [@.3] AND [od].[DOB_Year] <= [@.4]) ORDERED FORWARD)
Test 2:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 7,678. Ran in less than 1 sec.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName >= 'James'
AND od.FName <= 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--ShowPlan Results:
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK
[od].[LName]=[@.1] AND ([od].[FName],[od].[DOB_Year]) >=
([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <=
([@.3], [@.4])),
WHERE
[od].[DOB_YeTest 3:
-- This one (in the Graphical Execution Plan) displayed an Estimated Row
Count of 357,915. Took about 10 secs to run.
SET SHOWPLAN_TEXT ON
GO
SELECT od.OffenderID
FROM Offender_Details od
WHERE od.LName = 'Smith'
AND od.FName = 'James'
AND od.DOB_Year = 1960
GO
SET SHOWPLAN_TEXT OFF
GO
--SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith' AND
od.FName = 'James' AND od.DOB_Year = 1960
-- |--Clustered Index
Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
[od].[DOB_Year]=[@.3]) ORDERED FORWARD)
The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key, but
is non-clustered. It is not part of the Clustered Index. The table has a
clustered index of (FName, LName, DOB_Year). Table has about 15 million
rows in it currently. I tried the same type of thing with a non-clustered
index columns on a different table and ended up with less inspiring results.
I also tried it on a couple non-indexed columns, and confirmed (for myself
at least) that there was no point to that test
It seems like >= AND <= in place of = on the clustered index columns made a
significant difference for me. It would be cool if the Query Optimizer
would automatically take care of this conversion for me internally in this
situation. I would think SQL Server would recognize the >= AND <= and the =
as being equivalent and handle that when it generated the plan.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42602F3B.9612B18B@.toomuchspamalready.nl...
> If the only index on the table is a clustered index, then only the
> columns in this index are relevant. If any of the three column
> (FirstName, LastName, DOB_Year) is not part of this index, then
> rewriting them probably makes no difference.
> When you look at the query plan, make sure you differentiate between
> SEEK parameter and the predicates mentioned in the WHERE clause of the
> SEEK operator. It is the SEEK parameter that primarily determines the
> performance, because that determines which rows are read.
> I must say, I am starting to get very curious to see the actual
> (estimated) query plans of both queries. If you run "SET SHOWPLAN_TEXT
> ON" before you run the query, then SQL-Server will only generate a query
> plan. Please post both query plans. Maybe there is a data type mismatch
> somewhere, maybe there is a bug (or flaw) that you have uncovered, maybe
> we have overlooked something.
> Gert-Jan
>
> Michael C# wrote:
> <snip>|||BTW, in the end, this particular query returns just one record... although
there are situations where I'll be pulling back as many as 500+ records with
a search like this. Thanks.
"Michael C#" <howsa@.boutdat.com> wrote in message
news:OYBUAVgQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> OK, they just got the network up down there and I was able to run a few
> tests. Here are the results for you:
> Test 1:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,328. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year >= 1960
> AND od.DOB_Year <= 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> -- ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year >= 1960 AND od.DOB_Year <= 1960
> -- |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]
>
> Test 2:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 7,678. Ran in less than 1 sec.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName >= 'James'
> AND od.FName <= 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --ShowPlan Results:
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName >= 'James' AND od.FName <= 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK
[od].[LName]=[@.1] AND ([od].[FName], [od].[DOB_Year]) >=
> ([@.2], [@.4]) AND ([od].[FName], [od].[DOB_Year]) <
= ([@.3], [@.4])),
> WHERE
[od].[DOB_Ye> Test 3:
> -- This one (in the Graphical Execution Plan) displayed an Estimated Row
> Count of 357,915. Took about 10 secs to run.
> SET SHOWPLAN_TEXT ON
> GO
> SELECT od.OffenderID
> FROM Offender_Details od
> WHERE od.LName = 'Smith'
> AND od.FName = 'James'
> AND od.DOB_Year = 1960
> GO
> SET SHOWPLAN_TEXT OFF
> GO
> --SELECT od.OffenderID FROM Offender_Details od WHERE od.LName = 'Smith'
> AND od.FName = 'James' AND od.DOB_Year = 1960
> -- |--Clustered Index
> Seek(OBJECT
[OffenderData].[dbo].[Offender_Details].[IX_Offender_Details]
> AS [od]), SEEK
[od].[LName]=[@.1] AND [od].[FName]=[@.2] AND
> [od].[DOB_Year]=[@.3]) ORDERED FORWARD)
> The FName and LName columns are VARCHAR(32) NOT NULL. The DOB_Year column
> is INT NOT NULL. OffenderID column is a BIGINT. It is the Primary Key,
> but is non-clustered. It is not part of the Clustered Index. The table
> has a clustered index of (FName, LName, DOB_Year). Table has about 15
> million rows in it currently. I tried the same type of thing with a
> non-clustered index columns on a different table and ended up with less
> inspiring results. I also tried it on a couple non-indexed columns, and
> confirmed (for myself at least) that there was no point to that test
> It seems like >= AND <= in place of = on the clustered index columns made
> a significant difference for me. It would be cool if the Query Optimizer
> would automatically take care of this conversion for me internally in this
> situation. I would think SQL Server would recognize the >= AND <= and the
> = as being equivalent and handle that when it generated the plan.
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:42602F3B.9612B18B@.toomuchspamalready.nl...
>
Subscribe to:
Posts (Atom)