• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

query performance

using SQL server 2000
the table has 120,000 rows, and it has columns like as fullName, company, phone, fax, status

the query will be like below, note that 'FirstName', 'LastName' can be replaced to any string, 'myPhone' can be any 10 digit phone and 'myFax' can be any 10 digit fax number.

select fullName, phone,fax
from table
where status='Active'
and phone is not null
and len(phone)>0
and (company like 'FirstName%'
       or company like 'LastName%'
       or fullName like '%FirstName%'
       or fullName like '%LastName%'
       or phone = 'myPhone'
       or acfax = 'myFax')

basically given first name, last name, phone, and fax,
I need to find out any rows that
either phone is the same,
or fax is the same,
or company name starts with first name or starts with last name
or fullName contains either first name or last name

the problem is it is really slow since doing a full table scan, is there any way to improve the performance like setting some index?

thanks in advance

0
frrf
Asked:
frrf
  • 7
  • 4
  • 2
  • +2
3 Solutions
 
rafranciscoCommented:
Since your query is in the form of LIKE '%Text%', the indexes will not be used and a full table scan will be performed.  An alternative for you is the use of Full Text Search.
0
 
frrfAuthor Commented:
is it like using 'contains' instead of 'like'?
0
 
rafranciscoCommented:
Yes, that's the one.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
frrfAuthor Commented:
just to verify with you, the new sql will look like

select fullName, phone,fax
from table
where status='Active'
and phone is not null
and len(phone)>0
and (CONTAINS(ProductName, 'FirstName' OR 'LastName')
       or CONTAINS(fullName, 'FirstName')
       or CONTAINS(fullName, 'LastName')
       or phone = 'myPhone'
       or acfax = 'myFax')
0
 
rafranciscoCommented:
Yes, that's how it will look like.   I believe the ProductName should be CompanyName.  Also, it's not just a matter of changing the SQL statement.  You have to create a full-text indexing catalog for your database.
0
 
DanRollinsCommented:
Since all of your criteria are "AND" ed, you can minimize the number of rows scanned by creating an index on some of the fields.   I'd make an index on status and phone:

CREATE INDEX MyTable001 ON MyTable
(
    status, phone
)

The query optimizer will automatically force SQL Server to look at that index to create a subset of the rows to search for the other criteria.   A simple example:  If only 20 records have status='Active', then only 20 rows will need to be processed for matching the name criteria.

-- Dan
0
 
frrfAuthor Commented:
so what I need to do first is

EXEC sp_fulltext_table 'tableName', 'create', 'FullTextCategoryName', 'PK_tableName'
--Add some columns
EXEC sp_fulltext_column 'tableName','company','add'
EXEC sp_fulltext_column 'tableName','fullName','add'
--Activate the index
EXEC sp_fulltext_table 'tableName','activate'

and how can I tell my table is "enabled for full-text support"?
0
 
rafranciscoCommented:
Try right-clickin on your table in Enterprise Manager and if the Edit Full-Text Indexing is enabled, then your table is enabled for full-text support.  Another way is to go to the Full-Text Catalogs section of your database in Enterprise Manager.
0
 
frrfAuthor Commented:
Hi Dan
I didn't know that the SQL server will do this trick,

so if I have index on status and phone, using query below
select fullname, status, phone
where status ='1'
and phone ='5555555'
and fullname like '%JOE%'

Is it true that SQL server will get the subset of data from the below first
select fullname, status, phone
where status ='1'
and phone ='5555555'

then do a fullscan on the data set based on
and fullname like '%JOE%'

??

thanks
0
 
Scott PletcherSenior DBACommented:
"Phone" should definitely be in an index ... but since it can contain NULLs you won't be able to make it a unique index.

My guess would be that almost all records would be "Active" so an index on status won't help.  Btw, if you do want to index status, you should change it to a SMALLINT to reduce the size.  It could then be easily added to the phone (or other) index.

"Company" should have its own index.  

"Acfax" should also probably have its own index.

The difficult one is "fullName".  Probably best is to build a fulltext index on that column, but you could try a regular index and move it to the *last* column to be tested.

This follows the general rule that for ANDs, the "most restrictive" (least often true) condition should be first; for ORs, the least restrictive/least overhead (most often true) should be first.

For example:


WHERE LEN(phone) > 0
AND status='Active'
AND ((phone = 'myPhone'
   OR acfax = 'myFax'    
   OR company LIKE 'FirstName%'
   OR company LIKE 'LastName%')
   OR fullName LIKE '%FirstName%'
   OR fullName LIKE '%LastName%' )


I removed the "phone IS NOT NULL" because "LEN(phone) > 0" shouldn't be true unless phone is NOT NULL anyway.
The "extra" parens around the first set of ORs are to try to force SQL to first do the comparisons that can be satisified by direct index lookup; they don't affect the overall matching logic.  The index scan requirement (on fullname) is moved to last so that it only has to be done for a row that didn't match one of the earlier criteria.  That is, when the optimizer sees this:
WHERE (condA OR condB OR condC OR condD OR condE)
it is smart enough to "short-circuit" future comparisons if, for example, condA is true; since the others are OR'ed, once the first one is true, there is no need to do the rest, the final result will always be true.  The optimizer "understands" that (at least theoretically, if not in every specific case) so we should try to take advantage of that.
0
 
frrfAuthor Commented:
cool
I am running this query as in-line sql in ASP, not in stored procedure
using RS.Open sql,con,adOpenStatic,adLockReadOnly
does the optimizer work this way?
0
 
frrfAuthor Commented:
I right-clickin on the table, the Full-text Index Table option is disabled
I go to Full-Text Catelogs, right-click on the right pane, the first 4 options like "New Full-Text Index Catalog ..." are disabled
0
 
DanRollinsCommented:
The query optimization occrs at the backend.  Use Query Analyzer to test various scenarios with/without indexes.  In general, any performance gains seen in QA are going to be valid for the same SQL statements passed via ADO or ODBC from ASP or any other front-end system.
0
 
Anthony PerkinsCommented:
>>I right-clickin on the table, the Full-text Index Table option is disabled<<
That means:
1. Your edition of SQL Server does not support Full-Text Search
2. The Full-Text Search service is not started
3. The Full-Text Search components are not installed.
0
 
frrfAuthor Commented:
I am not going to turn on/install Full-Text Search service since I don't want to modify the production evironment.
Therefore I'll give it a try on the query optimizatoin.
Thanks for all your advice.

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now