Refreshing a sqldatareader in SQL Server 2000

[pha_roah]

I am writing a small application that traverses a website log file (already imported in SQL server 2000) and populates a field called "country_" based on the ip and another lookup table that matches ip long numbers with countries.

What I want to do is run a sql query giving me a set of records where country_ is not populated, then load the the first record, run an update statement for the ip address for this and all other records with the same ip address.
It works and updates, except that it goes through the records even if they have been updated simply because the datareader is not refreshed, even if I close it and reload it again.

I previously used a data control and updated record by record which worked, but was slow (I update about 300-600K records per run), so I am trying to see if I run the sql command directly if it would make a difference in performance.

Anyone know what I need to do to ensure I refresh the set after each loop?

Help is appreciated.

The code looks as follows (I have removed the regular declarations and parts that don't apply to the database)
------------------------------------
Dim myConnection As SqlConnection
Dim Countries_conn As SqlConnection
Dim myCommand As SqlCommand

Dim Countries As SqlCommand
Dim dr As SqlDataReader

goahead = True



myConnection = New SqlConnection(ConnectionStrText.Text)
myConnection.Open()
myCommand = New SqlCommand("Select * from staging where country_ IS Null or Country_ = 'Not Found'", myConnection)


Countries_conn = New SqlConnection(ConnectionStrText.Text)
Countries_conn.Open()

Do While goahead
dr = myCommand.ExecuteReader

If dr.HasRows Then
dr.Read()
ipindr = dr("IP")
numip = Dot2LongIP(Trim(ipindr))
Countries = New SqlCommand("UPDATE staging SET Country_ = (SELECT countryLong FROM IPCountry where " & numip & " BETWEEN FROM_Long AND To_Long) WHERE staging.IP = '" & ipindr & "'", Countries_conn)
ra = Countries.ExecuteNonQuery()

Countries_conn.Close()
Else
goahead = False
End If
myConnection.Close()
Loop
-------------------------------------------

Dot2LongIP is a function that converts ip address from Dot format to Long format so I can compare it to my ip country database.
Staging is the table where I want to update country_
IPcountry is the lookup table

 

[cjard]

If you can give me a snapshot of your database, I'lld give you the UPDATE query that will do it directly, no messing about.

i.e. something like

UPDATE 
  staging s
  INNER JOIN
  ipcountry i
  ON
    s.iplong BETWEEN i.from_long and i.to_long
SET
  c.countrylong = i.countrylong
WHERE
  c.country_ is null or c.country_ 'not found'

but this would need tweaking based on the data/schema - sqlserver might not want to update this join right away, which is why i would need the data

 

[pha_roah]

Thanks cjard.
Here is a screenshot of the tables.
I wasn't thinking of using one big sql statement because I have to convert the dot ip to long ip, but after you made the offer I realized I could calculate the long ip while importing the files into the database from the flat text file which I have to do anyway in order to filter out just the htm hits.
So here is the screen shot. I can provide you with a backup of the database but I am hesitant because there is another table in there that has over 3 million records.
Assume I added a field to staging and called it "IPLong" of type long.

 

[cjard]

Make the screenshot a PNG, 800x600, small as possible, and attach it to your post. THe forum software will show it automatically. No hosting required

 

[cjard]

PS when i said SNAPSHOT, i mean send me a data file I can attach to sql server. I cant write a query off a picture of a schema and know it is right!

 

[pha_roah]

Thanks cjard.
Based on your suggestion above, I created a new field in the staging table that stores the longip of the ip address, once this was in, I was able to run the following query which took only 25 minutes to update 400k records (previously it was more like 8 hours) and it runs fine from the sql server query analyzer.
However, when I try to run this query from within the vb code, I get a sql timeout exception, which leads me to think the vb thinks it timed out while executing the query and throws this error. Is there anyway to tell vb to wait for the query to execute? which should take about 30 minutes?

update staging
set country_ = (select countryLong from ipcountry where staging.Longip between From_Long and To_Long)
Where staging.country_ is null


Thanks for you help so far.

 

[pha_roah]

Here is the exception

System.Data.SqlClient.SqlException was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=0
Message="Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated."
Number=-2
Procedure=""
Server="(local)"
Source=".Net SqlClient Data Provider"
State=0
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at WindowsApplication1.Form1.populate_countries_Click(Object sender, EventArgs e) in C:\Documents and Settings\esmee\My Documents\Visual Studio 2005\Projects\Webreader\Webreader\Form1.vb:line 236
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

 

[cjard]

Thanks cjard.
took only 25 minutes to update 400k records

Don't you think that's a little slow? That's 267 records a second..

 

[pha_roah]

I don't know,
The way I see it, for each record in the staging table, SQL has to lookup the ip address in the ipcountry table which is about 80K records, so looking through 267X80K records per second is pretty good (I know that indexing makes it go less than 80K each pass).
Plus, my purpose is to populate a datamart which is a one time activity each three months and not a realtime user activated lookup, so 25 minutes for batch data uploads is very acceptable.

That being said, your comment about 267/minute being slow has spurred my interest. How can we make it faster than 25 minutes?
I will post a copy of the database tonight if you would like the challenge.

 

[cjard]

I don't know,
The way I see it, for each record in the staging table, SQL has to lookup the ip address in the ipcountry table which is about 80K records, so looking through 267X80K records per second is pretty good (I know that indexing makes it go less than 80K each pass).

Thats a little simplistic a way to view the modus operandi of a database. Its more likely that, in a huge join situation, the DB will load the entire table into a hashtable in memory and perform a very fast hash join op.. However, its down to the optimizer decision based on the way the query is written.

That being said, your comment about 267/minute being slow has spurred my interest. How can we make it faster than 25 minutes?

Take a look at
a) the query - is it well written (usually: no)
b) the data organization - is it optimally organized and indexed? (usually: no)

If we can write a query that selects all 400,000 records (quite small data set) in less than 25 minutes (quite long time in computing terms) then we should be able to write another that updates them in a similarly quicker time...

I will post a copy of the database tonight if you would like the challenge.

Crack on..

 

[pha_roah]

OK,
You can get the database at http://home.ehabweb.net:8975/weblog.zip
This is a SQL server backup file, so create a new database in your sqlserver and restore this file to it.

The table to be populated is "staging" and the ip's are in ipcountry.

Good luck

 

[cjard]

Ive tried a couple of times and I cant d/l the file - it times out. I'd prefer if you could just put the MDF and LDF in a zip file and attach it here.. or use megaupload.com (set it to email yourself, and post the download ticket)

 

[pha_roah]

I tried posting it directly, but kept getting errors uploading, I assumed it's because the zip file is 9MB.
Anyway, this one should work. it's a standard port so you won't have trouble.

http://www.ehabweb.net/weblog.zip

 

[cjard]

I tried posting it directly, but kept getting errors uploading, I assumed it's because the zip file is 9MB.

!

Anyway, this one should work. it's a standard port so you won't have trouble.

It's coming down now..

 

[cjard]

Can you just zip and send the MDF/LDF?

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Cannot open backup device 'C:\temp\webloglatest'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For help, click: http://go.microsoft.com/fwlink?Prod...99&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------