queries and s-l-o-w progams

here's the overview:
- i have a text file that in this case is 78 megs. each line of the file is basically a database row, in that it is a fixed field row. each row is 350 bytes.
- i have a table in my SQL server that is approx 2.4 million rows.
- i get values for 8 of the text file fields and need to compare them to the database.

what i'm doing right now is simply creating the query (that compares the text file fields with the DB in a SELECT * WHERE), opening the recordset with it, getting the recordcount, doing the little thing that needs to be done depending on the recordcount, and then closing the recordset. and i repeat that for each record in the text file. and it's killing me. i stopped the program about 40 minutes into it and i had about 54 Megs out of 78 Megs done. and that's not acceptable for this project.

now to my question....is there anything that i can do with queries, stored procedures, or anything that could speed this all up? in debugging it's taken up to one minute for the query to go through, and one minute time 232,000 rows is too long.

how about being able to open the RS and then do little queries once the RS is open? like how you can do RS.AddNew and things like that. i'm stumped and under the machine gun.

thanks!
bryan

Can you verify what, if any, indexes exist on those 8 fields. From
an earlier question, it appeared there were spaces in those fields
in SQL Server? Without totally changing your database structure,
about all you can do to speed things up is have indexes that can
actually be used to do the compares (a useless index is one
where a given value is found in over 20% of the rows.) Also, if
indexes exist, the order of the compares is important. The most
unique indexed field should be first in the statement, then the
next most unique, and so on, then the non indexed fields.

It would probably be to your advantage to experiment with
different variations of your query in the query analyzer, comparing
execution plans, and if necessary perform index analysis.

the only thing that could be indexed is store #. what this file contains (and the db as well) is label info. labels like you see at the grocery store. so the only info that will be consistent is store #. everything else is basically random.

i've been playing in VB with RS.Find once I do a SELECT *. i mean it takes about 3 minutes or so for the query to execute, but i'm thinking that once it's open i can to a RS. Find and it will return something usefull. all i need is a boolean to let me know if the record exists.

i'm quite new to SQL server and to databases of this size, so i'm sorry if i'm coming off like i don't really know much. cause in this case it's true.

thanks for taking the time,
bryan

- i have a table in my SQL server that is approx 2.4 million rows.
...
i've been playing in VB with RS.Find once I do a SELECT *. i mean it takes about 3 minutes or so for the query to execute, but i'm thinking that once it's open i can to a RS. Find and it will return something usefull. all i need is a boolean to let me know if the record exists.

It's doing pretty good if it can return a recordset with 2.4 million
rows in 3 minutes or so. Believe me, all recordset search functions
are known to be many times slower than an equivalent SQL
Select.


the only thing that could be indexed is store #. what this file contains (and the db as well) is label info. labels like you see at the grocery store. so the only info that will be consistent is store #. everything else is basically random.

The more unique (random) the data in a field, the better an index
on that field works. Store # sounds like it would be the least
unique.

sorry to ask such a beginner question, but could you explain SQL server indexes? i'm thinking that i just don't fully understand them. if i were to set up indexes on my "most unique" fields, this could/would speed up my queries? i know where to set up indexes, would i focus on unique values? would i need to 'edit SQL'?

now my head is spinning....
thanks again,
bryan

The reason indexes on very unique values speed up searchs is
the index can be scanned very quickly if it doesn't contain very
many row identifiers for a particular value. Indexes in databases
are almost always balanced tree structures, meaning finding a
single row in 2.4 million would only take at most 24 value
compares. Without the index, there is no choice but to start
looking through the rows of the table. Something known in SQL
Server as a table scan, and with a table this big, something to be
avoided if possible. Is the Primary key field for the table one of
the 8 fields being compared?

i elected not to have a primary key (and that was so stupid in hindsight) because i could not find a way that SQL server does an autonumber and because if i had a primary key on any of the fields i'd get duplicate key errors.

i think i just need to look for something online that will walk me through iindexing my table. it seems as though that could be the way to speed up the queries and still have then the way i intended. instead of opening the table with a generic SELECT and trying to do a Find or somethin gsimilar once the RS is open.

bryan

You can have a autonumber type field for the primary key. It is
called an identity column. Still, in this particular instance, it
wouldn't help you. The default for primary key fields is a clustered
index. You can only have one clustered index on a table, so you
might very well want that on one of these other fields. A clustered
index actually orders the rows in the table. They don't work all
that well with identity columns. If you have access to Enterprise
Manager, setting up indexes isn't that hard, but you do need to
analyze which fields would benefit the most from an index.

One more thing, you said you couldn't find any one field that was
unique, are there any groups of fields that together would always
make the row unique?

Originally posted by Thinker

One more thing, you said you couldn't find any one field that was
unique, are there any groups of fields that together would always
make the row unique?
well it would have to be a combination of about 5 fields before i'd say i'm sure the rows are unique.

i have enterprise manager up and i'm looking at setting up a couple indexes. for now i'm going with a hit and miss approach on which fields and what kind of index as long as i can't screw anything up in the data or anything important.

bryan

A compound index on 5 fields might very well be in order in this
case. Once again, the order of the fields in the compound index
is very important. The most unique needs to be first, then the
next, for all 5 fields. This could be a clustered index, and could
significantly speed up those queries, especially if the first field in
the index is also one you are searching on.

well first off i have to thank you for al your help. this has really helped me see that i need to do more on the databse end to speed my apps up.

i've jsut set a new clustered index with the 5 fields, however i didn't rearrange the order. so i'll go back and do that now. hopefully this will do the trick....

oh last thing....
i'm wanting to set this index and try my app again with executing one query at a time for each row in the text file. so in your estimation, just setting the clustered index will help? i don't need to put anything in my SELECT besides all the WHERE info? just making sure before i start this query and get stuck watching it for hours.

thanks again.
bryan

Whether clustered or not right now won't matter for a test. It
should still be much faster. But, rather than try to test with all
232000 rows, can you create a subset of that text file with
enough rows that the results can be extrapolated out to guess
how long it will take for the whole file. You could even do single
queries in Query Analyzer and see how fast they are.

oh yeah, i'm splitting up the text file right now for the test.

again, many thanks for the help. i need to send you a case of beer! :D

bryan

I've been watching the conversation... very, very interesting.
I hope you can get this to work right.

Can I ask a really simple question?
If all you need is a recordcount, is it critical that you return a recordset from the server?

I do this all the time, but I do not return recordsets. It is pretty fast too. I think I do have a routine that even uses several arguments (I see you have 5, uggh) at a time.

Just curious.
diver

That's a good point. I couldn't be sure, because it looked like
depending on the recordcount, he would have to actually do
something with the records. I do hope he posts back, I would like
to know how anything we discussed actually helped.

Thinker...

This one had me wondering... and I went and looked on my server because I had done a profile to see how much time the Select Count(*) method was taking me via a stored procedure.

Turns out it was only taking .000007 of a second for each firing. Now, I realize that he's got a couple million records, and a 5 combo SQL syntax would be nasty... but still, I can't see it taking 3 minutes (!).

I've recently started doing something new on these... for example, I had a procedure where I had to see if a record still existed on the server before I deleted it from an Access database table. I found that the numerous select count(*) methods were working, and better than returning the entire recordset and doing a find method.

The problem got worse when the connection got poor (maybe a 45k or something). So, instead, I grab the entire recordset (disconnected) & do a getrows method. Then I use a binary search algorythm to locate the record in the array. I've found that by doing this with other recordsets, I can create a "ghost" directory for grids and everthing else and use a straight absolute position. It saves me almost 97% time useage.

diver

ok, and i'm back. i ran my app with the index and it was ten times faster. i think it took about 30 minutes to go through the label file and compare each row with the table.

i don't necessarily need to return a record, just a recordcount. this whole thing is see if the current row in the label file is a duplicate. meaning, the current row is in the table. so i need to go through the table, and basically just return a recordcount. after running it, my project leader is still kind of unsatisfied with the running time, so i'm looking to hopefully cut it in half somehow.

i still thank Thinker for his help, now i'm trying to see if i can fine tune this all to maybe get the time into a more managable frame.

thanks guys!

You are using a stored procedure to do the select count(*), right?

diver

no i'm not. i've never used a stored procedure, so i'm not sure how i could use it with the variable data for each query.

any tips?

A stored proc could speed things up because the SQL is 'prepared'
which is the closest thing to compiling you can get with SQL. It
cuts the time out to parse your SQL each time you submit it. Since
you are searching for 8 values, it would require passing 8
parameters, not a really big deal, but will require care. One
thing I am not sure about. It seems to me it if you want to just
return a recordcount, it would be faster to say
Select Count(FirstFieldInIndex) than to say Select Count(*). But I
seem to remember diver saying in some tests he did, Count(*)
was just as fast or faster. You might want to compare for yourself.
Any way you look at it, diver is right, if all you need is a record
count, returning Count(something) is faster then returning
something. The code must change because you will always have
one row in the recordset, and you have to check the value of the
returned field. Example

Set rs = cn.Execute("Select Count(field) As RecCnt From Table1", _
intRecs,adCmdText 'or adCmdStoredProc if using one
RecCnt = rs!RecCnt

I am sure either diver or myself could give you an example of this
in a stored proc.

i tried the count(*) and it is fast. my question is how can i use that in so far as seeing if it returned a zero. i see that the code needs to be changed, because one row is always retirend, but i can't seem to find what count(*) returns. if i knew that then i could just change my code from:


'use the label vars to create a query
sSQL = "Select * FROM krg_redundant WHERE"
'sSQL = "* FROM krg_redundant WHERE"
sSQL = sSQL & " store = '" & sStore & "'"
sSQL = sSQL & " AND gname = '" & sGName & "'"
sSQL = sSQL & " AND pack = '" & sPack & "'"
sSQL = sSQL & " AND lsize = '" & sSize & "'"
sSQL = sSQL & " AND rtl = '" & sRTL & "'"
sSQL = sSQL & " AND unp = '" & sUNP & "'"
sSQL = sSQL & " AND oen = '" & sOEN & "'"
sSQL = sSQL & " AND conupc = '" & sConUPC & "'"
sSQL = sSQL & " AND regrtl = '" & sRegrtl & "'"
sSQL = sSQL & " AND regsav = '" & sRegsav & "'"

Debug.Print sSQL

'open the table with the generated query
objRS.Open sSQL, objConn, adOpenStatic, adLockOptimistic


'if the recordcount = 0 then there were no matches...
'therefore no dupes. write out to print file
If objRS.RecordCount = 0 Then
Print #iFileID3, sLine

'if the recordcount is not 0 then there are dupes,
'and write out to the dupes file
Else

Print #iFileID2, sLine

End If

objRS.Close

to then use the count(*) and instead of recordcount....use what?

thanks!

Sure, easy enough.


'use the label vars to create a query
sSQL = "Select count(*) As RecCnt FROM krg_redundant WHERE"
'sSQL = "* FROM krg_redundant WHERE"
sSQL = sSQL & " store = '" & sStore & "'"
sSQL = sSQL & " AND gname = '" & sGName & "'"
sSQL = sSQL & " AND pack = '" & sPack & "'"
sSQL = sSQL & " AND lsize = '" & sSize & "'"
sSQL = sSQL & " AND rtl = '" & sRTL & "'"
sSQL = sSQL & " AND unp = '" & sUNP & "'"
sSQL = sSQL & " AND oen = '" & sOEN & "'"
sSQL = sSQL & " AND conupc = '" & sConUPC & "'"
sSQL = sSQL & " AND regrtl = '" & sRegrtl & "'"
sSQL = sSQL & " AND regsav = '" & sRegsav & "'"

Debug.Print sSQL

'open the table with the generated query
objRS.Open sSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText '<- note changes here, should help speed it up also


'if the recordcount = 0 then there were no matches...
'therefore no dupes. write out to print file
' the recordset should have one row/one field.
If objRS!RecCnt = 0 Then 'fieldname from As alias in SQL
Print #iFileID3, sLine

'if the recordcount is not 0 then there are dupes,
'and write out to the dupes file
Else

Print #iFileID2, sLine

End If

objRS.Close

ah, very nice. again, many thanks!

edit: actually one question...in your code you have:

If objRS!RecCnt = 0 Then 'fieldname from As alias in SQL


what is the alias you are referring to? because that is not a field in my table and of course give me the "cannot find..." error.

just trying to learn how to effectively use databases finally.

Actually, the fastest way I've discovered on the record counts, is to use a command object, and pass the parameter from the command object to a stored procedure. Then all the stored procedure does is return the count.

You have to "load" the parameters of command object... I don't know how familiar you are with the command object... if you are not, let me know how many fields and their datatypes and I can create a sample for you.

diver

wow, thanks for the help. i have 10 fields at most (we're still hammering out which fields are needed for the correct results, but as of now we're using all 10 fields), and all the fields in the table are "char" fields.

i've never used a stored procedure, so if you have a link to a tutorial, or you can give me a quick rundown on how to create, call and use the results from the store procedure....i'll have to track you down for a case of beer too.

thanks again.

Originally posted by grimes

edit: actually one question...in your code you have:

If objRS!RecCnt = 0 Then 'fieldname from As alias in SQL


what is the alias you are referring to? because that is not a field in my table and of course give me the "cannot find..." error.

just trying to learn how to effectively use databases finally.

I probably should have put something else in to set off this part of
your code that I also changed...

sSQL = "Select count(*) As RecCnt FROM krg_redundant WHERE"

See that As RecCnt. If you don't use that, it has nothing to tie
back to the fields collection of the recordset. Since you got the
error, I assume you didn't see this change.

From what I can guess about what diver is suggesting with the
command object, you wouldn't be returning a recordset at all from
the stored proc. The count would be returned in a command
parameter object. I can't confirm or deny that this would be
faster than returning a one row, one field, readonly, forward only
cursor, but it is possible.

It is faster...

Remember, the stored procedure is just getting thrown some parameters, and it resides on the server... so it processes the query with those parameters and just throws back an integer. Much, much lighter operation in my opinion... and if the connection is poor, it will be noticeable.

I'm trying to figure out, however, the actual constraints on the SELECT statement... are we talking 10 fields ??? I mean like... WHERE blah AND blah AND blah AND..... ??

diver

Remember, the stored procedure is just getting thrown some parameters, and it resides on the server... so it processes the query with those parameters and just throws back an integer. Much, much lighter operation in my opinion... and if the connection is poor, it will be noticeable.

I agree this would have to be the lightest weight return, but like I
said, this is against a recordset with a single row with a single
column and it is readonly and forward only. Seems hard to see
how that could be a great deal heavier. Yeah, there is the fields
collection objects. Well, might as well go with return value if using
a stored proc anyway.

Yep, 8-10 values. See the SQL statements above, and also in
this thread.
http://www.visualbasicforum.com/showthread.php?s=&threadid=22806

Yes, I can agree with that on a "one" record basis... what I was thinking, was more on the 232,000 record level as discussed earlier.

For example, if it takes 30 minutes to run 232,000 recs, for scenario purposes we could say that is .0075 of a second.

Now, if you can cut the time by 20% by using a stored procedure, down to .0054 of a second, then you reach a 21 minute processing time.

One step closer to shaving down that 30 minutes.

diver

well i was going to respond, but i think i can just read along now and get better help. you know, without me asking stupid quetions.

thanks again you two, i'm learning tons and getting a really good feel for better database work. first thing tomorrow i'm hoping to look at creating a stored procedure. and yes, it's a SELECT * FROM RS WHERE...AND...AND....etc. it's what i have to do to really compare the flat file record with the table.

thanks!

Okay... I typed this up but did not run it... it looks pretty close, you should get the idea... I'll post the stored procedure later...
Apologize for the imperfect posting I can't get rid of the text wraps in this thing...


Dim oCmd As ADODB.Command

Sub BigCount()
Dim rs As ADODB.Recordset
Dim sField As String
Dim iMax As Integer
Dim x As Integer

Set oCmd.ActiveConnection = YOUR DATABASE
Set oCmd.CommandType = adCmdStoredProc
Set oCmd.CommandText = "spCountRecords"
'*** Load up the command object...calling procedure below...
ConfigParam
Set rs = New ADODB.Recordset
rs.Open..... open criteria ???
'*** You can start a loop here, if you have criteria in recordset
Do While Not rs.EOF
'*** Load in the new data to the command object parameters
With oCmd
iMax = .Parameters.Count - 1
'*** If your data (criteria) is in a recordset, get it from there
For x = 0 To iMax
'*** If it is not in a recordset, do it some other way
sField = .Parameters(x).Name
.Parameters(x).Value = rs.Collect(sField)
Next
'*** Fire off the command object (stored procedure)
.Execute
End With
rs.MoveNext
Loop
End Sub

Sub ConfigParam()
Set oCmd = New ADODB.Command
'*** Load up the command object (do this once)
With oCmd
'*** Note that the "output" for the record count is the first one.
.Parameters.Append .CreateParameter("Output", adInteger, adParamOutput)
'*** All others are adVarChar... 10 character long... you can adjust...
.Parameters.Append .CreateParameter("sStore", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sGName", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sPack", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sSize", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sRTL", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sUNP", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sOEN", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sConUPC", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sRegrtl", adVarChar, adParamInput, 10)
.Parameters.Append .CreateParameter("sRTL", adVarChar, adParamInput, 10)
End With
End Sub


diver

Now I've probably got some of the fields mispelled and such... you will have to proofread, but you should get the idea... yes, I see now I have some typos. Remember that the order you load the command object parameters MUST be the same order as they are listed in the stored procedure. I do all of mine in alphabetical order... so you might want to rearrange these.

This is what the command object is going to fire when you use the .execute method. Those variables at the top with the @ sign in front of them are the input parameters that we loaded up in the previous VB code.


Alter Procedure spCountRecords
(
@outparm int OUTPUT,
@sGName varchar(10)
@sPack varchar(10)
@Ssize varchar(10)
@sRTL varchar(10)
@sUNP varchar(10)
@sOEN varchar(10)
@sConUPC varchar(10)
@sRegrtl varchar(10)
@sRTL varchar(10)
)
AS
SELECT @outparm = COUNT(*)

FROM [YOUR TABLE]

WHERE [sGName] = @sGName
AND [sPack] = @sPack

etc....


Hope it works!
diver

diver, I have a question about the way you did the parameters.
Have you ever just used the command.parameter.refresh to have
ADO automatically load the parameter collection from the stored
proc? I know it doesn't work with Access, but it works well with
SQL Server.




(Had to fix a minor problem in your stored proc code caused by a
bad word filter.)

Yeah... I was thinking that he had some big text file database... and it would end up in a recordset somewhere...

I personally have never gotten the refresh method to work the way I like, and because you might have different datatypes as well... if his data is coming from some big recordset / text file, who knows... either way, it's a few lines of extra code, but you know it won't goof up on you.

diver

In my experience with SQL Server 7, Refresh loads the Command
Parameters collection straight from the Parameters defined in the
stored proc. Datatypes, output parameters, etc. all seem to
match up perfectly. Just to be clear we are talking about the
same thing, I am referring to the Refresh method of the
Parameters Collection of the Command Object.

Sub ConfigParam()
Set oCmd = New ADODB.Command
'*** Load up the command object (do this once)
With oCmd
Set .ActiveConnection = oConn
.CommandType = adCmdStoredProc
.CommandText = "spCountRecords"
.Parameters.Refresh
End With
End Sub

What problems have you had with this method?

.....wow. that's great, i really have to thank you both for all your help and diver for that code. hopefully i can get out of my morning meetings early enough to get to the code and see if i can get that to work by lunch.

you both have been great. thanks again!!!

Grimes...

Great... let us know what happened, looking forward to it.


Thinker...

Basically, the problem with the parameter "refresh", is speed.
It is faster to create parameters by-hand rather than using Refresh since that requires an extra trip to the database to figure out your Stored Procedure's parameters. I've just done it this way to save time.

I'm a speed fanatic, what can I say?
diver

Originally posted by diver


Alter Procedure spCountRecords
(
@outparm int OUTPUT,
@sGName varchar(10)
@sPack varchar(10)
@Ssize varchar(10)
@sRTL varchar(10)
@sUNP varchar(10)
@sOEN varchar(10)
@sConUPC varchar(10)
@sRegrtl varchar(10)
@sRTL varchar(10)
)
AS
SELECT @outparm = COUNT(*)

FROM [YOUR TABLE]

WHERE [sGName] = @sGName
AND [sPack] = @sPack

etc....



ok, i made time to start this early. couple questions....
-at the top...does "@sRTL varchar(10)" refer to the table field?
-i looked at a few of the system stored procedures, and i didn't see them named the way you have this one. for example "CREATE PROCEDURE dbo.dt_displayoaerror....as" is what i see. will your code work as is, or do i need to change it to reflect this?
-where is it that the procedure gets my variables that i'm passing it for the SQL statement? it may jsut be early, but i don't see anything like that.

i think that's it for now. again, many thanks for all you help.

diver:

Thinker...

Basically, the problem with the parameter "refresh", is speed.
It is faster to create parameters by-hand rather than using Refresh since that requires an extra trip to the database to figure out your Stored Procedure's parameters. I've just done it this way to save time.

Since he will be doing many, many queries, the few milliseconds
for one parameter.refresh won't be noticable. If it were only one
or a few queries using this command object I can see it. In this
case, the advantage of using the Refresh is it won't require
changing the VB program if a new search parameter needs to be
added or an existing one removed from the stored proc.

grimes:
This method of adding parameters to the command parameters
collection means it associates by order. The @sGName variable
in the stored proc automatically associates with the sGName
parameter in the parameters collection just because they are both
first in order (of the input parameters, output parameters are
ordered separately.)

Saying oCmd.Parameters("sGName").Value = sGName means
when the stored proc is executed, the value stored in the variable
sGName gets passed to the @sGName parameter in the stored
proc. diver's example code loads the values of the parameters
by using the name property of a field object in the fields collection
of the recordset object as an index into the parameters collection
of the command object (say that 3 times fast.) This might or might
not work depending on how you get the values you are searching
for. I expect you are getting them from opening the textfile as a
flat file and using some input statment(s) to retrieve the values
for each record.

We are throwing a bunch of stuff at you and aren't explaining it in
great detail. If you are picking any of it up, you are doing great.

Originally posted by Thinker
diver's example code loads the values of the parameters
by using the name property of a field object in the fields collection
of the recordset object as an index into the parameters collection
of the command object (say that 3 times fast.)
ok, even reading that three times made my head hurt.

i see now how it all loads. it's just that the example he gave me looks fine, however comparing the code that actually goes in the databse to other sotred procedures...well they don't look alike. and that's not big deal if they don't need to, but i'm thinking that the first line needs to be the "create procedure", or whatever is says.

well i'm still plugging away at this, and i'm actually surprised that i'm getting about 85% of it. it's just the little things (basically in my previous post) that i still have questions on.

oh and you're right, i am reading each line as an input and getting certain values that way.

still plugging and chugging....

Ok, the first time in the query analyzer, you use Create Procedure
but every time you make a change to it after that, you use Alter
Procedure. Alter Procedure just replaces the existing one with the
one you are submitting again.

No they don't look alike... there were some typos in the field names and I assigned a standard (10) to the length of the field... you need to check that.

Use the .refresh method if you want, you can always change it later if you need to....

By having your 223,000 rows of "text" data out there somewhere, I was assuming here, that you would be normalizing that data into a recordset, or at least part of it.. etc... It may be easier to do the iteration that way... you never mentioned (I think) how you would be iterating with the 223,000 rows of text data... what methods are you using for that?


diver

basically i have to treat the file as a table. i read each row, grab about 10 values and use those values to look for the same record in my table.

i've been busy with an "emergency" thing that i have to write, so i haven't had a change to really try out the procedure. but the more i read about it, the more i think that your code is good as it is.

thanks!