Database Discussion - Speed up query

Print Page | Close Window

Speed up query

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=1432
Printed Date: 29 March 2026 at 6:49pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Speed up query

Posted By: Shojaee
Subject: Speed up query
Date Posted: 28 March 2003 at 2:53pm

Hello,

I have 2 tables on SQL Server 2000. I need to read some values from one table and search for those values in the second table. I'm using this method:

- Query for all matching records in table 1.

- Put the results in an array (with GetRows).

- Loop through that array and query for records.

The problem is, for only 25 matched records from table 1, it takes about 20 seconds to search the second table. I believe this is too long. What should I do to speed up this operation?

Thanks in advance.

-------------
Light travels faster than sound. This is why some people appear bright until you hear them speak.

Replies:

Posted By: Bunce
Date Posted: 28 March 2003 at 5:04pm

Try and post your code for us to look at.

Anyway, it would be best to perform the entire query in your initial SQL statement.

To limit the amount of records in one table, depending on your criteria in another, you need to JOIN the tables together. That will save the round trip to the web server and back. http://www.w3schools.com/sql

That said, hats off to ya for using getrows!! Not many people use/understand its advantages.

Cheers,
Andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.

Posted By: Shojaee
Date Posted: 29 March 2003 at 4:59am

This is the first query:

sSQL = "select distinct TAC_ID from TAC_GRP"
rsConfig.CursorType = 3
rsConfig.Open sSQL, objConn
iTotalGroups = rsConfig.RecordCount
rsArray = rsConfig.GetRows
rsConfig.Close

At this point, rsArray contains 25 elements.

And this is the second query:

For iCount = 0 To (iTotalGroups - 1)
sGroup = rsArray(0, iCount)
sSQL = "select * from TAC_USR where TAC_ATTR='[Global]Groups' and TAC_VAL like '%" & sGroup & "%'"
rsConfig.CursorType = 3
rsConfig.Open sSQL, objConn
Response.Write("<tr>" & vbCRLF)
Response.Write("<td nowrap>" & vbCRLF)
Response.Write("<p align=""left"">"& sGroup & vbCRLF)
Response.Write("</td>" & vbCRLF)
Response.Write("<td nowrap>" & vbCRLF)
Response.Write("<p align=""center"">" & rsConfig.RecordCount & vbCRLF)
Response.Write("</td>" & vbCRLF)
Response.Write("</tr>" & vbCRLF)
Response.Write("<tr><td width=50% height=5></td>")
Response.Write("<td width=50% height=5></td></tr>")
rsConfig.Close
Next

Thanks for your attention.

-------------
Light travels faster than sound. This is why some people appear bright until you hear them speak.

Posted By: neehouse
Date Posted: 08 April 2003 at 10:24pm

Could try using the power of SQL and Joining to only have one Query.

Also, the like is killing you... Try using exact matches if you can...