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

 

[Karl Blessing]

I got it downto 1 second or less (by changing the select statement, to only select sic2, since I'm just looking for different sic2s, then later split, also changed RsSic(0) into a variable, so i wouldnt be calling the recordset all the time during comparing

so it does about a second to less than, from the time it opens the RS initially, to the time it's complete, for a table of 13,000 records, it does it in about 10 seconds (which returned only 2 different sic2 btw) for a table that had 173,000. I am still unable to open the really really big one, which is said to be the main one that contains everything, havent been able to view it in either my application or my dianostic ASP page I Created, since it times out in both.

He also mentioned that he wanted each sic2 splits, to be stored in binary files, so I'm not sure how to do that, but I did get help on how to create the tables when I know which sic2 I want.

 

[Karl Blessing]

Using ADODC and Databound control, I was able to get a count back from the ADODC, however this cound is 651396 I am assuming that there is a lot more records than that, since typically anything that halts at 32... or 65.. has reached beyound the bounds that most datacontrols can handle, all I know is that with in code , ADO times out when I try to initially open that recordset, even if I set max records to 1.

 

[Karl Blessing]

on MSDN I searched for the timeout error on the DB that was really large, this is what I found

[tt]
0x80040E31 -2147217871
Execution aborted because a resource limit has been reached; no results have been returned
[/tt]

In other MSDN articles, it appears that this particular SQL Table exceeds the limit that ADO is capable of.

any Clue?

 

[Karl Blessing]

he doesnt like my method, he wants to go straight from fully opened recordset (not closed and then opened) to Binary files for each different Sic2.

 

[MikeLacey]

does RDO have the same limits? (not that I really know what I'm talking about here)

 

[Karl Blessing]

RDO is clientside , and uses IE, basically what it does is that it downloads the recordsets, and caches them on the client, I would think that RDO would have the same limit, but then again you cache what you can grab, but I would imagine it being very very large, and you would have to download all of it.

 

[Waynest]

Hi guys

I also have found move next loops to be painfully slow using ADO with SQL Server. You might be interested to hear that the problem improved noticeably when we moved from SQL 6.5 to 7.0, I guess because its smart enough to make improvements to the indexes all by itself.

I plan to try some of the things you've suggested too to see if I can squeeze out a bit more performance.

 

[Karl Blessing]

just in case you are all interested this is what I have now.

the difference is, that you tell the program which Sic2 you want created into a file(the file is of random access, so it's userdefined type to make it binary, and smaller than sequential)

[tt]
Private Sub Command1_Click()
Dim RsSic As New Recordset
Dim fs As Object
Dim SicPath As String
Dim Found As Boolean
Dim Sic2er As Sic2File
Dim FileNo As Integer

If Right(Dir1.Path, 1) = &quot;\&quot; Then
SicPath = Dir1.Path & &quot;sic&quot; & Text1.Text & &quot;.dat&quot;
Else
SicPath = Dir1.Path & &quot;\sic&quot; & Text1.Text & &quot;.dat&quot;
End If

RsSic.Open &quot;select sic4, lat, lng, id from empdb_map4 where sic2 = &quot; & Text1.Text & &quot; order by sic4&quot;, Conn, adOpenForwardOnly, adLockReadOnly
If Not RsSic.EOF Then
Open SicPath For Random As #1 Len = Len(Sic2er)
Found = True
While Not RsSic.EOF
Sic2er.Sic4 = RsSic(0).Value
Sic2er.latbit = RsSic(1).Value
Sic2er.lngbit = RsSic(2).Value
Sic2er.id = RsSic(3).Value
Put #1, , Sic2er
RsSic.MoveNext
Wend
Close #1
RsSic.Close
MsgBox &quot;File Created&quot;, vbOKOnly, &quot;Status&quot;
Else
MsgBox &quot;Sic not found&quot;, vbOKOnly, &quot;Status&quot;
End If
End Sub
[/tt]

(also if you are curious where the connection is opened, it's opened when the form is loaded, and closed when the form is unloaded, that way any wait for openning a connection occurs while loading, and doesnt waste time when running the process over and over again)

in the code above everything runs fast, the only thing you have to wait on is the RsSic.Open, which seems to make sense.

also if you are even more curious here is how I Set the type, and here is how one could read it.

[tt]
Private Type Sic2File
Sic4 As Byte
latbit As Double
lngbit As Double
id As Long
End Type

Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub

Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub

Private Sub File1_DblClick()
Dim Sic2er As Sic2File
Dim SicFile
Dim Looper As Long
ListView1.ListItems.Clear
Looper = 1
If Right(File1.Path, 1) = &quot;\&quot; Then
Open File1.Path & File1.FileName For Random As #1 Len = Len(Sic2er)
Else
Open File1.Path & &quot;\&quot; & File1.FileName For Random As #1 Len = Len(Sic2er)
End If
Do Until EOF(1)
Get #1, , Sic2er
ListView1.ListItems.Add Looper, , Sic2er.Sic4
ListView1.ListItems(Looper).ListSubItems.Add , , Sic2er.latbit
ListView1.ListItems(Looper).ListSubItems.Add , , Sic2er.lngbit
ListView1.ListItems(Looper).ListSubItems.Add , , Sic2er.id
Looper = Looper + 1
Loop
Close #1
End Sub
[/tt]

 

[Snaggs]

OK, Here's my two cents worth. Let me throw caution to the wind by saying that referring to the field name by its ordinal value makes for very FRAGILE code. If someone comes in to modify the SQL statement and they insert a field in the SQL statement in front of the one your code is relying on, the fields have now shifted and will produce very erroneous results. There are three ways that I know of the access field information from a recordset.

rsData.Fields(&quot;FieldName&quot
rsData(0..n)
rsData!FieldName

I read in an article some time ago that using the ! operator was faster then the .Fields(&quot;FieldName&quot method. However I can't find supporting documentation at this moment for ADO Recordsets. The Index value of rsData(0) was mentioned as the fastest.

...on to your question... Looking at the code you have, depending on the data, your SQL statement could get very long by concatenating AND on the end with the value. Here would be my recommendation.

Try using the key word DISTINCT in your query or try a GROUP BY in your query.

I would order by sic2 first, then by sic4. This will cause your table/file names to be created in order also as well as the data in them. A sample query might look like this:

SELECT DISTINCT SIC2, SIC4 FROM EmpDB_Bit ORDER BY SIC2, SIC4

This will give you all the unique records for each SIC2 and SIC4 combo. If your data looked like this:

SIC2 SIC4
---- ----
0001 000A
0001 000A
0001 000B
0002 000M
0003 000N
0004 000P
0005 000Y
0005 000Y
0005 000Z

The above query results would look like this:

0001 000A
0001 000B
0002 000M
0003 000N
0004 000P
0005 000Y
0005 000Z

Notice how the '0001 000A' and '0005 000Y' records were suppressed? However this may not be what you desire because this will strip out the duplicates.

On the other hand if you use a Group By then you will get your original dataset, including your duplicates. Then all you have to do is rip through the recordset looking at SIC2 as you did above. When it changes, you know you have to write to a new table/file. Just put the data in the table/file that you need, ie: you may have to query more fields than shown above. This results in one query rather than many. Also as the others mentioned, create an index on any field you order by or use in a where clause (This is just a general rule that I follow... I'm not a DBA by any means... <Disclaimer ends here>)

There are some tricks that you can do with self-joins, although I don't think your task requires it, that are beneficial also.

 

[Karl Blessing]

calling them by numbers is not too risky if you tell the query exactly which fields you want and in what order

...sic2, latbit, lngbit, id....
...0 , 1 , 2 , 3.....

also check my last post, I game up with an alternative solution(he only wants to type in a SIC4, and get all the records ordered by Sic2s into a file.

 

[MikeLacey]

Karl,

You *could* always use constants instead of field numbers.....? (it might be more readable, in a week)

Const FLD_SIC2=0, FLD_LATBIT=1, FLD_LNGBIT=2, FLD_ID=3

It's fragile if you allow people to use &quot;Select *&quot; in SQL. so don't do that then; <smile> always have people specify column lists in Select statements.

You should be able to make it faster if you use the method from my earlier post about reducing the number of references.

 

[Snaggs]

OK, but it's still risky. Look at it this way...

Select sic2, latbit, lngbit, id from EmpDB_Bit

Do Until rs.EOF
s2 = rs(0) 'Sic2
lat = rs(1) 'latbit
lng = rs(2) 'lngbit
id = rs(3) 'id
rs.MoveNext
Loop

Now 6 months later someone else comes in and adds this:

Select sic1, sic2, latbit, lngbit, id From EmpDB_Bit

and isn't aware of the ordinal references or are not used to seeing them. So the code now looks like this:

Do Until rs.EOF
s1 = rs!sic1
s2 = rs(0) 'Sic2
lat = rs(1) 'latbit
lng = rs(2) 'lngbit
id = rs(3) 'id
rs.MoveNext
Loop

What you really have now is this:

Do Until rs.EOF
s1 = rs!Sic1 'sic1 - This is one ok
s2 = rs(0) 'Sic1 - Sic1 is being assigned to s2
lat = rs(1) 'Sic2 - lat is being assigned Sic2
lng = rs(2) 'latbit - lng is being assigned lat
id = rs(3) 'lngbit - id is being assigned id
'The rs(4) 'id' value never gets assigned to anything.
rs.MoveNext
Loop

Had the code been written like this:

Do Until rs.EOF
s1 = rs!Sic1 'Sic1
s2 = rs!Sic2 'Sic2
lat = rs!latbit 'lat
lng = rs!lngbit 'lng
id = rs!id 'id
rs.MoveNext
Loop

This would never be an issue.

In most cases like two examples up, once you make the change, you'll start getting Type Mismatch errors. If you don't then you'll start getting errors where data is being placed in the wrong field. ...again my two cents worth of defensive programming.

 

[Karl Blessing]

there two reasons I use the numbers

1) It is very much faster
2) even if 6 months down the road I doubt it, as I am the only developer on this project, also other than what I post here, the code is heavily commented, and is expected to be finished permenetly, (it's going to be a one time untility or so)

 

[Snaggs]

I did use the ordinal refernece one time, but like you did, I commented it heavily as to why I was doing it and made note of the issue of adding fields to the SQL statement.

Most of my development has been interaction with other team members, so I never know who's going to check out the code and do whatever to it. Point well taken.

 

[MikeLacey]

To reduce the risk we have quite strict programming standards, code review sessions and test plans.

<grin> All very boring, unsexy and effective.

 

[Karl Blessing]

hehe, here we have so few developers ( 2 to be exact, including me ) that we dont have meetings, we just talk one on one, while our CEO or our VP emails us or calls to tell us what they want to show for the meeting at the State (Empoyee's Security Commision) the next day.

but yet the more effective methods will bore ya, unless you actually like the task you have been given.