Is there a way to optimize this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

Is there a way to optimize this?

Author

Topic

Linok
Starting Member

34 Posts

Posted - 2007-10-05 : 09:31:57

Hey everyone,

I'm trying to cross-reference two tables--one has workshop attendee information and the other has sales data--to determine how many attendees actually purchased a product. The query I have to check the sales table is as follows:


SELECT 
  Source,
  Document_Date,
  CustomerName, 
  PayorType,
  Country,
  City,
  State,
  RepTerritory,
  SLPName,
  SLPPracticeName,
  SLPAddress,
  SLPCity,
  SLPState,
  SLPZip,
  ShipName,
  ShipAddress1,
  ShipAddress2,
  ShipAddress3,
  CustomerNumber,
  OrderNumber
FROM  dbo.SalesData
WHERE 
  (DocType = 'Invoice') 
  AND (Document_Date >= @DVU_Event_Date) 
  AND (CustomerName LIKE '%' + UPPER(@LastName) + '%') 
  OR (ShipAddress1 LIKE '%' + UPPER(@LastName) + '%') 
  OR (ShipName LIKE '%' + UPPER(@LastName) + '%')

The only way I'm found to accurately check the sales table is by running this query passing every last name of the attendees list. The problem I have it that there are 1003 attendees and currently 16005 sales equaling 16,053,015 rows searched. Does anyone know of a better way to approach this?

Thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 09:36:39

Why don't you join Attendee table with SalesData?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Linok
Starting Member

34 Posts

Posted - 2007-10-05 : 09:43:32

They don't really have a join point. I've had to create a program that will properly format @LastName before it even gets passed in.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-05 : 09:51:54

sure it does. the join doesn't have to be a straight '='

salesData s
join attendee a on a.documentdate >= s.eventdate
and (
(s.CustomerName LIKE '%' + UPPER(a.LastName) + '%')
OR (s.ShipAddress1 LIKE '%' + UPPER(a.LastName) + '%')
OR (s.ShipName LIKE '%' + UPPER(a.LastName) + '%')
)

haven't checked the syntax propery but you get the idea

Em

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-05 : 09:57:38

Why do you need UPPER() there anyway? Is it case-sensitive database?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pootle_flump

1064 Posts

Posted - 2007-10-05 : 10:19:38

There is only one table in the query.....

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-05 : 10:28:21

no... OP wants to get data from both salesdata and attendee tables

Em

Linok
Starting Member

34 Posts

Posted - 2007-10-05 : 11:24:46

Thanks for the response everyone!

I'm going to see what I can do with the join once I get the new sales data moved (they finally let me access the DB instead of giving me excel spreadsheets) ;-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources