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

 

[MattSTech]

I linked the tables in access and am trying to get the results I need. Will post back how it pans out.

 

[MattSTech]

Ok. I am trying to simplify it a bit. I have the tables linked and the code is giving me all the records that both tables have in common. How do I get the rows that are in the table named soitem that are not in the table named tblSchedBkt? The WHERE Null isn't working>

SELECT soitem.fsono, soitem.finumber
FROM soitem INNER JOIN tblSchedBkt ON (soitem.finumber = tblSchedBkt.ITEM) AND (soitem.fsono = tblSchedBkt.SONO)

 

[MattSTech]

Oh my. If only I would just listen instead of type.

Sorry for the last 4 posts and I suppose the entire thread. The below works.

Your help is certainly appreciated George, Andy!

SELECT soitem.fsono, soitem.finumber
FROM soitem LEFT JOIN tblSchedBkt ON (soitem.finumber = tblSchedBkt.ITEM) AND (soitem.fsono = tblSchedBkt.SONO) WHERE tblSchedbkt.SONO Is NULL

 

[gmmastros]

I'm curious. You said it took 1.5 minutes with the original method. How long does it take now?

-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]

Correct me if I am wrong (George or MattSTech), but isn't it the same as saying:

SELECT soitem.fsono, soitem.finumber
FROM soitem 
WHERE soitem.fsono  NOT IN (SELECT DISTINCT SONO FROM tblSchedBkt)
AND soitem.finumber NOT IN (SELECT DISTINCT ITEM FROM tblSchedBkt)

If it is the same, it is easier to read, IMO

It may involve more manipulation of data on the DB side tho....

Have fun.

---- Andy

 

[tedsmith]

Are the fields indexed?
Using msaccess created tables in vb6 recodsets makes a huge difference to searching.

I have a table with 56,000 rows of 20 columns in one app and it only takes a fraction of a second to find any single numerical record when they are indexed as opposed to 10 seconds when not indexed.

 

[MattSTech]

I am very sorry for the late replay. I went off to conquer the world with my new knowledge of JOINS and ran into a problem. 64-Bit SQL Server does not offer Jet as an option for linked servers. So I set off on the direction of upsizing my Access DB to SQL. I have since done this and now have a data type problem I believe.
How do I force my comparisons to only compare numeric value?
e.g. ITEM = 3 soitem = 003 should be a match

SELECT fsono,fenumber,SONO,ITEM,INT FROM 
M2MData01..soitem LEFT JOIN CPC_Tools..tblSchedBkt ON (CPC_Tools..tblSchedBkt.SONO=soitem.fsono) AND (CPC_Tools..tblSchedBkt.ITEM=soitem.fenumber)
WHERE CPC_Tools..tblSchedbkt.SONO Is Null

 

[gmmastros]

I assume it's this part causing you a problem:

[tt]
(CPC_Tools..tblSchedBkt.ITEM=soitem.fenumber)
[/tt]

It's possible that the columns have different data types. Can you please verify this for me, by running the following queries:

Select data_type
From   M2MData01.information_schema.columns
where  table_name = 'soitem'
       and column_name = 'fenumber'

Select data_type
from   CPC_Tools.information_schema.columns
where  table_name = 'tblSchedBkt'
       and column_name = 'item'

If the data types are differnet, then it is time you learn about data type precedence. First, take a look at this:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

If one is a varchar and the other is a number, the varchar would be converted to a number for the purposes of the comparison.

If both are varchar, and you would like to compare them as numbers, you have a couple choices. You could pad both columns with zero's for the comparison or you could remove the leading zeros from both columns.

Either way, you should understand that you are causing SQL Server to do more work, which means a decrease in performance. If your columns are storing numbers, then you should use a number type (like int, bigint, decimal, etc...). If you need to store alpha/numerics, then you should be consistent with your zero padding. If you can't change the structure of either table, then there are other tricks you could do to improve performance, like indexed computed columns.

Let me know the answers to the questions and then I will reply with my recommendation.


-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]

data type = char
data type = nvarchar

What I have is a database with some data created in a legacy system, and some from the current system. Some of the data matches (old system, no padded zeros) while others do not (new system, padded zeros)
I can't change the structure now. Once I have ported all my data over, I can do it, but until this system is operational, I just can't.

How would I compare forcing both to pad zeros?

 

[gmmastros]

It's not too difficult to do. The idea is, you add a hardcoded string of zeros to the front of your data, and then take x number of characters to the right.

So, if your data was 123, and you wanted to pad it with 0's to be 6 characters, you do this:

Right('000000' + YourColumn, 6)
Right('000000' + '123', 6)
Right('000000123', 6)
'000123'

So...

(Right('000000' + CPC_Tools..tblSchedBkt.ITEM, 6) = Right('000000' + soitem.fenumber, 6))

You may not need to pad the data from both tables, but you may want to do that just in case.

-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]

Can I do the same thing with format(mydata,"000000") or does this do something bad? I have used this in the past.

 

[gmmastros]

There is no format function in SQL Server. The code I show effectively does the same thing.

-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