Suggestions for large query - SQL Server Forums

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)

Suggestions for large query

Author

Topic

fwdtech
Starting Member

11 Posts

Posted - 2009-04-08 : 18:21:09

I've been handed a project where I need to produce a set of data from SQL that I would like some suggestions for.

There are two tables, one containing reps(representatives) and another containing active cases. A third table contains group information. The [group] table has an id value [groupID] that is part of every single rep record so that each rep has [groupID] in their record.

The report is looking to aggregate every case for each rep in a given group. The problem is that (using real data) group CSU55 has 671 rep ID's in it. The case table has 88k+ records.

What is the most efficient way to query those 88k+ records for an arbitrary number of rep ID's. The case table cannot be modified to add fields such as the groupid. It only contains the repID.

So something akin to 'select caseID from cases where repid=(one of a possible 671 rep id's)' is what I'm trying to accomplish.

Thanks.

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-08 : 23:34:01

Hi,
Provide the table structure and report columns for more clarity
Kunal

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-08 : 23:51:16

SELECT G.GroupName, count(*) Total, Count(distinct C.CaseID) Cases
FROM [Group] G
INNER JOIN Reps R ON G.GroupID=R.GroupID
INNER JOIN Cases C ON R.RepID=C.RepID
WHERE G.GroupName='CSU55'
GROUP BY G.GroupName

Kunal is right, you need to provide the table structure and all the columns you want in the output, but this is a start.

fwdtech
Starting Member

11 Posts

Posted - 2009-04-08 : 23:58:39

quote:
Originally posted by kunal.mehta

Hi,
Provide the table structure and report columns for more clarity
Kunal

CREATE TABLE [dbo].[AGENTS](
[SYNC] [int] NULL,
[AGENT_ID] [varchar](11) NOT NULL,
[AGENCY_ID] [varchar](11) NOT NULL
}

CREATE TABLE [dbo].[CASES](
[SYNC] [int] NULL,
[CASE_ID] [varchar](11) NOT NULL,
[CLIENT_ID] [varchar](11) NULL,
[AGENT_ID] [varchar](11) NULL
}

CREATE TABLE [dbo].[AGENCIES](
[SYNC] [int] NULL,
[AGENCY_ID] [varchar](11) NOT NULL
}

Record 1 in [AGENTS] is {0,'GBG9052290','CSU55'}
Record 2 in [AGENTS] is {0,'RPK4582210','CSU55'}

Record 1 in [CASES] is {0,'MCOP1855','','GBG9052290'}
Record 2 in [CASES] is {0,'RDVV2290','','RPK4582210'}

[AGENCIES] was added which looks like Record 1 {0,'CSU55'}

So, a single record in agencies relates to multiple records in agents. cases multiple records of agents but no field for their agency ID.

So I would query agencies, get "CSU55", query the [AGENTS] table and get 2 agents matching "CSU55". Now, I need to look through [CASES] for any records worked on by both of those agents.

Cases as well over 100k records and the last query in CSU55 reported back 671 records. So do I query cases with a select statement that is 671 "and" statements or is there a more efficient way to do a query of that nature?

fwdtech
Starting Member

11 Posts

Posted - 2009-04-09 : 00:13:43

quote:
Originally posted by robvolk

SELECT G.GroupName, count(*) Total, Count(distinct C.CaseID) Cases
FROM [Group] G
INNER JOIN Reps R ON G.GroupID=R.GroupID
INNER JOIN Cases C ON R.RepID=C.RepID
WHERE G.GroupName='CSU55'
GROUP BY G.GroupName

Kunal is right, you need to provide the table structure and all the columns you want in the output, but this is a start.

SELECT C.case_id
FROM [agencies] G
INNER JOIN [agents] R ON G.[agency_id]=R.[agency_id]
INNER JOIN [cases] C ON R.[agent_id]=C.[agent_id]
WHERE G.[agency_id]='CSU55'
GROUP BY C.[case_id] ORDER BY C.[case_id]

That appears to have worked perfectly! I'm getting back 24k records in a matter of seconds versus the query I hacked out. I printed out the execution plan to get a better idea of what the query above does. Once I test it tomorrow I'll know against the main report.

Thank you for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources