Huge IN query

[erickwidya]

i've got query that using dynamic query and involved using very huge IN clause (it's for account code)

it receive an error that said something like "the queries involved large complex query"

any other approach? this query is for Financial Reports that used more than 5 years record

PS: i'm using SQL 2005 and C#

thanks,

[jmcilhinney]

Can we see the query? If you can't make it more efficient in some way then most likely it would be best to create a stored procedure at least.

[erickwidya]

thx jmcilhinney

Can we see the query? > are u sure?????

If you can't make it more efficient in some way then most likely it would be best to create a stored procedure at least. -> maybe to figure it out to change IN clause to something else


PS: it involved lotsssss of IN Clause but i will post it just for you

hold on a sec..i need to prepare the data

[kevchadders]

If there are lots of account codes, maybe you could move them to their own table and join to that?

[jmcilhinney]

thx jmcilhinney

Can we see the query? > are u sure?????

Just to make sure you understand, I wanted to see the query itself, i.e. the SQL code, not the results of the query. kevchadders is quite correct that the database design may be able to be improved but we won't really know until we see the query and, perhaps, the schema of the tables involved.

[BillBoeBaggins]

Perhaps if you are receiving input of a large list of Account numbers perhaps dumping them into a temp table then either A)JOIN-ing to it (as noted above) or B)Use it as a Subquery Source.

It's been a while since I have posted on these boards, if I am overstepping my bounds slap me into place.

[szlamany]

To elaborate on what KevC said...

IN is a not a good conditional expression - it makes sense in very few places.

Something like:

Select * From Address_T Where State in ('CT','NY')

or

Select * From Address_T Where State in (Select States From NewEngland_T)

What is in the sub-query must be "short and sweet" - that's the whole point.

But regardless - IN is not a good choice - it's always better to think in SET's.

When you think in sets the query above would be written.

Select * From NewEngland_T NE
Left Join Address_T AD on AD.State=NE.States

The syntax above is coming "FROM" NewEngland first - a table with few rows.

That's the small set.

And joined to that SET is the larger set of address - and those addresses must be part of the NewEngland_T set.

This SET-building - set processing - is how sql is designed to process data quickly.

The IN clause is a kludge at best - it can almost always be represented in a SET-based join expression - which will always be faster.

And if it's required - you build a TEMP table first of the "smaller" set and join the larger set to that.

[erickwidya]

select

      account_code,

      sum(balance) as sum_balance,

      sum(balance_oper) as sum_balance_oper,

      sum(nat_balance) as sum_nat_balance into np_coa_temp_' + @companyCode + '_' + @userName +

' from

      gltrxdet inner join gltrx on

            gltrxdet.journal_ctrl_num = gltrx.journal_ctrl_num

where

      date_applied < ' + convert(varchar(10), @dateFrom) + ' and

      account_code <> '''' and

      account_code in

      (there are +22000 records here)

group by account_code

[erickwidya]

The IN clause is a kludge at best - it can almost always be represented in a SET-based join expression - which will always be faster. -> so it's better to use JOIN instead of IN?

i miss this one sz This SET-building - set processing - is how sql is designed to process data quickly.

[jmcilhinney]

Where does the list of account codes come from?

[erickwidya]

thx jmcilhinney

Where does the list of account codes come from? -> it come from another query that get acct code from certain date range criteria and insert it to temp table

[jmcilhinney]

So, as has already been recommended, you should probably be using a join rather than IN, but if you're not going to show us all the code then we can only guess at what might be the complete solution.

[erickwidya]

but if you're not going to show us all the code then we can only guess at what might be the complete solution. -> not that i don't want to show the code but it's to complicated because it call another sub within a sub..and it's not my work

let me try it first and will let you know the result

thanks,

[szlamany]

The IN clause is a kludge at best - it can almost always be represented in a SET-based join expression - which will always be faster. -> so it's better to use JOIN instead of IN?

Yes

Having 22000 records in the IN () is a real problem.

Those 22000 records should be put into a TEMP TABLE.

That is the only way to work with that many rows.

Those 22000 records should most likely be put into a TEMP TABLE that has an INDEX built on it.

I would also recommend putting that main query - with the GROUP BY - into a VIEW. If this is SQL 2005 ENTERPRISE then you can experiment with putting all 4 columns into a PRIMARY KEY index that is clustered. By doing I believe that you will "materialize" the view into reality - I've never actually needed to do this - but I've read about it (maybe KevC can share more on this).

i miss this one sz This SET-building

Think about the sets of data you have here.

22000 account codes is a huge set - right?

You haven't given much other details but if you have 22000 account codes to select that must mean that these GL tables have 100,000+ rows - right?

Think about all this data as sets and think about how you want to come FROM the "smaller" set - and JOIN on the "detail data" from other "sets".

Making the VIEW is a way to take a huge SET and drop it down in size to a more manageable number of rows.

And all this set-talk is the way the SQL database engine is designed to process data. It's optimized to handle data this way.

[kevchadders]

I would also recommend putting that main query - with the GROUP BY - into a VIEW. If this is SQL 2005 ENTERPRISE then you can experiment with putting all 4 columns into a PRIMARY KEY index that is clustered. By doing I believe that you will "materialize" the view into reality - I've never actually needed to do this - but I've read about it (maybe KevC can share more on this).

Having a Clustered Index is a good option as one of the most important characteristic for a Clustered Index key is to satisfy range queries. Using a clustered index will physically order the data in a table, based on the fields you have used to create that Clustered Index.

A View with a clustered index is called an Indexed View. Indexing views in 2005 has been expanded to include the likes of Scalar aggregates, e.g. SUM ...and as your using a couple of SUM’s in your SQL statement... its certainly an option to consider as szlamany suggests as this could increase the speed of your query quite dramatically.

Here is a good link from Microsoft that talks about it
http://www.microsoft.com/technet/pro.../impprfiv.mspx
(read all... and 3rd of the way down talks about What's new for Indexed Views in SQL Server 2005)

Here is another ok link as well
http://www.sql-server-performance.co...ndexes_p1.aspx

[erickwidya]

You haven't given much other details but if you have 22000 account codes to select that must mean that these GL tables have 100,000+ rows - right? -> yes..u are correct sz..there are +180,000 rows

View is using fixed query right? i'm afraid we can't used it because the Account Query is also based on specific criteria..

perhaps using index..

i will follow up again

thx sz and kevchadders

[erickwidya]

got good news and bad news.

good news : using JOIN now can display 2 years records while it can't before
bad news : when select more than 2 years records it got an error that said memory not enough or something like that

here's how we do it

	ALTER proc [dbo].[spr_createCOATable] 
@dateFrom int,
@userName varchar(32),
@companyCode varchar(2)
as
declare @sqlcommand varchar (8000)
set @sqlcommand =
'select 
	gltrxdet.account_code, 
	sum(balance) as sum_balance, 
sum(balance_oper) as sum_balance_oper, 
	sum(nat_balance) as sum_nat_balance into np_coa_temp_' + @companyCode + '_' + @userName +
' from 
	gltrxdet inner join gltrx on 
		gltrxdet.journal_ctrl_num = gltrx.journal_ctrl_num 
	inner join np_coaCriteria coa on coa.account_Code = gltrxdet.account_Code
where 
	date_applied < ' + convert(varchar(10), @dateFrom) + ' and 
	gltrxdet.account_code <> '''' 
group by gltrxdet.account_code'

exec(@sqlcommand)

after that using this

select substring(det.account_code,1,7) + '-' + substring(det.account_code,8,3) + '-' + 
substring(det.account_code,11,5) + '-' + substring(det.account_code,16,3) as account_code, 
det.journal_ctrl_num, convert(datetime,dateadd(dd, date_applied - 639906,'1/1/1753')) as date_applied, 
description, balance, balance_oper, nat_balance, sum_balance, sum_balance_oper, 
datename(mm,convert(datetime,dateadd(dd, date_applied - 639906,'1/1/1753'))) + '-' + 
datename(yyyy,convert(datetime,dateadd(dd, date_applied - 639906,'1/1/1753'))) as month, document_2, 
sum_nat_balance, account_description from gltrxdet as det inner join glchart on det.account_code = 
glchart.account_code inner join gltrx as head on det.journal_ctrl_num = head.journal_ctrl_num left join 
np_coa_temp_" + companyCode + "_" + userName + " on np_coa_temp_" + companyCode + "_" + userName 
+ ".account_code = det.account_code where det.account_code in (select * from np_coaCriteria) and 
date_applied >= " + date_from + " and date_applied <= " + date_to

then grab the value to a DataTable variable so it can open using SQL 2005 Reporting Services

as u can see, all using dynamic query (EXEC (@CMD) syntax thing) because we use np_coa_temp_' + @companyCode + '_' + @userName..i'm not sure why because this code from my senior and now they're gone

any insight?

PS: hope now it got more details

thx,