Is there any way to improve performance here? - 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 2000 Forums

SQL Server Development (2000)

Is there any way to improve performance here?

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-10-27 : 09:37:11

My Query is:


select 
	Partnumber, min(convert(varchar(10), b.requestdatetime, 101)) as [DownloadDate], convert(varchar(10), purchase_date, 101) as [PurchaseDate], Invoice, Quantity, convert(money, NetPrice) as [NetPrice], convert(money, Totalsalenet) as [TotalSales] 
from 
	sales_history a 
join 
	smc_new_products.dbo.etechmodelrequests b 
on 
	RTrim(a.partnumber) = substring(b.configname, 0, (Len(a.partnumber) + 1)) 
where 
	purchase_date between '04/01/2001' and '03/31/2002' 
and 
	accountnumber in 
		(select accountnumber from sales_company where company_name like'ABBOTT LABORATORIES (IL)') 
and 
	(family like '%') 
group by 
	partnumber, convert(varchar(10), purchase_date, 101), invoice, quantity, convert(money, NetPrice), convert(money, Totalsalenet) 
order by 
	TotalSales desc

The Sales History table has several million records. The etechmodelrequests table has a couple of hundred-thousand.

They are in two different databases to make matters worse.

Right now this query is taking forever. When I remove the Join and don't get any info from the etechmodelrequests table, the query runs in 11 seconds.

Any suggestions are much appreciated as always.

MakeYourDaddyProud

184 Posts

Posted - 2003-10-27 : 09:49:15

Your substring has an error with '0' parameter should be an actual start position not offset, but this may not be the source of your woes...

Looks like a table scan on the etechmodelrequests table. This might be due to the fact that benefit of any index on that table in lost (You are substringing for each row in sales_history)

I think this is a matter of design. Presumably a.partnumber is a char with trailing spaces and b.configname. Try using a LEFT command instead for another try.

on
RTrim(a.partnumber) = substring(b.configname, 0, (Len(a.partnumber) + 1))
BECOMES
on
a.partnumber = b.configname

... and see if spaces are ignored...

b.configname must be the leftmost part of an established index on that table.

Daniel Small MIAP
www.danielsmall.com IT Factoring

label
Posting Yak Master

197 Posts

Posted - 2003-10-27 : 10:21:04

quote:
Originally posted by danny2sweet

RTrim(a.partnumber) = substring(b.configname, 0, (Len(a.partnumber) + 1))
BECOMES
on
a.partnumber = b.configname

... and see if spaces are ignored...

Yeah, taking the substring thing off worked.

I left the Rtrim on and it's running at about 10-15 seconds now. The only bad thing is that I lose about 20% of the matches I was coming up with before.

Of course, that may have to be what we use if I can't improve speed. Thanks for your help.

MakeYourDaddyProud

184 Posts

Posted - 2003-10-27 : 10:28:24

Ok, what are the characteristics of the missed ones against the matched ones?? Look at a column on one value that matched and one that didn't and post here. I will look at it...

Daniel Small MIAP
www.danielsmall.com IT Factoring

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-27 : 10:41:29

As Daniel says, definitely that JOIN will cause problems. You can also try this:


select 
   ....
from 
	sales_history a 
inner join
	(select distinct accountnumber from sales_company 
         where  company_name like 'ABBOTT LABORATORIES (IL)') b
on
   a.accountnumber = b.accountnumber
inner join 
	smc_new_products.dbo.etechmodelrequests b 
on 
	RTrim(a.partnumber) = substring(b.configname, 0, (Len(a.partnumber) + 1)) 
where 
	purchase_date between '04/01/2001' and '03/31/2002' 
and 
	(family like '%')  --if needed, use Family is not null
group by 
	partnumber, purchase_date, invoice, quantity, NetPrice, Totalsalenet
order by 
	TotalSales desc

Note the changes in the GROUP BY. If purchase_date doesn't have a time in it (I assume it doesn't), there is no need to convert each date to a varchar() just for a nice display. Just group by the values you need, and convert at the top in your SELECT.

not sure if the inner join vs. WHERE IN () will make a difference, but give it a try.

and if the LIKE '%' is in there to not allow Nulls, use "Not Is Null" instead.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources