| Author |
Topic |
|
pug2694328
Posting Yak Master
166 Posts |
 Posted - 2006-02-07 : 17:29:13
|
I have a bunch of queries that are taking way longer than I would have hoped.Basically they are updating a temp table that I create by pulling from some large databases.Here's the temp table:create table #SecurityMaster (Code int, MQAid varchar(20), Sedol varchar(7), Cusip varchar(9), Ticker varchar(15), ISIN varchar(20), IBES_Ticker varchar(15), AssetType varchar(3), AssetDesc varchar(100), Issuer varchar(30), Issue varchar(20), ISO_Currency varchar(3), Exchange varchar(3), ExchDesc varchar(100), ISO_Country varchar(2), Price float, PriceDate smalldatetime, GIC int, SP_Sector int, ADR_Ratio float, MktCap float, Volume float, PE_Ratio float, PB_Ratio float, PCF_Ratio float, DivYield float, MSCI_Sector int, MSCI_IndGrp int, MSCI_Ind int, MSCI_SubInd int, MSCI_Industry_Desc varchar(50)) Here's a sample query (one of many) that fills the temp table: update #SECURITYMASTER set ibes_ticker = i.iticker from #SECURITYMASTER m join gsecmstr gsec on gsec.gsid = m.mqaid join gsecmap mp on mp.seccode = gsec.seccode join ibgsinfo3 i on i.code = mp.vencode and mp.ventype = 2 WHERE SUBSTRING(M.MQAID,1,1) = '@' Any ideas on how I might speed them up? |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-02-07 : 17:52:07
|
| Have you tried changing your where clause so it is WHERE M.MQAID LIKE '@%' |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-02-07 : 20:32:24
|
| And put an index on M.MQAID==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
pug2694328
Posting Yak Master
166 Posts |
Posted - 2006-02-08 : 10:49:09
|
Hey that's much faster.About a 50% reduction in wall time.The UNIQUE assignation creates secondary indexes, correct?create table #SecurityMaster (Code int, MQAid varchar(20), Sedol varchar(7), Cusip varchar(9), Ticker varchar(15), ISIN varchar(20), IBES_Ticker varchar(15), AssetType varchar(3), AssetDesc varchar(100), Issuer varchar(30), Issue varchar(20), ISO_Currency varchar(3), Exchange varchar(3), ExchDesc varchar(100), ISO_Country varchar(2), Price float, PriceDate smalldatetime, GIC int, SP_Sector int, ADR_Ratio float, MktCap float, Volume float, PE_Ratio float, PB_Ratio float, PCF_Ratio float, DivYield float, MSCI_Sector int, MSCI_IndGrp int, MSCI_Ind int, MSCI_SubInd int, MSCI_Industry_Desc varchar(50) ,PRIMARY KEY (MQAID) ,UNIQUE (CUSIP,CODE) ) |
|
|
|
|
|
|
Query running too slow