The problem is that you're using cursors. Cursors in SQL Server are very, very slow. Come up with an alternate way of doing your import.
----- Original Message ----- From: Duane Boudreau <[EMAIL PROTECTED]> Date: Thursday, January 23, 2003 9:02 am Subject: OT: SQL Help > Hi All, > > I need the help of an SQL expert here. I am importing some rather > large text > files (250MB +) into a database. The structure I am working with > looks like: > > tmp_vehix (~600,000 recs) > Col001 - Col0067 (Col031 - Col065 contain option FK data) > > tmp_opts (~2900 recs) > Col001, Col002 > > vehix > vin (PK) > > vehix_options > vin (PK) > option_id (PK) > > options > option_id (PK) > ga_options_id (case sensitive, matches the values in > tmp_vehix.Col031 - > tmp_vehix.Col065) > > Everything is going pretty smooth save the last part where I copy > the data > in option fields Col031 - Col065 in tmp_vehix to their options > equivalentsin the vehix_options table. The query I have written > writes the 8.5 million > records out to the table it is incredibly slow, estimated time is > 24 hours. > Would anyone mind taking a peak at the query to see if there are > any glaring > pain points that I could optimize and speed this beast up? Or possibly > suggest improvements to the structure of vehix, vehix_options and > options?Thanks in advance! > > Duane > > The query looks like this (some of the repetitious sections have been > shortened). > > DECLARE @vin varchar(20), @Col031 varchar(255), @Col032 varchar(255), > @Col033 varchar(255), @Col034 varchar(255), @Col035 > varchar(255), @Col036 varchar(255), @Col037 varchar(255), > @Col038 varchar(255), > @Col039 varchar(255), @Col040 varchar(255), @Col041 > varchar(255), @Col042 varchar(255), @Col043 varchar(255), > @Col044 varchar(255), > @Col045 varchar(255), @Col046 varchar(255), @Col047 > varchar(255), @Col048 varchar(255), @Col049 varchar(255), > @Col050 varchar(255), > @Col051 varchar(255), @Col052 varchar(255), @Col053 > varchar(255), @Col054 varchar(255), @Col055 varchar(255), > @Col056 varchar(255), > @Col057 varchar(255), @Col058 varchar(255), @Col059 > varchar(255), @Col060 varchar(255), @Col061 varchar(255), > @Col062 varchar(255), > @Col063 varchar(255), @Col064 varchar(255), @Col065 > varchar(255), @opt_id int, @counter as int > > DECLARE options_cursor CURSOR FOR > SELECT Col002, Col031, Col032, Col033, Col034, Col035, Col036, > Col037, Col038, Col039, Col040, Col041, Col042, > Col043, Col044, Col045, > Col046, Col047, Col048, Col049, Col050, Col051, > Col052, Col053, > Col054, Col055, Col056, Col057, Col058, Col059, > Col060, Col061, > Col062, Col063, Col064, Col065 > FROM tmp_vehix > > OPEN options_cursor > > FETCH NEXT FROM options_cursor > INTO @vin, @Col031, @Col032, @Col033, @Col034, @Col035, @Col036, > @Col037,@Col038, > @Col039, @Col040, @Col041, @Col042, @Col043, @Col044, @Col045, > @Col046,@Col047, > @Col048, @Col049, @Col050, @Col051, @Col052, @Col053, @Col054, > @Col055,@Col056, > @Col057, @Col058, @Col059, @Col060, @Col061, @Col062, @Col063, > @Col064,@Col065 > > WHILE @@FETCH_STATUS = 0 > BEGIN > > SET @counter = @counter + 1 > IF LEN(@Col031) > 0 > BEGIN > SET @opt_id = (SELECT option_id FROM options WHERE > CAST(ga_option_id as > varbinary(255)) = CAST(@Col031 as varbinary(255))) > IF @opt_id IS NOT NULL > BEGIN > INSERT INTO vehix_options (vin, option_id) > VALUES (@vin, @opt_id) > END > > IF LEN(@Col032) > 0 > BEGIN > SET @opt_id = (SELECT option_id FROM options > WHERE CAST(ga_option_id as > varbinary(255)) = CAST(@Col032 as varbinary(255))) > IF @opt_id IS NOT NULL > BEGIN > INSERT INTO vehix_options (vin, option_id) > VALUES (@vin, @opt_id) > END > > IF LEN(@Col033) > 0 > BEGIN > SET @opt_id = (SELECT option_id FROM > options WHERE CAST(ga_option_id as > varbinary(255)) = CAST(@Col033 as varbinary(255))) > IF @opt_id IS NOT NULL > BEGIN > INSERT INTO vehix_options (vin, > option_id) VALUES (@vin, @opt_id) > END > END > END > END > > FETCH NEXT FROM options_cursor > INTO @vin, @Col031, @Col032, @Col033, @Col034, @Col035, @Col036, > @Col037,@Col038, @Col039, > @Col040, @Col041, @Col042, @Col043, @Col044, @Col045, > @Col046, @Col047, > @Col048, @Col049, > @Col050, @Col051, @Col052, @Col053, @Col054, @Col055, > @Col056, @Col057, > @Col058, @Col059, > @Col060, @Col061, @Col062, @Col063, @Col064, @Col065 > END > > CLOSE options_cursor > DEALLOCATE options_cursor > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
