Wayne Barron
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.
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
(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%>
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
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?
Why are you using 2 queries? I mean, what is the purpose of the 1st and 2nd queries?
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.
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.
ASKER
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.
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:
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%>
ASKER
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.
, (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.
ASKER
Angelll, I just now saw your reply.
Just ignore my last comment.
I will test out the code, and get back with you.
Just ignore my last comment.
I will test out the code, and get back with you.
ASKER
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%>
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:
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
ASKER
@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.
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.
ASKER
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.
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.
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"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
(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
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
ASKER
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.
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"
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"
ASKER
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?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
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)
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)
ASKER
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?
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?
ASKER
I found some information on the MAXDOP
http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/
http://blog.sqlauthority.com/2010/03/15/sql-server-maxdop-settings-to-limit-query-to-run-on-specific-cpu/
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.
ASKER
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
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
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="SE
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