ADO Recordset Search 1

[MattSTech]

There has got to be a faster way of doing this.

At first I was using code to open and close the recordset, this is way too slow.

For i = lstvwBreakdown.ListItems.Count To 1 Step -1
            strSQL = "SELECT SONO,ITEM FROM tblSchedBkt WHERE SONO = '" & lstvwBreakdown.ListItems(i).Text & "' AND ITEM = " & lstvwBreakdown.ListItems(i).SubItems(1)
            objRS.Open strSQL, objCon, adOpenKeyset, adLockReadOnly
                    If Not objRS.EOF Then
                        frmSplash.lblProg.Caption = "Assigning - " & lstvwBreakdown.ListItems(i).Text
                        DoEvents
                        lstvwBreakdown.ListItems.Remove i
                    End If
            objRS.Close
Next

I then thought it would be faster to leave the recordset open and just filter within it. This took 10x as long.

What is a better way to search this?

            strSQL = "SELECT SONO,ITEM FROM tblSchedBkt" ' WHERE
            objRS.Open strSQL, objCon, adOpenKeyset, adLockReadOnly
                For i = lstvwBreakdown.ListItems.Count To 1 Step -1
                    objRS.MoveFirst
                    strFindstring = "SONO = '" & lstvwBreakdown.ListItems(i).Text & "' AND ITEM = '" & lstvwBreakdown.ListItems(i).SubItems(1) & "'"
                    objRS.Filter = strFindstring
                    If Not objRS.EOF Then
                        frmSplash.lblProg.Caption = "Assigning - " & lstvwBreakdown.ListItems(i).Text
                        DoEvents
                        lstvwBreakdown.ListItems.Remove i
                    End If
                    objRS.Filter = ""
                Next
            objRS.Close

 

[gmmastros]

What database engine are you using? SQL Server, Access, Oracle, etc...

How many rows are in the table (tblSchedBkt)?
How many rows (typically) are in the lstvwBreakdown?

Please describe (generally) what you do with each row after you call the recordset. I mean inside the If Not objRS.EOF loop.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

Also, how did you declare your recordset?

This should be pretty fast:

[blue]
Dim objRS As ADODB.Recordset[/blue]

strSQL = "SELECT SONO,ITEM FROM tblSchedBkt"

Set objRS = New ADODB.Recordset[blue]
objRS.CursorLocation = adUseClient[/blue]
objRS.Open strSQL, objCon, adOpenKeyset, adLockReadOnly

For i = lstvwBreakdown.ListItems.Count To 1 Step -1[green]
    'objRS.MoveFirst[/green]
    strFindstring = "SONO = '" & lstvwBreakdown.ListItems(i).Text _
        & "' AND ITEM = '" & lstvwBreakdown.ListItems(i).SubItems(1) & "'"
    objRS.Filter = strFindstring
    If Not objRS.EOF Then
        frmSplash.lblProg.Caption = "Assigning - " & _
            lstvwBreakdown.ListItems(i).Text
        DoEvents
        lstvwBreakdown.ListItems.Remove i
    End If
    objRS.Filter = ""
Next
objRS.Close
Set objRS = Nothing

Have fun.

---- Andy

 

[MattSTech]

I am hitting an access database that I will eventually turn into a sql database, but just assume access right now.

The table has about 50000 rows. It will likely grow to 70000 over the next 5 years.

breakdown list starts at 50000 records and winds its way down to 100 or so "unscheduled" lines.

If I find a record that matches the SONO field and the ITEM field I simply remove it from the list.

The code posted it literally all it does. The concept is assigning a group "bucket" to put breakdown lines into. If a new line is generated in another database (All lines in the other database populate the breakdown list) I just compare this list to the tblSchedBkt table to determine if a bucket has been assigned.

 

[gmmastros]

If a new line is generated in another database

Is the "other" database an Access database also? Is it a separate database, or is it really just another table in the current database?

I don't work with Access too much, but I am 95% sure that you can write a single query that joins the data from separate tables in separate databases. If you can accomplish this, then a simple left join query (WHERE column is null) will get you all the rows that exist in one table but don't exist in the other.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MattSTech]

Anderzejek, yes, this is how I do it, it just was above and below my code and I didn't paste it.

It looks like all that is added is
objRS.CursorLocation = adUseClient.

Doing this made the code just as fast as the open and close table method. This still takes about a minute and a half to accomplish. Still too slow.

 

[MattSTech]

The other DB is SQL. I am just getting into INNER joins in my programming life. I haven't used this before. I will dig into it and see if it is what I want. This would be great.

Thanks,

 

[Andrzejek]

list starts at 50000 records and winds its way down to 100 or so

I would definitely come up with the SELECT statement to just get those 100 records or so instead of deleting almost 100% of your items from the list.

Have fun.

---- Andy

 

[gmmastros]

Matt,

Given that your other database is SQL Server, I would suggest this:

In SQL Server, create a linked server to your access database. Setting up a linked server is not usually difficult to do. You may run in to a small problem if your Access database is not on the same computer as your SQL database. If they are on the same computer, then it will be a little easier.

Once you have the linked server in place, you will be able to access all of the data in the Access database just by using the linked server. You can then write a LEFT JOIN query in the SQL Database that joins to the table in the Access database and return the rows that exist in one table but not the other.

Given that you only have 50,000 (ish) rows, I would expect the query to take less than 1/2 a second (slightly longer if the Access database is on another computer).

I'm fairly certain that you could do the same thing in reverse by creating a link in the Access database to the SQL database. I've never done this, but I'm 95% certain it's possible.

I would encourage you to do a little research on linked servers. [google]Set up linked server from SQL Server to Access[/google]. And then a little research on using a left join to find missing data [google]sql server left join find missing data[/google].

If you run in to any problems, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MattSTech]

I don't really have the first clue how to do this. I am looking into a join as mentioned above. My current statement that fills the list is below.

strSQL = "SELECT SOMAST.fsono,SOMAST.fcustno,SOMAST.Fcompany,SOMAST.fcustpono,SOMAST.fstatus,SOITEM.finumber," & _
                "SOITEM.fgroup,SOITEM.finumber,SOITEM.fpartno,SOITEM.fdesc,SOITEM.fduedate,SOITEM.FQUANTITY,SOITEM.fprodcl," & _
                "SORELS.Finvqty,SORELS.Forderqty,SORELS.funetprice,SOMAST.fduedate AS SOMAST_DUEDATE,SOITEM.fduedate AS SOITEM_DUEDATE " & _
                "FROM(sorels INNER JOIN ((SOITEM_EXT INNER JOIN soitem ON SOITEM_EXT.FKey_ID = SOITEM.identity_column) " & _
                "INNER JOIN SOMAST ON SOITEM.fsono = SOMAST.fsono) ON (SOITEM.finumber = SORELS.finumber) " & _
                "AND (SORELS.fsono = SOITEM.fsono))" & _
                " WHERE SOMAST.fstatus <> '" & "Closed'" & "AND SOMAST.fstatus <> '" & "Cancelled'"

Where would I start adding the join to the other database?

 

[MattSTech]

Ok I will dig into those. Sorry for the delayed postings, you guys are way too fast.

 

[Bluejay07]

I believe your last statement can be changed from

" WHERE SOMAST.fstatus <> '" & "Closed'" & "AND SOMAST.fstatus <> '" & "Cancelled'"

to

" WHERE SOMAST.fstatus <> 'Closed' AND SOMAST.fstatus <> 'Cancelled'"

All you were doing was closing and opening a string without any additional information. Also a space was required between 'closed' and 'and'.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[MattSTech]

Good catch on that. I was using variables for these in the past and the quotes carried over.
Thanks,

 

[Andrzejek]

Well, Bluejay07 - if you want to be picky you may go with:

WHERE SOMAST.fstatus [blue]NOT IN ('Closed', 'Cancelled')[/blue]

Have fun.

---- Andy

 

[MattSTech]

Obviously my first try is a failure, but here is what I am working with.
I pulled out my Where to simplify it a bit

            strSQL = "SELECT SOMAST.fsono,SOMAST.fcustno,SOMAST.Fcompany,SOMAST.fcustpono,SOMAST.fstatus,SOITEM.finumber,SOITEM.fenumber," & _
                "SOITEM.fgroup,SOITEM.finumber,SOITEM.fpartno,SOITEM.fdesc,SOITEM.fduedate,SOITEM.FQUANTITY,SOITEM.fprodcl," & _
                "SORELS.Finvqty,SORELS.Forderqty,SORELS.funetprice,SOMAST.fduedate AS SOMAST_DUEDATE,SOITEM.fduedate AS SOITEM_DUEDATE " & _
                "FROM(sorels INNER JOIN ((SOITEM_EXT INNER JOIN soitem ON SOITEM_EXT.FKey_ID = SOITEM.identity_column) " & _
                "INNER JOIN SOMAST ON SOITEM.fsono = SOMAST.fsono) ON (SOITEM.finumber = SORELS.finumber) " & _
                "AND (SORELS.fsono = SOITEM.fsono)) LEFT JOIN (SELECT SONO,ITEM FROM tblSchedBkt IN '" & strCarverLoc & _
                "') As tblBktList ON tblSchedBkt.SONO,tblSchedBkt.ITEM = SOMAST.fsono,SOITEM.fenumber"

 

[MattSTech]

I keep messing a bit while researching a bit. It doesn't seem to like the "IN"

strSQL = "SELECT SOMAST.fsono,SOMAST.fcustno,SOMAST.Fcompany,SOMAST.fcustpono,SOMAST.fstatus,SOITEM.finumber,SOITEM.fenumber," & _
                "SOITEM.fgroup,SOITEM.finumber,SOITEM.fpartno,SOITEM.fdesc,SOITEM.fduedate,SOITEM.FQUANTITY,SOITEM.fprodcl," & _
                "SORELS.Finvqty,SORELS.Forderqty,SORELS.funetprice,SOMAST.fduedate AS SOMAST_DUEDATE,SOITEM.fduedate AS SOITEM_DUEDATE " & _
                "FROM(sorels INNER JOIN ((SOITEM_EXT INNER JOIN soitem ON SOITEM_EXT.FKey_ID = SOITEM.identity_column) " & _
                "INNER JOIN SOMAST ON SOITEM.fsono = SOMAST.fsono) ON (SOITEM.finumber = SORELS.finumber) " & _
                "AND (SORELS.fsono = SOITEM.fsono)) where not exists ((SELECT SONO,ITEM FROM tblSchedBkt IN " & strCarverLoc & _
                ")WHERE tblSchedBkt.SONO,tblSchedBkt.ITEM = SOMAST.fsono,SOITEM.fenumber)"

 

[gmmastros]

Try this:

            strSQL = "SELECT SOMAST.fsono,SOMAST.fcustno,SOMAST.Fcompany,SOMAST.fcustpono,SOMAST.fstatus,SOITEM.finumber,SOITEM.fenumber," & _
                "SOITEM.fgroup,SOITEM.finumber,SOITEM.fpartno,SOITEM.fdesc,SOITEM.fduedate,SOITEM.FQUANTITY,SOITEM.fprodcl," & _
                "SORELS.Finvqty,SORELS.Forderqty,SORELS.funetprice,SOMAST.fduedate AS SOMAST_DUEDATE,SOITEM.fduedate AS SOITEM_DUEDATE " & _
                "FROM(sorels INNER JOIN ((SOITEM_EXT INNER JOIN soitem ON SOITEM_EXT.FKey_ID = SOITEM.identity_column) " & _
                "INNER JOIN SOMAST ON SOITEM.fsono = SOMAST.fsono) ON (SOITEM.finumber = SORELS.finumber) " & _
                "AND (SORELS.fsono = SOITEM.fsono)) LEFT JOIN (SELECT SONO,ITEM FROM tblSchedBkt " & _
                "') As tblBktList ON tblBktList.SONO = SOMAST.fsono and tblBktList.ITEM = SOITEM.fenumber " & _
WHERE tblBktList.SONO Is NULL"

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

It doesn't seem to like the "IN"

If you are talking about 'my' IN - it should work in the context of [tt]"WHERE SOMAST.fstatus NOT IN ('Closed', 'Cancelled')"[/tt]

Have fun.

---- Andy

 

[MattSTech]

This gives me an unclosed quote error. So I remove the ') before "As tblBktList ON..."
Then it gives me an "incorrect syntax near "ON"?

 

[MattSTech]

After looking at this more, there is no reference to the second database gmmastros. I am working with an Access DB with the table tblSchedBkt and a SQL DB with multiple tables.

I would need to reference the Access DB somewhere in this statement yes?

My code, strCarverLoc, held the path to the access DB.