Must be a faster way (ADO, DataBound Control, SQL Server DB)

[Karl Blessing]

At the moment I have a small demostration table that has been precreated, it contains about 1,500 records as a demo (the actual table contains over 25,000 records)

anyways the table is setup as so...

[tt]
SQL Query used to get table is: select * from empdb_bit order by sic2, sic4

Sic2 | Sic4 | latbit | lngbit | ct | id

the goal is of course to create new tables with the names of the Sic2 (like sic02) and each new table (or file as we want to create them into binary files later on) will look like:

Sic4 | Lat | Lng | ID

also the new table format above, must be sorted by Sic4 (which I could probally do just by saying order by Sic4)

at the moment I have this code to identify the different Sic2:


Dim TmpSic As String
Label1.Caption = ""
With Adodc1.Recordset
.MoveFirst
If Not .EOF Then
TmpSic = .Fields("Sic2&quot
While Not .EOF
If TmpSic <> .Fields(&quot;Sic2&quot Then
Label1.Caption = Label1.Caption & vbCrLf & TmpSic
TmpSic = .Fields(&quot;Sic2&quot
End If
.MoveNext
Wend
Label1.Caption = Label1.Caption & vbCrLf & TmpSic
End If
End With

[/tt]
but it's incredibly slow for the number of records. is there perhaps a SQL Statement, that can least help speed up the process? I am using SQL Server 6.5

 

[Webrookie]

Have you tried indexing the table? Or using a stored procedure?

I know that I had an ASP application that hit SQL Server 7.0 tables. The query retrieved the data, and it was written to the screen but as soon as it hit the .movenext, it slowed down.

The only things not tried were indexing and Stored Procedure. The Indexing worked, the loop ran much faster.

 

[Karl Blessing]

hmm, but the final goal is to take this huge table, and Split it into new tables/files based on their Sic2 numbers, I have no experience with Indexing or stored procedures.

 

[Webrookie]

Right, well there should be a method through the SQL Server software console to index the table. Try indexing it on the sic2 number.

Have you tried timing each event in the code? Print a timestamp with relevant information when a line is done executing. Find out where it's taking longest....

That's what we did here, and we found it was merely a movenext statement, indexed the table, and it ran fine.

 

[Karl Blessing]

I'm thinking of an idea, although not sure how to do it easily through these data bound controls (I would much prefer to do it all in code) but heres the idea I was thing in a loop

first start out with
select * from empdb_bit order by sic2, sic4

then grab the sic2 for the first one
then change the recordset to be

select * from empdb_bit order by sic2, sic4 where sic2 <> firstsic2found

then
select * from empdb_bit order by sic2, sic4 where sic2 <> firstsic2found and sic2 <> secondsic2found

and so forth until I get an empty record

but also keeping track of each one found
then I could just create a table using ...Where sic2 = thenumIwant

and just loop through creating a new table/file for each number.

benefit is, I'll only be checking the first of each new sic2.

 

[Webrookie]

Karl, I swear, you are doing this exactly the way we did.

We did the same thing, we divided the query in two and nested it. That didn't work for us either.

It produced the same results, but still slow.

Maybe it will work for you though...

 

[Karl Blessing]

hmm well this is the code I'm running but it's saying that I have a syntax error near the &quot;where&quot; clause

[tt]
Private Sub Command2_Click()
Dim Conn As New ADODB.Connection
Dim RsSic As New ADODB.Recordset
Dim TmpSql1 As String
Dim TmpSql2 As String

TmpSql1 = &quot;select * from empdb_bit order by sic2, sic4&quot;

Conn.Open &quot;DSN=empdb&quot;, &quot;sa&quot;, &quot;sql&quot;
RsSic.Open TmpSql1, Conn, adOpenForwardOnly, adLockReadOnly

Label1.Caption = &quot;&quot;
While Not RsSic.EOF
RsSic.MoveFirst
Label1.Caption = Label1.Caption & RsSic(&quot;Sic2&quot
TmpSql2 = &quot; where sic2 > &quot; & RsSic(&quot;Sic2&quot
RsSic.Close
RsSic.Open TmpSql1 & TmpSql2, Conn, adOpenForwardOnly, adLockReadOnly
Wend
If Not (RsSic.EOF = True And RsSic.BOF = True) Then
RsSic.Close
End If
End Sub
[/tt]

 

[Webrookie]

I think it's because you are concatanating your 'where' clause after your 'order by' clause, and I don't think that's possible?

can you do:

select * from table order by field1 where field2=3

?

 

[Karl Blessing]

I am also thinking that when you open the recordset it's slow because it's loading the whole thing, I remeber there used to be a paging option, where I could set for the thing to return no more than a single record, this would still be valid to the SQL Statement, as I would be getting the lowest Sic2, then I am checking the first anyways, I remeber using this on one of my Dianostic projects that lets the user view information about every single DSN registered on a Webserver, and I was giving a count of how many records exist, but it would be so slow , because even ifI was just getting the count, it was loading every single record, so I had it set to return only a single record for the query to get the count, and it works fast like a charm(btw the company loves my dianostic page, since it will let you look at the data types each fields are, it's DSN info, lets you run queries against it, lets you check version numbers of any Dll, exe, on the webserver, as well as WebServer information)

 

[Karl Blessing]

I think this would be it

[tt]
RsSic.MaxRecords = 1
[/tt]

if it is, it'll speed up my code above if I can get it to work.

 

[Webrookie]

Yeah, that will do it too.

You are right, I think it is returning the entire recordset, when you only need to work with one record at at time.

That should do it I bet.

 

[Karl Blessing]

I have here this new code, it's still slow for some reason I threw in time checks, like you mentined

[tt]
Private Sub Command2_Click()
Dim Conn As New ADODB.Connection
Dim RsSic As New ADODB.Recordset
Dim TmpSql1 As String
Dim TmpSql2 As String
Dim TOrder As String

TmpSql1 = &quot;select * from empdb_bit&quot;
TmpSql2 = &quot;&quot;
TOrder = &quot; order by sic2, sic4&quot;
RsSic.MaxRecords = 1
RsSic.PageSize = 1
List1.AddItem &quot;Past Init &quot; & Time()

Conn.Open &quot;DSN=empdb&quot;, &quot;sa&quot;, &quot;sql&quot;
RsSic.Open TmpSql1 & TOrder, Conn, adOpenStatic, adLockReadOnly
List1.AddItem &quot;First Recordset Opened [total count:&quot; & RsSic.RecordCount & &quot;]&quot; & Time()
Label1.Caption = &quot;&quot;
While Not RsSic.EOF
RsSic.MoveFirst
List1.AddItem &quot;MoveFirst Executed &quot; & Time()
Label1.Caption = Label1.Caption & vbCrLf & RsSic(&quot;Sic2&quot
If TmpSql2 = &quot;&quot; Then
TmpSql2 = &quot; where sic2 <> &quot; & RsSic(&quot;Sic2&quot
Else
TmpSql2 = TmpSql2 & &quot; and sic2 <> &quot; & RsSic(&quot;Sic2&quot
End If
RsSic.Close
List1.AddItem &quot;Closed &quot; & Time()
RsSic.Open TmpSql1 & TmpSql2 & TOrder, Conn, adOpenForwardOnly, adLockReadOnly
List1.AddItem &quot;Reopened &quot; & Time()
Wend
List1.AddItem &quot;Finished Looping &quot; & Time()
If Not (RsSic.EOF = True And RsSic.BOF = True) Then
RsSic.Close
End If

End Sub
[/tt]

heres the result the &quot;log&quot; so to speak

[tt]
Past Init 5:06:29 PM
First Recordset Opened [total count:1]5:06:34 PM
notice it says 1 count, so we know it's getting back only one, I need to throw a check in after it closes the first time to see if it keeps that true all the time
MoveFirst Executed 5:06:34 PM
Closed 5:06:35 PM
Reopened 5:06:36 PM
MoveFirst Executed 5:06:37 PM
Closed 5:06:37 PM
Reopened 5:06:38 PM
MoveFirst Executed 5:06:39 PM
Closed 5:06:39 PM
Reopened 5:06:40 PM
Finished Looping 5:06:40 PM
[/tt]

you'll notice the log seems to state that it took 6 seconds to complete, for some reason it felt longer than this, anyways thats that above.

 

[Karl Blessing]

oh heres the new log when I ran a check for count right after it got reopened.

[tt]
Past Init 5:11:52 PM
First Recordset Opened [total count:1]5:11:55 PM
MoveFirst Executed 5:11:55 PM
Closed 5:11:55 PM
Reopened [total count:1]5:11:56 PM
MoveFirst Executed 5:11:56 PM
Closed 5:11:56 PM
Reopened [total count:1]5:11:57 PM
MoveFirst Executed 5:11:57 PM
Closed 5:11:57 PM
Reopened [total count:0]5:11:58 PM
Finished Looping 5:11:58 PM
[/tt]

also I made sure to change the cursor type for forwardonly to static so it could get a count.

 

[Webrookie]

Ok, so let me rethink think...

1. it's taking six seconds to do a record
2. you have 1500 records
3. 1500 X 6sec/record = 9000 seconds = 150 minutes = 2.5 hours

And you only ran that loop on one record, correct?


Ok, time to index..

 

[Karl Blessing]

no, its taking 6 sec to do the whole thing (lil longer on second try)

 

[MikeLacey]

This approach might be of help as well -- reducing the number of object references by &quot;pointing&quot; direct to the fields object.

Also -- get to the field value using the numeric constant rather than the name of the field in a string

This example works with Oracle Objects for OLE but the tricks are VB, rather than Oracle tricks -- so should work
[tt]
Dim flds() As Object
Dim i,fldcount As Integer
Const CURRENT_SALARY = 5, PLANNED_RAISE = 6
' Get the field count, and output the names
[tab]fldcount = dynset.Fields.Count
[tab]ReDim flds(0 To fldcount - 1)
[tab]For i = 0 To fldcount - 1
[tab][tab]Set flds(i) = dynset.Fields(i)
[tab]Next I
[tab]' Traverse until EOF is reached, updating rows as we go
[tab]Do Until dynset.EOF
[tab][tab]dynset.DbEdit

[tab][tab]flds(CURRENT_SALARY).value = 0
[tab][tab]flds(PLANNED_RAISE).value = 0
[tab][tab]dynset.DbUpdate
[tab][tab]dynset.DbMoveNext
[tab]Loop
[/tt]

 

[Karl Blessing]

changing the &quot;Sic2&quot; to zero(Since it's the first in the table) and changing the cursor type to forward only(since I already know it's only getting back 1 recordset so no need for static, since forward only is the fastest of the cursors)

this is the new log

[tt]
Past Init 11:41:02 AM
Connection Opened 11:41:03 AM
First Recordset Opened 11:41:04 AM
MoveFirst Executed 11:41:05 AM
Closed 11:41:05 AM
Reopened 11:41:06 AM
MoveFirst Executed 11:41:07 AM
Closed 11:41:07 AM
Reopened 11:41:08 AM
MoveFirst Executed 11:41:09 AM
Closed 11:41:09 AM
Reopened 11:41:09 AM
Finished Looping 11:41:09 AM
[/tt]

took about 8 seconds total (perhaps I should limit the log to only show top then bottom, so that it doesnt take process time to add to the list view) but so far it seems to take longer , of course going to need to make a routine later that throws each sic2 into a new db of it's own.

 

[Karl Blessing]

Hmm I got it cut down to 3 seconds, by removing all the logging info (except for the first and the last) then removing the &quot;MoveFirst&quot; thats always there when the recordset reopens(doesnt seem to affect it)

 

[MikeLacey]

so -- my suggestion wasn't of much help then?

 

[Karl Blessing]

well if you look at it this way, 6 seconds to 3, the time was cut in half for just using numbers instead of the fieldname.