SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Need help with Query with Multiple IN clauses
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aawan
Starting Member

USA
24 Posts

Posted - 03/31/2006 :  18:07:58  Show Profile  Reply with Quote
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

USA
37157 Posts

Posted - 03/31/2006 :  18:13:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
What columns are indexed on the commissions table?

Tara Kizer
aka tduggan
Go to Top of Page

aawan
Starting Member

USA
24 Posts

Posted - 03/31/2006 :  18:18:34  Show Profile  Reply with Quote
Tara,

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

Ali
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 03/31/2006 :  18:20:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

aawan
Starting Member

USA
24 Posts

Posted - 03/31/2006 :  19:08:24  Show Profile  Reply with Quote
*hangs head in shame*
There are approximately 22950 rows in the table.
Go to Top of Page

aawan
Starting Member

USA
24 Posts

Posted - 03/31/2006 :  19:10:29  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000