Different Methods of JOINing to table with 6.4 Million Records

Question

Different Methods of JOINing to table with 6.4 Million Records

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 1

I'll try to get you a better sample dataset. Working on it now.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

I had to fix some of the data in the second data set you PM'ed as the number of columns did not match. Some of the selct statements only had 6 columns instead of 7. Now the problem is that I am getting primary key violations attempting to insert data into both tables.

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 3

That's what I get for using Textpad (for the macros) to produce the sample dataset instead of SSMS, lol. Anyways, I PM'd the changes to you.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 4

Matthew Lehn (4/21/2009)
That's what I get for using Textpad (for the macros) to produce the sample dataset instead of SSMS, lol. Anyways, I PM'd the changes to you.

If you post them here, instead of PMing them, I'll take a look at them too. Not that Lynn isn't up to handling whatever you've got, but more eye-brain sets on the problem will often result in options that might not otherwise come up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is the sample data.

INSERT INTO dbo.BackgroundViews

SELECT '1/8/09 9:18:01',46636,17,15,8,'222.127.161.230'

UNION ALL

SELECT '1/8/09 9:18:02',46636,17,15,8,'82.109.68.165'

UNION ALL

SELECT '1/8/09 9:18:03',46636,17,15,8,'136.229.5.44'

UNION ALL

SELECT '1/8/09 9:18:04',46636,17,15,8,'62.2.187.21'

UNION ALL

SELECT '1/8/09 9:18:05',46636,17,15,8,'86.96.227.88'

UNION ALL

SELECT '1/8/09 9:18:06',23452,17,15,8,'86.96.228.86'

UNION ALL

SELECT '1/8/09 9:19:07',23452,17,15,8,'86.96.228.86'

UNION ALL

SELECT '1/8/09 9:18:08',46636,17,15,8,'202.79.203.51'

UNION ALL

SELECT '1/8/09 9:18:09',46636,17,15,8,'207.164.145.50'

UNION ALL

SELECT '1/8/09 9:18:10',46636,17,15,8,'12.164.196.100'

UNION ALL

SELECT '1/8/09 9:18:11',46636,17,15,8,'67.216.107.26'

UNION ALL

SELECT '1/8/09 9:18:12',46636,17,15,8,'207.212.113.163'

UNION ALL

SELECT '1/8/09 9:18:13',46636,17,15,8,'75.51.250.230'

UNION ALL

SELECT '1/8/09 9:18:14',53136,17,15,8,'158.35.225.227'

UNION ALL

SELECT '1/8/09 9:18:15',53136,17,15,8,'158.35.225.229'

UNION ALL

SELECT '1/8/09 9:18:16',46636,17,15,8,'64.178.245.247'

UNION ALL

SELECT '1/8/09 9:18:17',46636,17,15,8,'65.114.89.221'

UNION ALL

SELECT '1/8/09 9:18:18',46636,17,15,8,'208.97.90.34'

UNION ALL

SELECT '1/8/09 9:18:19',46636,17,15,8,'167.78.4.18'

UNION ALL

SELECT '1/8/09 9:18:20',46636,17,15,8,'64.207.232.26'

UNION ALL

SELECT '1/8/09 9:18:21',46636,17,15,8,'76.246.168.2'

UNION ALL

SELECT '1/8/09 9:18:22',46636,17,15,8,'74.41.98.134'

UNION ALL

SELECT '1/8/09 9:18:23',46636,17,15,8,'74.43.230.70'

UNION ALL

SELECT '1/8/09 9:19:24',46636,17,15,8,'74.43.230.70'

UNION ALL

SELECT '1/8/09 10:08:25',46636,17,15,8,'74.43.230.70'

UNION ALL

SELECT '1/8/09 9:18:26',97570,17,15,8,'194.73.98.253'

UNION ALL

SELECT '1/8/09 9:18:27',46636,17,15,8,'66.42.199.83'

UNION ALL

SELECT '1/8/09 9:18:28',46636,17,15,8,'66.161.195.130'

UNION ALL

SELECT '1/8/09 9:18:29',46636,17,15,8,'216.68.182.15'

UNION ALL

SELECT '1/8/09 9:18:30',46636,17,15,8,'66.0.251.6'

UNION ALL

SELECT '1/8/09 9:18:31',46636,17,15,8,'216.220.231.132'

UNION ALL

SELECT '1/8/09 9:18:32',46636,17,15,8,'76.236.174.81'

UNION ALL

SELECT '1/8/09 9:18:33',46636,17,15,8,'92.41.119.229'

UNION ALL

SELECT '1/8/09 9:18:34',46636,17,15,8,'124.197.112.183'

UNION ALL

SELECT '1/8/09 9:18:35',46636,17,15,8,'116.197.240.159'

UNION ALL

SELECT '1/8/09 9:18:36',46636,17,15,8,'119.160.120.31'

UNION ALL

SELECT '1/8/09 9:18:37',46636,17,15,8,'119.160.120.33'

UNION ALL

SELECT '1/8/09 9:18:38',46636,17,15,8,'119.160.120.37'

UNION ALL

SELECT '1/8/09 9:18:39',46636,17,15,8,'164.144.252.26'

UNION ALL

SELECT '1/8/09 9:18:40',46636,17,15,8,'204.112.175.190'

UNION ALL

SELECT '1/8/09 9:18:41',46636,17,15,8,'84.104.37.2'

UNION ALL

SELECT '1/8/09 9:18:42',46636,17,15,8,'85.72.212.214'

UNION ALL

SELECT '1/8/09 9:18:43',46636,17,15,8,'82.99.30.3'

UNION ALL

SELECT '1/8/09 9:18:44',25645,17,15,8,'82.99.30.4'

UNION ALL

SELECT '1/8/09 9:18:45',46636,17,15,8,'82.99.30.11'

UNION ALL

SELECT '1/8/09 9:18:46',46636,17,15,8,'82.99.30.16'

UNION ALL

SELECT '1/8/09 9:18:47',46636,17,15,8,'82.99.30.17'

UNION ALL

SELECT '1/8/09 9:18:48',76756,17,15,8,'82.31.92.148'

UNION ALL

SELECT '1/8/09 9:18:49',46636,17,15,8,'82.18.143.188'

UNION ALL

SELECT '1/8/09 9:18:50',2432,17,15,8,'209.69.34.80'

UNION ALL

SELECT '1/8/09 9:18:51',2432,17,15,8,'209.69.34.80'

UNION ALL

SELECT '1/8/09 9:24:52',2432,17,15,8,'209.69.34.80'

UNION ALL

SELECT '1/8/09 9:23:53',2432,17,15,8,'209.69.34.80'

UNION ALL

SELECT '1/8/09 9:18:54',46636,17,15,8,'66.83.116.142'

UNION ALL

SELECT '1/8/09 9:18:55',46636,17,15,8,'66.148.136.74'

UNION ALL

SELECT '1/8/09 9:18:56',46636,17,15,8,'160.91.112.58'

UNION ALL

SELECT '1/8/09 9:18:57',46636,17,15,8,'67.117.240.42'

UNION ALL

SELECT '1/8/09 9:18:58',46636,17,15,8,'67.214.242.186'

UNION ALL

SELECT '1/8/09 9:18:59',46636,17,15,8,'209.32.106.147'

UNION ALL

SELECT '1/8/09 9:21:01',46636,17,15,8,'12.231.144.130'

UNION ALL

SELECT '1/8/09 9:21:02',46636,17,15,8,'24.189.57.7'

UNION ALL

SELECT '1/8/09 9:21:03',46636,17,15,8,'67.86.11.184'

UNION ALL

SELECT '1/8/09 9:21:04',46636,17,15,8,'67.87.107.235'

UNION ALL

SELECT '1/8/09 9:21:05',46636,17,15,8,'67.82.155.51'

UNION ALL

SELECT '1/8/09 9:21:06',46636,17,15,8,'58.108.162.95'

UNION ALL

SELECT '1/8/09 9:21:07',46636,17,15,8,'198.28.128.149'

UNION ALL

SELECT '1/8/09 9:21:08',46636,17,15,8,'94.23.1.19'

UNION ALL

SELECT '1/8/09 9:21:09',46636,17,15,8,'66.186.188.22'

UNION ALL

SELECT '1/8/09 9:21:10',46636,17,15,8,'131.89.192.112'

UNION ALL

SELECT '1/8/09 9:21:11',46636,17,15,8,'74.10.199.33'

UNION ALL

SELECT '1/8/09 9:21:12',46636,17,15,8,'213.6.230.34'

UNION ALL

SELECT '1/8/09 9:21:13',46636,17,15,8,'206.219.255.152'

UNION ALL

SELECT '1/8/09 11:32:14',46636,17,15,8,'206.219.255.152'

UNION ALL

SELECT '1/8/09 9:21:15',46636,17,15,8,'207.188.72.204'

UNION ALL

SELECT '1/8/09 9:21:16',46636,17,15,8,'219.73.49.193'

UNION ALL

SELECT '1/8/09 9:21:17',46636,17,15,8,'88.149.187.34'

UNION ALL

SELECT '1/8/09 9:21:18',46636,17,15,8,'204.186.62.186'

UNION ALL

SELECT '1/8/09 9:21:19',46636,17,15,8,'70.15.242.32'

UNION ALL

SELECT '1/8/09 9:25:20',46636,17,15,8,'205.245.72.4'

UNION ALL

SELECT '1/8/09 9:21:21',46636,17,15,8,'212.159.65.33'

UNION ALL

SELECT '1/8/09 9:21:22',46636,17,15,8,'64.185.112.146'

UNION ALL

SELECT '1/8/09 9:21:23',46636,17,15,8,'91.98.185.126'

UNION ALL

SELECT '1/8/09 9:21:24',46636,17,15,8,'65.112.25.34'

UNION ALL

SELECT '1/8/09 9:21:25',46636,17,15,8,'65.166.8.34'

UNION ALL

SELECT '1/8/09 9:21:26',46636,17,15,8,'149.68.100.111'

UNION ALL

SELECT '1/8/09 9:21:27',46636,17,15,8,'98.99.184.230'

UNION ALL

SELECT '1/8/09 9:21:28',46636,17,15,8,'204.234.82.253'

UNION ALL

SELECT '1/8/09 9:21:29',46636,17,15,8,'204.234.242.170'

UNION ALL

SELECT '1/8/09 9:21:30',46636,17,15,8,'216.36.37.99'

UNION ALL

SELECT '1/8/09 9:21:31',46636,17,15,8,'192.18.128.5'

UNION ALL

SELECT '1/8/09 9:21:32',46636,17,15,8,'66.170.5.46'

UNION ALL

SELECT '1/8/09 9:21:33',46636,17,15,8,'208.99.203.200'

UNION ALL

SELECT '1/8/09 9:21:34',46636,17,15,8,'208.99.199.19'

UNION ALL

SELECT '1/8/09 9:21:35',46636,17,15,8,'208.99.193.13'

UNION ALL

SELECT '1/8/09 9:21:36',46636,17,15,8,'208.99.193.15'

UNION ALL

SELECT '1/8/09 9:21:37',96648,17,15,8,'209.192.121.254'

UNION ALL

SELECT '1/8/09 9:21:38',96648,17,15,8,'209.192.121.254'

UNION ALL

SELECT '1/8/09 9:29:39',96648,17,15,8,'209.192.121.254'

UNION ALL

SELECT '1/8/09 9:48:40',46636,17,15,8,'74.15.163.68'

UNION ALL

SELECT '1/8/09 9:21:41',46636,17,15,8,'74.15.202.173'

UNION ALL

SELECT '1/8/09 9:21:42',46636,17,15,8,'76.66.77.121'

UNION ALL

SELECT '1/8/09 9:21:43',46636,17,15,8,'89.172.172.11'

UNION ALL

SELECT '1/8/09 9:21:44',46636,17,15,8,'68.20.98.17'

UNION ALL

SELECT '1/8/09 9:21:45',46636,17,15,8,'216.26.206.107'

UNION ALL

SELECT '1/8/09 9:21:46',46636,17,15,8,'93.164.160.30'

UNION ALL

SELECT '1/8/09 9:21:47',46636,17,15,8,'216.204.11.230'

UNION ALL

SELECT '1/8/09 9:21:48',46636,17,15,8,'198.211.220.36'

UNION ALL

SELECT '1/8/09 9:21:49',46636,17,15,8,'60.53.112.143'

UNION ALL

SELECT '1/8/09 9:21:50',46636,17,15,8,'118.100.3.168'

UNION ALL

SELECT '1/8/09 9:21:51',46636,17,15,8,'118.100.45.83'

UNION ALL

SELECT '1/8/09 9:21:52',46636,17,15,8,'118.100.246.38'

UNION ALL

SELECT '1/8/09 9:21:53',46636,17,15,8,'18.101.7.150'

UNION ALL

SELECT '1/8/09 9:21:54',46636,17,15,8,'66.183.170.93'

UNION ALL

SELECT '1/8/09 9:21:55',46636,17,15,8,'64.180.22.110'

UNION ALL

SELECT '1/8/09 9:21:56',46636,17,15,8,'216.194.111.144'

UNION ALL

SELECT '1/8/09 9:21:57',46636,17,15,8,'192.91.75.29'

UNION ALL

SELECT '1/8/09 9:21:58',46636,17,15,8,'170.92.1.251'

UNION ALL

SELECT '1/8/09 9:21:59',46636,17,15,8,'74.94.131.177'

UNION ALL

SELECT '1/8/09 9:34:00',46636,17,15,8,'125.160.209.219'

UNION ALL

SELECT '1/8/09 9:34:01',46636,17,15,8,'60.242.55.237'

UNION ALL

SELECT '1/8/09 9:34:02',46636,17,15,8,'193.1.178.89'

UNION ALL

SELECT '1/8/09 9:34:03',46636,17,15,8,'209.94.196.170'

UNION ALL

SELECT '1/8/09 9:34:04',46636,17,15,8,'88.249.22.135'

UNION ALL

SELECT '1/8/09 9:34:05',46636,17,15,8,'78.167.114.239'

UNION ALL

SELECT '1/8/09 9:34:06',46636,17,15,8,'129.81.173.161'

UNION ALL

SELECT '1/8/09 9:34:07',46636,17,15,8,'129.81.173.161'

UNION ALL

SELECT '1/8/09 9:34:08',46636,17,15,8,'207.235.75.245'

UNION ALL

SELECT '1/8/09 9:34:09',46636,17,15,8,'209.163.180.61'

UNION ALL

SELECT '1/8/09 9:34:10',46636,17,15,8,'63.254.39.35'

UNION ALL

SELECT '1/8/09 9:34:11',46636,17,15,8,'96.247.205.194'

UNION ALL

SELECT '1/8/09 9:34:12',46636,17,15,8,'198.241.174.15'

UNION ALL

SELECT '1/8/09 9:34:13',46636,17,15,8,'77.54.152.72'

UNION ALL

SELECT '1/8/09 9:34:14',46636,17,15,8,'147.72.234.5'

UNION ALL

SELECT '1/8/09 9:34:15',46636,17,15,8,'64.56.129.107'

UNION ALL

SELECT '1/8/09 9:34:16',46636,17,15,8,'69.14.236.54'

UNION ALL

SELECT '1/8/09 9:34:17',46636,17,15,8,'174.130.61.219'

UNION ALL

SELECT '1/8/09 9:34:18',46636,17,15,8,'93.190.138.249'

UNION ALL

SELECT '1/8/09 9:34:19',46636,17,15,8,'12.154.134.29'

UNION ALL

SELECT '1/8/09 9:34:20',46636,17,15,8,'66.89.51.139'

UNION ALL

SELECT '1/8/09 9:34:21',46636,17,15,8,'67.91.62.18'

UNION ALL

SELECT '1/8/09 9:34:22',46636,17,15,8,'67.153.132.146'

UNION ALL

SELECT '1/8/09 9:34:23',46636,17,15,8,'206.205.229.34'

UNION ALL

SELECT '1/8/09 9:34:24',46636,17,15,8,'209.12.203.118'

UNION ALL

SELECT '1/8/09 9:34:25',46636,17,15,8,'209.12.58.22'

UNION ALL

SELECT '1/8/09 9:34:26',46636,17,15,8,'93.158.148.31'

UNION ALL

SELECT '1/8/09 9:34:27',46636,17,15,8,'70.228.8.113'

UNION ALL

SELECT '1/8/09 9:34:28',46636,17,15,8,'66.128.82.98'

UNION ALL

SELECT '1/8/09 9:34:29',46636,17,15,8,'210.201.139.100'

UNION ALL

SELECT '1/8/09 9:34:30',46636,17,15,8,'83.237.1.68'

UNION ALL

SELECT '1/8/09 9:34:31',46636,17,15,8,'194.247.41.25'

GO

INSERT INTO dbo.IPCITYISP

SELECT 3732904448,3732906495,'PH','PHILIPPINES','RIZAL','MAKATI','-'

UNION ALL

SELECT 1382892704,1382892735,'UK','UNITED KINGDOM','ENGLAND','SALFORD','070339 ENER-G'

UNION ALL

SELECT 2296709120,2296774655,'US','UNITED STATES','MINNESOTA','ST. PAUL','3M COMPANY'

UNION ALL

SELECT 1040366336,1040366367,'CH','SWITZERLAND','ZURICH','ZURICH','5702 NIEDERLENZ'

UNION ALL

SELECT 1449189376,1449197567,'AE','UNITED ARAB EMIRATES','DUBAI','DUBAI EMIRATES','TELECOMMUNICATIONS CORPORATION'

UNION ALL

SELECT 3394226944,3394227711,'SG','SINGAPORE','SINGAPORE','SINGAPORE','EQUINIX ASIA PACIFIC PTE LTD'

UNION ALL

SELECT 3483668768,3483668799,'CA','CANADA','ONTARIO','ETOBICOKE','ERCO WORLDWIDE'

UNION ALL

SELECT 212124672,212125183,'US','UNITED STATES','ARIZONA','SEDONA','ESEDONA WIRELESS'

UNION ALL

SELECT 1138256640,1138256895,'US','UNITED STATES','TEXAS','GILMER','ETEX COMMUNICATIONS LP'

UNION ALL

SELECT 3486806400,3486806527,'US','UNITED STATES','NEVADA','EUREKA','EUREKA COUNTY'

UNION ALL

SELECT 1261697760,1261697767,'US','UNITED STATES','TEXAS','PLANO','EUROPEAN ARTSTONE INC'

UNION ALL

SELECT 2652241920,2653159423,'US','UNITED STATES','TEXAS','IRVING','EXXON MOBIL CORPORATION'

UNION ALL

SELECT 1085469952,1085470207,'US','UNITED STATES','ALABAMA','FLAT ROCK','FARMERS TELEPHONE COOPERATIVE'

UNION ALL

SELECT 1098012672,1098013183,'US','UNITED STATES','CALIFORNIA','VENTURA','FIRST COMMUNICATIONS'

UNION ALL

SELECT 3496040992,3496040999,'CA','CANADA','ONTARIO','-','FLITER VAC INTL YORK MILLS'

UNION ALL

SELECT 2806906880,2806972415,'US','UNITED STATES','FLORIDA','TALLAHASSEE','FLORIDA DEPARTMENT OF HEALTH'

UNION ALL

SELECT 1087367192,1087367199,'US','UNITED STATES','OKLAHOMA','TULSA','FLOW-QUIP CONTROLS INC'

UNION ALL

SELECT 1291233280,1291233407,'US','UNITED STATES','TEXAS','FT. WORTH','FLUID COMPRESSOR PARTNERS'

UNION ALL

SELECT 1244226048,1244226303,'US','UNITED STATES','ILLINOIS','ALEDO','FRONTIER COMMUNICATIONS OF AMERICA INC'

UNION ALL

SELECT 1244390912,1244391167,'US','UNITED STATES','UTAH','DELTA','FRONTIER COMMUNICATIONS OF AMERICA INC'

UNION ALL

SELECT 3259589344,3259589375,'UK','UNITED KINGDOM','ENGLAND','LONDON','FTIP002770468 SCORE (EUROPE) LTD'

UNION ALL

SELECT 1110099712,1110099967,'US','UNITED STATES','OHIO','AMELIA','FUSE INTERNET ACCESS'

UNION ALL

SELECT 1117895424,1117898239,'US','UNITED STATES','OHIO','CINCINNATI','FUSE INTERNET ACCESS'

UNION ALL

SELECT 3628378112,3628383743,'US','UNITED STATES','OHIO','CINCINNATI','FUSE INTERNET ACCESS'

UNION ALL

SELECT 1107360512,1107360519,'US','UNITED STATES','ALABAMA','THEODORE','MITSUBISHI POLYCRYSTALINE SILICON'

UNION ALL

SELECT 3638355808,3638355903,'US','UNITED STATES','MAINE','BANGOR','MMC SITES'

UNION ALL

SELECT 1290579536,1290579543,'US','UNITED STATES','TEXAS','PLANO','MOB HOLDINGS INC'

UNION ALL

SELECT 1546190336,1546255359,'UK','UNITED KINGDOM','-','-','MOBILE BROADBAND SERVICE'

UNION ALL

SELECT 2093301760,2093318143,'SG','SINGAPORE','SINGAPORE','SINGAPORE','MOBILEONE LTD'

UNION ALL

SELECT 1959116800,1959133183,'SG','SINGAPORE','SINGAPORE','SINGAPORE','MOBILEONE LTD'

UNION ALL

SELECT 2007005184,2007005439,'PK','PAKISTAN','SINDH','KARACHI','MOBILINK INFINITY WIMAX CORE'

UNION ALL

SELECT 2760900608,2760966143,'US','UNITED STATES','MISSOURI','ST. LOUIS','MONSANTO'

UNION ALL

SELECT 3429936640,3429937151,'CA','CANADA','MANITOBA','WINNIPEG','MTS ALLSTREAM INC'

UNION ALL

SELECT 1416111360,1416111615,'NL','NETHERLANDS','NOORD-HOLLAND','ANNA PAULOWNA','MULTIKABEL QUICKNET'

UNION ALL

SELECT 1430828544,1430849791,'GR','GREECE','ATTIKI','ATHENS','MULTIPROTOCOL SERVICE PROVIDER TO OTHER ISP''S AND END USERS'

UNION ALL

SELECT 1382227456,1382227583,'SE','SWEDEN','STOCKHOLMS LAN','STOCKHOLM','MUNAX AB'

UNION ALL

SELECT 1377786880,1377787135,'UK','UNITED KINGDOM','ENGLAND','DERBY','NTL INFRASTRUCTURE - LEICESTER'

UNION ALL

SELECT 1376946176,1376949247,'UK','UNITED KINGDOM','ENGLAND','PORTSMOUTH','NTL INFRASTRUCTURE - LEWISHAM'

UNION ALL

SELECT 3510968832,3510969343,'US','UNITED STATES','MICHIGAN','CLINTON TOWNSHIP','NTT AMERICA INC'

UNION ALL

SELECT 1112765440,1112765695,'US','UNITED STATES','SOUTH CAROLINA','GOOSE CREEK','NUVOX COMMUNICATIONS INC'

UNION ALL

SELECT 1117030400,1117030655,'US','UNITED STATES','KANSAS','KANSAS CITY','NUVOX COMMUNICATIONS INC'

UNION ALL

SELECT 2690347008,2690347263,'US','UNITED STATES','TENNESSEE','KNOXVILLE','OAK RIDGE NATIONAL LABORATORY'

UNION ALL

SELECT 1131802664,1131802671,'US','UNITED STATES','CALIFORNIA','SAN FRANCISCO','OCTANT TECHNOLOGIES INC'

UNION ALL

SELECT 1138160240,1138160511,'US','UNITED STATES','-','-','OFF CAMPUS TELECOMMUNICATIONS'

UNION ALL

SELECT 3508562576,3508563967,'US','UNITED STATES','MINNESOTA','MINNEAPOLIS','ONVOY'

UNION ALL

SELECT 216502400,216502407,'US','UNITED STATES','CALIFORNIA','FREMONT','OORJA PROTONICS'

UNION ALL

SELECT 415053824,415054847,'US','UNITED STATES','NEW YORK','BROOKLYN','OPTIMUM ONLINE (CABLEVISION SYSTEMS)'

UNION ALL

SELECT 1129709568,1129713663,'US','UNITED STATES','CONNECTICUT','WILTON','OPTIMUM ONLINE (CABLEVISION SYSTEMS)'

UNION ALL

SELECT 1129801728,1129803263,'US','UNITED STATES','CONNECTICUT','WATERTOWN','OPTIMUM ONLINE (CABLEVISION SYSTEMS)'

UNION ALL

SELECT 1129487104,1129487359,'US','UNITED STATES','NEW JERSEY','WEST MILFORD','OPTIMUM ONLINE (CABLEVISION SYSTEMS)'

UNION ALL

SELECT 980197376,980199423,'AU','AUSTRALIA','NEW SOUTH WALES','SYDNEY','OPTUS INTERNET - RETAIL'

UNION ALL

SELECT 3323756544,3323756799,'US','UNITED STATES','MASSACHUSETTS','DANVERS','OSRAM SYLVANIA INC'

UNION ALL

SELECT 1578565632,1578567423,'FR','FRANCE','NORD-PAS-DE-CALAIS','ROUBAIX','OVH SAS'

UNION ALL

SELECT 1119534080,1119534335,'US','UNITED STATES','CALIFORNIA','CALABASAS','OXFORD NETWORKS'

UNION ALL

SELECT 2203648000,2203779071,'US','UNITED STATES','CALIFORNIA','SAN FRANCISCO','PACIFIC GAS AND ELECTRIC COMPANY'

UNION ALL

SELECT 1242220320,1242220383,'US','UNITED STATES','NEW YORK','NEW YORK','PAETEC COMMUNICATIONS INC'

UNION ALL

SELECT 3573997568,3574004479,'IL','ISRAEL','-','-','PALESTINE TELECOMMUNICATIONS COMPANY (PALTEL)'

UNION ALL

SELECT 3470509568,3470524415,'US','UNITED STATES','CALIFORNIA','PASADENA','PARSONS CORPORATION'

UNION ALL

SELECT 3485222400,3485223167,'CA','CANADA','ONTARIO','TORONTO','PATHWAY COMMUNICATIONS'

UNION ALL

SELECT 3679002624,3679011839,'HK','HONG KONG','HONG KONG (SAR)','HONG KONG','PCCW LIMITED'

UNION ALL

SELECT 1486207776,1486207783,'IT','ITALY','LAZIO','ROME','PENSOTTI - FABBRICA CALDAIE LEGNANO SPA'

UNION ALL

SELECT 3434757632,3434757887,'US','UNITED STATES','PENNSYLVANIA','ALLENTOWN','PENTELEDATA INC'

UNION ALL

SELECT 1175450112,1175450367,'US','UNITED STATES','PENNSYLVANIA','MILTON','PENTELEDATA INC. - CABLE'

UNION ALL

SELECT 3455404032,3455404287,'US','UNITED STATES','MARYLAND','SALISBURY','PERDUE FARMS INCORPORATED'

UNION ALL

SELECT 3567206144,3567211007,'UK','UNITED KINGDOM','ENGLAND','LONDON','PLUSNET TECHNOLOGIES LTD'

UNION ALL

SELECT 1085894784,1085894847,'US','UNITED STATES','WASHINGTON','WALLA WALLA','POCKETINET COMMUNICATIONS'

UNION ALL

SELECT 1533190144,1533198335,'IR','IRAN, ISLAMIC REPUBLIC OF','ESFAHAN','TEHRAN','POOL FOR BROADBAND CUSTOMERS'

UNION ALL

SELECT 1097865504,1097865535,'US','UNITED STATES','OREGON','PORTLAND','PORTLAND INDUSTRIAL'

UNION ALL

SELECT 1101400064,1101400575,'US','UNITED STATES','VIRGINIA','CHARLOTTESVILLE','SPRINT DSL NETWORK FL'

UNION ALL

SELECT 2504261632,2504327167,'US','UNITED STATES','NEW YORK','JAMAICA','ST. JOHN''S UNIVERSITY'

UNION ALL

SELECT 1650701568,1650720767,'US','UNITED STATES','WASHINGTON','SEATTLE','STARBUCKS COFFEE COMPANY'

UNION ALL

SELECT 3437908480,3437908735,'US','UNITED STATES','NEBRASKA','OMAHA','STATE OF NEBRASKA / OFFICE OF THE CIO'

UNION ALL

SELECT 3437949440,3437949695,'US','UNITED STATES','NEBRASKA','YORK','STATE OF NEBRASKA / OFFICE OF THE CIO'

UNION ALL

SELECT 3626246144,3626248191,'US','UNITED STATES','ARIZONA','MESA','STERLING NETWORK SERVICES LLC'

UNION ALL

SELECT 3222436864,3222455039,'US','UNITED STATES','CALIFORNIA','SANTA CLARA','SUN MICROSYSTEMS INC'

UNION ALL

SELECT 1118438656,1118439423,'US','UNITED STATES','WISCONSIN','MIDDLETON','SUPRANET COMMUNICATIONS INC'

UNION ALL

SELECT 3496200960,3496201279,'US','UNITED STATES','WASHINGTON','SEATTLE','SWIFT VENTURES INC'

UNION ALL

SELECT 3496199424,3496199999,'US','UNITED STATES','WASHINGTON','SEATTLE','SWIFT VENTURES INC'

UNION ALL

SELECT 3496198144,3496199167,'US','UNITED STATES','WASHINGTON','SEATTLE','SWIFT VENTURES INC'

UNION ALL

SELECT 3519052160,3519052287,'US','UNITED STATES','ALABAMA','SYLACAUGA','SYLACAUGA UTILITIES BOARD'

UNION ALL

SELECT 1242538240,1242539007,'CA','CANADA','ONTARIO','BURLINGTON','SYMPATICO HSE'

UNION ALL

SELECT 1242548224,1242549503,'CA','CANADA','QUEBEC','QUEBEC','SYMPATICO HSE'

UNION ALL

SELECT 1279412224,1279414271,'CA','CANADA','ONTARIO','MISSISSAUGA','SYMPATICO HSE'

UNION ALL

SELECT 1504485376,1504493567,'HR','CROATIA','PRIMORSKO-GORANSKA','RIJEKA','T-COM CROATIA INTERNET NETWORK'

UNION ALL

SELECT 1142186496,1142186751,'US','UNITED STATES','NEW YORK','ROCHESTER','TAPCO INTERNATIONAL'

UNION ALL

SELECT 3625635176,3625639935,'CA','CANADA','ONTARIO','THUNDER BAY','TBAYTEL'

UNION ALL

SELECT 1571069952,1571070207,'ES','SPAIN','ANDALUCIA','MALAGA','TDC-BREDBAANDSADSL-STATIC-NET'

UNION ALL

SELECT 3637251040,3637251047,'US','UNITED STATES','NEW HAMPSHIRE','MILFORD','TECHNICAL GRAPHICS INC'

UNION ALL

SELECT 3335773200,3335773239,'US','UNITED STATES','CALIFORNIA','SOUTH LAKE TAHOE','TEL WEST COMMUNICATIONS LLC'

UNION ALL

SELECT 1010135040,1010137087,'MY','MALAYSIA','WILAYAH PERSEKUTUAN','KUALA LUMPUR','TELEKOM MALAYSIA BERHAD'

UNION ALL

SELECT 1986265600,1986266111,'MY','MALAYSIA','-','-','TELEKOM MALAYSIA BERHAD'

UNION ALL

SELECT 1986275328,1986277375,'MY','MALAYSIA','WILAYAH PERSEKUTUAN','KUALA LUMPUR','TELEKOM MALAYSIA BERHAD'

UNION ALL

SELECT 1986327552,1986328319,'MY','MALAYSIA','PERAK','IPOH','TELEKOM MALAYSIA BERHAD'

UNION ALL

SELECT 3664053760,3664054271,'NZ','NEW ZEALAND','AUCKLAND','AUCKLAND','TELSTRACLEAR LTD'

UNION ALL

SELECT 1119322112,1119339775,'CA','CANADA','BRITISH COLUMBIA','VICTORIA','TELUS COMMUNICATIONS INC'

UNION ALL

SELECT 1085543936,1085545215,'CA','CANADA','BRITISH COLUMBIA','COQUITLAM','TELUS COMMUNICATIONS INC'

UNION ALL

SELECT 3636620800,3636621311,'US','UNITED STATES','OHIO','COLUMBUS','TERA-BYTE DOT COM INC'

UNION ALL

SELECT 3227207936,3227223039,'US','UNITED STATES','TEXAS','PLANO','TEXAS INSTRUMENTS'

UNION ALL

SELECT 2858156032,2858221567,'US','UNITED STATES','MINNESOTA','MINNEAPOLIS','THE TORO COMPANY'

UNION ALL

SELECT 1247708080,1247708087,'US','UNITED STATES','MASSACHUSETTS','SUDBURY','TI SALES'

UNION ALL

SELECT 2107691008,2107692825,'ID','INDONESIA','JAWA BARAT','TANGERANG','TLKM_D2_BB_SPEEDY_TAN'

UNION ALL

SELECT 1022505216,1022507007,'AU','AUSTRALIA','NEW SOUTH WALES','SYDNEY','TPG INTERNET PTY LTD'

UNION ALL

SELECT 3238113280,3238115327,'IE','IRELAND','KERRY','TRALEE','TRALEE INSTITUTE OF TECHNOLOGY'

UNION ALL

SELECT 3512648832,3512648895,'TT','TRINIDAD AND TOBAGO','ARIMA','ARIMA','TSTT ISP'

UNION ALL

SELECT 1492713472,1492720639,'TR','TURKEY','ANKARA','ANKARA','TT ADSL-ALCATEL STATIC_ULU'

UNION ALL

SELECT 1319594496,1319598079,'TR','TURKEY','MUGLA','MUGLA','TT ADSL-TTNET ALC DYNAMIC_ULUS'

UNION ALL

SELECT 2169569280,2169634815,'US','UNITED STATES','LOUISIANA','NEW ORLEANS','TULANE UNIVERSITY'

UNION ALL

SELECT 3488303872,3488304127,'US','UNITED STATES','SOUTH CAROLINA','CHESTER','TW TELECOM HOLDINGS INC'

UNION ALL

SELECT 3517166592,3517166847,'US','UNITED STATES','TEXAS','AUSTIN','TW TELECOM HOLDINGS INC'

UNION ALL

SELECT 1073620768,1073620775,'US','UNITED STATES','MINNESOTA','MINNEAPOLIS','TWIN CITY FAN COMPANIES LTD'

UNION ALL

SELECT 1626852608,1626852863,'US','UNITED STATES','VIRGINIA','LEESBURG','VERIZON INTERNET SERVICES INC'

UNION ALL

SELECT 3337715712,3337748479,'US','UNITED STATES','CALIFORNIA','SAN FRANCISCO','VISA INTERNATIONAL'

UNION ALL

SELECT 1295423488,1295423743,'PT','PORTUGAL','SETUBAL','ALMADA','VODAFONE PORTUGAL'

UNION ALL

SELECT 2471029248,2471029503,'US','UNITED STATES','PENNSYLVANIA','MONROEVILLE','WESTINGHOUSE ELECTRIC COMPANY'

UNION ALL

SELECT 1077444864,1077445247,'CA','CANADA','MANITOBA','WINNIPEG','WIBAND COMMUNICATIONS'

UNION ALL

SELECT 1158605824,1158606335,'US','UNITED STATES','MICHIGAN','NORTHVILLE','WIDEOPENWEST MICHIGAN'

UNION ALL

SELECT 2927769600,2927771647,'US','UNITED STATES','ARKANSAS','LITTLE ROCK','WINDSTREAM COMMUNICATIONS INC'

UNION ALL

SELECT 1572768186,1572769535,'NL','NETHERLANDS','ZUID-HOLLAND','MAASDIJK','WORLDSTREAM'

UNION ALL

SELECT 211453440,211453567,'US','UNITED STATES','NEW YORK','YAPHANK','XENTAUR CORPORATION'

UNION ALL

SELECT 1113142016,1113142271,'US','UNITED STATES','ILLINOIS','ADDISON','XO COMMUNICATIONS'

UNION ALL

SELECT 1130053120,1130053375,'US','UNITED STATES','NEW YORK','NEW YORK','XO COMMUNICATIONS'

UNION ALL

SELECT 1134134272,1134135295,'US','UNITED STATES','CALIFORNIA','SACRAMENTO','XO COMMUNICATIONS'

UNION ALL

SELECT 3469600000,3469600511,'US','UNITED STATES','-','-','XO COMMUNICATIONS'

UNION ALL

SELECT 3507276656,3507276663,'US','UNITED STATES','NORTH CAROLINA','CHARLOTTE','XSPEDIUS COMMUNICATIONS CO'

UNION ALL

SELECT 3507239432,3507239447,'US','UNITED STATES','KENTUCKY','LOUISVILLE','XSPEDIUS COMMUNICATIONS CO'

UNION ALL

SELECT 1570671808,1570674943,'RU','RUSSIAN FEDERATION','MOSCOW CITY','MOSCOW','YANDEX ENTERPRISE NETWORK'

UNION ALL

SELECT 1189349488,1189349495,'US','UNITED STATES','TEXAS','PLANO','YOUNG CHEMICAL COMPANY'

UNION ALL

SELECT 1115705600,1115706367,'CA','CANADA','ONTARIO','TORONTO','YOURLINK INC'

UNION ALL

SELECT 3536423776,3536423783,'TW','TAIWAN','T''AI-PEI','TAIPEI','YULI CO. LTD'

UNION ALL

SELECT 1408041216,1408041471,'RU','RUSSIAN FEDERATION','KRASNODAR','SOCHI','ZAO MTU-INTEL'

UNION ALL

SELECT 3270977792,3270978047,'UK','UNITED KINGDOM','ENGLAND','STOKE ON TRENT','ZETNET LTD'

GO

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

No setup code, just my code. I am including the code for both user-defined functions as well.

/****** Object: UserDefinedFunction [dbo].[ufn_Tally2] Script Date: 04/22/2009 08:02:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE function [dbo].[ufn_Tally2](

@pStartValue bigint = 1,

@pEndValue bigint = 1000000,

@pIncrement bigint = 1

)

returns table

as

return(

with BaseNum (

N

) as (

select 1 union all

select 1

),

L1 (

N

) as (

select

bn1.N

from

BaseNum bn1

cross join BaseNum bn2

cross join BaseNum bn3

),

L2 (

N

) as (

select

a1.N

from

L1 a1

cross join L1 a2

),

L3 (

N

) as (

select top ((abs(case when @pStartValue < @pEndValue

then @pEndValue

else @pStartValue

end -

case when @pStartValue < @pEndValue

then @pStartValue

else @pEndValue

end))/abs(@pIncrement) + 1)

a1.N

from

L2 a1

cross join L2 a2

),

Tally (

N

) as (

select

row_number() over (order by a1.N)

from

L3 a1

)

select

((N - 1) * @pIncrement) + @pStartValue as N

from

Tally

);

GO

/****** Object: UserDefinedFunction [dbo].[ufn_DelimitedSplit2] Script Date: 04/22/2009 08:02:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE function [dbo].[ufn_DelimitedSplit2] (

@pString varchar(max),

@pDelimiter char(1)

)

returns table

as

return

with ItemSplit(

ItemOrder,

Item

) as (

SELECT

N,

SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)

FROM

dbo.ufn_Tally2(1,len(@pString),1)

WHERE

N < LEN(@pDelimiter + @pString + @pDelimiter)

AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter

)

select

row_number() over (order by ItemOrder) as ItemID,

Item

from

ItemSplit

GO

set statistics io on;

set statistics time on;

select

ici.ipISP

into

#tmpISP

from

(dbo.IPCITYISP ici

cross apply dbo.ufn_Tally2 (ici.ipFROM, ici.ipTO, 1) t2)

inner join dbo.BackgroundViews bv

on ((select sum((cast(Item as bigint) % 256) * (power(256,(4 - ItemID)))) from dbo.ufn_DelimitedSplit2(bv.IPAddr,'.')) = t2.N)

;

set statistics time off;

set statistics io off;

set statistics io on;

set statistics time on;

select

ipISP,

count(ipISP)

from

#tmpISP

group by

ipISP

order by

ipISP

;

drop table #tmpISP

;

set statistics time off;

set statistics io off;

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 7

Here is everything you need, including the DDL, DML, and expected result set. I just combined everything so you don't have to refer to three different posts. You can open it right in SSMS. The only caveat to this example is that it runs MUCH quicker than the actual query, but that is because the query processor is scanning 150 records as opposed to 6.4million. I cannot attach the full 6.4m table due to the size (over 500 MB) and because it is a purchased, licensed product. The overall query plan is the same, though.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 8

I replaced the final query with this:

DECLARE

@compid INT,

@begDate SMALLDATETIME,

@endDate SMALLDATETIME

SELECT

@compid = 17,

@begDate = '1/1/2008',

@endDate = '12/31/2009'

SELECT

@endDate = @endDate + 1 ;

SELECT

ipISP,

COUNT(*)

FROM

dbo.BackgroundViews

CROSS APPLY (SELECT

SUM(BigIntOctet) AS BigIntIP

FROM

(SELECT

(CAST(SUBSTRING(IPAddr + '.', Number,

CHARINDEX('.', IPAddr + '.', Number)

- Number) AS BIGINT) % 256) * (POWER(256, 4 - row_number() OVER (ORDER BY Number))) BigIntOctet

FROM

dbo.Numbers

WHERE

Number = ipFrom

AND BigIntIP = @begDate

AND ClickDT < @endDate

AND CompID = @CompID

GROUP BY

ipISP

ORDER BY

ipISP ;

(Formatting by RedGate SQL Refactor.)

On my machine, the query you currently have took 306 milliseconds on the sample dataset you provided. The rebuilt query took 12 milliseconds on the same data.

I also tried adding an index:

CREATE INDEX IDX_BackgroundViews ON dbo.BackgroundViews (CompID, ClickDT)

INCLUDE (IPAddr) ;

That cut the original query down to 305 milliseconds (no real effect), but cut the rewritten query down to 8 milliseconds. Not sure how it will scale on tables that are the size yours is, but it might make a difference. Did make it an index seek.

Try that query, with and without the index, see if it helps.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 9

Occurred to me after I wrote the above that there's an easier way to do this:

SET NOCOUNT ON ;

SET STATISTICS TIME ON ;

DECLARE

@compid INT,

@begDate SMALLDATETIME,

@endDate SMALLDATETIME ;

SELECT

@compid = 17,

@begDate = '1/1/2008',

@endDate = '12/31/2009' ;

SELECT

@endDate = @endDate + 1 ;

WITH BigIntIPs(BigIntIP)

AS (SELECT

CAST(PARSENAME(ipAddr, 1) AS BIGINT) % 256

+ CAST(PARSENAME(ipAddr, 2) AS BIGINT) % 256 * 256

+ CAST(PARSENAME(ipAddr, 3) AS BIGINT) % 256 * 65536

+ CAST(PARSENAME(ipAddr, 4) AS BIGINT) % 256 * 16777216

FROM

dbo.BackgroundViews

WHERE

ClickDT >= @begDate

AND ClickDT = ipFrom

AND BigIntIP <= ipTo

GROUP BY

ipISP

ORDER BY

ipISP ;

No real performance improvement over the other version on the small dataset I'm working with, but might be easier to read/maintain, and might work better on the larger datasets.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 10

I'll try your latest version and let you know. The previous version actually ran SLOWER than using the original version using a temporary table, believe it or not. Nice touch on using the tally table to perform the IP conversion, though. Every little bit helps. In addition, the index did improve the query by 20 seconds, though.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 11

Yeah, scale can make a huge difference. While it sped stuff up on the small dataset, it slowed it down on the big one. That's why I like to do my performance tests on at least a million rows of data.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 12

Just for a speed test on a large number of rows, I did this:

set nocount on;

go

create table IPs (

ID int identity primary key,

IPAddr varchar(15));

go

insert into dbo.IPs(IPAddr)

select top 1000000 cast(abs(checksum(newid()))%256+1 as varchar(3)) + '.' +

cast(abs(checksum(newid()))%256+1 as varchar(3)) + '.' +

cast(abs(checksum(newid()))%256+1 as varchar(3))

from dbo.Numbers n1

cross join dbo.Numbers n2;

go

declare @BigIP bigint;

set statistics time on;

select @BigIP = CAST(PARSENAME(ipAddr, 1) AS BIGINT) % 256

+ CAST(PARSENAME(ipAddr, 2) AS BIGINT) % 256 * 256

+ CAST(PARSENAME(ipAddr, 3) AS BIGINT) % 256 * 65536

+ CAST(PARSENAME(ipAddr, 4) AS BIGINT) % 256 * 16777216

from dbo.IPs;

set statistics time off;

go

declare @BigIP bigint;

set statistics time on;

select @BigIP = dbo.fnDot2LongIP(IPAddr)

from dbo.IPs;

set statistics time off;

The first one, using the parsename and hard-coded math, finished 1-million rows in 2.5 seconds. The second one, I killed the process at the 5 minute mark, because it wasn't done yet.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 13

Thanks for the parsename trick. I do recall reading about that (probably on here) quite some time ago. For the amount of IPs that had to be converted using the original UDF, it was only a few seconds, but every little bit helps. I was thinking the CTE route like you've done as well. I had one cross apply example way back that I never liked, so I discarded it. Your version is different though.

So far, some of the tests I've conducted still show using a temp table as being the quickest. But, I also added similar indexes (CompanyID, Date, INCLUDE IPAddr) to the other stats tables (besides BackgroundViews) that my original query references. Average execution time thus far is about 1:00 to 1:40, depending on the company and date range. I've even had some finish in like 20 seconds or so. The averages have gone down a good 20-45 seconds from before and CPU usage is slightly less now.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Be sure to post your final solution. It may help others with a similar issue/problem.

GSquared SSC Guru Points: 260824 More actions · Answer 15

Well, I'm glad we helped speed it up.

What's your query look like at this point? Might be other aspects of it that can also be sped up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon