Need some performance help, please!

shizox
01-09-2003, 08:34 AM
hi guys, i have the following code that i would like to tune to make it run better either from SQL point or VB. currently it runs about 7 seconds. we have about 30,000 records in our database and we plan to grow it to 3,000,000 (yup, i need some help).

the recordset rstCandidates (main loop) has only 1,300 records when it takes 7 seconds.

thanks guys.

'code to get recordset with one column of IDs


'go through all of them
While Not rstCandidates.EOF
arPreselectedCandidates = rstCandidates.GetRows(50)
'this will return quoted ids separated by comma ex: 'id01','id02',etc

strPreselectedCandidates = GetPreselectedCandidatesString(arPreselectedCandidates)

Set rstCanTemp = New ADODB.Recordset

strSQL = "SELECT * FROM table1 " & _
"WHERE Contact_ID IN (" & strPreselectedCandidates & "); "

strSQL = strSQL & "SELECT * FROM table2 " & _
"WHERE Contact_ID IN (" & strPreselectedCandidates & "); "

strSQL = strSQL & "SELECT * FROM table3 " & _
"WHERE Contact_ID IN (" & strPreselectedCandidates & "); "


With rstCanTemp
Set .ActiveConnection = con
.Source = strSQL
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With

'i found that running it once is faster then 3 separate queries.

If Not rstCanTemp.EOF Then arr1 = rstCanTemp.GetRows
Set rstCanTemp = rstCanTemp.NextRecordset

If Not rstCanTemp.EOF Then arr2 = rstCanTemp.GetRows
Set rstCanTemp = rstCanTemp.NextRecordset

If Not rstCanTemp.EOF Then arr3 = rstCanTemp.GetRows
rstCanTemp.Close


.
.
.

loop

Stewie
01-09-2003, 08:58 AM
do you really need ALL of the fields in the tables?

shizox
01-09-2003, 08:58 AM
i'm simply concatinating the string 3 times joining 3 separate queries, i don't really know what your asking:-\

shizox
01-09-2003, 08:59 AM
which fields?

Stewie
01-09-2003, 08:59 AM
try this aswell

.CursorLocation = adUseserver

you could also use a stored procedure to do the searching in SQL

shizox
01-09-2003, 09:03 AM
Originally posted by Stewie
try this aswell

.CursorLocation = adUseserver

Server side cursor doesn't support mutirecordsets and its usually slower than client side

Stewie
01-09-2003, 09:07 AM
When i say do you need all the fields in the tables i meant, Do you need to Select all of the fields in the tables?
how many fields are there?

because i have found that in a table with about 10-15 fields, if i only select the 2 i need or whatever. it is a heck of a lot faster than grabbing all fields

shizox
01-09-2003, 09:09 AM
Originally posted by Stewie
When i say do you need all the fields in the tables i meant, Do you need to Select all of the fields in the tables?
how many fields are there?

because i have found that in a table with about 10-15 fields, if i only select the 2 i need or whatever. it is a heck of a lot faster than grabbing all fields

ohh, gotcha.

and the answer is i don't in reality, i changed the queries in this example to make it easier to read for you guys.

thanks

Stewie
01-09-2003, 09:13 AM
maybe your just cursed with a slow network like me!!

but i think you should try making it a stored procedure, because they can make things really fast!

shizox
01-09-2003, 09:19 AM
Originally posted by Stewie
maybe your just cursed with a slow network like me!!

but i think you should try making it a stored procedure, because they can make things really fast!

lol, its not the network issue as this runs on my development box.
and i cant really use stored procedure since the queries contain 'IN ()' clause and optimizer will have to compile the SP everytime it runs, thus no performance gain...

thanks for your thought though

Stewie
01-09-2003, 09:29 AM
im doing well..
*moves aside lets the experts help*

MrFerrari
01-09-2003, 10:50 AM
I assume you are using MS SQL? Which version are you using? Also, I assume you have indecies (thats the plural of index right?) set up as well? How many fields from these tables are you querying, as you said you simplified the queries?

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum