Query problem - serious performance issues. PLEASE HELP 
Author Message
 Query problem - serious performance issues. PLEASE HELP

Hello everybody,

I posted this question a while ago, and I still have serious problem
with this query:

select distinct c.Prefix, c.FirstName, c.MiddleName, c.LastName,
c.industry, c.city, c.memberstatus, c.record_id
from contacts c inner join industry_profiles ip on
c.record_id=ip.contact_fk
where c.memberstatus='IA'
  and c.FirstName Like '%%'
  and c.LastName Like '%%'
  and c.Industry Like '%%'
  and c.city Like '%%'
  and (isnull(ip.product_and_services, ' ') Like '%composite%'
   or isnull(ip.nature_of_business, ' ') Like '%composite%')
order by c.lastname

After trying out the recommendation (as suggested by one of the user),
to create indecies: Contacts table has a clustered index on
memberstatus, two non-clusterd indecies: one on LastName and other on
Industry. There is also a unique index (by default) on record_id, as
its the primary key.

The problem is that when this query is submitted to the SQL server
from IIS, its brings the server down to a crawl - 100% CPU utilization
(same result from query analyzer).

However, INTERESTINGLY, when I run this query in a UNION (the
difference is that the memberstatus is different for each query and
each is joining to a diff table, based on the status. It doesn't chew
up any CPU.

There are around 7700 records, ONLY, in the contact table.

IMPORTANT: If I replace %% with %, it works. WHY is that? The reason I
have %% is that I am always doing a wild card (IN WORD) search from
IIS. So, if a user does not specify a value in the asp form, it
replaces it with %%

Please HELP; Its a simple query and I can't figure out why this is
happening.



Tue, 20 Jul 2004 05:20:55 GMT
 Query problem - serious performance issues. PLEASE HELP



Quote:
> Hello everybody,

> I posted this question a while ago, and I still have serious problem
> with this query:

> select distinct c.Prefix, c.FirstName, c.MiddleName, c.LastName,
> c.industry, c.city, c.memberstatus, c.record_id
> from contacts c inner join industry_profiles ip on
> c.record_id=ip.contact_fk
> where c.memberstatus='IA'
>   and c.FirstName Like '%%'
>   and c.LastName Like '%%'
>   and c.Industry Like '%%'
>   and c.city Like '%%'
>   and (isnull(ip.product_and_services, ' ') Like '%composite%'
>    or isnull(ip.nature_of_business, ' ') Like '%composite%')
> order by c.lastname

> After trying out the recommendation (as suggested by one of the user),
> to create indecies: Contacts table has a clustered index on
> memberstatus, two non-clusterd indecies: one on LastName and other on
> Industry. There is also a unique index (by default) on record_id, as
> its the primary key.

> The problem is that when this query is submitted to the SQL server
> from IIS, its brings the server down to a crawl - 100% CPU utilization
> (same result from query analyzer).

> However, INTERESTINGLY, when I run this query in a UNION (the
> difference is that the memberstatus is different for each query and
> each is joining to a diff table, based on the status. It doesn't chew
> up any CPU.

> There are around 7700 records, ONLY, in the contact table.

btw, how many are in the industy_profiles table ?

Quote:

> IMPORTANT: If I replace %% with %, it works. WHY is that? The reason I
> have %% is that I am always doing a wild card (IN WORD) search from
> IIS. So, if a user does not specify a value in the asp form, it
> replaces it with %%

if the user doesn't enter anything, don't search for it ! you'll have to
build your sql statement.

strTmp=trim(request.form("blabla"))
if  strTmp<> "" then
    sqlwhere=sqlwhere & " and c.firstname like '%" & strTmp & "%'"
end if
and so on for the other fields. along those lines you'll have to go. there
are examples in this NG

something else.   LIKE '%somestring%' tends to be slow. basically the index
is useless. how about searching LIKE 'somestring%' by default and let the
user know that he can enter a % at the beginning of the input-box if he
wishes ?

Quote:
>   and (isnull(ip.product_and_services, ' ')

i'd say get rid of the nulls... don't allow nulls in the table and use ''
for char types and 0 for numeric types as default. nulls are a pain in a*

- Show quoted text -

Quote:
> Please HELP; Its a simple query and I can't figure out why this is
> happening.



Tue, 20 Jul 2004 05:54:06 GMT
 Query problem - serious performance issues. PLEASE HELP

Quote:

> However, INTERESTINGLY, when I run this query in a UNION (the
> difference is that the memberstatus is different for each query and
> each is joining to a diff table, based on the status. It doesn't chew
> up any CPU.

Since SQL Server has a cost-based optimizer small changes can give radically
different results, depending on which plan which is chosen.

Quote:
> There are around 7700 records, ONLY, in the contact table.

And how many are there in the industry_profiles table?

Quote:
> IMPORTANT: If I replace %% with %, it works. WHY is that? The reason I
> have %% is that I am always doing a wild card (IN WORD) search from
> IIS. So, if a user does not specify a value in the asp form, it
> replaces it with %%

Now that's cute. But it appears that you have a workaround.

I'm afraid that there is not help that I can offer. To say anyhing
sensisble about this I would need to have the CREATE TABLE and CREATE INDEX
statements for the two tables, as well as the data. And while 7000 rows
is not that much, it is still too much to post here.

I would suggest that you open a case with Microsoft support. There might
be an optimizer bug involved here, but this is far from certain. If it
is deemed to be a bug, you will get your expenses back.

Then again, the strategy suggested by Beat Richi might be a better
approach altogether.

--
Erland Sommarskog, Abaris AB

SQL Server MVP



Tue, 20 Jul 2004 07:05:34 GMT
 Query problem - serious performance issues. PLEASE HELP
Thanks Erland and Beat for your suggestions

The industry_profile table, as you requested, has only 1280 rows. The
two fields (product_and_services AND nature_of_business) in the where
clause are ntext. There are no indices on this table.

As Erland pointed out; Yes, I do have a work around. But, it still
doesn't really make sence why it should not be working with %%.

However, I'll try calling MS if I can't resolve it with differnt
scenarios.

Thanks,

Asim

Quote:


> > However, INTERESTINGLY, when I run this query in a UNION (the
> > difference is that the memberstatus is different for each query and
> > each is joining to a diff table, based on the status. It doesn't chew
> > up any CPU.

> Since SQL Server has a cost-based optimizer small changes can give radically
> different results, depending on which plan which is chosen.

> > There are around 7700 records, ONLY, in the contact table.

> And how many are there in the industry_profiles table?

> > IMPORTANT: If I replace %% with %, it works. WHY is that? The reason I
> > have %% is that I am always doing a wild card (IN WORD) search from
> > IIS. So, if a user does not specify a value in the asp form, it
> > replaces it with %%

> Now that's cute. But it appears that you have a workaround.

> I'm afraid that there is not help that I can offer. To say anyhing
> sensisble about this I would need to have the CREATE TABLE and CREATE INDEX
> statements for the two tables, as well as the data. And while 7000 rows
> is not that much, it is still too much to post here.

> I would suggest that you open a case with Microsoft support. There might
> be an optimizer bug involved here, but this is far from certain. If it
> is deemed to be a bug, you will get your expenses back.

> Then again, the strategy suggested by Beat Richi might be a better
> approach altogether.



Tue, 20 Jul 2004 22:53:00 GMT
 Query problem - serious performance issues. PLEASE HELP

Quote:
> doesn't really make sence why it should not be working with %%.

again, why would you ? try to narrow down the performance hogs. i wouldn't
search   field='%%'   doesn't make sense. have you tried without those ??
maybe it IS the problem.

good luck with the Microsoft Support



Wed, 21 Jul 2004 00:11:19 GMT
 Query problem - serious performance issues. PLEASE HELP

you do have an index of some sort on industry profile, field
contact_fk?

you might try giving the compiler a hint to use the index on
memberstatus - I could see where it might look over all the searches,
and decide to just do a sequential scan. But 7700 records shouldn't
take very long to scan.

good luck,
Doug Miller



Wed, 21 Jul 2004 00:53:47 GMT
 Query problem - serious performance issues. PLEASE HELP

Quote:

> The industry_profile table, as you requested, has only 1280 rows. The
> two fields (product_and_services AND nature_of_business) in the where
> clause are ntext. There are no indices on this table.

1280 rows may not sound much, but how long are those ntext columns
in average?

Quote:
> As Erland pointed out; Yes, I do have a work around. But, it still
> doesn't really make sence why it should not be working with %%.

I don't know about the optimizer's strategy in this case. It may be
a bug, please note the word "may".

--
Erland Sommarskog, Abaris AB

SQL Server MVP



Thu, 22 Jul 2004 05:45:07 GMT
 Query problem - serious performance issues. PLEASE HELP

Quote:

> Hello everybody,

> I posted this question a while ago, and I still have serious problem
> with this query:

> select distinct c.Prefix, c.FirstName, c.MiddleName, c.LastName,
> c.industry, c.city, c.memberstatus, c.record_id
> from contacts c inner join industry_profiles ip on
> c.record_id=ip.contact_fk
> where c.memberstatus='IA'
>   and c.FirstName Like '%%'
>   and c.LastName Like '%%'
>   and c.Industry Like '%%'
>   and c.city Like '%%'
>   and (isnull(ip.product_and_services, ' ') Like '%composite%'
>    or isnull(ip.nature_of_business, ' ') Like '%composite%')
> order by c.lastname

> After trying out the recommendation (as suggested by one of the user),
> to create indecies: Contacts table has a clustered index on
> memberstatus, two non-clusterd indecies: one on LastName and other on
> Industry. There is also a unique index (by default) on record_id, as
> its the primary key.

> The problem is that when this query is submitted to the SQL server
> from IIS, its brings the server down to a crawl - 100% CPU utilization
> (same result from query analyzer).

> However, INTERESTINGLY, when I run this query in a UNION (the
> difference is that the memberstatus is different for each query and
> each is joining to a diff table, based on the status. It doesn't chew
> up any CPU.

> There are around 7700 records, ONLY, in the contact table.

> IMPORTANT: If I replace %% with %, it works. WHY is that? The reason I
> have %% is that I am always doing a wild card (IN WORD) search from
> IIS. So, if a user does not specify a value in the asp form, it
> replaces it with %%

> Please HELP; Its a simple query and I can't figure out why this is
> happening.

Asim,
I suggest that you use Full Text Indexing on the tables because SQL
Server can not use indexes when you use wild card characters to start
a search (eg: LIKE '%composite%'). Instead it has to do a table scan,
hence your query runs increasingly slower as the table gains more
rows. Full text indexing solves this problem.

Good Luck
Dave Hart



Fri, 23 Jul 2004 01:21:01 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Urgent help - Very serious database performance issue

2. ODBC Timeout Issues - serious help needed please

3. query problem in 7.2.1: serious planner issue

4. URGENT please - Serious Query problem

5. URGENT please - Serious Query problem

6. Serious performance issues

7. Serious problem -- please help!

8. PLEASE HELP, SERIOUS PROBLEM

9. Performance issue - Please help!

10. Performance Issue - Please Help!

11. SQL Server Performance Issue Please help !

12. Help Please, SQL 6.5 NT 4.0 SP4 performance issues


 
Powered by phpBB® Forum Software