Query problem - serious performance issues. PLEASE HELP
| Author |
Message |
|
Asim Chowdh
#1 / 8
|
 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 |
|
 |
|
Beat Richl
#2 / 8
|
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* 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 |
|
|
|
Erland Sommarsko
#3 / 8
|
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 |
|
|
|
Asim Chowdh
#4 / 8
|
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 |
|
|
|
Beat Richl
#5 / 8
|
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 |
|
|
|
Doug Mill
#6 / 8
|
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 |
|
|
|
Erland Sommarsko
#7 / 8
|
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 |
|
|
|
David Ha
#8 / 8
|
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 |
|
| |
|
