Is there a better way? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is there a better way?

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2004-09-20 : 12:04:42

This is a sample query generated by an application I run. It simply searches for one or more text strings in several fields in a join.

SELECT Tasks.TaskID,
Tasks.Customer,
Tasks.TaskType,
TaskDates.Resource,
Tasks.TaskDescription,
TaskDates.Status,
TaskDates.DateValue
FROM Tasks INNER JOIN TaskDates ON Tasks.TaskID = TaskDates.TaskID WHERE (Tasks.TaskDescription LIKE '%emergency%'
OR Tasks.Customer LIKE '%emergency%'
OR Tasks.JobNumber LIKE '%emergency%'
OR Tasks.TaskType LIKE '%emergency%'
OR Tasks.CallerName LIKE '%emergency%'
OR Tasks.CallCloseComments LIKE '%emergency%'
OR TaskDates.Status LIKE '%emergency%'
OR TaskDates.Resource LIKE '%emergency%')
AND (Tasks.TaskDescription LIKE '%PB-3175%'
OR Tasks.Customer LIKE '%PB-3175%'
OR Tasks.JobNumber LIKE '%PB-3175%'
OR Tasks.TaskType LIKE '%PB-3175%'
OR Tasks.CallerName LIKE '%PB-3175%'
OR Tasks.CallCloseComments LIKE '%PB-3175%'
OR TaskDates.Status LIKE '%PB-3175%'
OR TaskDates.Resource LIKE '%PB-3175%')
AND (Tasks.TaskDescription LIKE '%complete%'
OR Tasks.Customer LIKE '%complete%'
OR Tasks.JobNumber LIKE '%complete%'
OR Tasks.TaskType LIKE '%complete%'
OR Tasks.CallerName LIKE '%complete%'
OR Tasks.CallCloseComments LIKE '%complete%'
OR TaskDates.Status LIKE '%complete%'
OR TaskDates.Resource LIKE '%complete%')
ORDER BY TaskDates.DateValue DESC

I'm wondering if there's a better way to build these query strings to improve performance.

Tasks table ddl:
CREATE TABLE [Tasks] (
[TaskID] [int] IDENTITY (1, 1) NOT NULL ,
[Sent] [bit] NOT NULL CONSTRAINT [DF__TemporaryU__Sent__2116E6DF] DEFAULT (0),
[Confirmed] [bit] NOT NULL CONSTRAINT [DF__Temporary__Confi__220B0B18] DEFAULT (0),
[Customer] [nvarchar] (50) NULL ,
[CallerName] [nvarchar] (50) NULL ,
[CallerPhone] [nvarchar] (50) NULL ,
[TaskType] [nvarchar] (50) NULL ,
[TaskDescription] [nvarchar] (255) NULL ,
[JobNumber] [nvarchar] (50) NULL ,
[StartDate] [datetime] NULL ,
[CallCloseComments] [nvarchar] (255) NULL ,
[IsPM] [bit] NOT NULL CONSTRAINT [DF__TemporaryU__IsPM__22FF2F51] DEFAULT (0),
[DateEntered] [datetime] NULL ,
[TimeEntered] [datetime] NULL ,
[EnteredBy] [nvarchar] (50) NULL ,
[IsTSP] [bit] NOT NULL CONSTRAINT [DF__Temporary__IsTSP__23F3538A] DEFAULT (0),
[InitAssigned] [nvarchar] (50) NULL ,
[LaborType] [nvarchar] (50) NULL ,
[Printed] [bit] NOT NULL CONSTRAINT [DF__Temporary__Print__24E777C3] DEFAULT (0),
[SubLocation] [nvarchar] (50) NULL ,
[CreatedBy] [nvarchar] (50) NULL ,
[SystemType] [nvarchar] (50) NULL ,
CONSTRAINT [aaaaaTasks_PK] PRIMARY KEY NONCLUSTERED
(
[TaskID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

TaskDates table ddl:
CREATE TABLE [TaskDates] (
[DateValue] [datetime] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[Resource] [nvarchar] (50) NULL ,
[Status] [nvarchar] (50) NULL ,
[Confirmed] [bit] NOT NULL CONSTRAINT [DF__Temporary__Confi__1699586C] DEFAULT (0),
[CallFirstRequired] [bit] NOT NULL CONSTRAINT [DF__Temporary__CallF__178D7CA5] DEFAULT (0),
[Locked] [bit] NOT NULL CONSTRAINT [DF__Temporary__Locke__1881A0DE] DEFAULT (0),
[Private] [bit] NOT NULL CONSTRAINT [DF__Temporary__Priva__1975C517] DEFAULT (0),
[Duration] [real] NULL CONSTRAINT [DF__Temporary__Durat__1A69E950] DEFAULT (0),
[OTDuration] [real] NULL CONSTRAINT [DF__Temporary__OTDur__1B5E0D89] DEFAULT (0),
[Customer] [nvarchar] (255) NULL ,
[TaskID] [int] NULL CONSTRAINT [DF__Temporary__TaskI__1C5231C2] DEFAULT (0),
[Type] [nvarchar] (50) NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Owner] [nvarchar] (50) NULL ,
[LastSyncTime] [datetime] NULL ,
[DateName] [varchar] (50) NULL ,
CONSTRAINT [aaaaaTaskDates_PK] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

Thanks in advance.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-20 : 12:10:39

have you tried Full text catalogs??
if it's not an option see
http://www.sqlteam.com/item.asp?ItemID=1876

Go with the flow & have fun! Else fight the flow

sbt1
Yak Posting Veteran

89 Posts

Posted - 2004-09-20 : 12:38:28

While that looks like a good idea, it won't work for my application. Since my app is designed to work on SQL Server, MSDE, MySQL, and other similar databases it needs to be pretty generic SQL.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 04:17:39

well. then i guess you're stuck with that... generic sql is always a pain when it comes to searching...
maybe someone else knows a better way.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 10:14:29

Well it would probably be better to split it into several searches maybe with a Union (not sure if that is generic enough sql..)

[musing]
Maybe if you could get rid of some of the ORs... something like

...
	((Tasks.TaskDescription+'|'+Tasks.Customer+'|'+Tasks.JobNumber+'|'+Tasks.TaskType+'|'+
	Tasks.CallerName+'|'+Tasks.CallCloseComments+'|'+TaskDates.Status+'|'+TaskDates.Resource) LIKE '%PB-3175%')
...

instead of:

...
	(Tasks.TaskDescription LIKE '%PB-3175%' 
	OR Tasks.Customer LIKE '%PB-3175%' 
	OR Tasks.JobNumber LIKE '%PB-3175%' 
	OR Tasks.TaskType LIKE '%PB-3175%' 
	OR Tasks.CallerName LIKE '%PB-3175%' 
	OR Tasks.CallCloseComments LIKE '%PB-3175%' 
	OR TaskDates.Status LIKE '%PB-3175%' 
	OR TaskDates.Resource LIKE '%PB-3175%')
...

[/musing]

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 10:38:15

corey could you post a sample of that 'or' of yours in northwind or pubs, because all i'm getting when trying this is an error...

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 10:49:58

Does this work for you? I realized I don't actually have northwind on this server... oh well.


Declare @str1 varchar(100),
	@str2 varchar(100),
	@str3 varchar(100),
	@str4 varchar(100)

Set @str1 = 'York'
Set @str2 = 'TX'
Set @str3 = 'Lake'
Set @str4 = 'Ge'

Select * From pubs.dbo.publishers Where (isnull(pub_name,'')+'|'+isnull(city,'')+'|'+isnull(state,'')+'|'+isnull(country,'')) like '%'+@str1+'%'
Union 
Select * From pubs.dbo.publishers Where (isnull(pub_name,'')+'|'+isnull(city,'')+'|'+isnull(state,'')+'|'+isnull(country,'')) like '%'+@str2+'%'
Union 
Select * From pubs.dbo.publishers Where (isnull(pub_name,'')+'|'+isnull(city,'')+'|'+isnull(state,'')+'|'+isnull(country,'')) like '%'+@str3+'%'
Union 
Select * From pubs.dbo.publishers Where (isnull(pub_name,'')+'|'+isnull(city,'')+'|'+isnull(state,'')+'|'+isnull(country,'')) like '%'+@str4+'%'

Select * from pubs.dbo.publishers

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 11:10:15

yeah it works...

<SlapOnTheHead> i was doing col1|col2 instead of col1+'|'+col2 like ... </SlapOnTheHead>

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 11:16:00

do you think that would be more efficient? or is it not worth the effort?

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 11:22:30

you know i have no idea... never used that syntax. i always used normal or.

if i had to do the search page without full text i let the users chose max of 5 columns per search.
it worked ok... never heard no complaints.

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 12:31:18

quote:
...never heard no complaints

So you hear complaints all of the time

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 12:40:31

yup...

ok that's a fluke because in my language we have double negation. kind of illogical but ok...

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 12:48:24

how do double negatives work for your language?? Or dare I ask?

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-21 : 12:57:50

acctually they work ok... no problem. but they are seldomly used i must say that.

our language is also the one of two in the world who have "doubles". we have i, two of us, more thatn two of us

. it's hard to explain because there is no translation that i know of...
so it's a hard language to learn properly. most of us don't....

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 13:00:45

I think I'll just stick to my english... I'm not really even great at that

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources