Techniques in accessing SQL server database

[vikoy]

Hi to all,

Any tip or ideas on what Data access and search method/routine should we use in getting a recordset? we had 2.5 million records to search and have it stored in SQL server. We are just starting developing the interface and we don't want to suffer the user of waiting to get the data from the network for so long.

Thanks in advanced.

[JHausmann]

Consider using stored procedures to get to the data...

[vikoy]

Hi dude,

thanks for replying....

>>> Another idea I just had would be could you retrieve say the first 50 records and when they get to the end of those retrieve the next 50 records?

This is basically a good idea, but what i'm thinking is upon searching for the first 50 match in the table , then the user will attempt to get a match for 50 records again , would the search will begin to the first records again? I think is not optimize since every attempt of searching the table would begin in the first record in the table.

can you give me an example (code) on how to ignore search on records that has been searched in the table?

TIA. Please help.

[Nathan]

I've never actually done it before, but we are talking about figuring it out for my work. Today looks like it will be pretty slack in the morning so I'll work on it a bit and let you know if I get something working...

[Nathan]

That was a lot easier than I thought it would be... you can do it with the sql query assuming that you have a unique field. If not it may take a bit more manipulation but here's what I have.

SELECT TOP 50 *
FROM TblName
WHERE FieldName > ?
ORDER BY FieldName

where ? is "" for the first retrieve and the highest value of fieldname retrieved in the previous queries.

let me know if this works for you or if you need more help.

[JHausmann]

<sigh> Makes me wanna upgrade. Can't use top in 6.5...

[Nathan]

Really? I didn't know that...

could you use Max Records in a data environment command then and leave TOP 50 off of your sql query? I think it should end up working the same... Let me know...

[vikoy]

Brothers,

SELECT TOP 50 *
FROM TblName
WHERE FieldName > ?
ORDER BY FieldName

>>>>where ? is "" for the first retrieve and the highest value of fieldname retrieved in the previous queries.


Could you be more specific on this? I haven't tried SQL before that's why I'm pretty hard to understand the code...

Thanks in advance.

[Nathan]

OK here's what I mean. First I would recommend using the dataenvironment to save yourself some time.

DE1 = the data environment
TOP50 = the command in the data environment

To setup the command create a command and include the SQL in the command SQL area. then here is a quick sample of what you could do with the code.

in a command a ? means that it is a parameter where information will be supplied when the commands recordset is opened in the code.

Private Form_Load()
'this will
DE1.TOP50 ""
End Sub

Private cmdNext_Click()
DE1.rsTOP50.MoveNext
If DE1.rsTOP50.EOF Then
DE1.rsTOP50.MoveLast
strHighValue = DE1.rsTOP50(0)
DE1.rsTOP50.Close
DE1.TOP50 strHighValue
DE1.TOP50.MoveFirst
End If
End Sub

if this isn't clear let me know and I'll try to explain furthur.

[vikoy]

Dude,


You got an excellent code, but using Data environment and commands in design time has limitations for me, what if I do it programmatically? but how? any idea?

From time to time I got a clear understanding of SQL commands.

BTW,--->strHighValue = DE1.rsTOP50(0) whats does it mean?



Please help again. thanks....

[Nathan]

first I am assuming that you have an open connection (cn)

Option Explicit

Dim rs As New ADODB.Recordset

Private Sub Form_Load()
Dim strSQL As String

strSQL = "SELECT TOP 50 * FROM TblName WHERE FieldName > '' ORDER BY FieldName"
rs.Open strSQL, cn
rs.MoveFirst
End Sub

Private Sub cmdNext_Click()
Dim strHighVal As String
Dim strSQL As String

rs.MoveNext
If rs.EOF Then
rs.MoveLast
strHighVal = rs("fieldname")
rs.Close
strSQL = "SELECT TOP 50 * FROM TblName WHERE FieldName > '" & strHighVal & "' ORDER BY FieldName"
rs.Open strSQL, cn
rs.MoveFirst
End If
End Sub


in cmdNext_Click I am moving the recordset to the next record if the recordset is at the end of file I get the fieldname value for the last record in the recordset and store that in highval. then I close the recordset and reopen in using the previous high value. then move to the first record. Note that the fieldname must be a unique value or you could potentially miss a record or two. You might also have to rebind controls on your form every time you open your recordset. It would be best to just create a function to do this and call it immediately after all rs.Movefirst after the rs.Open.

if you need more help let me know.

[vikoy]

Nathan,

Million thanks.... Your reply clarifies all things.....

Vic

[vikoy]

Sanon,

What a nice opinion, stored procedures are optimized because they are executed at the server side. You are saying that paging technique is much better,correct? But how was it done? can you give me a sample code on it? Iwas thinking of having records displayed only in 50 instead of a bunch of 5,000, right?

THNAKS.

[sanon]

Here what it looks like...

Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRs As ADODB.Recordset
Dim spPara As Variant

Private Sub cmdNext_Click()
    Dim intPage As Integer
    
    intPage = objRs.AbsolutePage
    intPage = intPage + 1
    If intPage <= objRs.PageCount Then
        objRs.AbsolutePage = intPage
    End If
End Sub

Private Sub cmdPrevious_Click()
    Dim intPage As Integer
    
    intPage = objRs.AbsolutePage
    intPage = intPage - 1
    If intPage > 0 Then
        objRs.AbsolutePage = intPage
    End If
End Sub


Private Sub Form_Load()
    Set objConn = New ADODB.Connection
    Set objCmd = New ADODB.Command
    Set objRs = New ADODB.Recordset
    
    'Connect the database via an ODBC
    With objConn
        .ConnectionString = "DSN=XXX;UID=ZZZ;PWD=KKK"
        .Open
    End With
    'Assign a keyword which to be sent to a store proc
    spPara = InputBox("Keyword", Search)
    'Prepare to call store proc
    With objCmd
        .ActiveConnection = objConn
        .CommandText = "store_proc_name"
        .CommandType = adCmdStoredProc
    End With
    'Prepare a recordset
    With objRs
        .CursorLocation = adUseClient
        .PageSize = 50
    End With
    'Call store proc and retrive the result into a recordset
    Set objRs = objCmd.Execute(, spPara)
    objRs.ActiveConnection = Nothing
    'Close the connection to the database for releasing resources
    Set objConn = Nothing
End Sub


Private Sub Form_Terminate()
    Set objCmd = Nothing
    Set objRs = Nothing
End Sub

[Clunietp]

Hey guys, the pagecount/pagesize/cachesize/etc... only works with server side cursors.

If you are using client side cursors (which you are, as shown in the code example), you can specify the Maxrecords property, but that's all you can do to limit the number of records returned (besides refining your SQL statement)

[sanon]

Thanks Clunietp to give me some notice, but I'm pretty sure that PageSize and PageCount properties can be used in Client Side cursors. However, the cachsize can be used on only server side cursor as you mentioned.

[vikoy]

Guys,

Sanon,Clunietp and Nathan..... Please do help beginners like me and more power to all of you.

a simple idea mean so much in us.... Thanks again.

[asabi]

Correct me if I am wrong guys, but it should all run on SQL server ...

All the code I saw, was using access ..

as far as I know "top 50" wouldn't work for SQL server you will have to use "set rowcount 50 select ..."

to make it work on SQL server ..

[Nathan]

Top 50 works for SQL Server. That's what I'm using and what i was using to test it.

[Serge]

It works with SQL Server 7.

[Clunietp]

Originally posted by sanon
Thanks Clunietp to give me some notice, but I'm pretty sure that PageSize and PageCount properties can be used in Client Side cursors. However, the cachsize can be used on only server side cursor as you mentioned.

thanks for the correction Sanon, I believe you are correct

[AKA]

Sanon,

Very fine example !

You use client side cursors, does not that mean that you will move all 5000 record to the client and that was the thing we wanted to avoid.

Just a quick addition to the posts,
If you are searching all that data, and only getting a few thousand records back, then the index impact on the query could vastly outweigh the time taken to transfer or process any data you want.
Best make sure that the indexes are pretty good from the start. You probably already have several indexes, but just in case...

Also, if you do bring large resultsets back to the client with client side cursors, has anyone else noticed that if the resultset tops out memory then it will stop the operation with no errors or warning and leave you with an empty resultset?