Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_name')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
--
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:
> Have you checked the fragentation of the indexes on the table(s) that the sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_name')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:
> > Has anyone seem this before. I've actually caught it once but I think
> > it's happening multiple times. I have a partitioned view and
> > performance is generally really good. However, every once in a great
> > while we'll see a stored procedure "hang" and just run for hours.
> > Doing much digging the only abnormalities I found where these: 1) the
> > server was requesting a large amount of PAGE and KEY locks (meaning an
> > index lock was requested. Checking the locks for that SPID confirmed
> > this) 2) At one time during testing I saw that the execution plan
> > called for an estimated 1 billion rows to be returned. Amazing since
> > the underlying table only have 400,000 records. Now, the theory is
> > this: Since I'm using a partitioned view and Microsoft claims that
> > they retain no histogram (statistical data from which to build an
> > "intelligent" execution plan) somehow it's figuring this 1 billion
> > estimated row count again and is entering some kind of horrendous
> > loop. Any thoughts?|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> > Has anyone seem this before. I've actually caught it once but I think
> > it's happening multiple times. I have a partitioned view and
> > performance is generally really good. However, every once in a great
> > while we'll see a stored procedure "hang" and just run for hours.
> > Doing much digging the only abnormalities I found where these: 1) the
> > server was requesting a large amount of PAGE and KEY locks (meaning an
> > index lock was requested. Checking the locks for that SPID confirmed
> > this) 2) At one time during testing I saw that the execution plan
> > called for an estimated 1 billion rows to be returned. Amazing since
> > the underlying table only have 400,000 records. Now, the theory is
> > this: Since I'm using a partitioned view and Microsoft claims that
> > they retain no histogram (statistical data from which to build an
> > "intelligent" execution plan) somehow it's figuring this 1 billion
> > estimated row count again and is entering some kind of horrendous
> > loop. Any thoughts?

Query plan miscalculates row count causing system slow down?

Has anyone seem this before. I've actually caught it once but I think
it's happening multiple times. I have a partitioned view and
performance is generally really good. However, every once in a great
while we'll see a stored procedure "hang" and just run for hours.
Doing much digging the only abnormalities I found where these: 1) the
server was requesting a large amount of PAGE and KEY locks (meaning an
index lock was requested. Checking the locks for that SPID confirmed
this) 2) At one time during testing I saw that the execution plan
called for an estimated 1 billion rows to be returned. Amazing since
the underlying table only have 400,000 records. Now, the theory is
this: Since I'm using a partitioned view and Microsoft claims that
they retain no histogram (statistical data from which to build an
"intelligent" execution plan) somehow it's figuring this 1 billion
estimated row count again and is entering some kind of horrendous
loop. Any thoughts?Have you checked the fragentation of the indexes on the table(s) that the sp
uses?
dbcc showcontig ('table_name').
If the scan desity is low, you might want to do a defrag.
dbcc indexdefrag ('database_name','table_name','index_nam
e')
or in 2005
alter index <index_name> | ALL
rebuild with online = ON
on <table_name>
Sometimes rebuilding is better because is certain cases, indexdefrag doesn't
make the all of index pages contiguous.
--
MG
"tmorris" wrote:

> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||Look in BOL for update statistics
TheSQLGuru
President
Indicium Resources, Inc.
"tmorris" <TheRealPawn@.gmail.com> wrote in message
news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
> Has anyone seem this before. I've actually caught it once but I think
> it's happening multiple times. I have a partitioned view and
> performance is generally really good. However, every once in a great
> while we'll see a stored procedure "hang" and just run for hours.
> Doing much digging the only abnormalities I found where these: 1) the
> server was requesting a large amount of PAGE and KEY locks (meaning an
> index lock was requested. Checking the locks for that SPID confirmed
> this) 2) At one time during testing I saw that the execution plan
> called for an estimated 1 billion rows to be returned. Amazing since
> the underlying table only have 400,000 records. Now, the theory is
> this: Since I'm using a partitioned view and Microsoft claims that
> they retain no histogram (statistical data from which to build an
> "intelligent" execution plan) somehow it's figuring this 1 billion
> estimated row count again and is entering some kind of horrendous
> loop. Any thoughts?
>|||DBCC dbreindex is preformed on all tables once a week as a form of
matanance.
We've concided doing this again midweek or one of the other processes
that update statistics on columns and indexes in SQL 2000. However, in
the test enviroment it doesn't seem to have any impact one way or
another. Like I, and microsoft said, partitioned views don't contain
any statistical history from which to base an exection plan. Thanks
for the suggestion though. I'm looking more of someone to confirm my
theory or say it's complete bunk.
Now, the theory is this: Since I'm using a partitioned view and
Microsoft claims that they retain no histogram (statistical data from
which to build an "intelligent" execution plan) somehow it's figuring
this 1 billion estimated row count again and is entering some kind of
horrendous loop. Any thoughts?
On Apr 23, 11:26 am, Hurme <michael.ge...@.thomson.com> wrote:[vbcol=seagreen]
> Have you checked the fragentation of the indexes on the table(s) that the
sp
> uses?
> dbcc showcontig ('table_name').
> If the scan desity is low, you might want to do a defrag.
> dbcc indexdefrag ('database_name','table_name','index_nam
e')
> or in 2005
> alter index <index_name> | ALL
> rebuild with online = ON
> on <table_name>
> Sometimes rebuilding is better because is certain cases, indexdefrag doesn
't
> make the all of index pages contiguous.
> --
> MG
> "tmorris" wrote:|||We Run DBCC ReIndex on all tables as a weekly maintenance job. And we
looked as doing update statistics 25% midweek but it seemed to have no
effect in our test environment
On Apr 23, 12:42 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Look in BOL for update statistics
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "tmorris" <TheRealP...@.gmail.com> wrote in message
> news:1177340474.292787.166810@.q75g2000hsh.googlegroups.com...
>

Query Performance

I have 2 tables, Make and Model where Make is the master table and Model has
a reference of make table by Make_Id since there can be multiple models for a
single make. Now, i have 4 SQL's below and i want to know which SQL should
perform better.
select make.make_name, model.model_name
from make, model
where make.make_id = 1000
and make.make_id = model.make_id
select make.make_name, model.model_name
from make, model
where make.make_id = 1000
and model.make_id = make.make_id
select make.make_name, model.model_name
from make, model
where make.make_id = model.make_id
and make.make_id = 1000
select make.make_name, model.model_name
from make, model
where model.make_id = make.make_id
and make.make_id = 1000
Thanks,
Moin
On Wed, 20 Apr 2005 12:14:02 -0700, MoinJ wrote:

>I have 2 tables, Make and Model where Make is the master table and Model has
>a reference of make table by Make_Id since there can be multiple models for a
>single make. Now, i have 4 SQL's below and i want to know which SQL should
>perform better.
>select make.make_name, model.model_name
>from make, model
>where make.make_id = 1000
>and make.make_id = model.make_id
>
>select make.make_name, model.model_name
>from make, model
>where make.make_id = 1000
>and model.make_id = make.make_id
>
>select make.make_name, model.model_name
>from make, model
>where make.make_id = model.make_id
>and make.make_id = 1000
>
>select make.make_name, model.model_name
>from make, model
>where model.make_id = make.make_id
>and make.make_id = 1000
>Thanks,
>Moin
Hi Moin,
The usual answer to "which of these queries performas better" is to test
them all, in _your_ database, on _your_ tables with _your_ data and on
_your_ hardware. However, in this case it's safe to say that you won't
see any difference. The query optimizer is free to reshuffle the
expressions in the where clause (as long as it won't effect the end
result), so you can expect all four queries to result in the exact same
execution plan.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- Steve
Nope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegro ups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>
|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:

> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>
|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.
|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegro ups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- SteveNope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:
> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>

Query multiple fields at once??

I have a table with 10 fields. I want to select any row that contains
the word "bob" in any one of the 10 fields. I've been writing this
query by checking each field individually connected with an OR
statement. Such as...
Select * from tablename where (column1 like '%bob%') or (column2 like
'%bob%') or ... and so on.
Is there an easier way to search all fields/columns in a more simple
SQL query?
The concept of ... Select * from tablename where (* like '%bob%')
?
Thanks,
- SteveNope. Your method of using the [OR] is the only way.
That you have to search multiple columns hints of denormalized data. Perhaps
this type of operation would be simpler if the table schema was
reconsidered.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||Hi Stephen
This sounds like your design may want further normalisation?
I guess you could do something like
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
'|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
like '%bob%'
so long as you are not looking for something containing the delimiter! I am
not sure how that would perform. Other possible options might be:
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column1 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column2 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column3 like '%bob%'
UNION
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
from tablename
where column4 like '%bob%'
etc ...
If you know that only one column contained the search string then you could
use UNION ALL in the above method.
or
Select column1, column2, column3, column4, column5, column6, column7,
column8, column9, column10
FROM ( Select column1 AS Searchcolumn, column1, column2, column3, column4,
column5, column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column2 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column3 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column4 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename
UNION ALL
Select column5 AS Searchcolumn, column1, column2, column3, column4, column5,
column6, column7, column8, column9, column10
from tablename ) A
where Searchcolumn like '%bob%'
HTH
John
"kennedystephen" wrote:

> I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>|||And if it were equality instead of like, you could reverse the clause, e.g.
WHERE 'bob' IN (column1, column2, column3, ...)
But I agree that the design does not seem fundamentally sound to me.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0256D861-C01E-4766-BE20-09ED5790337B@.microsoft.com...
> Hi Stephen
> This sounds like your design may want further normalisation?
> I guess you could do something like
> Select column1, column2, column3, column4, column5, column6, column7,
> column8, column9, column10
> from tablename
> where ISNULL(column1,'') + '|' + ISNULL(column2,'') + '|' +
> ISNULL(column3,'') + '|' + ISNULL(column4,'') + '|' + ISNULL(column5,'') +
> '|' + ISNULL(column6,'') + '|' + ISNULL(column7,'') + '|' +
> ISNULL(column8,'') + '|'+ ISNULL(column9,'') + '|' + ISNULL(column10,'')
> like '%bob%'
> so long as you are not looking for something containing the delimiter! I
> am
> not sure how that would perform.|||stephen,
If this is a common requirement for your applications, then you might want
to consider full text indexing and searches. BOL can get you started.
-- Bill
"kennedystephen" <skennedy@.oaconsulting.com> wrote in message
news:1162572395.398641.35870@.h48g2000cwc.googlegroups.com...
>I have a table with 10 fields. I want to select any row that contains
> the word "bob" in any one of the 10 fields. I've been writing this
> query by checking each field individually connected with an OR
> statement. Such as...
> Select * from tablename where (column1 like '%bob%') or (column2 like
> '%bob%') or ... and so on.
> Is there an easier way to search all fields/columns in a more simple
> SQL query?
> The concept of ... Select * from tablename where (* like '%bob%')
> ?
> Thanks,
> - Steve
>

query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-j
The error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***

query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.codecomments.com ***

query multiple databases on different servers

I want to query from a SQL Server 2000 database and put the results into a
SQL Server 2005 database located on another server. I thought that I had
used the following syntax in the past (without added any linked servers):
INSERT INTO houston.mytestdb.dbo.memo
SELECT * FROM tennessee.mytestdb.dbo.memo
But I get an error: Could not find server 'tennessee' in sysservers. Execute
sp_addlinkedserver...
If I do add this server using the stored procedure I get the following
errors when trying to execute the sql:
Server: Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Server: Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI" for linked server "tennessee" returned message
"Communication link failure".
What's that all about?
Anyway I can avoid adding linked servers in the first place?
Thanks!
-jThe error you are getting is that the link is trying to use integrated
security and there is no context being passed. Could be a lot of reasons
- firewall, no trust between domains, ...
Did you set you set up the linked server to use integrated security?
You will also probably need to make sure that dtc is started on both
m/c's.
Do you really want to own a remote table from the local server? It's
usually better to populate from the machine where the table resides
calling a stored procedure to extract.
*** Sent via Developersdex http://www.developersdex.com ***

Query multiple database tables

Sql2005? -NEW to SQL. Have a database which creates tables basically named the same thing except the date. i.e. dbo.table05012006, dbo.table05022006. I need to query a table if the date is = yesterday. I am searching for a way to do this everyday dynamically. Is this even possible?

If you are doing this in SSIS then look up property expressions in Books Online. You can use this to dynamically build the SELECT statement, generating the table name based on a date, or today's date - 1.

Having tables in this way seems a bit strange, certainly at such a low level of day. You may want to investigate partitioned views, or partitioned tables. These concepts present a uniform view a to something like a SELECT statement, but you can still organise the data into tables or partitions respectively.

Query multiple cubes in a single MDX?

I am wondering if there is a way to query more than one cube in a single mdx statement. Here's the deal - I have a cube that has a measure group that is a count of invoices, and another cube that has additional measures / dimensions of interest. The 2nd cube satisfies everything we need, except for the count of invoices. Is there a way to query the invoice cube as a calculated measure in the mdx that is querying the 2nd cube? It would be easy to add the invoice measure group to the 2nd cube, but that would increase the processing time way too much (unfortunately we have to do a full process). Here's a stupid question - would adding the measure group to another cube in the database cause two physical reads of the table? I am guessing yes, but you never know.

I read about linked dimensions / measures, but it looks like they are for measures / dimsneions in a different AS database. There probably is a simple solution for this, but I am still fairly new to AS / mdx.

thank you in advance,

John Hennesey

Hello John! I think that the best solution is to have both measure groups in the same cube, even if processing time can increase.

I have not seen an MDX select with two cubes in the From clause. Subcube statements also refer to the same cube. I am not on the top 10 MDX expert list but I think that MDX will not help.

Can you not add the invoice number to the second measure group an aggregate it with count?

Kind Regards

Thomas Ivarsson

|||

Yeah, I did some tests this morning to see how long it takes to query the invoice dimension. Look like this is the way to go!

FYI - I did find a LookupCube function, but I think this is a last resort method - much slower than designing it properly from scratch.

Thanks for your quick response -

John