June 19, 2003 at 1:31 pm
I need to sum up some information for a report and I have the following query built:
select c.id, c.phone, c.fullname, c.lastname, c.firstname,
(select IsNull(count(oh.id),0) from orderheader oh where oh.customerid = c.id and oh.date_delivery between '1/1/03' and '2/14/03' and oh.ordertype = 1 and oh.companyid = 1154) as PriorCount,
(select isnull(sum(IsNull(oh.revenue_food,0) + isnull(oh.revenue_house,0) + isnull(oh.revenue_convfee,0) + isnull(oh.revenue_delfee,0)-isnull(revenue_discount, 0)),0) from orderheader oh where oh.customerid = c.id and oh.date_delivery between '1/1/03' and '2/14/03' and oh.ordertype = 1 and oh.companyid = 1154) as PriorSales, (select count(oh.id) from orderheader oh where oh.customerid = c.id and oh.date_delivery between '2/15/03' and '3/31/03' and oh.ordertype = 1 and oh.companyid = 1154) as AfterCount, (select isnull(sum(isnull(oh.revenue_food,0) + isnull(oh.revenue_house,0) + isnull(oh.revenue_convfee,0) + isnull(oh.revenue_delfee,0)),0) from orderheader oh where oh.customerid = c.id and oh.date_delivery between '2/15/03' and '3/31/03' and oh.ordertype = 1 and
oh.companyid = 1154) as AfterSales
From customers C where c.companyid = 1154 AND c.id in (<value list is inserted here>)
Now, the dates (of course) will change and so will the companyid and the list of values for the "IN CLAUSE". The "IN CLAUSE" is built in VB by ripping through a text file that has the ID value as the first column in a delimited file. My problem is that a typical "IN CLAUSE" is going to contain over 14,000 id values. My questions:
(1) Is there a better way to do this?
(2) Is there a limitation to the number of values that can be used in the "IN CLAUSE"?
Any suggestions would be greatly appreciated.
June 19, 2003 at 1:48 pm
Yikes, this will run terribly slow, especially if the column is not indexed. I would recommend you make a table to hold the "in" values, then instead of doing an in do a join where the 2 values are =. So if you make a new table called In and it has a field called values you would insert all the values from the text file into new rows in this table then do an inner join on c.id=in.value. You could explore creating a temp table to hold the values (depending on how often this will be executed, and by how many people), then doing the join on this temp table, that will go away once they logout.
June 19, 2003 at 3:39 pm
I think there was a discussion on this site about the 'limit' in 2000, bit low on fuel to search for it.
Other potential problem with IN is NULLS and NOT NULLS.
Agree with other poster rather a temp table.
How static is this text file?
Can't the SQL load the file once a day, etc.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply