Need help with Query with Multiple IN clauses

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 2000 Forums

SQL Server Development (2000)

Need help with Query with Multiple IN clauses

Author

Topic

aawan
Starting Member

24 Posts

Posted - 2006-03-31 : 18:07:58

I have code that seems to be crashing my web server (NOT the DB server).

Still I am looking for ways to optimize my queries so that I can get the best performance possible.

I am looking for advice as to how can I improve a query such as this.

The create script for the table involved is:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Commissions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Commissions]
GO

CREATE TABLE [dbo].[Commissions] (
	[FSR] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PolicyNumber] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Insured] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TR] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EntryDate] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EffectDate] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NetPayable] [float] NULL ,
	[DealerID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

I have another query similar to the one below where I add one more IN clause, with several hundred PolicyNumbers.
I have a web application that executes this query, and the DealerID's are dynamically generated by it and plugged into the query.

SELECT DealerID, PolicyNumber, FSR, NetPayable, TR, EntryDate, EffectDate, Insured
	FROM commissions
	WHERE DealerID IN ('ABAUTO','ALLGWY','ALMKIA','ANAMIT','ANTFOR','AUTFUL','AUTMAL','AUTORA','AVKIA1','BANRAU','BEACHE','BELCHE','BELTOY','BENTOY','BESTBU','BEVRLY','BINGTO','BOUBUI','BRYAUT','BUEHON','CABTOY','CALMEX','CALSAL','CALSPE','CAMAUT','CAPMIT','CARCON','CARKIA','CARMAX','CENCHR','CENSPO','CENTOY','CHALAQ','CHAMCH','CHAMON','CITGRO','CLICHR','CLIFOR','COLDOD','COPLEX','CORONA','COSHON','CREBMW','CREFOR','CRODOD','CROOXN','CROVEN','DAYTON','DEACEN','DEALER','DIAHON','DIAMON','DONKFO','DOUHYU','DOUNHB','DOUNIO','DRIBEA','DRICOR','DRIDOW','DRIELM','DRIFON','DRIGAR','DRIMON','DRIMOT','DRIOXN','DRIRIV','DRISAN','DRITOR','DRIVAN','DRIVLB','DRIWIL','ELEAUT','ELMFOR','ELMHYU','ELMTOY','ENTERR','ENTGAR','ENTMON','ENTRIA','ESCJEE','FELIXC','FIEIND','FINAUT','FIRDOD','FIRHON','FONNIS','FORMON','FORUPL','FRADOD','FULLMI','FULTOY','FUTCLO','FUTKIA','GARHON','GARNIS','GLECHE','GLEKIA','GLENIS','GLETHO','GLETOY','GONCHR','GOUHON','GRELI2','GUNCHE','GUNNIS','HARBHO','HAWMTR','HIDKIA','HOLMOT','HOLTOY','HONHOL','HONNOR','HONOXN','HONTHO','HOSAMO','HOUIMP','HWORLD','HYUGLE','INLPON','IRVHYU','KALMOT','KEYACU','KEYHON','KEYLEX','KEYSTO','KEYTOY','KEYWOO','KIAVAL','LAKFOR','LEWIRN','LEWIRT','LEWTBP','LIBCHE','LIBCHR','LLOFOR','LONGMA','LONGOT','LONLIN','MACDOW','MACFOR','MACINF','MAGAR2','MAGICA','MAGICF','MAGSAL','MANHAT','MARAU1','MARAU2','MARCHE','MCCUNE','MEDCAR','MELCAJ','MELFOR','MERCLA','METFOR','METHON','MIKTOY','MILCRE','MILCUL','MILHON','MILINF','MILMIT','MILNIS','MIRALO','MODMAZ','MODTOY','MORPON','MOSBRO','MOSDOD','MOSENC','MOSMOT','MOSNIS','MOSPOW','MOTOYO','MULCHE','NATGRO','NATSAL','NEWVOL','NILSAL','NISCLO','NORTHT','PACFOR','PALMIT','PANSAL','PARDOD','PENDOW','PENONT','PENTOY','PERNDU','PEYACU','PEYFOR','PEYINF','PEYLIN','PLANAC','POHOND','PORAUD','POTOYO','PRIVAD','PUEBUI','PUEFOR','PUEMIT','PUENTE','PUETOY','QUACON','QUAHYU','QUINIS','QUTOYO','RANDOD','RAZMAZ','REDCHR','REPAUT','REPSAL','RIOPON','RIVHON','RIVHYU','RIVNIS','ROBHON','ROCHON','ROGROG','ROGTOY','RYDVAN','SAMOVW','SANMON','SANPAU','SANSAL','SATTOR','SCOHON','SELHON','SELNIS','SHAPON','SHOMOT','SIECHE','SIEMON','SIMGMC','SIMNIS','SOCEN1','SOCEN2','SOCEN3','SOUBCJ','SOUFOR','SOUTCM','SPIDOD','SPIHON','SUNFIN','SUNFOR','SUNIMI','SUNIPU','SUNWES','SUPCAL','SUPCHR','SUPNIS','SUPSA2','SUPSEL','SUPUSE','SUZ111','TEAINF','TEAMNI','THOTOY','TODCHE','TORNIS','TORTOY','TOYCEN','TOYCER','TOYESC','TOYGLE','TOYMON','TOYOXN','TOYRAN','TUSACU','TUSMIT','UNAUTO','UNINIS','VADODG','VALCHE','VALHON','VALIMP','VANISS','VENTOY','VICHYU','VICMIT','VICMOT','VICNIS','VIEWMO','VOLCER','WESCTO','WHITKI','WORDOD','WORFOR','ZACMOT') AND
		  FSR IN ('BW ','BW2','DT ','JT ','JT2','KA ','KA2','KF ','KF2','LJ ','LS ','MD ','MD2','PC ','RT ','SH ','SH2')
	Group By FSR, DealerID, PolicyNumber, EntryDate, EffectDate, Insured,TR, NetPayable
	ORDER BY FSR, DealerID,  PolicyNumber, EffectDate

Any advice is appreciated,
Thanks,
Ali

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-31 : 18:13:58

What columns are indexed on the commissions table?

Tara Kizer
aka tduggan

aawan
Starting Member

24 Posts

Posted - 2006-03-31 : 18:18:34

Tara,

I know I will be crucified for this...but None :(

Ali

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-31 : 18:20:34

Well you need a primary key at least. How many rows are in the table? An index on DealerID would help, probably a composite index of DealerID, PolicyNumber would be better.

Tara Kizer
aka tduggan

aawan
Starting Member

24 Posts

Posted - 2006-03-31 : 19:08:24

*hangs head in shame*
There are approximately 22950 rows in the table.

aawan
Starting Member

24 Posts

Posted - 2006-03-31 : 19:10:29

BTW, there is a many to many to many relationship between DealerID's, Policy Numbers and FSRs.

FSR is a field service rep.
An FSR can be assigned to many Dealers. In addition a PolicyNumber can be shared between 2 FSR's.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources