Need faster way to query database mutliple times (within For Loop currently)
I have an application that generates lists of tags. Once the lists are generated the user can generate Excel reports from the lists.
My problem is I want to query the database for all the tags in each list but the only way I know how is to use a For Loop and query the database for each and every tag. This is taking way too long.
My problem is I want to query the database for all the tags in each list but the only way I know how is to use a For Loop and query the database for each and every tag. This is taking way too long.
For m = 0 To lstTags.Items.Count - 1
currTagQry = "SELECT * from T_TEST WHERE TAG = '" & lstTags.Items(m) & "' ORDER BY TAG, LOCATION"
Dim cmd6 As New System.Data.OleDb.OleDbCommand(currTagQry, conn6)
Dim data6 As New System.Data.OleDb.OleDbDataAdapter(cmd6)
ds_CurrTagQry.Reset()
Try
data6.Fill(ds_CurrTagQry, "CURRTAGQRY")
Catch ex As Exception
End Try
For p = 0 To ds_CurrTagQry.Tables("CURRTAGQRY").Rows.Count - 1
shtTags.Cells(lstExcelRow, 1).value = ds_CurrTagQry.Tables("CURRTAGQRY").Rows(p).Item("TAG").ToString()
Next
NextSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to insert code in the loop to get a sense of what is taking a long time ... is querying the db taking a long itme? Or is inserting it into excel taking a long time?
If the db query is taking a long time,
1. Do you have appropriate indexes (on the Tag field)? This will generally speed up the process.
2. How far is the server (physically) from where you are running this code? Is network latency an issue?
3. If both of the above do not solve the problem, then you can run a single query that returns all the data and do the parsing in VBA.
SS
If the db query is taking a long time,
1. Do you have appropriate indexes (on the Tag field)? This will generally speed up the process.
2. How far is the server (physically) from where you are running this code? Is network latency an issue?
3. If both of the above do not solve the problem, then you can run a single query that returns all the data and do the parsing in VBA.
SS
Personally I wouldn't use a data adapter. I have found them slow in the past.
If you are just looping through the results, I would change the SQL to the IN-List proposed above and use a DataReader.
If you are just looping through the results, I would change the SQL to the IN-List proposed above and use a DataReader.
ASKER
thanks. I tried the IN-List method that that seems to work a little faster.
I'll try passing the list to a temp table and see how that method works.
I'll try passing the list to a temp table and see how that method works.
Try doing this from the SQL Server itself
http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/
http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/
ASKER
i'm working with oracle
Why not use the Oracle data provider over the OleDB data provider? The latter being more generic, hence a tad slower.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Correction:
... from MyTable" & _
original contains more complex: from " & TdTable("Td") & _
I try to change TableName to a constant and do´nt remove the final ")"
... from MyTable" & _
original contains more complex: from " & TdTable("Td") & _
I try to change TableName to a constant and do´nt remove the final ")"
SELECT * from T_TEST WHERE TAG in ('abc','def','ghi',...... ) ORDER BY TAG, LOCATION