August 8, 2012 at 8:23 am
Dear All,
I have a problem with my query. This is my table structure and some data. Actually there is 400.000 records on test_table1 and 100.000 record on test_table2.
-- Structure for test_table1
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#test_table1','U') IS NOT NULL
DROP TABLE #test_table1
--===== Create the test table with
CREATE TABLE #test_table1
(
ID VARCHAR(15) NOT NULL,
AccountNumber VARCHAR(14) NOT NULL,
InvType VARCHAR(100),
FirstName VARCHAR(40),
MiddleName VARCHAR(40),
LastName VARCHAR(40),
Sex CHAR,
BirthDate DATETIME,
Nationality VARCHAR(100)
)
ALTER TABLE #test_table1
ADD CONSTRAINT pk_testtable1 PRIMARY KEY (ID,AccountNumber)
GO
INSERT INTO #test_table1
( ID ,
AccountNumber ,
InvType ,
FirstName ,
MiddleName ,
LastName ,
Sex ,
BirthDate ,
Nationality
)
SELECT 'IDD100211088647','AD001029100150','ID','Robert','','','','1965-02-10','India' UNION ALL
SELECT 'IDD060210753675','AD001070800147','ID','Tomy Goretha','','','','1979-02-06','India' UNION ALL
SELECT 'IDD291213785582','AD001088000166','ID','Andy','','','','1960-12-29','India' UNION ALL
SELECT 'IDD171273376143','AD001117700152','ID','Elisabeth Risort','','','','1978-12-17','India' UNION ALL
SELECT 'IDD150676045636','AD001144800134','ID','Lisbon','','','','1988-06-15','India' UNION ALL
SELECT 'IDD140810826918','OD001Q01400177','ID','DEWI HARTANA','','','','1965-08-14','India' UNION ALL
SELECT 'IDD250720871814','HP001024400163','ID','MELLIA SENJAYA','','','','1978-07-25','India' UNION ALL
SELECT 'IDD150811181900','HP001058100185','ID','JEFFREY WINATA','','','','1981-08-15','India' UNION ALL
SELECT 'IDD281211987560','HP001087300118','ID','HILMAN TASLIM','','','','1947-12-28','India' UNION ALL
SELECT 'CPD070258721868','HP001128200166','CP','GEO LINK Inc.','','','','2006-02-07','India' UNION ALL
SELECT 'IDD030212790091','HP001138200159','ID','Tony Liu','','','','1972-02-03','India' UNION ALL
SELECT 'IDD111011542143','HP001153500190','ID','SULIANTO. DR','','','','1960-10-11','India' UNION ALL
SELECT 'IDD221019714259','PC001084200115','ID','Thio Li Hong','','','','1964-10-22','India' UNION ALL
SELECT 'IDD291035208792','KK001843800187','ID','Asing','','','','1959-10-29','India' UNION ALL
SELECT 'IDD131152529026','KK001937800160','ID','Tjan Harmen','','','','1969-11-13','India' UNION ALL
SELECT 'IDD301113785425','LG001016000118','ID','WILLY','','','','1962-11-30','India' UNION ALL
SELECT 'IDD170810859747','YP001SLKS00191','ID','Justin','','','','1984-08-17','India' UNION ALL
SELECT 'IDD031113718148','KS001438600172','ID','Romy','','','','1976-11-03','India' UNION ALL
SELECT 'IDD200974783105','AI001T07000104','ID','Piter Tiendey','','','','1982-09-20','India' UNION ALL
SELECT 'IDD240716430035','KI001514400112','ID','Jason','','','','1954-07-24','India' UNION ALL
SELECT 'IDD010333270592','PI001082100167','ID','Anita Silvia','','','','1965-03-01','India' UNION ALL
SELECT 'IDD211265224285','AZ001468300195','ID','Laita','','','','1966-12-21','India' UNION ALL
SELECT 'IDD060210753675','KI001685100106','ID','TOMY GORETHA','','','','1979-02-06','India' UNION ALL
SELECT 'IDD201211017802','NI001Z96800144','ID','WILLIYANTO RUSTARDY','','','','1981-12-20','India' UNION ALL
SELECT 'IDD210210332908','II001107200169','ID','Lim Melie','','','','1972-02-21','India' UNION ALL
SELECT 'IDD121157256963','KI001023900111','ID','Suryani Kurniawan','','','','1945-11-12','India' UNION ALL
SELECT 'IDD101022266907','AO001019700100','ID','TIO HANDOKO PRASETYO','','','','1968-10-10','India' UNION ALL
SELECT 'IDD010534829574','PD001252100191','ID','Adrie Limen','','','','1960-05-01','India' UNION ALL
SELECT 'IDD161110364421','YJ001436600122','ID','William Sulaeman','','','','1965-11-16','India' UNION ALL
SELECT 'IDD131022268377','AO001052300180','ID','JANLIS JULIANNI','','','','1939-10-13','India' UNION ALL
SELECT 'IDD100921134637','GR001827100170','ID','JONY HALIM','','','','1978-09-10','India' UNION ALL
SELECT 'IDD180913400152','OD001C12500122','ID','SLAMET KITA SINULINGA, DRS','','','','1942-09-18','India' UNION ALL
SELECT 'IDD051021069254','GR001566400142','ID','BOND HAWANA','','','','1979-10-05','India' UNION ALL
SELECT 'IDD040816849012','KI001360500114','ID','Joe Eng Hong/Johan','','','','1970-08-04','India' UNION ALL
SELECT 'IDD060521189194','GR001912000129','ID','SLAMET IWAN SANTOSO','','','','1967-05-06','India' UNION ALL
SELECT 'IDD170829068074','AI001282400143','ID','Endah Lestari','','','','1985-08-17','India' UNION ALL
SELECT 'IDD070910456619','YP001J41200110','ID','ANDY TANUJAYA','','','','1964-09-07','India' UNION ALL
SELECT 'IDD031210140608','CC001369000127','ID','EKA SUHENDRA','','','','1982-12-03','India' UNION ALL
SELECT 'IDD221019714259','DH001283800179','ID','THIO LI HONG','','','','1964-10-22','India' UNION ALL
SELECT 'IDD010711055519','KI001778100167','ID','TJIN DAVID JONG','','','','1982-07-01','India' UNION ALL
SELECT 'IDD310310370253','CP001C83600186','ID','TEDDY DJOHAN','','','','1974-03-31','India' UNION ALL
SELECT 'IDD190623118846','II001002000134','ID','Hengki Jananto','','','','1951-06-19','India' UNION ALL
SELECT 'IDD220522182906','PG001006300124','ID','BUDY BUNTARAM','','','','1971-05-22','India' UNION ALL
SELECT 'IDD041211604624','KI001414100189','ID','Harianto','','','','1972-12-04','India' UNION ALL
SELECT 'IDD040634101228','YP001581500139','ID','VERONIKA HARYUNI SETYONINGSIH SE','','','','1975-06-04','India' UNION ALL
SELECT 'IDD100310611990','MG001336800184','ID','Irvan Martius','','','','1986-03-10','India' UNION ALL
SELECT 'IDD241226273385','FS001319200186','ID','Muhammad Zulkarnainirmm','','','','1953-12-24','India' UNION ALL
SELECT 'IDD100211088647','OD0012CD200101','ID','SUBIANTO IRAWAN','','','','1965-02-10','India' UNION ALL
SELECT 'IDD310718801712','PG001088600176','ID','Eka Namara Ginting','','','','1968-07-31','India' UNION ALL
SELECT 'IDD270210768064','ID001TO0700105','ID','ONGGO IWAN KUSNADI','','','','1953-02-27','India' UNION ALL
SELECT 'IDD190120904263','GR001852900150','ID','HENGKI SETIAWAN','','','','1953-01-19','India' UNION ALL
SELECT 'IDD030121523612','GR001369900168','ID','LYDIA HERLIANTY W','','','','1953-01-03','India' UNION ALL
SELECT 'IDD150839126309','PD001N74100193','ID','ELISA SANTI','','','','1985-08-15','India' UNION ALL
SELECT 'IDD220751171639','PG001131800138','ID','Yuliana Hartono','','','','1980-07-22','India' UNION ALL
SELECT 'IDD290910673445','DX001K01300114','ID','Agus Soejanto Phurwo','','','','1968-09-29','India' UNION ALL
SELECT 'IDD261213404585','CD001A46900120','ID','ANDY PRABAJAYA','','','','1960-12-26','India' UNION ALL
SELECT 'IDD210910516022','YP001SJWO00126','ID','SUNARYO SUGIANTO IR','','','','1956-09-21','India' UNION ALL
SELECT 'IDD170513053063','DH001423200192','ID','DRS. JUSAK PRIATNA RAHAJU','','','','1969-05-17','India' UNION ALL
SELECT 'IDD101112676801','PI001156000124','ID','INONG PRIYONO','','','','1961-11-10','India' UNION ALL
SELECT 'IDD221221152736','CP001C71100122','ID','FERRY LESLIE MULIJONO','','','','1975-12-22','India' UNION ALL
SELECT 'IDD160311199450','YU001170200184','ID','Budiman Gunawan','','','','1957-03-16','India' UNION ALL
SELECT 'IDD180413806478','KI001357000131','ID','Firman Simanjuntak','','','','1963-04-18','India' UNION ALL
SELECT 'IDD160274247817','PD001R09700187','ID','HANDY JOSEPH','','','','1986-02-16','India' UNION ALL
SELECT 'IDD141111505092','NI001459800178','ID','TJANG PAK NING','','','','1958-11-14','India' UNION ALL
SELECT 'IDD010534829574','IF001431400185','ID','ADRIE LIMEN','','','','1960-05-01','India' UNION ALL
SELECT 'IDD170414903576','IN001MTO000109','ID','Hermanto','','','','1975-04-17','India' UNION ALL
SELECT 'IDD180952380962','OD001G46000192','ID','GUNAWAN PERAJOGO','','','','1973-09-18','India' UNION ALL
SELECT 'IDD090541968354','BZ001232500121','ID','Danar Samron','','','','1983-05-09','India' UNION ALL
SELECT 'IDD230216671734','KI001700400137','ID','HETTY','','','','1967-02-23','India' UNION ALL
SELECT 'IDD310712757382','KK001614600103','ID','Wahono Santoso','','','','1974-07-31','India' UNION ALL
SELECT 'IDD080211049034','AI001527700110','ID','Yani Hermawan','','','','1951-02-08','India' UNION ALL
SELECT 'IDD161013447250','CC001577100186','ID','VENNY YULIANI YAP','','','','1957-10-16','India' UNION ALL
SELECT 'IDD100323130256','II001012300120','ID','Lay Susan Margareth','','','','1964-03-10','India' UNION ALL
SELECT 'IDD260459747772','DH001430800144','ID','TASMAN','','','','1986-04-26','India' UNION ALL
SELECT 'IDD280812738261','BS001017900194','ID','IR. JOSEPH MARTINUS','','','','1944-08-28','India' UNION ALL
SELECT 'IDD220352691867','CP001424900171','ID','YOHAN WILYANTO','','','','1979-03-22','India' UNION ALL
SELECT 'IDD181133917183','KK001477300138','ID','Christine Tejosukmono','','','','1976-11-18','India' UNION ALL
SELECT 'IDD290916613071','KI001441700127','ID','Tijono Widjojo','','','','1953-09-29','India' UNION ALL
SELECT 'IDD261056109047','AN001155700111','ID','V. SARI DEWI','','','','1977-10-26','India' UNION ALL
SELECT 'IDD020111563203','IN001ALX000166','ID','Alexander Sugiman Tjioe','','','','1959-01-02','India' UNION ALL
SELECT 'IDD040610765504','CP001261200143','ID','EFENDI HATMAJA','','','','1955-06-04','India' UNION ALL
SELECT 'IDD190112727552','TP001063000121','ID','SANTOSO.','','','','1973-01-19','India' UNION ALL
SELECT 'IDD050216473863','YP001SHFJ00137','ID','YOPIE NATA SURYO PUTRO ST','','','','1977-02-05','India' UNION ALL
SELECT 'IDD020111563203','AO001057700151','ID','ALEXANDER SUGIMAN TJIOE','','','','1959-01-02','India' UNION ALL
SELECT 'IDD270311546155','TF001W27500106','ID','RITA HERAWATI TATANG','','','','1974-03-27','India' UNION ALL
SELECT 'IDD210511352207','SH001213300149','ID','Djonli Tamir','','','','1967-05-21','India' UNION ALL
SELECT 'IDD070534037094','KK001299000192','ID','KRISTIANA CHENDRA','','','','1947-05-07','India' UNION ALL
SELECT 'IDD051110230525','MG001312600102','ID','Lina Sutjipto','','','','1972-11-05','India' UNION ALL
SELECT 'IDD270311546155','KK001321200159','ID','Rita Herawati Tatang','','','','1974-03-27','India' UNION ALL
SELECT 'IDD250816880449','YP001SLX100173','ID','ANDREW BUDIMAN','','','','1964-08-25','India' UNION ALL
SELECT 'IDD110910623702','PG001075000170','ID','Lenna Wikarta','','','','1968-09-11','India' UNION ALL
SELECT 'IDD200946655179','KI001744000122','ID','EDWIN KUSWANTO','','','','1987-09-20','India' UNION ALL
SELECT 'IDD200913395516','SQ001F32500120','ID','SUHAMAN','','','','1966-09-20','India' UNION ALL
SELECT 'IDD131012752644','PF001115000190','ID','TATANG BUDY SANTOSO','','','','1968-10-13','India' UNION ALL
SELECT 'IDD171210720489','AF001W02900104','ID','WATNI DJOHAN','','','','1936-12-17','India' UNION ALL
SELECT 'IDD271118424291','CC001CY7A00101','ID','NOVIYAN HALIM','','','','1972-11-27','India' UNION ALL
SELECT 'IDD031011952290','BS001175600168','ID','HENGKY CANDRA','','','','1953-10-03','India' UNION ALL
SELECT 'IDD110140578811','AO001011900185','ID','MARNI M. HUTAPEA','','','','1968-01-11','India' UNION ALL
SELECT 'IDD291220065756','AH001209300183','ID','PETER GEORGE ANDRE LAWA','','','','1969-12-29','India' UNION ALL
SELECT 'IDD160113996506','YP001SEOW00139','ID','DAVID TANDRIS','','','','1983-01-16','India'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#test_table2','U') IS NOT NULL
DROP TABLE #test_table2
--===== Create the test table with
CREATE TABLE #test_table2(
[Row] [varchar](255) NULL,
[Type] [varchar](255) NULL,
[BS] [varchar](255) NULL,
[Filled] [varchar](255) NULL,
[ReportTime] [varchar](255) NULL,
[Date] [datetime] NULL,
[Price] [varchar](255) NULL,
[TransVol] [numeric](18, 0) NULL,
[TransVal] [varchar](255) NULL,
[Board] [varchar](255) NULL,
[OrderId] [varchar](255) NULL,
[TradeId] [varchar](255) NULL,
[AskClient] [varchar](255) NULL,
[AskPrice] [varchar](255) NULL,
[AskPretradeVol] [varchar](255) NULL,
[AskYield] [varchar](255) NULL,
[AskFlags] [varchar](255) NULL,
[BidOrderId] [varchar](255) NULL,
[BidTimeEntered] [varchar](255) NULL,
[BidBroker] [varchar](255) NULL,
[BidTrader] [varchar](255) NULL,
[BidClient] [varchar](255) NULL,
[BidPrice] [varchar](255) NULL,
[BidPretradeVol] [varchar](255) NULL,
[BidYield] [varchar](255) NULL,
)
INSERT INTO #test_table2
( Row ,
Type ,
BS ,
Filled ,
ReportTime ,
Date ,
Price ,
TransVol ,
TransVal ,
Board ,
OrderId ,
TradeId ,
AskClient ,
AskPrice ,
AskPretradeVol ,
AskYield ,
AskFlags ,
BidOrderId ,
BidTimeEntered ,
BidBroker ,
BidTrader ,
BidClient ,
BidPrice ,
BidPretradeVol ,
BidYield
)
SELECT '2065','TRADE','Trd','','Jan 1 1900 10:18AM','Apr 12 2012 12:00AM','440','100000','44000000','Regular Board','-','153859260','237783','Rp440.00','x100,000','0%','RG','374735749','10:18:34','YU','YUTR1004','290473','440','2396000.00','0%' UNION ALL
SELECT '3186','TRADE','Trd','','Jan 1 1900 10:05AM','Apr 13 2012 12:00AM','420','7500','3150000','Regular Board','-','154012853','237783','Rp420.00','x250,000','0%','RG','375065587','10:05:06','OD','ODTR1005','448719','420','7500.00','0%' UNION ALL
SELECT '3187','TRADE','Trd','','Jan 1 1900 10:05AM','Apr 13 2012 12:00AM','420','15000','6300000','Regular Board','-','154012864','237783','Rp420.00','x242,500','0%','RG','375065608','10:05:07','YP','YPTR1008','173528','420','15000.00','0%' UNION ALL
SELECT '3190','TRADE','Trd','','Jan 1 1900 10:14AM','Apr 13 2012 12:00AM','420','1000','420000','Regular Board','-','154018373','237783','Rp420.00','x227,500','0%','RG','375076569','10:14:32','PD','PDTR1004','137648','420','1000.00','0%' UNION ALL
SELECT '2045','TRADE','Trd','','Jan 1 1900 10:18AM','Apr 12 2012 12:00AM','440','100000','44000000','Regular Board','-','153859240','237783','Rp440.00','x100,000','0%','RG','374735749','10:18:34','YU','YUTR1004','290473','440','2783000.00','0%' UNION ALL
SELECT '3191','TRADE','Trd','','Jan 1 1900 10:29AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154024980','237783','Rp420.00','x226,500','0%','RG','375090049','10:29:09','YP','YPTR1004','495874','420','5000.00','0%' UNION ALL
SELECT '3192','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 13 2012 12:00AM','420','12500','5250000','Regular Board','-','154025523','237783','Rp420.00','x221,500','0%','RG','375090915','10:30:04','YP','YPTR1007','173528','420','12500.00','0%' UNION ALL
SELECT '3193','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 13 2012 12:00AM','420','12500','5250000','Regular Board','-','154025630','237783','Rp420.00','x209,000','0%','RG','375091204','10:30:21','YP','YPTR1005','173528','420','12500.00','0%' UNION ALL
SELECT '3194','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 13 2012 12:00AM','420','13000','5460000','Regular Board','-','154025928','237783','Rp420.00','x196,500','0%','RG','375091629','10:30:44','YP','YPTR1006','173528','420','13000.00','0%' UNION ALL
SELECT '3195','TRADE','Trd','','Jan 1 1900 10:31AM','Apr 13 2012 12:00AM','420','2500','1050000','Regular Board','-','154026640','237783','Rp420.00','x183,500','0%','RG','375092713','10:31:42','AI','AITR1001','747831','420','2500.00','0%' UNION ALL
SELECT '3196','TRADE','Trd','','Jan 1 1900 10:31AM','Apr 13 2012 12:00AM','420','3000','1260000','Regular Board','-','154026654','237783','Rp420.00','x181,000','0%','RG','375092732','10:31:43','YP','YPTR1005','173528','420','3000.00','0%' UNION ALL
SELECT '3198','TRADE','Trd','','Jan 1 1900 10:31AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154026714','237783','Rp420.00','x178,000','0%','RG','375092817','10:31:48','YP','YPTR1001','331135','420','5000.00','0%' UNION ALL
SELECT '3199','TRADE','Trd','','Jan 1 1900 10:32AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154026938','237783','Rp420.00','x173,000','0%','RG','375093467','10:32:32','YP','YPTR1001','169331','420','5000.00','0%' UNION ALL
SELECT '3200','TRADE','Trd','','Jan 1 1900 10:32AM','Apr 13 2012 12:00AM','420','500','210000','Regular Board','-','154027050','237783','Rp420.00','x168,000','0%','RG','375093625','10:32:45','YP','YPTR1004','173528','420','500.00','0%' UNION ALL
SELECT '3201','TRADE','Trd','','Jan 1 1900 10:33AM','Apr 13 2012 12:00AM','420','500','210000','Regular Board','-','154027104','237783','Rp420.00','x167,500','0%','RG','375093841','10:33:03','PD','PDTR1006','102979','420','500.00','0%' UNION ALL
SELECT '3203','TRADE','Trd','','Jan 1 1900 10:33AM','Apr 13 2012 12:00AM','420','500','210000','Regular Board','-','154027338','237783','Rp420.00','x167,000','0%','RG','375094319','10:33:44','DH','DHTR1001','769149','420','500.00','0%' UNION ALL
SELECT '3204','TRADE','Trd','','Jan 1 1900 10:37AM','Apr 13 2012 12:00AM','420','5000','2100000','Regular Board','-','154029126','237783','Rp420.00','x166,500','0%','RG','375097953','10:37:26','NI','NITR1003','741218','420','5000.00','0%' UNION ALL
SELECT '3205','TRADE','Trd','','Jan 1 1900 10:38AM','Apr 13 2012 12:00AM','420','161500','67830000','Regular Board','-','154029530','237783','Rp420.00','x161,500','0%','RG','375098904','10:38:41','CP','CPTR1001','116591','420','500000.00','0%' UNION ALL
SELECT '1519','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','812500','337187500','Regular Board','-','153844940','148436','Rp415.00','x1,000,000','0%','RG','374705816','9:53:19','YU','YUTR1004','290473','415','812500.00','0%' UNION ALL
SELECT '1520','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','1500','622500','Regular Board','-','153844941','148436','Rp415.00','x187,500','0%','RG','374705999','9:53:22','YP','YPTR1006','224666','415','1500.00','0%' UNION ALL
SELECT '1521','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','100000','41500000','Regular Board','-','153844942','148436','Rp415.00','x186,000','0%','RG','374707112','9:54:09','KI','KITR1003','715904','415','100000.00','0%' UNION ALL
SELECT '1522','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','2000','830000','Regular Board','-','153844943','148436','Rp415.00','x86,000','0%','RG','374707138','9:54:10','NI','NITR1002','757942','415','2000.00','0%' UNION ALL
SELECT '1523','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','500','207500','Regular Board','-','153844944','148436','Rp415.00','x84,000','0%','RG','374708022','9:54:50','PD','PDTR1006','130494','415','500.00','0%' UNION ALL
SELECT '1524','TRADE','Trd','','Jan 1 1900 9:55AM','Apr 12 2012 12:00AM','415','83500','34652500','Regular Board','-','153844962','148436','Rp415.00','x83,500','0%','RG','374709516','9:55:57','KI','KITR1004','600049','415','100000.00','0%' UNION ALL
SELECT '877','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828514','587218','Rp425.00','x51,500','0%','RG','374671739','9:33:46','PD','PDTR1007','728545','425','5000.00','0%' UNION ALL
SELECT '878','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','2500','1062500','Regular Board','-','153828590','587218','Rp425.00','x46,500','0%','RG','374671933','9:33:51','PD','PDTR1005','122460','425','2500.00','0%' UNION ALL
SELECT '879','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','500','212500','Regular Board','-','153828602','587218','Rp425.00','x44,000','0%','RG','374671966','9:33:52','NI','NITR1001','120149','425','500.00','0%' UNION ALL
SELECT '880','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','2500','1062500','Regular Board','-','153828621','587218','Rp425.00','x43,500','0%','RG','374672018','9:33:54','KK','KKTR1005','339786','425','2500.00','0%' UNION ALL
SELECT '881','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828625','587218','Rp425.00','x41,000','0%','RG','374672034','9:33:54','YP','YPTR1006','659802','425','5000.00','0%' UNION ALL
SELECT '882','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','12500','5312500','Regular Board','-','153828641','587218','Rp425.00','x36,000','0%','RG','374672098','9:33:56','AI','AITR1001','293662','425','12500.00','0%' UNION ALL
SELECT '883','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828663','587218','Rp425.00','x23,500','0%','RG','374672178','9:33:59','KK','KKTR1001','448650','425','5000.00','0%' UNION ALL
SELECT '885','TRADE','Trd','','Jan 1 1900 9:34AM','Apr 12 2012 12:00AM','425','10000','4250000','Regular Board','-','153828685','587218','Rp425.00','x18,500','0%','RG','374672259','9:34:01','BS','BSTR1001','119875','425','10000.00','0%' UNION ALL
SELECT '887','TRADE','Trd','','Jan 1 1900 9:34AM','Apr 12 2012 12:00AM','425','8500','3612500','Regular Board','-','153828737','587218','Rp425.00','x8,500','0%','RG','374672478','9:34:07','YP','YPTR1006','339523','425','126500.00','0%' UNION ALL
SELECT '872','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','51500','21887500','Regular Board','-','153828451','587218','Rp425.00','x116,500','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','51500.00','0%' UNION ALL
SELECT '873','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','11000','4675000','Regular Board','-','153828463','587218','Rp425.00','x65,000','0%','RG','374671619','9:33:42','PD','PDTR1001','407483','425','11000.00','0%' UNION ALL
SELECT '874','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','1000','425000','Regular Board','-','153828473','587218','Rp425.00','x54,000','0%','RG','374671646','9:33:43','YP','YPTR1004','134763','425','1000.00','0%' UNION ALL
SELECT '875','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','1500','637500','Regular Board','-','153828480','587218','Rp425.00','x53,000','0%','RG','374671665','9:33:44','PD','PDTR1004','591222','425','1500.00','0%' UNION ALL
SELECT '818','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','500','212500','Regular Board','-','153827895','326552','Rp425.00','x500','0%','RG','374670078','9:33:00','KK','KKTR1003','555723','425','394000.00','0%' UNION ALL
SELECT '545','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 12 2012 12:00AM','440','1709000','751960000','Regular Board','-','153826141','326552','Rp435.00','x1,709,000','0%','RG','374659503','9:30:12','YU','YUTR1001','290473','440','2289500.00','0%' UNION ALL
SELECT '1918','TRADE','Trd','','Jan 1 1900 10:16AM','Apr 12 2012 12:00AM','425','180500','76712500','Regular Board','-','153858447','326552','Rp425.00','x180,500','0%','RG','374734244','10:16:55','KK','KKTR1004','466377','425','924000.00','0%' UNION ALL
SELECT '1919','TRADE','Trd','','Jan 1 1900 10:16AM','Apr 12 2012 12:00AM','425','45000','19125000','Regular Board','-','153858448','326552','Rp425.00','x45,000','0%','RG','374734244','10:16:55','KK','KKTR1004','466377','425','743500.00','0%' UNION ALL
SELECT '1572','TRADE','Trd','','Jan 1 1900 9:57AM','Apr 12 2012 12:00AM','410','1000','410000','Regular Board','-','153846025','326552','Rp410.00','x1,000','0%','RG','374626151','9:30:01','GA','GATR1001','169280','410','1093500.00','0%' UNION ALL
SELECT '986','TRADE','Trd','','Jan 1 1900 9:34AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153829271','326552','Rp425.00','x5,000','0%','RG','374673850','9:34:50','YU','YUTR1001','290473','425','1903000.00','0%' UNION ALL
SELECT '1364','TRADE','Trd','','Jan 1 1900 9:48AM','Apr 12 2012 12:00AM','425','14000','5950000','Regular Board','-','153839838','326552','Rp425.00','x14,000','0%','RG','374684670','9:39:52','XA','XATR1002','739184','425','155000.00','0%' UNION ALL
SELECT '2221','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 12 2012 12:00AM','430','10000','4300000','Regular Board','-','153861300','326552','Rp430.00','x27,500','0%','RG','374739819','10:23:08','YP','YPTR1008','201922','435','10000.00','0%' UNION ALL
SELECT '2222','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 12 2012 12:00AM','430','2000','860000','Regular Board','-','153861391','326552','Rp430.00','x17,500','0%','RG','374739933','10:23:11','PD','PDTR1001','407483','430','2000.00','0%' UNION ALL
SELECT '2223','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 12 2012 12:00AM','430','15500','6665000','Regular Board','-','153861406','326552','Rp430.00','x15,500','0%','RG','374739968','10:23:14','KK','KKTR1004','466377','430','250000.00','0%' UNION ALL
SELECT '1661','TRADE','Trd','','Jan 1 1900 9:58AM','Apr 12 2012 12:00AM','410','3000','1230000','Regular Board','-','153847391','326552','Rp410.00','x3,000','0%','RG','374626151','9:30:01','GA','GATR1001','169280','410','741000.00','0%' UNION ALL
SELECT '1700','TRADE','Trd','','Jan 1 1900 10:00AM','Apr 12 2012 12:00AM','415','2500','1037500','Regular Board','-','153848934','100173','Rp415.00','x223,000','0%','RG','374716222','10:00:42','KI','KITR1003','742276','415','2500.00','0%' UNION ALL
SELECT '1701','TRADE','Trd','','Jan 1 1900 10:00AM','Apr 12 2012 12:00AM','415','2500','1037500','Regular Board','-','153848935','100173','Rp415.00','x220,500','0%','RG','374716223','10:00:42','KI','KITR1003','742276','415','2500.00','0%' UNION ALL
SELECT '1176','TRADE','Trd','','Jan 1 1900 9:37AM','Apr 12 2012 12:00AM','435','100000','43500000','Regular Board','-','153831189','136310','Rp435.00','x118,000','0%','RG','374679287','9:37:33','AI','AITR1001','237783','435','100000.00','0%' UNION ALL
SELECT '870','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','43500','18487500','Regular Board','-','153828449','164421','Rp425.00','x43,500','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','100000.00','0%' UNION ALL
SELECT '871','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','5000','2125000','Regular Board','-','153828450','189306','Rp425.00','x5,000','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','56500.00','0%' UNION ALL
SELECT '872','TRADE','Trd','','Jan 1 1900 9:33AM','Apr 12 2012 12:00AM','425','51500','21887500','Regular Board','-','153828451','587218','Rp425.00','x116,500','0%','RG','374671556','9:33:41','AI','AITR1001','237783','425','51500.00','0%' UNION ALL
SELECT '89','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','125000','35625000','Regular Board','-','153573994','109051','Rp285.00','x125,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3203500.00','0%' UNION ALL
SELECT '90','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','6000','1710000','Regular Board','-','153574008','609238','Rp285.00','x6,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3078500.00','0%' UNION ALL
SELECT '91','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','6000','1710000','Regular Board','-','153574013','758837','Rp285.00','x6,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3072500.00','0%' UNION ALL
SELECT '92','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153574028','142813','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3066500.00','0%' UNION ALL
SELECT '93','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','34500','9832500','Regular Board','-','153574045','163807','Rp285.00','x34,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3056500.00','0%' UNION ALL
SELECT '94','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','25000','7125000','Regular Board','-','153574055','164528','Rp285.00','x25,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','3022000.00','0%' UNION ALL
SELECT '95','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','25000','7125000','Regular Board','-','153574062','166400','Rp285.00','x25,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2997000.00','0%' UNION ALL
SELECT '96','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','4500','1282500','Regular Board','-','153574075','219315','Rp285.00','x4,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2972000.00','0%' UNION ALL
SELECT '97','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','2500','712500','Regular Board','-','153574088','282040','Rp285.00','x2,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2967500.00','0%' UNION ALL
SELECT '98','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','87500','24937500','Regular Board','-','153574097','214569','Rp285.00','x87,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2965000.00','0%' UNION ALL
SELECT '99','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153574119','282040','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2877500.00','0%' UNION ALL
SELECT '166','TRADE','Trd','','Jan 1 1900 9:59AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153590565','715581','Rp280.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1463000.00','0%' UNION ALL
SELECT '167','TRADE','Trd','','Jan 1 1900 10:01AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153592028','164267','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1461000.00','0%' UNION ALL
SELECT '168','TRADE','Trd','','Jan 1 1900 10:05AM','Apr 10 2012 12:00AM','285','100000','28500000','Regular Board','-','153593702','204216','Rp285.00','x100,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1451000.00','0%' UNION ALL
SELECT '169','TRADE','Trd','','Jan 1 1900 10:06AM','Apr 10 2012 12:00AM','285','30000','8550000','Regular Board','-','153593933','221829','Rp285.00','x30,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1351000.00','0%' UNION ALL
SELECT '170','TRADE','Trd','','Jan 1 1900 10:06AM','Apr 10 2012 12:00AM','285','68500','19522500','Regular Board','-','153594268','199581','Rp285.00','x68,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1321000.00','0%' UNION ALL
SELECT '171','TRADE','Trd','','Jan 1 1900 10:07AM','Apr 10 2012 12:00AM','285','3000','855000','Regular Board','-','153594679','221258','Rp285.00','x3,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1252500.00','0%' UNION ALL
SELECT '172','TRADE','Trd','','Jan 1 1900 10:09AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153595819','164284','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1249500.00','0%' UNION ALL
SELECT '173','TRADE','Trd','','Jan 1 1900 10:14AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153598117','100424','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1239500.00','0%' UNION ALL
SELECT '174','TRADE','Trd','','Jan 1 1900 10:14AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153598130','221808','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1234500.00','0%' UNION ALL
SELECT '175','TRADE','Trd','','Jan 1 1900 10:18AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153599240','660363','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1232500.00','0%' UNION ALL
SELECT '176','TRADE','Trd','','Jan 1 1900 10:23AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153600837','450121','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1230500.00','0%' UNION ALL
SELECT '23','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','58500','16672500','Regular Board','-','153573413','103949','Rp280.00','x58,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','5000000.00','0%' UNION ALL
SELECT '24','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','100000','28500000','Regular Board','-','153573423','164532','Rp285.00','x100,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4941500.00','0%' UNION ALL
SELECT '25','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','50000','14250000','Regular Board','-','153573426','164508','Rp285.00','x50,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4841500.00','0%' UNION ALL
SELECT '26','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','25000','7125000','Regular Board','-','153573430','467574','Rp285.00','x25,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4791500.00','0%' UNION ALL
SELECT '27','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153573431','164472','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4766500.00','0%' UNION ALL
SELECT '28','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153573436','164534','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4756500.00','0%' UNION ALL
SELECT '29','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','6000','1710000','Regular Board','-','153573438','340370','Rp250.00','x6,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4746500.00','0%' UNION ALL
SELECT '30','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','22500','6412500','Regular Board','-','153573439','263891','Rp255.00','x22,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4740500.00','0%' UNION ALL
SELECT '31','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','1500','427500','Regular Board','-','153573443','467538','Rp270.00','x1,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4718000.00','0%' UNION ALL
SELECT '32','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','50000','14250000','Regular Board','-','153573446','164508','Rp285.00','x50,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4716500.00','0%' UNION ALL
SELECT '33','TRADE','Trd','','Jan 1 1900 9:30AM','Apr 10 2012 12:00AM','285','10000','2850000','Regular Board','-','153573448','114241','Rp285.00','x10,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','4666500.00','0%' UNION ALL
SELECT '100','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','8500','2422500','Regular Board','-','153574138','360110','Rp285.00','x8,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2875500.00','0%' UNION ALL
SELECT '101','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','14000','3990000','Regular Board','-','153574164','231188','Rp285.00','x14,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2867000.00','0%' UNION ALL
SELECT '102','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5500','1567500','Regular Board','-','153574165','106588','Rp285.00','x5,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2853000.00','0%' UNION ALL
SELECT '103','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153574177','111691','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2847500.00','0%' UNION ALL
SELECT '104','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','2000','570000','Regular Board','-','153574266','146366','Rp285.00','x2,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2842500.00','0%' UNION ALL
SELECT '105','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','37000','10545000','Regular Board','-','153574267','164729','Rp285.00','x37,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2840500.00','0%' UNION ALL
SELECT '106','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153574294','216005','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2803500.00','0%' UNION ALL
SELECT '107','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','5000','1425000','Regular Board','-','153574318','115461','Rp285.00','x5,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2798500.00','0%' UNION ALL
SELECT '108','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','1000','285000','Regular Board','-','153574319','210692','Rp285.00','x1,000','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2793500.00','0%' UNION ALL
SELECT '109','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','84500','24082500','Regular Board','-','153574332','262603','Rp285.00','x84,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2792500.00','0%' UNION ALL
SELECT '110','TRADE','Trd','','Jan 1 1900 9:31AM','Apr 10 2012 12:00AM','285','3500','997500','Regular Board','-','153574344','216058','Rp285.00','x3,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','2708000.00','0%' UNION ALL
SELECT '177','TRADE','Trd','','Jan 1 1900 10:30AM','Apr 10 2012 12:00AM','285','2500','712500','Regular Board','-','153602959','101354','Rp285.00','x2,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1220500.00','0%' UNION ALL
SELECT '178','TRADE','Trd','','Jan 1 1900 10:41AM','Apr 10 2012 12:00AM','285','3500','997500','Regular Board','-','153606644','731066','Rp285.00','x3,500','0%','RG','374032596','9:30:00','SQ','SQTR1001','127686','285','1218000.00','0%'
Relation between both table is Substring(#test_table1.ID,8,6) equal AskClient or BidClient. and there's can be same ID on #test_table1, because it refer to same person but different accountNumber.
From both table i has to get all of data from #test_table2 and FirstName,MiddleName,LastName from #test_table1.
I has made this query
SELECT
test2.*,
sellerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),
sellerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),
sellerLastName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),
buyerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),
buyerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),
buyerLastName = (SELECT TOP 1 test1.LastName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient)
FROM #test_table2 test2
I realize that from my query i will get poor performance when it execute, but i have no idea how to relate between both table without subquery. I have create with join but it gives me more data than i need it. I need query that give better performance than mine.
For information, i'm using SQL Server 2008 on Windows Server 2008
Would anyone here help me, a suggestion or advice maybe. I'm sorry for my bad english. I really appreciate all of your help.
Regards
August 8, 2012 at 8:49 am
First, thank you for the setup, it will help a lot. Just one thing missing that would help more, expected results. What should the query return based on the sample data you provided. Please don't describe, show us in a third table like you provided us the sample data. This will give us something to actually test against.
Plus, for me, it helps to see what I am trying to accomplish.
August 8, 2012 at 8:56 am
i'm sorry for not describe it clearly. What i want is exactly same like query that i made before that i made before
SELECT
test2.*,
sellerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),
sellerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),
sellerLastName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.AskClient),
buyerFirstName = (SELECT TOP 1 test1.FirstName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),
buyerMiddleName = (SELECT TOP 1 test1.MiddleName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient),
buyerLastName = (SELECT TOP 1 test1.LastName FROM #test_table1 test1 WHERE SUBSTRING(test1.ID,8,6) = test2.BidClient)
FROM #test_table2 test2
When you run this query with table and data that i provide, that's exactly the return i expected. The problem is, my query has a poor performance, when the data get bigger. When i execute it with 400.000 record on #test_table1 and 100.000 record on #test_table2 it tooks 20 minutes and still not finish. So i need suggestion about how to make it more efficient.
Thanks.
August 8, 2012 at 9:13 am
I believe there's a problem with your sample data because there's not to much matches (only 19)
However, you might find useful to use a CTE with the ranking function ROW_NUMBER.
Here's an example:
;WITH Test AS(
SELECTSUBSTRING(t.ID,8,6) AS id,
t.FirstName,
t.MiddleName,
t.LastName,
ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.ID) AS RN
FROM#test_table1 t)
SELECT
test2.*,
sellerFirstName = t1a.FirstName,
sellerMiddleName = t1a.MiddleName,
sellerLastName = t1a.LastName,
buyerFirstName = t1b.FirstName,
buyerMiddleName = t1b.MiddleName,
buyerLastName = t1b.LastName
FROM #test_table2 test2
LEFT
JOIN Test t1a ON t1a.ID = test2.AskClient AND t1a.RN = 1
LEFT
JOIN Test t1b ON t1b.ID = test2.BidClient AND t1a.RN = 1
WHERE t1a.FirstName IS NOT NULL
OR t1b.FirstName IS NOT NULL
Last recommendations:
- Don't just copy and paste, undestand it before you use it and test it
- Don't use SELECT * in production, always use explicit columns.
August 8, 2012 at 8:00 pm
Luis Cazares (8/8/2012)
I believe there's a problem with your sample data because there's not to much matches (only 19)However, you might find useful to use a CTE with the ranking function ROW_NUMBER.
Here's an example:
;WITH Test AS(
SELECTSUBSTRING(t.ID,8,6) AS id,
t.FirstName,
t.MiddleName,
t.LastName,
ROW_NUMBER() OVER (PARTITION BY t.ID ORDER BY t.ID) AS RN
FROM#test_table1 t)
SELECT
test2.*,
sellerFirstName = t1a.FirstName,
sellerMiddleName = t1a.MiddleName,
sellerLastName = t1a.LastName,
buyerFirstName = t1b.FirstName,
buyerMiddleName = t1b.MiddleName,
buyerLastName = t1b.LastName
FROM #test_table2 test2
LEFT
JOIN Test t1a ON t1a.ID = test2.AskClient AND t1a.RN = 1
LEFT
JOIN Test t1b ON t1b.ID = test2.BidClient AND t1a.RN = 1
WHERE t1a.FirstName IS NOT NULL
OR t1b.FirstName IS NOT NULL
Last recommendations:
- Don't just copy and paste, undestand it before you use it and test it
- Don't use SELECT * in production, always use explicit columns.
That's a behaviour of my data, i just have to relate both table, and it didn't has to be match at all.
But thanks for your suggestion, i have modified your suggested query and i think it solve my problem. Thanks a lot guys.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy