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)
 Sql query, several joins

Author  Topic 

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2008-03-06 : 12:23:01
I have the following:

select
	ad.bank,ad.account_number,ad.account_short_name,
	p.security_id,isnull(b.NAME,isnull(tw.name,ms.name)) as SecurityDescription,
	p.security_price,mc.classcode,
		sum(case
		when p.security_price = 9999999.99900 then 0
		when mc.classcode between '000'and'299' then (p.quantity * p.security_price) / 100
		when mc.classcode between '950'and'999' then (p.security_price * p.quantity) * -1
		when mc.classcode between '300' and '324' or mc.classcode between '330' and '399'then p.acm_ctf_mtf_price * p.quantity
		when p.security_price = '0' then p.cost
	else
		(p.security_price * p.quantity)
	end) as MV,p.position_date,p.quantity,p.cost,p.acm_ctf_mtf_price
	from positions p
	left join BloombergEquityData b on p.security_id = b.ID_CUSIP 
	left join account_detail ad on p.account_id = ad.account_id
	left join metavanteclasscodes mc on p.security_id = mc.cusip
	left join MacgregorSecurityMaster ms on p.security_id = ms.cusip
	left join TobaSecurityWarehouse tw on p.security_id = tw.cusip
	where p.position_date = '03/05/2008' and
	((ad.investment_authority_id in ('2','3') and ad.bank in ('98','7R')) or ad.bank = '9W')-- and ad.account_number = '41G006012'
	group by	ad.account_number,ad.account_short_name,p.security_id,
			ms.name,b.NAME,p.security_price,p.position_date,ad.bank,mc.classcode,p.quantity,p.cost,p.acm_ctf_mtf_price,isnull(b.NAME,isnull(tw.name,ms.name))
	order by ad.account_number,p.security_id


For a given day, in this example 3/5 if I were to just select all the records I would get back around 550,000. If I put the filter on where I tell it to just show me one account its snappy. If I remove that filter and have it show me all the accounts it take forever. Here is the layout of my positions table where i am pulling the majority of the information:

CREATE TABLE [dbo].[positions](
	[account_id] [int] NOT NULL,
	[security_id] [varchar](50) NOT NULL,
	[position_date] [smalldatetime] NOT NULL,
	[quantity] [decimal](18, 5) NOT NULL,
	[cost] [decimal](18, 5) NULL,
	[security_price] [decimal](18, 5) NULL,
	[acm_ctf_mtf_price] [decimal](18, 9) NULL,
 CONSTRAINT [PK_positions] PRIMARY KEY CLUSTERED 
(
	[account_id] ASC,
	[position_date] ASC,
	[security_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF


My questions is if you can identify any reason why the query might take more than 10 minutes (actually still running after 10 minutes) to complete when I have it bring me back all the records, not sure if its a lack of indexes or what, but was hoping for some suggestions on what I might be missing. I apologize if I havent included enough information, still learning how to troubleshoot the performance side of things.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-03-06 : 23:42:42
I think using between in comparions slows down things as it then becomes row by row and would ignore indexes. Changing those to be like

mc.classcode >= '000'and mc.classcode <= '299'

may help a little, provided mc.classcode is an indexed, or part of an index that can be used in the query.


Not sure if just bringing the row level info on a straight select (to eliminate all the aggregates and group by's needed at the outset)..and then doing a select with aggregate off of that may do better...impossible to say without all the tables structures and indexes (and even then)


select
	ad.bank,ad.account_number,ad.account_short_name,
	p.security_id,isnull(b.NAME,isnull(tw.name,ms.name)) as SecurityDescription,
	p.security_price,mc.classcode,
    case
		when p.security_price = 9999999.99900 then 0
		when mc.classcode >= '000'and mc.classcode  <= '299' then (p.quantity * p.security_price) / 100
		when mc.classcode >= '950' and mc.classcode  <='999' then (p.security_price * p.quantity) * -1
		when (mc.classcode >=  '300' and mc.classcode  <= '324') or (and mc.classcode  >= '330' and mc.classcode  <= '399')
						then p.acm_ctf_mtf_price * p.quantity
		when p.security_price = '0' then p.cost
	else (p.security_price * p.quantity) end as MV
	,p.position_date,
	p.quantity,p.cost,p.acm_ctf_mtf_price
INTO #Temp
from positions p
	left join BloombergEquityData b on p.security_id = b.ID_CUSIP 
	left join account_detail ad on p.account_id = ad.account_id
	left join metavanteclasscodes mc on p.security_id = mc.cusip
	left join MacgregorSecurityMaster ms on p.security_id = ms.cusip
	left join TobaSecurityWarehouse tw on p.security_id = tw.cusip
	where  p.position_date = '03/05/2008' and
	((ad.investment_authority_id in ('2','3') and ad.bank in ('98','7R')) or ad.bank = '9W')-- and ad.account_number = '41G006012'



select
	bank,
	account_number,
	account_short_name,
	security_id,
	SecurityDescription,
	security_price,mc.classcode,
	sum(MV) as MVTotal,
	position_date,
	quantity,
	cost,
	acm_ctf_mtf_price
from  #temp
group by	bank,
			account_number,
			account_short_name,
			security_id,
			SecurityDescription,
			security_price,mc.classcode,
			position_date,
			quantity,
			cost,
			acm_ctf_mtf_price
			mc.classcode,
			quantity,
			cost,
			acm_ctf_mtf_price,
			 
	

order by  account_number, security_id




Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2008-03-07 : 10:58:09
appreciate the advice, will give it a shot and let you know.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-03-07 : 11:03:05
quote:
Originally posted by duhaas

appreciate the advice, will give it a shot and let you know.


I wouldn't call my attempt at advice...but from what I read around here, it seems all those group bys, and the between operator causes a lot of row by row processing. If the results you needed were in a table, it would possibly be quicker...I really don't know for sure.



Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2008-03-07 : 11:07:28
well, advice or not its something to go off of. i was thinking, i wonder if it would make sense for me to do the math on import of the records instead of during the select
Go to Top of Page

duhaas
Constraint Violating Yak Guru

310 Posts
Posted - 2008-03-07 : 12:46:25
after doing a database maintenence task and updating stats, and reindexing everything, it now runs in 39 seconds.
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2008-03-07 : 13:29:37
Half a million rows is a lot of data to stream to your client. Keep that in mind. Try select * into #temp from {your query} to get a slightly better idea of how long the query takes to execute.

The first position of your clustered index is AccountID, so it makes perfect sense that predicating on AccountID would yield a better plan. I'm pretty sure that the use of "between" in this case has nothing to do with performance since it should resolve to x >= y and x>=z anyway. You haven't told us anything about the 5 other tables in the query. Why not run your query through the Database Engine Tuning Advisor and see what it says to do?


Jay
to here knows when
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-03-07 : 14:09:42
quote:
Originally posted by dataguru1971

I think using between in comparions slows down things as it then becomes row by row and would ignore indexes.

Why would you think that? BETWEEN will use indexes (or should) just like other comparison operators do or don't (<> often does not use indexes).
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-03-07 : 14:15:53
Also, it looks like you can change your LEFT OUTER JOIN to "account_detail" to an INNER JOIN. You criteria in the WHERE clause is affectively making this an INNER JOIN. Unless you want it to actually be a LEFT OUTER JOIN, then you should move the criteria up out of the where clause and onto the join condition.
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-03-07 : 15:18:25
quote:
Originally posted by Lamprey

quote:
Originally posted by dataguru1971

I think using between in comparions slows down things as it then becomes row by row and would ignore indexes.

Why would you think that? BETWEEN will use indexes (or should) just like other comparison operators do or don't (<> often does not use indexes).




I was thinking of the issue if it is used in the Where clause...I guess it doesn't matter in the select portion or case/when construct.


Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-03-07 : 15:31:30
quote:
Originally posted by dataguru1971

quote:
Originally posted by Lamprey

quote:
Originally posted by dataguru1971

I think using between in comparions slows down things as it then becomes row by row and would ignore indexes.

Why would you think that? BETWEEN will use indexes (or should) just like other comparison operators do or don't (<> often does not use indexes).


I was thinking of the issue if it is used in the Where clause...I guess it doesn't matter in the select portion or case/when construct.

I don't want to beat a dead horse, I'm trying to understand what you mean. Are you saying there is an issue using between in a where clause or you thought it might cause an issue? I was not aware of any issue using between in a where clause, so I'm just checking incase there is something I should be aware of. :)
Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts
Posted - 2008-03-07 : 15:43:14
In a where clause, if you want for example a range of dates, it is more efficient to do

WHERE [datecolumn] >= @datevalue1 and [datecolumn] <= @datevalue2

My understanding is if you use

WHERE [datecolumn] between @datevalue1 and @datevalue2

it would do scan instead of make use of indexes (if they are applicable)...

I could be completely wrong, but that is what I recall...there was post yesterday or last night that I saw Jeff Moden and MVJ discussing it, but can't find the post now..




Poor planning on your part does not constitute an emergency on my part.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site