June 19, 2003 at 1:32 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 20, 2003 at 1:00 am
With that number of ID's I think I'd be inclined to drop the values into a table and use a join rather than an IN clause. I'd also be inclined to set the whole thing up as a stored procedure rather than execute it as in-line sql from vb.
Need to know a little more about your app's topography before being able to offer a solid suggestion about getting the Id values into a table. If this is a reporting system is it on-line with multi-users or a static report that's created periodically for consumption.
Off the top of my head for the multi-user option you could have a table for the id's and assign each user a unique session number as they run the app, fill the id table keyed on that session number to get them a unique version of the report (remember to remove their set of id's after the report is collated)
Phew I need a coffee
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 20, 2003 at 2:39 am
Mike,
You certainly have to add the ID values to a temporary table. A previous thread (not so long ago) talked about performance problems with a huge IN clause.
If possibly, I would use a 'real' temp table, so you don't have to worry about multiple users. You can create a temp table using a '#' before the name.
How to add them to a 'temporary' table?
You could BCP them in from VB, but I think you will lose the possibilities of using the real temp table.
A completely different approach could be to convert your plain text document to an XML doc and using that in your join. Never done that, so I have no clue about the performance.
June 20, 2003 at 6:00 am
Nothing very original - a suggestion for implementing the ideas already given, and for amnding the SQL to run as a simple SELECT statement:
--DDL:
create table custlist(id int identity(1,1), custid int, spid int)
--Query:
set nocount on
set ansi_warnings off
declare @spid int, @filepath varchar(255), @bcp varchar(2000), @password varchar(20)
set @spid = @@spid
set @filepath = '\\share\dir\...\dir\filename.extension'
set @bcp = 'bcp in db.owner.custlist '+ @filepath +' -f custid_only.fmt -S SERVERNAME -U USERID -P ' + @password
set transaction isolation level serializable
begin tran
exec xp_cmdshell(@bcp)
update custlist set spid = @spid
where spid is null
commit
set transaction isolation level read committed --(or whatever your current setting was)
select
c.id,
c.phone,
c.fullname,
c.lastname,
c.firstname,
isnull(
sum(
case when oh.date_delivery between '1/1/03' and '2/14/03'
then 1
else 0
end
)
,0) PriorCount,
isnull(
sum(
case when oh.date_delivery between '1/1/03' and '2/14/03'
then
IsNull(oh.revenue_food,0)
+ isnull(oh.revenue_house,0)
+ isnull(oh.revenue_convfee,0)
+ isnull(oh.revenue_delfee,0)
- isnull(revenue_discount, 0)
else 0
end
)
,0) PriorSales,
isnull(
sum(
case when oh.date_delivery between '2/15/03' and '3/31/03'
then 1
else 0
end
)
,0) AfterCount,
isnull(
sum(
case when oh.date_delivery between '2/15/03' and '3/31/03'
then
IsNull(oh.revenue_food,0)
+ isnull(oh.revenue_house,0)
+ isnull(oh.revenue_convfee,0)
+ isnull(oh.revenue_delfee,0)
- isnull(revenue_discount, 0)
else
0
end
)
,0) AfterSales
from customers c
join custlist cl
on cl.custid = c.id
and cl.spid = @@spid
left join orderheader oh
on oh.customerid = c.id
where c.companyid = 1154
and oh.ordertype = 1
and oh.companyid = 1154
delete custlist where spid = @spid
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 20, 2003 at 7:16 am
With the number of id's you are suggesting, I don't think there is an easy way. As already mentioned above the use of IN should be avoided. Passing the list to the proc would be difficult as well due to the 8k varchar limit unless you use text.
What I suggest is
1. create a control table to hold latestid
2. write a proc that updates latestid and returns it
3. create a permanent table (selectedid) containing recid (from latestid returned above) and paramid.
4. create a proc to store recid,paramid in the table
5. in the app use the procs above to create the selection id's
6. change your proc to join to this table (selectedid)
7. write an use a proc to delete the id's from the selectedid table
No idea of performance.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply