Link to home
Start Free TrialLog in
Avatar of Wayne Barron
Wayne BarronFlag for United States of America

asked on

asp classic - speed up Query in LOOP

Hello All;
(ASP Classic, SQL Server 2005)

OK, I am using the following code in a LOOP.

<%
UserID = LoggedID
Set MyChoosen = Server.CreateObject("ADODB.Command")
MyChoosen.ActiveConnection=askConn
MyChoosen.Prepared = true
MyChoosen.commandtext="SELECT Cats.CatID, Cats.CatName, Cats.SCID, COUNT(Media.CatID) AS ctThreads FROM Cats LEFT OUTER JOIN Media ON Cats.CatID = Media.CatID FULL OUTER JOIN SubCategories ON Cats.SCID = SubCategories.SCID GROUP BY Cats.CatID, Cats.SCID, SubCategories.SubName, SubCategories.MCID, Cats.CatName HAVING SubCategories.MCID=? and Cats.SCID=? "
MyChoosen.Parameters.Append MyChoosen.CreateParameter("@MCID", adInteger, adParamInput, , chMCID)
MyChoosen.Parameters.Append MyChoosen.CreateParameter("@SCID", adInteger, adParamInput, , MapSCID)
set rsMyCh1 = MyChoosen.execute

if not rsMyCh1.eof then
    arrMyCh1 = rsMyCh1.getrows()
end if
rsMyCh1.close
set rsMyCh1 = nothing

if isArray(arrMyCh1) then

        For rm = LBound(arrMyCh1, 2) To UBound(arrMyCh1, 2)
CatID 		        = arrMyCh1(0, rm)
CatName 		    = arrMyCh1(1, rm)
MapSCID 		    = arrMyCh1(2, rm)
ctThreads 		    = arrMyCh1(3, rm)

if strusername<>"" then
Set sqlgetCat = Server.CreateObject("ADODB.Command")
sqlgetCat.ActiveConnection=Conn
sqlgetCat.Prepared = true
sqlgetCat.commandtext="SELECT top 1 CatID, UserID from Lists with (index(Index_CatID_UserID)) where UserID=1 and CatID=?"
sqlgetCat.Parameters.Append sqlgetCat.CreateParameter("@UserID", adInteger, adParamInput, , UserID)
sqlgetCat.Parameters.Append sqlgetCat.CreateParameter("@CatID", adInteger, adParamInput, , CatID)
set rsSelCat = sqlgetCat.execute
if rsSelCat.eof then
%>
The record does not exist for this user in this Category
<%else%>
Add this record to your Category
<%end if%>

<%else%>
Load the records for the Non-Members
<%end if
end if
next%>

Open in new window


Now, this loads really slow, while looking through all the records of the rsSelCat  to see if the CatID and UserID exist per Category.
Is there another way to check this?

Checking through both my Laptop and the LIVE site.
It takes about 22 seconds to load this page.

Now, since the Non-Members, do not have any saved Categories, it by-passes this Query, and just shows them the Category Names with links to them. and that loads very fast, about 1.5 seconds.
Showing over 100+ records to the page.

How can I speed up this Logged User check to see if the record exist or not?
I am using the TOP 1, so that it will stop after finding the record for each category, so this speed things up a bit.
I created the index for the Table, and not really sure if that helped out of now.

Any help will be great.
Carrzkiss
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

there are several aspects that could be used to fasten.
first query: is that one slow or not? does that take much time?
second query: is that one slow or not?

for those one: a explain plan would help to show if there are missing indexes.
in short: if the first query is slow, no need to look at the second one already.


>sqlgetCat.commandtext="SELECT top 1 CatID, UserID from Lists with (index(Index_CatID_UserID)) where UserID=1 and CatID=?"

you specify the index in the query, which is bad practice. remove that part, the index being on the table shall do it.

next, you may consider moving that logic into the main sql, by a LEFT JOIN or subquery and return the "text/html" based on that field value, so you don't need to run a second sql inside the loop


now, looking at the first query, I wonder about the FULL OUTER JOIN, are you 100% sure that one is needed?

SELECT Cats.CatID, Cats.CatName, Cats.SCID
, COUNT(Media.CatID) AS ctThreads
FROM Cats
LEFT OUTER JOIN Media ON Cats.CatID = Media.CatID
FULL OUTER JOIN SubCategories ON Cats.SCID = SubCategories.SCID
GROUP BY Cats.CatID, Cats.SCID, SubCategories.SubName, SubCategories.MCID, Cats.CatName
HAVING SubCategories.MCID=? and Cats.SCID=?

please also "move" the HAVING into a "WHERE" clause, and change the SQL like this:

SELECT c.CatID, c.CatName, c.SCID
, (select COUNT(Media.CatID) FROM Media m WHERE m.CatID = c.CatID ) AS ctThreads
, (select count(*) FROM  Lists l where l.UserID=1 and l.CatID= .CatID ) as ctListUser1
FROM Cats c
LEFT JOIN SubCategories sc ON c.SCID = sc.SCID
WHERE sc.MCID=? and c.SCID=?

you will need a index on the join conditions, and instead of the loop use the result of ctListUser1 field


tip: move all the sql into a stored procedure which you can test and optimize server side, and hide all that complexity from the application code



hope this helps
You might not need the loop at all.
Why are you using 2 queries? I mean, what is the purpose of the 1st and 2nd queries?
Avatar of Wayne Barron

ASKER

@Angellll.
I am not certain to why the FULL OUTER JOIN was there. This particular statement was done a few years ago, I am just in the process of making things faster and better than they were before.

OK.
You stated: >> instead of the loop use the result of ctListUser1 field

The 1st Query loads the records to the page.
These records are all of the Categories, Sub-cats, and so forth that are available on the site for the user to browse.

The 2nd Query checks to see if the Member has subscribed to any of the Cats. If they have, then it shows
If rsSelCat.eof then
Give Form to sub to this Cat
else
[Subbed]
end if

I am not really sure if using the result of ctListUser1 if going to work.
However, I could be wrong. I am getting ready to run a test of the Query you provided.

@Vitor, hopefully what I wrote above, answers your question as well.
Also.
This was not supposed to be added into the code provided in the 2nd Query.
where UserID=1
Supposed to be
where UserID=?

Not really sure if that will change up anything or not, in the code you provided.
userid=1 can of course also be with a parameter...

how to use all of this?
well, starting from your original code:
<%
UserID = LoggedID
Set MyChoosen = Server.CreateObject("ADODB.Command")
MyChoosen.ActiveConnection=askConn
MyChoosen.Prepared = true
MyChoosen.commandtext="SELECT c.CatID, c.CatName, c.SCID
, (select COUNT(Media.CatID) FROM Media m WHERE m.CatID = c.CatID ) AS ctThreads
, (select count(*) FROM  Lists l where l.UserID=? and l.CatID= .CatID ) as ctListUser
FROM Cats c
JOIN SubCategories sc ON c.SCID = sc.SCID
WHERE sc.MCID=? and c.SCID=?  "
MyChoosen.Parameters.Append MyChoosen.CreateParameter("@UserID", adInteger, adParamInput, , UserID )
MyChoosen.Parameters.Append MyChoosen.CreateParameter("@MCID", adInteger, adParamInput, , chMCID)
MyChoosen.Parameters.Append MyChoosen.CreateParameter("@SCID", adInteger, adParamInput, , MapSCID)
set rsMyCh1 = MyChoosen.execute

if not rsMyCh1.eof then
    arrMyCh1 = rsMyCh1.getrows()
end if
rsMyCh1.close
set rsMyCh1 = nothing

if isArray(arrMyCh1) then
     For rm = LBound(arrMyCh1, 2) To UBound(arrMyCh1, 2)
CatID 		        = arrMyCh1(0, rm)
CatName 		    = arrMyCh1(1, rm)
MapSCID 		    = arrMyCh1(2, rm)
ctThreads 		    = arrMyCh1(3, rm)
ctListUser                    = arrMyCh1(4, rm)


if ctListUser  = 0 then
%>
The record does not exist for this user in this Category
<%else%>
Add this record to your Category
<%end if%>

<%else%>
Load the records for the Non-Members
<%end if
end if
next%>
                                  

Open in new window

This part right here
, (select count(*) FROM  Lists l where l.UserID=1 and l.CatID= l.CatID ) as ctListUser1

I am slightly confused as to what it is supposed to do?
When I run the entire Query inside of Management Studio, I get 31 for all values that are shown.
For example.
I am doing the following

WHERE sc.MCID=1 and c.SCID=10

The results is:
24 records rendered
And the ctListUser1 = 31 (Is shown down on all)

So I am not really sure what this ctListUser1 is supposed to doing.
Slightly Confused here.
Angelll, I just now saw your reply.
Just ignore my last comment.
I will test out the code, and get back with you.
OK
This part here, is not working.
 (It is changed up, I goofed when I wrote the example,
 it first checks if record DOES NOT exist, and gives option to ADD to list, else, it DOES EXIST)
However, it is not working.

if ctListUser  = 0 then
%>
The record does not exist for this user in this Category, add it to your list
<%else%>
The record already exist.
<%end if%>
please clarify "it is not working"
angellll's observations are all correct.
I just think the main query should be something like:
SELECT Cats.CatID, Cats.CatName, Cats.SCID, Lists.UserID, COUNT(Media.CatID) AS ctThreads 
FROM Cats 
	LEFT OUTER JOIN Lists ON Cats.CatID = Lists.CatID 
	LEFT OUTER JOIN Media ON Cats.CatID = Media.CatID 
	FULL OUTER JOIN SubCategories ON Cats.SCID = SubCategories.SCID 
WHERE SubCategories.MCID=? AND Cats.SCID=? AND UserID=?
GROUP BY Cats.CatID, Cats.CatName, Cats.SCID, Lists.UserID

Open in new window

@Angellll
The original Query for checking

if rsSelCat.eof then
%>
The record does not exist for this user in this Category
Add to your List
<%else%>
This Category Exist in your list
<%end if%>

It should look something like this, when the page loads for a Member.

one (Subbed)
two
three (Subbed)
four
five
six (Subbed)

In the above example. 1,3,6 are in the Members List already, so they show (subbed)
2,4,5 are not in his list, so he will click on each name, to add to his list.

In the original code, this works great, just super slow.

In your Query, for checking
if ctListUser  = 0 then
else
end if

It does not come back with the intended results, instead it does not show the (Subbed) at all.

@Vitor
I ran your code in Management Studio, it comes back with everything that the member is Subbed too, so that is good.
However, if I run this in the site, it is only going to show that member what they are (subbed) too, and nothing else.
So, it needs to show everything, PLUS what he is Subbed too.
OK, after playing around with it, I finally got it working.

This Query runs everything in ONE.
I first check the values of the records, and then check for all Is Null records. (this will return ONLY Null Records).
And then we check for the Member ID in the OR Query.

"SELECT Cats.CatID, Cats.CatName, Cats.SCID, COUNT(Media.CatID) AS ctThreads, 
Lists.UserID FROM Cats WITH (INDEX(Index_Cats))  INNER JOIN 
SubCategories ON Cats.SCID = SubCategories.SCID LEFT OUTER JOIN  
Lists ON Cats.CatID = Lists.CatID LEFT OUTER JOIN Media ON Cats.CatID = Media.CatID 
WHERE (SubCategories.MCID = 1) AND (Cats.SCID = 1) and lists.Userid is null 
OR (SubCategories.MCID = 1) AND (Cats.SCID = 1) AND (Lists.UserID = 1) 
GROUP BY SubCategories.SubName, SubCategories.MCID, Lists.UserID, Cats.SCID, Cats.CatID, Cats.CatName"

Open in new window


Everything seems to be running good.
I am going to do some more testing, and then if all goes right, I will upload this to the LIVE server.

The amount of records returned in this query is: 556 (This number will grow as the site grows)
Non Member - About 1 second
Member - About 3.5 seconds

That is a heck of a lot better than 21 seconds.

I will leave this question open until tomorrow, to give Angellll and Vitor time to respond to the code that I supplied.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Making this
(SubCategories.MCID = 1) AND (Cats.SCID = 1) AND (Lists.UserID = 1)
Into this
(Lists.UserID = 1)

gives the wrong results.
See, I have to check to see if the ID for the SCID and MCID are equal to the Cats that are listed to the page.
If not, then they will ALL show (subbed), as I just tried your theory before posting back.
So, it has to be the way I presented it.

So far, it is working pretty good.
It shows all Cats to the page, and for each one that the member is subbed to.
It shows (subbed) beside the Cat for that member.

As for the Index.
I was trying it out to see if it would help speed things up.
However, it did not, so it has already been removed.
the change you put (just removing stuff) is not what I described as change...
please review the change I suggested

anyhow, in regards to performance, that change may help or not, but is not "relevant" as such, but the explain plan would help
You are totally correct.
I missed on that part bigtime.
I have changed it to:
WHERE SubCategories.MCID = 1 AND Cats.SCID = 1
   and  ( lists.Userid is null  OR Lists.UserID = 1 )

And it works correctly.

As for this: >>> but the explain plan would help
Are you wanting me to explain more about the page concept (or) are you referring to something else.

As for load time.
It loads about 20 seconds faster on my local machine, however, on the LIVE Hosting Shared Server, it loads at the same speed, absolutely no change what-so-ever.
explain plan, see here:
https://technet.microsoft.com/en-us/library/ms178071%28v=sql.105%29.aspx
it's one of the first tools to use to check why a query is "slow"
Hello Guy.
Question for you.
I am back checking and trying to make my queries faster.
I did speed them up a little, by creating INDEX's for both tables.
(1 query, using multiple tables)
However, when I did the Display estimated execution plan on the Query.
It used the INDEX that I created for the Users table, however, it used the Clustered DEFAULT INDEX for the Other table.
You posted here http:Q_28660475.html#a40737630
That using the FORCE INDEX in the Query is bad.
With that stated, what can I do to make the Query use the index that I created for it?
it depends.
are the statistics updated on the indexes?
some indexes are not "as efficient" as others, or at least sql server may "estimate" it that way.
also the estimated execution plan is exactly that: estimated, when running the query it may run differently.

without seeing a lots of details this is not easy at all to find out...
could you attach the actual execution plan file (a .xml file you can save as from the graph) here
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I removed the main SQL Query in the Categories list, as I found it, that it was not needed.
And wa-la.
Speed up the page on the local server, and then I uploaded the file to the LIVE server, and the page loads in about 3 seconds.
Before, it took upwards of 15 - 40 seconds to load.
So, with all the work that I have done, cutting down my Queries, from what I created 2 years ago, to what I have now, has sped up the site, 10 fold.

The next step is to make the Choose Page, load faster.

I think this is about to be wrapped up (thank goodness)
I have learned so much, but am waiting for Guy to get back with me, on what I sent him.
Knowledge is everything! And I am trying to get as much Knowledge as possible in SQL Queries.
I got my Category page to load in 3 seconds on my server.
With the same identical records.
It takes up to 15 seconds on the LIVE server.
So, I am going to say that this is a SQL Server (SHARED) Issue
That is causing it to be so slow on the LIVE Server.

So, I think that I am going to look in on a Microsoft CLOUD SQL Server (Maybe)
As it is going to be at least 8 more months, before I will be able to host from In-House.
As that will be when our ISP will upgrade our internet.
Cannot wait. Found out that it will be
60mb Down
40mb Up
Dang, cannot wait.
Right now, we are at: 7mb down / 1mb up
A "shared server" => dare with me ;)

still you should check if the index stats are updated, which compatibility version your db is, and if OPTION (MAXDOP 1) in the query would help (https://msdn.microsoft.com/en-us/library/ms181714.aspx)
I ran the Execution Plan on the Queries for that page, and they all seem to be picking up on the Indexes, just as they are on my system.
The Database version, is 2005, the same as what I am running here as well.
Is that what you was asking?

also, I look into the MAXDOP, how would I use that in a query?
I found the OPTION(MAXDOP 1) compared in a batch indeed often shows in the estimated explain plan a "less good" result than the sql without that, but in reality I found that for most small queries they still run better (faster), as the overhead to get the query run with parallelism is higher than what it could save in the end.
After finding the issue, and that issue being that it was an undeeded Query that I wrote a few years ago, of which I am still wondering WHY it was there to begin with. I have resolved the issue of the slow loading for members.
However, the issue still resides on the Hosting Providers servers, as it takes about 10-15 seconds for the Zones page to load, and on my development system, it takes about 2 seconds to load.
My Dev System, is
64-bit
8gb memory.
4 core processor
So, I am pretty certain that the hosting provider, does not allow that much on their end, for a shared server.

I am going to look in on SQL Cloud services that are available, and see if I can find one somewhere, that I can use, until I start hosting from in-house.
I know that Microsoft has a trial for like, 30 days, that I can try out, to see how that works, with running their SQL Server, and continue to use my current Hosting provider.
I have to do something, as I have another site, that is going to be going live within the month, that is expected to get a lot of hits within the first week of its release. I will just have to wait and see, and hope that I do not hit my limit.

(Darn ISP needs to get off their backside, and keep their promise of upgrading our system, enough is enough)

Thanks Angellll for staying with me, and assisting me through this issue.
I have learned a lot over the last few days.

Take care
Carrzkiss